[20111219]查询与删除重新的索引.txt

[20111219]查询与删除重新的索引.txt

工作需要,要查询哪些索引重复索引,我记得以前的toad带的知识库里面的有一个脚本,可以确定,可以找了半天没有发现,

新版的toad 9.X以上的知识库排版查询没有以前方便（自己感觉）.

google找到如下链接：

http://www.orafaq.com/node/926

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