Find Redundant Indexes:Redundant Indexes are indexes that already exist 'inside' other indexes (the combination, order of fields, and start from the first field).
These indexes are usually unnecessary.
The Script:SELECT o1.NAME||'.' || n1.NAME redundant_index,
o2.NAME || '.' || n2.NAME sufficient_index
FROM sys.icol$ ic1, sys.icol$ ic2, sys.ind$ i1, sys.obj$ n1, sys.obj$ n2,
sys.user$ o1, sys.user$ o2
WHERE ic1.pos# = 1
AND ic2.bo# = ic1.bo#
AND ic2.obj# != ic1.obj#
AND ic2.pos# = 1
AND ic2.intcol# = ic1.intcol#
AND i1.obj# = ic1.obj#
AND bitand(i1.property, 1) = 0
AND (SELECT MAX(pos#) * (MAX(pos#) + 1) / 2
FROM sys.icol$
WHERE obj# = ic1.obj#) =
(SELECT SUM(xc1.pos#)
FROM sys.icol$ xc1, sys.icol$ xc2
WHERE xc1.obj# = ic1.obj#
AND xc2.obj# = ic2.obj#
AND xc1.pos# = xc2.pos#
AND xc1.intcol# = xc2.intcol#)
AND n1.obj# = ic1.obj#
AND n2.obj# = ic2.obj#
AND o1.user# = n1.owner#
AND o2.user# = n2.owner#
AND o1.NAME NOT IN ('SYS', 'OUTLN', 'SYSTEM', 'WMSYS', 'ORDSYS', 'MDSYS', 'AURORA$JIS$UTILITY$')
