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

--script name: idx_quality.sql   --Author : Leshami --Blog: http://blog.csdn.net/leshami 
--index quality retrieval
SET LINESIZE 145
SET PAGESIZE 1000
SET VERIFY OFF

CLEAR COMPUTES
CLEAR BREAKS

BREAK ON table_name ON num_rows ON blocks

COLUMN 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 table
set linesize 180
col cl_nam format a20
col table_name format a25
col cl_pos format 9
col idx_typ format a15
SELECT 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     dscd
FROM  dba_ind_columns a, dba_indexes b
WHERE a.index_name = b.index_name
      AND owner = upper('&owner')
      AND a.table_name LIKE upper('%&table_name%')
ORDER BY 2, 4;