Oracle索引质量介绍和分析脚本分享
索引质量的高低对数据库整体性能有着直接的影响。良好高质量的索引使得数据库性能得以数量级别的提升，而低效冗余的索引则使得数据库性能缓慢如牛，即便是使用高档的硬件配置。因此对于索引在设计之初需要经过反复的测试与考量。那对于已经置于生产环境中的数据库，我们也可以通过查询相关数据字典得到索引的质量的高低，通过这个分析来指导如何改善索引的性能。下面给出了演示以及索引创建的基本指导原则，最后给出了索引质量分析脚本。
1、查看索引质量
--获取指定schema或表上的索引质量信息报告 gx_adm@CABO3>@idx_quality Entervalueforinput_owner:GX_ADM Entervalueforinput_tbname:CLIENT_TRADE_TBL-->如果我们省略具体的表名则会输出整个schema的索引质量报告 TableTableIndexDataBlksLeafBlksClustIndex TableRowsBlocksIndexSizeMBperKeyperKeyFactorQuality -------------------------------------------------------------------------------------------------------------------------- CLIENT_TRADE_TBL6,318,035278488I_TDCL_ARC_STL_DATE_STOCK6231213171,0175-Excellent I_TDCL_ARC_STL_DATE_CASH6231813174,5995-Excellent I_TDCL_ARC_CANCEL_DATE832388288,6785-Excellent I_TDCL_ARC_INPUT_DATE14424913310,9745-Excellent I_TDCL_ARC_TRADE_DATE14426914337,0975-Excellent PK_CLIENT_TRADE_TBL20011798,2162-Good I_TDCL_ARC_GRP_REF_ID14411811,4682-Good UNI_TDCL_ARC_REF_ID13611765,6032-Good I_TDCL_ARC_CONTRACT_NUM7211834,4912-Good I_TDCL_ARC_SETTLED_DATE612995380,6991-Poor I_TDCL_ARC_ACC_NUM18462433,899,4461-Poor I_TDCL_ARC_PL_STK17621814,348,8041-Poor I_TDCL_ARC_INSTRU_ID1202,66784,273,0381-Poor --从上面的单表输出的索引质量可知，出现了4个处于Poor级别的索引，也就是说这些个索引具有较大的聚簇因子，几乎接近于表上的行了 --对于这几个索引的质量还应结合该索引的使用频率来考量该索引存在的必要性 --对于聚簇因子，只能通过重新组织表上的数据来，以及调整相应索引列的顺序得以改善 --查询单表上索引列的相关信息 gx_adm@CABO3>@idx_info Entervalueforowner:GX_ADM Entervaluefortable_name:CLIENT_TRADE_TBL TABLE_NAMEINDEX_NAMECL_NAMCL_POSSTATUSIDX_TYPDSCD ------------------------------------------------------------------------------------------------------------ CLIENT_TRADE_TBLI_TDCL_ARC_ACC_NUMACC_NUM1VALIDNORMALASC I_TDCL_ARC_CANCEL_DATECANCEL_DATE1VALIDNORMALASC I_TDCL_ARC_CONTRACT_NUMCONTRACT_NUM1VALIDNORMALASC I_TDCL_ARC_GRP_REF_IDGRP_REF_ID1VALIDNORMALASC I_TDCL_ARC_INPUT_DATEINPUT_DATE1VALIDNORMALASC I_TDCL_ARC_INSTRU_IDINSTRU_ID1VALIDNORMALASC I_TDCL_ARC_PL_STKSTOCK_CD1VALIDNORMALASC I_TDCL_ARC_PL_STKPL_CD2VALIDNORMALASC I_TDCL_ARC_SETTLED_DATESETTLED_DATE1VALIDNORMALASC I_TDCL_ARC_STL_DATE_CASHSTL_DATE_CASH1VALIDNORMALASC I_TDCL_ARC_STL_DATE_STOCKSTL_DATE_STOCK1VALIDNORMALASC I_TDCL_ARC_TRADE_DATETRADE_DATE1VALIDNORMALASC PK_CLIENT_TRADE_TBLBUSINESS_DATE1VALIDNORMALASC PK_CLIENT_TRADE_TBLREF_ID2VALIDNORMALASC UNI_TDCL_ARC_REF_IDREF_ID1VALIDNORMALASC --从上面的查询结果可知，当前表TRADE_CLIENT_TBL上含有13个索引，应该来说该表索引存在一定冗余。 --大多数情况下，单表上6-7个索引是比较理想的。过多的索引导致过大的资源开销，以及降低DML性能。
2、索引创建的基本指导原则
索引的创建应遵循精而少的原则
收集表上所有查询的各种不同组合，找出具有最佳离散度的列(或主键列等)创建单索引
对于频繁读取而缺乏比较理想离散值的列为其创建组合索引
对于组合索引应考虑下列因素来制定合理的索引列顺序，以下优先级别由高到低来作为索引的前导列，第二列等等
列被使用的频率
该列是否经常使用“=”作为常用查询条件
列上的离散度
组合列经常按何种顺序排序
哪些列会作为附件性列被添加
3、索引质量分析脚本
--scriptname:idx_quality.sql--Author:Leshami--Blog:http://blog.csdn.net/leshami
--indexqualityretrieval
SETLINESIZE145
SETPAGESIZE1000
SETVERIFYOFF
CLEARCOMPUTES
CLEARBREAKS
BREAKONtable_nameONnum_rowsONblocks
COLUMNownerFORMATa14HEADING'Indexowner'
COLUMNtable_nameFORMATa25HEADING'Table'
COLUMNindex_nameFORMATa25HEADING'Index'
COLUMNnum_rowsFORMAT999G999G990HEADING'Table|Rows'
COLUMNMBFORMAT9G990HEADING'Index|SizeMB'
COLUMNblocksHEADING'Table|Blocks'
COLUMNnum_blocksFORMAT9G990HEADING'Data|Blocks'
COLUMNavg_data_blocks_per_keyFORMAT999G990HEADING'DataBlks|perKey'
COLUMNavg_leaf_blocks_per_keyFORMAT999G990HEADING'LeafBlks|perKey'
COLUMNclustering_factorFORMAT999G999G990HEADING'Clust|Factor'
COLUMNIndex_QualityFORMATA13HEADING'Index|Quality'
--SPOOLindex_quality
SELECTi.table_name,
t.num_rows,
t.blocks,
i.index_name,
o.bytes/1048576mb,
i.avg_data_blocks_per_key,
i.avg_leaf_blocks_per_key,
i.clustering_factor,
CASE
WHENNVL(i.clustering_factor,0)=0THEN'0-NoStats'
WHENNVL(t.num_rows,0)=0THEN'0-NoStats'
WHEN(ROUND(i.clustering_factor/t.num_rows*100))<6THEN'5-Excellent'
WHEN(ROUND(i.clustering_factor/t.num_rows*100))BETWEEN7AND11THEN'4-VeryGood'
WHEN(ROUND(i.clustering_factor/t.num_rows*100))BETWEEN12AND15THEN'2-Good'
WHEN(ROUND(i.clustering_factor/t.num_rows*100))BETWEEN16AND25THEN'2-Fair'
ELSE'1-Poor'
END
index_quality
FROMdba_indexesi,dba_segmentso,dba_tablest
WHERE
--i.index_nameLIKEUPPER('%&&1%')AND
i.owner=t.owner
ANDi.table_name=t.table_name
ANDi.owner=o.owner
ANDi.index_name=o.segment_name
ANDt.owner=UPPER('&input_owner')
ANDt.table_nameLIKEUPPER('%&input_tbname%')
ORDERBYtable_name,
num_rows,
blocks,
index_qualityDESC;
--SPOOLOFF;
===========================================================================================
--scriptname:idx_info.sql
--gettheindexcolumninformationbyspecifiedtable
setlinesize180
colcl_namformata20
coltable_nameformata25
colcl_posformat9
colidx_typformata15
SELECTb.table_name,
a.index_name,
a.column_namecl_nam,
a.column_positioncl_pos,
b.status,
b.index_typeidx_typ,
a.descenddscd
FROMdba_ind_columnsa,dba_indexesb
WHEREa.index_name=b.index_name
ANDowner=upper('&owner')
ANDa.table_nameLIKEupper('%&table_name%')
ORDERBY2,4;