优化索引

索引是加速查询速度的数据库对象。
但索引并非越多越好。过多的索引会影响DML语句的性能。
sqlplus scott/tiger
CREATE TABLE EMP (
emp_id NUMBER(8) NOT NULL,
last_name VARCHAR2(20) NOT NULL,
first_name VARCHAR2(20) NOT NULL );

CREATE INDEX 　EMP_LNON EMP ( last_name );

CREATE INDEX 　EMP_LN_FN ON EMP ( last_name, first_name );

当WHERE子句中有："LAST_NAME = arameter" or "LAST_NAME LIKE arameter"时。
索引EMP_LN会被用到。注意：EMP_LN_FN也会被用到。而且当WHERE子句后有FIRST_NAME = arameter也会使用第二个索引。
也就是说当你的库中有第二个索引的时候就没有必要建立第一个索引了。应该删除第一个索引，删除第一个索引不但不影响查询的性能还能加速DML语句。因此，这个删除第一个索引的过程就是优化。
优化的规则如下：
If 一个表中有两个索引 ( I1 and I2 )　and
I1索引所涉及的列数＜＝I2索引所涉及的列数 and
I1索引中列的顺序与I2索引中列的顺序相同
Then
IfI1索引是 UNIQUE then
IfI2索引是一个外键的参考列 then
Do Nothing
Else
删除I2
End If
Else
删除I1
End If
End If

用下面的语句查询出SCOTT模式下面重复的索引：
conn sys/sys as sysdba
SELECT
/*+ RULE */
tab_owner.name owner, t.name table_name,
o1.name || '(' || DECODE(bitand(i1.property, 1), 0, 'N', 1, 'U', '*') || ')' included_index_name ,
o2.name || '(' || DECODE(bitand(i2.property, 1), 0, 'N', 1, 'U', '*') || ')' including_index_name
FROMsys.USER$ tab_owner, sys.OBJ$ t, sys.IND$ i1, sys.OBJ$ o1, sys.IND$ i2, sys.OBJ$ o2
WHERE i1.bo# = i2.bo# AND i1.obj# <> i2.obj# AND i2.cols >= i1.cols AND i1.cols > 0 AND
i1.cols = ( SELECT /*+ ORDERED */ COUNT(1) FROM sys.ICOL$ cc1, sys.icol$ cc2
WHERE cc2.obj# = i2.obj# AND cc1.obj# = i1.obj# AND
cc2.pos# = cc1.pos# AND cc2.COL# = cc1.COL#) AND
i1.obj# = o1.obj# AND i2.obj# = o2.obj# AND t.obj# = i1.bo# AND
t.owner# = tab_owner.USER# AND tab_owner.name LIKE 'SCOTT'
ORDER BY 1, 2
OWNER TABLE_NAME
------------------------------ ------------------------------
INCLUDED_INDEX_NAME INCLUDING_INDEX_NAME
--------------------------------- ---------------------------------
SCOTT EMP
EMP_LN EMP_LN_FN

优化索引