查詢相似的索引

今天看到一個應用，發現索引簡直是亂建，許多表上每個欄位都有索引，寫一個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@]