Oracle索引问题诊断与优化

      为了诊断数据库的索引设计，可利用下面的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')