###########################################################

                       索引优化知识 edit by sky 参考:david qq:654268465

###########################################################

一、       索引相关概念:

1.       选择性:

是指索引列中不同值的数目与表中记录数的比，选择性越高(接近于1)，说明索引效率越高，用来评估索引有没有必要建立。

选择性的计算:

手工计算:

列的选择性=不同值的数目/行的总数

自动计算:

analyze table table_name compute statistics

select a.distinct_keys/b.num_rows from user_indexes a,user_tables b where a.table_name=’

&table_name’ and a.index_name=’&index_name’ and a.table_name=b.table_name;

或者每个列的选择性:

select column_name, num_distinct from user_tab_columns where table_name="表名"

select count (*) from table_name;

2.       集群因子:

通过索引扫描一张表需要访问表的数据块的数量，如果此值接近于表存储的块数，则说明表是按索引字段顺序存储的；如果接这于表中行的数量，则说明表不是按照索引字段的顺序存储的，可能使CBO选择错误的执行计划(如全表扫描)，此时可能需要重构表。

集群因子的查询:

select index_name,table_name,distinct_keys,num_rows,leaf_blocks,blevel,clustering_factor,last_analyzed from dba_indexes where wner=’&user_name’ and index_name=’&index_name’;

重构表(使表中的数据按照索引字段的顺序重新组织):

create table new_table as select * from old_table where 1=0;

insert /* + append */ into new_table select * from new_table order by 索引列名;

commit;

truncate table old_table;

insert /* + append */ into old_table select * from new_table;

commit;

alter index 索引名 rebuild;

！！！！！！！重构表时需要注意的两点:

同一个表中一个索引集群因子的高效则意味着另一个索引集群因子的低效

在一些物理性能不是很好的环境下重构表可能耗时

3.       访问索引的开销=选择性*集群因子

二、       索引的监视:

通过监视索引的使用情况，从而删除不使用的索引:

select index_name,index_type from user_indexes where table_name=’&TABLE_NAME’;

alter index monitoring usage;  ------增加小的系统开销

select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;

----------以上语句只能查看当前用户下被监控的索引

----------以下语句可以查看所有用户下被监控的索引

SELECT u.name owner,io.name index_name,t.name table_name,

decode(bitand(i.flags,65536),0,'NO','YES') monitoring,

decode(bitand(ou.flags,1),0,'NO','YES') used,

ou.start_monitoring start_monitoring,

ou.end_monitoring end_monitoring

FROM sys.user$ u,sys.obj$ io,sys.obj$ t,sys.ind$ i,sys.object_usage ou

WHERE i.obj#=ou.obj#

AND io.obj#=ou.obj#

AND t.obj#=i.bo#

AND u.user#=io.owner#;

AND u.user#=io.owner#;

三、       索引碎片的处理:

1．  确定索引碎片:

方法1:

validate index 用户名.索引名

或者analyze index index_name validate structure; 

------这两条语句将分析数据放入到index_stats表中，此表只保存最近一次的分析结果

select name,height, del_lf_rows, lf_rows,

round((del_lf_rows/(lf_rows+0.0000000001))*100) "Frag Percent" from index_stats;

------------------如果索引的叶子行的碎片超过10%，或者 index_stats中height > =4, 可以考虑对索引进行重建

--------确定索引中删除的、未填满的叶子(Leaf)行的百分比和height字段

方法2:

select object_id,object_name from user_objects where object_name like ‘索引名%’;

alter session set events ‘immediate trace name treedump level ;

----------转储索引树

2．索引创建的原则:

a.不在系统表空间中创建索引:

select index_name from dba_indexes where tablespace_name="SYSTEM" and owner not in ("SYS","SYSTEM");--------在系统表空间中的索引，重建索引

b.确保表及其索引位于不同的表空间中:

SELECT i.owner "OWNER",

i.index_name "INDEX",

t.table_name "TABLE",

i.tablespace_name "TABLESPACE"

FROM dba_indexes i, dba_tables t

WHERE i.owner = t.owner

AND i.table_name = t.table_name

AND i.tablespace_name = t.tablespace_name

AND i.owner NOT IN ('SYS', 'SYSTEM'); 

-------查找表和其索引在同一个表空间的索引并建

c.索引扩展次数过多会影响检索效率，确保索引扩展次数尽可能少

SELECT   COUNT ( * ),

owner,

segment_name,

tablespace_name

FROM   dba_extents

WHERE   segment_type = 'INDEX' AND owner NOT IN ('SYS', 'SYSTEM')

GROUP BY   owner, segment_name, tablespace_name

HAVING   COUNT ( * ) > 10

ORDER BY   COUNT ( * ) DESC;

-------查找扩展超过10次的索引并重建

d.确定创建索引时适当的存储参数:

SELECT owner "OWNER",

segment_name "INDEX",

tablespace_name "TABLESPACE",

bytes "BYTES/COUNT",

SUM (bytes) "TOTAL BYTES",

ROUND (SUM (bytes) / (1024 * 1024), 0) "TOTAL M",

COUNT (bytes) "TOTAL COUNT"

FROM dba_extents

WHERE segment_type = 'INDEX'

AND segment_name IN ('INDEX_NAME1', 'INDEX_NAME2')

GROUP BY owner,

segment_name,

segment_type,

tablespace_name,

bytes

ORDER BY owner, segment_name;

-------对于需要重建的索引查看其大小，以便确定合适的存储参数

SELECT ROUND (bytes / (1024 * 1024), 2) "free(M)"

FROM sm$ts_free

WHERE tablespace_name = '表空间名';  ------确定索引表空间有剩余空间

select (sum(bytes)/1024/1024)||'MB' from dba_segments where segment_name=’索引名’;

-------查看索引占用空间大小

select (sum(bytes)/1024/1024)||'MB' from dba_segments where segment_name=’表名’;

--------查看表占用空间大小

四、       重建索引:

1．重建

alter index 索引名 rebuild tablespace 索引表空间名 storage(initial 初始值 next 扩展值)  nologging parallel 2;

-----------并行比较消耗CPU，建议在CPU资源充足的情况下进行

2．  确认索引创建完好:

select * from dba_extents where segment_name="索引名";

select * from dba_ind_columns where index_name='索引名';------查看索引相关的列名

select * from “表名” where “索引列名”