为了诊断数据库的索引设计，可利用下面的SQL语句来查看Oracle数据库的index信息。
SELECT A.OWNER,A.TABLE_OWNER,A.TABLE_NAME,A.INDEX_NAME,A.INDEX_TYPE,
B.COLUMN_POSITION, B.COLUMN_NAME, C.TABLESPACE_NAME,
A.TABLESPACE_NAME,A.UNIQUENESS
FROM DBA_INDEXES A ,DBA_IND_COLUMNS B,DBA_TABLES C
WHERE A.OWNER = UPPER('hr')
AND A.OWNER = B.INDEX_OWNER
AND A.OWNER=C.OWNER
AND A.TABLE_NAME LIKE UPPER ('DEPARTMENTS')
AND A.TABLE_NAME = B.TABLE_NAME
AND A.TABLE_NAME = C.TABLE_NAME
AND A.INDEX_NAME = B.INDEX_NAME
ORDER BY A.OWNER , A.TABLE_OWNER ,A.TABLE_NAME ,A.INDEX_NAME ,B.COLUMN_POSITION
还可以使用下面的SQL语句直接查出某个库中没有建立index的表，分析是否有必要补充建立索引。
SELECT OWNER,TABLE_NAME FROM ALL_TABLES WHERE OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')
AND OWNER =UPPER('scott')
MINUS
SELECT OWNER ,TABLE_NAME FROM ALL_INDEXES WHERE OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')
一个表可以有几百个索引，但是对于频繁插入和更新表，索引越多系统的CPU，I/O负担就越重；建议每张表不超过5个索引。可用以下SQL语句查询出建立了过量index的表。
SELECT OWNER ,TABLE_NAME,COUNT(*) "count" FROM ALL_INDEXES
WHERE OWNER NOT IN('SYS','SYSTEM','OUTLN','DBSNMP')
AND OWNER = UPPER ('hr')
GROUP BY OWNER ,TABLE_NAME
HAVING COUNT(*) > ('4')
