Oracle索引质量介绍和分析脚本分享

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

--script name: idx_quality.sql   --Author : Leshami --Blog: http://blog.csdn.net/leshami --index quality retrievalSET LINESIZE 145SET PAGESIZE 1000SET VERIFY OFFCLEAR COMPUTESCLEAR BREAKSBREAK ON table_name ON num_rows ON blocksCOLUMN owner FORMAT a14 HEADING 'Index owner'COLUMN table_name FORMAT a25 HEADING 'Table'COLUMN index_name FORMAT a25 HEADING 'Index'COLUMN num_rows FORMAT 999G999G990 HEADING 'Table|Rows'COLUMN MB FORMAT 9G990 HEADING 'Index|Size MB'COLUMN blocks HEADING 'Table|Blocks'COLUMN num_blocks FORMAT 9G990 HEADING 'Data|Blocks'COLUMN avg_data_blocks_per_key FORMAT 999G990 HEADING 'Data Blks|per Key'COLUMN avg_leaf_blocks_per_key FORMAT 999G990 HEADING 'Leaf Blks|per Key'COLUMN clustering_factor FORMAT 999G999G990 HEADING 'Clust|Factor'COLUMN Index_Quality FORMAT A13 HEADING 'Index|Quality'--SPOOL index_quality SELECT i.table_name,     t.num_rows,     t.blocks,     i.index_name,     o.bytes / 1048576 mb,     i.avg_data_blocks_per_key,     i.avg_leaf_blocks_per_key,     i.clustering_factor,     CASE      WHEN NVL (i.clustering_factor, 0) = 0 THEN '0-No Stats'      WHEN NVL (t.num_rows, 0) = 0 THEN '0-No Stats'      WHEN (ROUND (i.clustering_factor / t.num_rows * 100)) < 6 THEN '5-Excellent'      WHEN (ROUND (i.clustering_factor / t.num_rows * 100)) BETWEEN 7 AND 11 THEN '4-Very Good'      WHEN (ROUND (i.clustering_factor / t.num_rows * 100)) BETWEEN 12 AND 15 THEN '2-Good'      WHEN (ROUND (i.clustering_factor / t.num_rows * 100)) BETWEEN 16 AND 25 THEN '2-Fair'      ELSE '1-Poor'     END      index_quality  FROM dba_indexes i, dba_segments o, dba_tables t  WHERE    --  i.index_name LIKE UPPER ('%&&1%') AND     i.owner = t.owner     AND i.table_name = t.table_name     AND i.owner = o.owner     AND i.index_name = o.segment_name     AND t.owner = UPPER('&input_owner')     AND t.table_name LIKE UPPER('%&input_tbname%')ORDER BY table_name,     num_rows,     blocks,     index_quality DESC;--SPOOL OFF;===========================================================================================--script name: idx_info.sql --get the index column information by specified tableset linesize 180col cl_nam format a20col table_name format a25col cl_pos format 9col idx_typ format a15SELECT b.table_name,      a.index_name,      a.column_name   cl_nam,      a.column_position cl_pos,      b.status,      b.index_type   idx_typ,      a.descend     dscdFROM  dba_ind_columns a, dba_indexes bWHERE a.index_name = b.index_name      AND owner = upper('&owner')      AND a.table_name LIKE upper('%&table_name%')ORDER BY 2, 4;