Check duplicate index


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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: