1 --获取指定schema或表上的索引质量信息报告 2 gx_adm@CABO3> @idx_quality 3 Enter value for input_owner: GX_ADM 4 Enter value for input_tbname: CLIENT_TRADE_TBL  -->如果我们省略具体的表名则会输出整个schema的索引质量报告 5  6                                  Table      Table                             Index Data Blks Leaf Blks        Clust Index 7 Table                             Rows     Blocks Index                     Size MB   per Key   per Key       Factor Quality 8 ------------------------- ------------ ---------- ------------------------- ------- --------- --------- ------------ ------------- 9 CLIENT_TRADE_TBL             6,318,035     278488 I_TDCL_ARC_STL_DATE_STOCK      62       312        13      171,017 5-Excellent10                                                   I_TDCL_ARC_STL_DATE_CASH       62       318        13      174,599 5-Excellent11                                                   I_TDCL_ARC_CANCEL_DATE         83       238         8      288,678 5-Excellent12                                                   I_TDCL_ARC_INPUT_DATE         144       249        13      310,974 5-Excellent13                                                   I_TDCL_ARC_TRADE_DATE         144       269        14      337,097 5-Excellent14                                                   PK_CLIENT_TRADE_TBL           200         1         1      798,216 2-Good15                                                   I_TDCL_ARC_GRP_REF_ID         144         1         1      811,468 2-Good16                                                   UNI_TDCL_ARC_REF_ID           136         1         1      765,603 2-Good17                                                   I_TDCL_ARC_CONTRACT_NUM        72         1         1      834,491 2-Good18                                                   I_TDCL_ARC_SETTLED_DATE        61       299         5      380,699 1-Poor19                                                   I_TDCL_ARC_ACC_NUM            184       624         3    3,899,446 1-Poor20                                                   I_TDCL_ARC_PL_STK             176       218         1    4,348,804 1-Poor21                                                   I_TDCL_ARC_INSTRU_ID          120     2,667         8    4,273,038 1-Poor22 23 --从上面的单表输出的索引质量可知，出现了4个处于Poor级别的索引，也就是说这些个索引具有较大的聚簇因子，几乎接近于表上的行了24 --对于这几个索引的质量还应结合该索引的使用频率来考量该索引存在的必要性25 --对于聚簇因子，只能通过重新组织表上的数据来，以及调整相应索引列的顺序得以改善26              27 --查询单表上索引列的相关信息             28 gx_adm@CABO3> @idx_info29 Enter value for owner: GX_ADM30 Enter value for table_name: CLIENT_TRADE_TBL31 32 TABLE_NAME                INDEX_NAME                     CL_NAM               CL_POS STATUS   IDX_TYP         DSCD33 ------------------------- ------------------------------ -------------------- ------ -------- --------------- ----34 CLIENT_TRADE_TBL          I_TDCL_ARC_ACC_NUM           ACC_NUM                   1 VALID    NORMAL          ASC35                           I_TDCL_ARC_CANCEL_DATE       CANCEL_DATE               1 VALID    NORMAL          ASC36                           I_TDCL_ARC_CONTRACT_NUM      CONTRACT_NUM              1 VALID    NORMAL          ASC37                           I_TDCL_ARC_GRP_REF_ID        GRP_REF_ID                1 VALID    NORMAL          ASC38                           I_TDCL_ARC_INPUT_DATE        INPUT_DATE                1 VALID    NORMAL          ASC39                           I_TDCL_ARC_INSTRU_ID         INSTRU_ID                 1 VALID    NORMAL          ASC40                           I_TDCL_ARC_PL_STK            STOCK_CD                  1 VALID    NORMAL          ASC41                           I_TDCL_ARC_PL_STK            PL_CD                     2 VALID    NORMAL          ASC42                           I_TDCL_ARC_SETTLED_DATE      SETTLED_DATE              1 VALID    NORMAL          ASC43                           I_TDCL_ARC_STL_DATE_CASH     STL_DATE_CASH             1 VALID    NORMAL          ASC44                           I_TDCL_ARC_STL_DATE_STOCK    STL_DATE_STOCK            1 VALID    NORMAL          ASC45                           I_TDCL_ARC_TRADE_DATE        TRADE_DATE                1 VALID    NORMAL          ASC46                           PK_CLIENT_TRADE_TBL          BUSINESS_DATE             1 VALID    NORMAL          ASC47                           PK_CLIENT_TRADE_TBL          REF_ID                    2 VALID    NORMAL          ASC48                           UNI_TDCL_ARC_REF_ID          REF_ID                    1 VALID    NORMAL          ASC49                         50 --从上面的查询结果可知，当前表TRADE_CLIENT_TBL上含有13个索引，应该来说该表索引存在一定冗余。51 --大多数情况下，单表上6-7个索引是比较理想的。过多的索引导致过大的资源开销，以及降低DML性能。

 1 --script name: idx_quality.sql     --Author : Leshami  --Blog: http://blog.csdn.net/leshami  2 --index quality retrieval 3 SET LINESIZE 145 4 SET PAGESIZE 1000 5 SET VERIFY OFF 6  7 CLEAR COMPUTES 8 CLEAR BREAKS 9 10 BREAK ON table_name ON num_rows ON blocks11 12 COLUMN owner FORMAT a14 HEADING ‘Index owner‘13 COLUMN table_name FORMAT a25 HEADING ‘Table‘14 COLUMN index_name FORMAT a25 HEADING ‘Index‘15 COLUMN num_rows FORMAT 999G999G990 HEADING ‘Table|Rows‘16 COLUMN MB FORMAT 9G990 HEADING ‘Index|Size MB‘17 COLUMN blocks HEADING ‘Table|Blocks‘18 COLUMN num_blocks FORMAT 9G990 HEADING ‘Data|Blocks‘19 COLUMN avg_data_blocks_per_key FORMAT 999G990 HEADING ‘Data Blks|per Key‘20 COLUMN avg_leaf_blocks_per_key FORMAT 999G990 HEADING ‘Leaf Blks|per Key‘21 COLUMN clustering_factor FORMAT 999G999G990 HEADING ‘Clust|Factor‘22 COLUMN Index_Quality FORMAT A13 HEADING ‘Index|Quality‘23 24 --SPOOL index_quality25 26   SELECT i.table_name,27          t.num_rows,28          t.blocks,29          i.index_name,30          o.bytes / 1048576 mb,31          i.avg_data_blocks_per_key,32          i.avg_leaf_blocks_per_key,33          i.clustering_factor,34          CASE35             WHEN NVL (i.clustering_factor, 0) = 0 THEN ‘0-No Stats‘36             WHEN NVL (t.num_rows, 0) = 0 THEN ‘0-No Stats‘37             WHEN (ROUND (i.clustering_factor / t.num_rows * 100)) < 6 THEN ‘5-Excellent‘38             WHEN (ROUND (i.clustering_factor / t.num_rows * 100)) BETWEEN 7 AND 11 THEN ‘4-Very Good‘39             WHEN (ROUND (i.clustering_factor / t.num_rows * 100)) BETWEEN 12 AND 15 THEN ‘2-Good‘40             WHEN (ROUND (i.clustering_factor / t.num_rows * 100)) BETWEEN 16 AND 25 THEN ‘2-Fair‘41             ELSE ‘1-Poor‘42          END43             index_quality44     FROM dba_indexes i, dba_segments o, dba_tables t45    WHERE 46      --    i.index_name LIKE UPPER (‘%&&1%‘) AND47          i.owner = t.owner48          AND i.table_name = t.table_name49          AND i.owner = o.owner50          AND i.index_name = o.segment_name51          AND t.owner = UPPER(‘&input_owner‘)52          AND t.table_name LIKE UPPER(‘%&input_tbname%‘)53 ORDER BY table_name,54          num_rows,55          blocks,56          index_quality DESC;57 58 --SPOOL OFF;59 60 ===========================================================================================61 --script name: idx_info.sql 62 --get the index column information by specified table63 set linesize 18064 col cl_nam format a2065 col table_name format a2566 col cl_pos format 967 col idx_typ format a1568 SELECT b.table_name,69            a.index_name,70            a.column_name     cl_nam,71            a.column_position cl_pos,72            b.status,73            b.index_type      idx_typ,74            a.descend         dscd75 FROM   dba_ind_columns a, dba_indexes b76 WHERE  a.index_name = b.index_name77            AND owner = upper(‘&owner‘)78            AND a.table_name LIKE upper(‘%&table_name%‘)79 ORDER  BY 2, 4;