Rule for good index :
If 2 indexes ( I1 and I2 ) exist for a table and
the number of columns in Index I1 is less or equal to the number of column
in index I2 and
index I1 has the same columns in the same order as leading columns of index
I2
Then
If index I1 is UNIQUE then
If index I2 is used to support Foregh Key or for Index Overload then
Do Nothing
Else
Index I2 can be DROPPED
End If
Else
Index I1 can be DROPPED
End If
End If
To check duplicate index, use this query :
1. from orafaq
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
FROM SYS.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 '%'
ORDER BY 1, 2
2. from dba-oracle
SELECT /*+ RULE */
A.TABLE_OWNER, A.TABLE_NAME, A.INDEX_OWNER, A.INDEX_NAME,
COLUMN_NAME_LIST, COLUMN_NAME_LIST_DUP, DUP DUPLICATE_INDEXES,
I.UNIQUENESS, I.PARTITIONED, I.LEAF_BLOCKS, I.DISTINCT_KEYS,
I.NUM_ROWS, I.CLUSTERING_FACTOR
FROM (SELECT TABLE_OWNER, TABLE_NAME, INDEX_OWNER, INDEX_NAME,
COLUMN_NAME_LIST_DUP, DUP,
MAX (DUP) OVER (PARTITION BY TABLE_OWNER, TABLE_NAME, INDEX_NAME)
DUP_MX
FROM (SELECT TABLE_OWNER, TABLE_NAME, INDEX_OWNER, INDEX_NAME,
SUBSTR
(SYS_CONNECT_BY_PATH (COLUMN_NAME, ','),
2
) COLUMN_NAME_LIST_DUP,
DUP
FROM (SELECT INDEX_OWNER, INDEX_NAME, TABLE_OWNER,
TABLE_NAME, COLUMN_NAME,
COUNT (1) OVER (PARTITION BY INDEX_OWNER, INDEX_NAME)
CNT,
ROW_NUMBER () OVER (PARTITION BY INDEX_OWNER, INDEX_NAME ORDER BY COLUMN_POSITION)
AS SEQ,
COUNT (1) OVER (PARTITION BY TABLE_OWNER, TABLE_NAME, COLUMN_NAME, COLUMN_POSITION)
AS DUP
FROM SYS.DBA_IND_COLUMNS
WHERE INDEX_OWNER NOT IN
('SYS', 'SYSTEM', 'DLOBAUGH'))
WHERE DUP != 1
START WITH SEQ = 1
CONNECT BY PRIOR SEQ + 1 = SEQ
AND PRIOR INDEX_OWNER = INDEX_OWNER
AND PRIOR INDEX_NAME = INDEX_NAME)) A,
(SELECT TABLE_OWNER, TABLE_NAME, INDEX_OWNER, INDEX_NAME,
SUBSTR
(SYS_CONNECT_BY_PATH (COLUMN_NAME, ','),
2
) COLUMN_NAME_LIST
FROM (SELECT INDEX_OWNER, INDEX_NAME, TABLE_OWNER, TABLE_NAME,
COLUMN_NAME,
COUNT (1) OVER (PARTITION BY INDEX_OWNER, INDEX_NAME)
CNT,
ROW_NUMBER () OVER (PARTITION BY INDEX_OWNER, INDEX_NAME ORDER BY COLUMN_POSITION)
AS SEQ
FROM SYS.DBA_IND_COLUMNS
WHERE INDEX_OWNER NOT IN ('SYS', 'SYSTEM'))
WHERE SEQ = CNT
START WITH SEQ = 1
CONNECT BY PRIOR SEQ + 1 = SEQ
AND PRIOR INDEX_OWNER = INDEX_OWNER
AND PRIOR INDEX_NAME = INDEX_NAME) B,
DBA_INDEXES I
WHERE A.DUP = A.DUP_MX
AND A.INDEX_OWNER = B.INDEX_OWNER
AND A.INDEX_NAME = B.INDEX_NAME
AND A.INDEX_OWNER = I.OWNER
AND A.INDEX_NAME = I.INDEX_NAME
ORDER BY A.TABLE_OWNER, A.TABLE_NAME, COLUMN_NAME_LIST_DUP;
Filed under: Oracle | Tagged: index, Oracle | Leave a comment »
pandazen
許 祿 煒 的 熊 猫 Blog - PandaZen blog 