[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

ITPub博客

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

最新文章