讨论:When To Index And How Index

zhuq_axy

init.ora中的compatible参数要设成8.1.6以上，用户建立function based index还要有qurey rewrite权限
如果缺省的优化模式是RBO，还可以使用/*+ first rows */ 或者 /*+ all rows */来强制使用函数索引。

null值是不能使用索引的，如果字段中有大量的NULL值，想要使用索引范围扫描，最好还是把NULL替换成一个其他的字符什么的。

有时候检索大表中大量的数据，（例如返回80%的数据）索引就起不了什么作用了，这时候如果机器硬件允许，可以做并行查询，用双提示/*+ FULL(tablename) PARALLEL(tablename,数值) */
由于硬件环境所限制，并行查询我用的也不多。在此抛砖引玉，望高手指点。

richto

When to drop an index, if you are using Oracle9i, you can turn on the Index Monitor for a few days, if the index has never been used, then it should be dropped.
---- But, believe it or not ---
a dropped index may affect other SQL statements’ execution plan although the original plan was not using the index. So, the overall SQL performance may be changed out of your expectation.  
I used DB Expert to test one of our systems in these few weeks, I found that some SQL statements was not using a specific index originally, but once a specific index was dropped, the execution plan may changed.  It is a very interest founding I have never imagined before!  I am using Oracle 9i and keep multiple SQL in Plan Version Tracker; I drop an index on in Pre-script in Plan Version Tracker and take a new Snapshot, then I recreate the Index on Post-script.  I found some SQL’s execution plan were change although the original plans were not using that index.   Please share with us if you find the same Oracle problem!