查询相似的索引

今天看到一个应用，发现索引简直是乱建，许多表上每个字段都有索引，写一个sql，查询相似的sql：

查询相似的索引：

SELECT TABLE_OWNER, TABLE_NAME, INDEX_NAME, COLUMN_NAME
FROM ALL_IND_COLUMNS
WHERE TABLE_OWNER = UPPER (':OWNER')
AND (TABLE_OWNER, TABLE_NAME, COLUMN_NAME) IN
(SELECT TABLE_OWNER, TABLE_NAME, COLUMN_NAME
FROM (SELECT TABLE_OWNER, TABLE_NAME, COLUMN_NAME,
COUNT (*) TCOUNT
FROM ALL_IND_COLUMNS
WHERE COLUMN_POSITION = 1
AND TABLE_OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')
HAVING COUNT (*) > 1
GROUP BY TABLE_OWNER, TABLE_NAME, COLUMN_NAME))
ORDER BY TABLE_OWNER, TABLE_NAME, COLUMN_NAME, INDEX_NAME

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-999044/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-999044/