今天又在一个生产库上干了一个表的一堆索引(这个表原先有37个索引)。后来还是发现有个索引不该干，补了一个回去。不过，怎么说索引多了看不下去，干》不干。
查sql使用情况：
WITH Q AS
(SELECT S.OWNER A_OWNER,
TABLE_NAME A_TABLE_NAME,
INDEX_NAME A_INDEX_NAME,
INDEX_TYPE A_INDEX_TYPE,
SUM(S.bytes) / 1048576 A_MB
FROM DBA_SEGMENTS S, DBA_INDEXES I
WHERE S.OWNER = UPPER('&INPUT_OWNER')
AND I.OWNER = UPPER('&INPUT_OWNER')
AND INDEX_NAME = SEGMENT_NAME
GROUP BY S.OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE)
SELECT A_OWNER OWNER,
A_TABLE_NAME TABLE_NAME,
A_INDEX_NAME INDEX_NAME,
A_INDEX_TYPE INDEX_TYPE,
A_MB MB,
DECODE(OPTIONS, null, ' -', OPTIONS) INDEX_OPERATION,
COUNT(OPERATION) NR_EXEC,
(WITH TMP AS (SELECT TABLE_NAME,
INDEX_NAME,
TO_CHAR(WM_CONCAT(COLUMN_POSITION)
OVER(PARTITION BY INDEX_NAME ORDER BY
COLUMN_POSITION)) COLUMN_POSITION,
TO_CHAR(WM_CONCAT(COLUMN_NAME)
OVER(PARTITION BY INDEX_NAME ORDER BY
COLUMN_POSITION)) COLUMN_NAME
FROM USER_IND_COLUMNS)
SELECT MAX(COLUMN_NAME) AS COLUMN_NAME
FROM TMP
GROUP BY INDEX_NAME
HAVING MAX(TABLE_NAME) = UPPER('&INPUT_TBNAME')
AND A_INDEX_NAME = INDEX_NAME) COLUMN_NAME,
'DROP INDEX ' || A_INDEX_NAME || ';' SQL_DROP
FROM Q, DBA_HIST_SQL_PLAN d
WHERE D.OBJECT_OWNER(+) = q.A_OWNER
AND D.OBJECT_NAME(+) = q.A_INDEX_NAME
AND Q.A_TABLE_NAME = UPPER('&INPUT_TBNAME')
GROUP BY A_OWNER,
A_TABLE_NAME,
A_INDEX_NAME,
A_INDEX_TYPE,
A_MB,
DECODE(OPTIONS, null, ' -', OPTIONS)
ORDER BY NR_EXEC