索引是加速查詢速度的數據庫對象。
但索引并非越多越好。過多的索引會影響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
但索引并非越多越好。過多的索引會影響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
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
