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性能。
2、索引创建的基本指导原则
索引的创建应遵循精而少的原则
收集表上所有查询的各种不同组合，找出具有最佳离散度的列(或主键列等)创建单索引
对于频繁读取而缺乏比较理想离散值的列为其创建组合索引
对于组合索引应考虑下列因素来制定合理的索引列顺序，以下优先级别由高到低来作为索引的前导列，第二列等等
列被使用的频率
该列是否经常使用“ = ”作为常用查询条件
列上的离散度
组合列经常按何种顺序排序
哪些列会作为附件性列被添加
3、索引质量分析脚本
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;