http://blog.csdn.net/leshami/article/details/23687137
索引品質的高低對資料庫整體性能有着直接的影響。良好高品質的索引使得資料庫性能得以數量級别的提升，而低效備援的索引則使得資料庫性能緩慢如牛，即便是使用高檔的硬體配置。是以對于索引在設計之初需要經過反複的測試與考量。那對于已經置于生産環境中的資料庫，我們也可以通過查詢相關資料字典得到索引的品質的高低，通過這個分析來指導如何改善索引的性能。下面給出了示範以及索引建立的基本指導原則，最後給出了索引品質分析腳本。
1、檢視索引品質
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-Excellent
10 I_TDCL_ARC_STL_DATE_CASH 62 318 13 174,599 5-Excellent
11 I_TDCL_ARC_CANCEL_DATE 83 238 8 288,678 5-Excellent
12 I_TDCL_ARC_INPUT_DATE 144 249 13 310,974 5-Excellent
13 I_TDCL_ARC_TRADE_DATE 144 269 14 337,097 5-Excellent
14 PK_CLIENT_TRADE_TBL 200 1 1 798,216 2-Good
15 I_TDCL_ARC_GRP_REF_ID 144 1 1 811,468 2-Good
16 UNI_TDCL_ARC_REF_ID 136 1 1 765,603 2-Good
17 I_TDCL_ARC_CONTRACT_NUM 72 1 1 834,491 2-Good
18 I_TDCL_ARC_SETTLED_DATE 61 299 5 380,699 1-Poor
19 I_TDCL_ARC_ACC_NUM 184 624 3 3,899,446 1-Poor
20 I_TDCL_ARC_PL_STK 176 218 1 4,348,804 1-Poor
21 I_TDCL_ARC_INSTRU_ID 120 2,667 8 4,273,038 1-Poor
22
23 --從上面的單表輸出的索引品質可知，出現了4個處于Poor級别的索引，也就是說這些個索引具有較大的聚簇因子，幾乎接近于表上的行了
24 --對于這幾個索引的品質還應結合該索引的使用頻率來考量該索引存在的必要性
25 --對于聚簇因子，隻能通過重新組織表上的資料來，以及調整相應索引列的順序得以改善
26
27 --查詢單表上索引列的相關資訊
28 gx_adm@CABO3> @idx_info
29 Enter value for owner: GX_ADM
30 Enter value for table_name: CLIENT_TRADE_TBL
31
32 TABLE_NAME INDEX_NAME CL_NAM CL_POS STATUS IDX_TYP DSCD
33 ------------------------- ------------------------------ -------------------- ------ -------- --------------- ----
34 CLIENT_TRADE_TBL I_TDCL_ARC_ACC_NUM ACC_NUM 1 VALID NORMAL ASC
35 I_TDCL_ARC_CANCEL_DATE CANCEL_DATE 1 VALID NORMAL ASC
36 I_TDCL_ARC_CONTRACT_NUM CONTRACT_NUM 1 VALID NORMAL ASC
37 I_TDCL_ARC_GRP_REF_ID GRP_REF_ID 1 VALID NORMAL ASC
38 I_TDCL_ARC_INPUT_DATE INPUT_DATE 1 VALID NORMAL ASC
39 I_TDCL_ARC_INSTRU_ID INSTRU_ID 1 VALID NORMAL ASC
40 I_TDCL_ARC_PL_STK STOCK_CD 1 VALID NORMAL ASC
41 I_TDCL_ARC_PL_STK PL_CD 2 VALID NORMAL ASC
42 I_TDCL_ARC_SETTLED_DATE SETTLED_DATE 1 VALID NORMAL ASC
43 I_TDCL_ARC_STL_DATE_CASH STL_DATE_CASH 1 VALID NORMAL ASC
44 I_TDCL_ARC_STL_DATE_STOCK STL_DATE_STOCK 1 VALID NORMAL ASC
45 I_TDCL_ARC_TRADE_DATE TRADE_DATE 1 VALID NORMAL ASC
46 PK_CLIENT_TRADE_TBL BUSINESS_DATE 1 VALID NORMAL ASC
47 PK_CLIENT_TRADE_TBL REF_ID 2 VALID NORMAL ASC
48 UNI_TDCL_ARC_REF_ID REF_ID 1 VALID NORMAL ASC
49
50 --從上面的查詢結果可知，目前表TRADE_CLIENT_TBL上含有13個索引，應該來說該表索引存在一定備援。
51 --大多數情況下，單表上6-7個索引是比較理想的。過多的索引導緻過大的資源開銷，以及降低DML性能。 2、索引建立的基本指導原則
索引的建立應遵循精而少的原則
收集表上所有查詢的各種不同組合，找出具有最佳離散度的列(或主鍵列等)建立單索引
對于頻繁讀取而缺乏比較理想離散值的列為其建立組合索引
對于組合索引應考慮下列因素來制定合理的索引列順序，以下優先級别由高到低來作為索引的前導列，第二列等等
列被使用的頻率
該列是否經常使用“ = ”作為常用查詢條件
列上的離散度
組合列經常按何種順序排序
哪些列會作為附件性列被添加
3、索引品質分析腳本
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 blocks
11
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_quality
25
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 CASE
35 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 END
43 index_quality
44 FROM dba_indexes i, dba_segments o, dba_tables t
45 WHERE
46 -- i.index_name LIKE UPPER ('%&&1%') AND
47 i.owner = t.owner
48 AND i.table_name = t.table_name
49 AND i.owner = o.owner
50 AND i.index_name = o.segment_name
51 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 table
63 set linesize 180
64 col cl_nam format a20
65 col table_name format a25
66 col cl_pos format 9
67 col idx_typ format a15
68 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 dscd
75 FROM dba_ind_columns a, dba_indexes b
76 WHERE a.index_name = b.index_name
77 AND owner = upper('&owner')
78 AND a.table_name LIKE upper('%&table_name%')
79 ORDER BY 2, 4; 4、相關參考
Oracle 聚簇因子(Clustering factor)
Oracle 索引監控(monitor index)
Oracle 索引監控與外鍵索引
收集統計資訊導緻索引被監控
Oracle 監控索引的使用率
NULL 值與索引(一)
NULL 值與索引(二)
函數使得索引列失效
![oracle中的start with connect by用法[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)