weitom1982

  查看数据库中有无多余的索引，即一个索引的字段为另一个索引的前几个字段。如index1的定义为test(filed1,filed2),index2的定义为test(filed1,filed2,filed3)，则认为index1是多余的。(摘自Ixora)

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