column redundant_index format a39

column sufficient_index format a39

select /*+ ordered */

o1.name||'.'||n1.name redundant_index,

o2.name||'.'||n2.name sufficient_index

from

(

select

obj#,

bo#,

count(*) cols,

max(decode(pos#, 1, intcol#)) leadcol#

from

sys.icol$

group by

obj#,

bo#

) ic1,

sys.icol$ ic2,

sys.ind$ i1,

sys.obj$ n1,

sys.obj$ n2,

sys.user$ o1,

sys.user$ o2

where

ic2.obj# != ic1.obj# and

ic2.bo# = ic1.bo# and

ic2.pos# = 1 and

ic2.intcol# = ic1.leadcol# and

i1.obj# = ic1.obj# and

bitand(i1.PRoperty, 1) = 0 and

ic1.cols * (ic1.cols + 1) / 2 =

( 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#

/