oracle笔记——索引

原创 2016年06月01日 15:58:50

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


一、新建了从未被命中的索引,占用空间资源,增加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,以免锁表】

版权声明:本文为博主原创文章,未经博主允许不得转载。

oracle学习笔记——视图、索引(转载)

oracle学习笔记——视图、索引(转载) 1.视图(VIEW) 1.1 概念        视图-----是由SELECT查询语句(可以是单表或者多表查询)定义的一个"逻辑表",只...

Oracle 11g笔记——索引组织表

一、索引组织表(Index-Organized Table, IOT)     索引组织表是一类特殊的表,它将索引和表的数据存储在一起。普通表的数据以无序(Heap)的方式存放在数据库中。     而...
  • lJean
  • lJean
  • 2016年05月10日 09:14
  • 1434

oracle索引技术——天天笔记(2)

函数索引 SELECT *   FROM DEMOT  WHERE F7 = 'F'; SELECT *   FROM DEMOT  W...

oracle学习笔记——视图、索引(转载)

http://hi.baidu.com/dcf1209/blog/item/a90ae62a323c733d5343c1a5.html 1.视图(VIEW) 1.1 概念        视图--...

oracle索引技术——天天笔记(1)

数据库的索引把用户感兴趣的列值连同其行标识符(ROWID)存储在一起 ROWID包含了存储列值的表行在磁盘上的物理位置 索引虽然能提高查询的性能,但是也不能一度的创建索引,因为索引是有代价的 消耗磁盘...

oracle数据库索引优劣描述——学习笔记

序言: 本来是复制、粘贴,但是也不能完全说是转载,就原创吧,还能到首页透口气呢。 索引分类: 索引可以分为:聚集索引,非聚集索引,以及唯一索引。 优点: 1、主要优点就是...

Oracle学习笔记(十九)——管理索引

一、管理索引 1、索引:用于加速数据存取的数据对象。合理使用索引可以大大降低i/o次数,从而提高数据访问性能。 2、创建索引 1)单列索引:基于单个列所建立的索引。 create ...

oracle学习笔记——索引

一、oracle的索引陷阱 一个表中有几百万条数据,对某个字段加了索引,但是查询时性能并没有什么提高,这主要可能是oracle的索引限制造成的。oracle的索引有一些索引限制,在这些索引限...

oracle笔记整理9——性能调优之索引、表分区、索引分区

1. 索引1) 需要建索引的列 a) where从句中频繁使用的关键字。 b) sql语句中频繁用于表连接的字段。 c) 可选择性高(重复值少)的字段。2) 索引限制条件a) where条...

oracle约束和索引笔记

  • 2012年03月23日 20:06
  • 1.19MB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:oracle笔记——索引
举报原因:
原因补充:

(最多只允许输入30个字)