oracle笔记——索引

数据库运行一定的时间后，索引方面可能出现两种问题：

一、新建了从未被命中的索引，占用空间资源，增加DML操作的性能消耗；

二、数据的变动导致索引碎片的增加，具体表现为索引所占用空间增加，查询效率下降；

问题一，可以对占用空间过大的索引进行分析，具体步骤：

1、查看当前用户所用表空间下的索引情况

select t.segment_name,t1.TABLE_NAME,t.bytes / 1024 / 1024 / 1024 sizeG

  from user_segments t,user_indexes t1

 where t.segment_name=t1.INDEX_NAME order by sizeG desc

2、将占用空间较大的索引开启监控

alter index {index_name} monitoring usage

3、过段时间查看监控结果

select * from SYS.V$OBJECT_USAGE

4、如果USED字段一直为NO，则表示此索引从未被使用过；

问题二：对索引进行分析，分析过程中会造成锁表，建议将数据异地恢复至从库后进行

以下为我的索引分析操作步骤：

1、新建一张临时表存放分析结果

create table TEST
(
  name        VARCHAR2(30),
  lf_rows     NUMBER,
  lf_blks     NUMBER,
  del_lf_rows NUMBER,
  ratio       NUMBER
)

2.通过存储过程循环分析，并将分析结果写入结果表中，当前会话分析第二个索引后，分析结果会前第一个覆盖，因此每分析完一笔保存一笔

[以下过程只分析索引占用空间排名前100的索引]

create or replace procedure p_index_check is
  cursor index_name is
    select segment_name from (select t.segment_name,t1.TABLE_NAME,t.bytes / 1024 / 1024 / 1024 sizeG
  from user_segments t,user_indexes t1
 where t.segment_name=t1.INDEX_NAME order by sizeG desc) where rownum<=100;
  sqlt varchar(200);
begin
  for iname in index_name loop
    begin
      sqlt := 'analyze index ' || iname.segment_name || '  validate structure ';
      EXECUTE IMMEDIATE sqlt;
      insert into TEST select t.name,
       t.lf_rows,
       t.lf_blks,
       t.del_lf_rows,
       (t.del_lf_rows / t.lf_rows) * 100 ratio
  from index_stats t where t.name=iname.segment_name;
  commit;
    EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line(SQLERRM);
    end;
  end loop;
end;

3、查看结果表上的ratio字段，其值为碎片比例，如索引程度过高

使用ALTER INDEX indexname REBUILD ONLINE;重建

【生产环境记得添加online，以免锁表】