今天看到一个应用，发现索引简直是乱建，许多表上每个字段都有索引，写一个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博客

查询相似的索引

最新文章