精华内容
下载资源
问答
  • 近来Oracle也提出了一些与之相反的观点,就是强烈建议不要定期重建索引。本文是参考了1525787.1并进行相应描述。 1、重建索引的理由  a、Oracle的B树索引随着时间的推移变得不平衡(误解)  b、索引碎片在不断增加 ...
  • 索引重建是一个争论不休被不断热烈讨论的议题。...近来Oracle也提出了一些与之相反的观点,就是强烈建议不要定期重建索引。本文是参考了1525787.1并进行相应描述。1、重建索引的理由a、Oracle的...

    索引重建是一个争论不休被不断热烈讨论的议题。当然Oracle官方也有自己的观点,我们很多DBA也是遵循这一准则来重建索引,那就是Oracle建议对于索引深度超过4级以及已删除的索引条目至少占有现有索引条目总数的20% 这2种情形下需要重建索引。近来Oracle也提出了一些与之相反的观点,就是强烈建议不要定期重建索引。本文是参考了1525787.1并进行相应描述。

    1、重建索引的理由

    a、Oracle的B树索引随着时间的推移变得不平衡(误解)

    b、索引碎片在不断增加

    c、索引不断增加,删除的空间没有重复使用

    d、索引 clustering factor (集群因子)不同步,可以通过重建修复(误解)

    2、重建索引的本质

    本质:重建索引在数据库内部是先执行删除操作,再执行插入操作。

    3、反对重建索引的理由

    a. 大多数脚本都依赖 index_stats 动态表。此表使用以下命令填充:

    analyze index ... validate structure;

    尽管这是一种有效的索引检查方法,但是它在分析索引时会获取独占表锁。对于大型索引,其影响会是巨大的,因为在此期间不允许对表执行DML 操作。

    虽然该方法可以在不锁表的情况下在线运行,但是可能要消耗额外的时间。

    b. 重建索引的直接结果是 REDO 活动可能会增加,总体系统负载也可能会提高。

    插入/更新/删除操作会导致索引随着索引的分割和增长不断发展。

    重建索引后,它将连接的更为紧凑;但是,随着对表不断执行 DML 操作,必须再次分割索引,直到索引达到平衡为止。

    结果,重做活动增加,且索引分割更有可能对性能产生直接影响,因为我们需要将更多的 I/O、CPU 等用于索引重建。

    经过一段时间后,索引可能会再次遇到“问题”,因此可能会再被标记为重建,从而陷入恶性循环。

    因此,通常最好是让索引处于自然平衡和(或)至少要防止定期重建索引。

    4、Oracle的最终建议

    一般而言,极少需要重建 B 树索引,基本原因是 B 树索引很大程度上可以自我管理或自我平衡。

    大多数索引都能保持平衡和完整,因为空闲的叶条目可以重复使用。

    插入/更新和删除操作确实会导致索引块周围的可用空间形成碎片,但是一般来说这些碎片都会被正确的重用。

    Clustering factor群集因子反映了给定的索引键值所对应的表中的数据排序情况。重建索引不会对群集因子产生影响,集群因子只能通过重组表的数据改变。

    强烈建议不要定期重建索引,而应使用合适的诊断工具。

    个人结论,如果重建索引的巨大工作量与之对应的是极小的收益,那就得不偿失。如果系统有可用空闲期,重建之前和之后的测量结果表明性能有提高,值得重建。

    5、改良方法

    通常是优先考虑index coalesce(索引合并),而不是重建索引。索引合并有如下优点:

    a、不需要占用近磁盘存储空间 2 倍的空间

    b、可以在线操作

    c、无需重建索引结构,而是尽快地合并索引叶块,这样可避免系统开销过大。

    6、真正需要重建索引的情形

    索引或索引分区因介质故障损坏

    标记为UNUSABEL的索引需要重建

    索引移动到新的表空间或需要改变某些存储参数

    通过SQL*Loader加载数据到表分区后,需要重建索引分区

    重建索引以启用键压缩

    位图索引本质不同于B树索引,建议重建

    展开全文
  • Oracle ~ 重建索引(包括分区)尽量不要重建索引真正需要重建索引的情形如何重建索引1、drop 原来的索引,然后再创建索引2 、直接重建2.1 alter index rebuild 和alter index rebuil online的区别注意点:重建分区表...

    尽量不要重建索引

    a. 大多数脚本都依赖 index_stats 动态表。此表使用以下命令填充:

    analyze index … validate structure;
    尽管这是一种有效的索引检查方法,但是它在分析索引时会获取独占表锁。对于大型索引,其影响会是巨大的,因为在此期间不允许对表执行DML 操作。
    虽然该方法可以在不锁表的情况下在线运行,但是可能要消耗额外的时间。

    b. 重建索引的直接结果是 REDO 活动可能会增加,总体系统负载也可能会提高。
    插入/更新/删除操作会导致索引随着索引的分割和增长不断发展。
    重建索引后,它将连接的更为紧凑;但是,随着对表不断执行 DML 操作,必须再次分割索引,直到索引达到平衡为止。
    结果,重做活动增加,且索引分割更有可能对性能产生直接影响,因为我们需要将更多的 I/O、CPU 等用于索引重建。
    经过一段时间后,索引可能会再次遇到“问题”,因此可能会再被标记为重建,从而陷入恶性循环。
    因此,通常最好是让索引处于自然平衡和(或)至少要防止定期重建索引。

    因此,一般情况下极少会重建索引, 通常是优先考虑index coalesce(索引合并),而不是重建索引。索引合并有如下优点:
    a、不需要占用盘存储空间 2 倍的空间
    b、可以在线操作
    c、无需重建索引结构,而是尽快地合并索引叶块,这样可避免系统开销过大。

    真正需要重建索引的情形

    • 索引或索引分区因介质故障损坏
    • 标记为UNUSABEL的索引需要重建
    • 索引移动到新的表空间或需要改变某些存储参数
    • 通过SQL*Loader加载数据到表分区后,需要重建索引分区
    • 重建索引以启用键压缩
    • 位图索引本质不同于B树索引,建议重建

    如何重建索引

    查询是否需要重建索引:Analyze index index_name validate structure;
    查询是否需要重建索引:select height,DEL_LF_ROWS/LF_ROWS from index_stats;
    当查询出来的 height>=4 或者 DEL_LF_ROWS/LF_ROWS>0.2 的场合 , 该索引考虑重建

    1、drop 原来的索引,然后再创建索引

    删除索引:drop index IX_PM_USERGROUP;

    创建索引:create index IX_PM_USERGROUP on T_PM_USER (fgroupid);

    说明:此方式耗时间,无法在24*7环境中实现,不建议使用。

    2 、直接重建

    alter index indexname rebuild;alter index indexname rebuild online;

    说明:此方式比较快,可以在24*7环境中实现,建议使用此方式

    2.1 alter index rebuild 和alter index rebuil online的区别

    1、扫描方式不同

    • Rebuild以index fast full scan(or table full scan) 方式读取原索引中的数据来构建一个新的索引,有排序的操作;

    • rebuild online 执行表扫描获取数据,有排序的操作;

    说明:Rebuild 方式 (index fast full scan or table full scan 取决于统计信息的cost)

    2 、rebuild 会阻塞 dml 操作 ,rebuild online 不会阻塞 dml 操作

    3 、rebuild online 时系统会产生一个 SYS_JOURNAL_xxx 的 IOT 类型的系统临时日志表 , 所有 rebuild online 时索引的变化都记录在这个表中 , 当新的索引创建完成后 , 把这个表的记录维护到新的索引中去 , 然后 drop 掉旧的索引 ,rebuild online 就完成了

    注意点:

    1、 执行rebuild操作时,需要检查表空间是否足够

    2、虽然说rebuild online操作允许dml操作,但是还是建议在业务不繁忙时间段进行

    Rebuild操作会产生大量redo log

    重建分区表上的分区索引

    重建分区索引方法:

    分区:Alter index indexname rebuild partition paritionname tablespace tablespacename;

    子分区:Alter index indexname rebuild subpartition partitioname tablespace tablespacename;

    展开全文
  • oracle重建索引

    2011-09-27 17:43:40
    原文出自:... 当我们创建索引时,oracle会为索引创建索引树,表和索引树通过rowid(伪列)来定位数据。当表里的数据发生更新时,oracle会自动维护索引树。但是在索引树中没有更

    原文出自:http://space.itpub.net/7199859/viewspace-555288

    当我们创建索引时,oracle会为索引创建索引树,表和索引树通过rowid(伪列)来定位数据。当表里的数据发生更新时,oracle会自动维护索引树。但是在索引树中没有更新操作,只有删除和插入操作。

    例如在某表id列上创建索引,某表id列上有值“101”,当我将“101”更新为“110”时,oracle同时会来更新索引树,但是oracle先将索引树中的“101”标示为删除(实际并未删除,只是标示一下),然后再将“110”写到索引树中。

    如果表更新比较频繁,那么在索引中删除标示会越来越多,这时索引的查询效率必然降低,所以我们应该定期重建索引。来消除索引中这些删除标记。

    一般不会选择先删除索引,然后再重新创建索引,而是rebuild索引。在rebuild期间,用户还可以使用原来的索引,并且rebuild新的索引时也会利用原来的索引信息,这样重建索引会块一些。


    G:\-N'Ac%[0这个实验来察看索引中的删除标记,并且如何重建索引。

    试验环境:oracle 8.1.7

     

    一、创建表、插入记录和创建索引
    :Z4Sto'k n0SQL> create table ind (id number,name varchar2(100));

    表已创建。

    SQL> create or replace procedure sp_insert_ind
    &Z#xK!^p&qq)J0  2  isITPUB个人空间XNw0_b6J@0aZ
      3  beginITPUB个人空间J/H-N*{2H5}7S(b;_
      4  for i in 1..10000 loopITPUB个人空间(M Cn-f f
      5  insert into ind values(i,to_char(i)||'aaaaaaaaaa');ITPUB个人空间j;nk+v#x-x[F{JUs
      6  end loop;ITPUB个人空间GuC/R9K"{!S:C-E
      7  end;
    :Cu0[8e�v`.L0  8  /

    过程已创建。

    SQL> exec sp_insert_ind

    PL/SQL 过程已成功完成。

    ITPUB个人空间Kkg!m].A3jq
    SQL> create index ind_id_idx on ind(id);

    索引已创建。

     

    二、收集索引信息

    --收集信息,没有更新数据字典,所以没有信息ITPUB个人空间 \vT"a#ZP#oex
    SQL> select lf_rows,lf_rows_len,del_lf_rows,del_lf_rows_len from index_stats;

    未选定行


    5})YlX5K6F Y7B\oc0--更新数据字典
    _V [v9ZIS7BI0SQL> ANALYZE INDEX ind_id_idx VALIDATE STRUCTURE;

    索引已分析


    o[s?I+T0P?0--参数含义:ITPUB个人空间[7DT$T$zZMy"Ol
    --LF_ROWS Number of values currently in the index
    %\!_hW%N)lS:?8}0--LF_ROWS_LEN Sum in bytes of the length of all values
    .bD`1Q'vNF0--DEL_LF_ROWS Number of values deleted from the index
    DN2zoM`^l'U0--DEL_LF_ROWS_LEN Length of all deleted values

    SQL> select lf_rows,lf_rows_len,del_lf_rows,del_lf_rows_len from index_stats;

       LF_ROWS LF_ROWS_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN                             ITPUB个人空间*wkKJ$yv5b;M
    ---------- ----------- ----------- ---------------                             
    uY7CFY:J5Lv\O0     10000      149801           0               0     

                           
    !\A,A'{3QT7c4R7a0--察看索引中已经标示为删除的行除以总共的行的数量,目前为0ITPUB个人空间8|4Bcy ^y
    SQL> SELECT (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 AS index_usage  FROM index_stats;

    INDEX_USAGE                                                                    ITPUB个人空间d:G6i'C4Z:I+c%h
    -----------                                                                    
    V%v)[K,C!u0          0      

     

    三、更新索引,并且重新察看信息
    s?,a3Xx5M8D0                                                            ITPUB个人空间G4OY|5vuh
    --更新表中1000行记录,这时会更新索引树
    8N6V"G%}m4Nh*a+NT0SQL> update ind set id=id+1 where id>9000;

    已更新1000行。

    ITPUB个人空间/rk`j3kA]�|T
    SQL> ANALYZE INDEX ind_id_idx VALIDATE STRUCTURE;

    索引已分析


    Y{@A\`E#O&p0--总共行的数量增加了1000行,并且标示为删除了1000行记录ITPUB个人空间!N;f#Fav{9Bk8}
    SQL> select lf_rows,lf_rows_len,del_lf_rows,del_lf_rows_len from index_stats;

       LF_ROWS LF_ROWS_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN                             ITPUB个人空间4|&u:z)Vt\i0n.NpW
    ---------- ----------- ----------- ---------------                             ITPUB个人空间)rXb6W|]E0A1|U
         11000      164792        1000           14990                             


    /E#\|A+S|q ~0--察看索引中已经标示为删除的行除以总共的行的数量,目前为 9.09631536,这个值如果查过20,肯定要重建索引了。
    iSW%PX/S0SQL> SELECT (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 AS index_usage  FROM index_stats;

    INDEX_USAGE                                                                    
    [X uU/UL0-----------                                                                    
    ]Ek)V{!])?s09.09631536                                                                    

     

    四、重建索引

    --重建索引
    VH~7~r,ON9W \0SQL> alter index ind_id_idx rebuild;

    索引已更改。

    SQL> select lf_rows,lf_rows_len,del_lf_rows,del_lf_rows_len from index_stats;

    未选定行

     

    ---以下信息又基本回到从前
    I]bN/Y9_v-L;rt"H0SQL> ANALYZE INDEX ind_id_idx VALIDATE STRUCTURE;

    索引已分析

    SQL> select lf_rows,lf_rows_len,del_lf_rows,del_lf_rows_len from index_stats;

       LF_ROWS LF_ROWS_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN                             ITPUB个人空间%Y0q3^ecX8@&I.yW
    ---------- ----------- ----------- ---------------                             ITPUB个人空间m1E*v5K`:y9uT)`"w
         10000      149802           0               0                             

    SQL> SELECT (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 AS index_usage  FROM index_stats;

    INDEX_USAGE                                                                    
    y|6T],O X y&Z0-----------                                                                    ITPUB个人空间2l|@hAt3FH
              0        


    展开全文
  • 主要介绍了Oracle重建索引Shell脚本、SQL脚本分享,索引是提高数据库查询性能的有力武器,定期重建索引是很有必要的事情,需要的朋友可以参考下
  • 浅谈oracle重建索引

    千次阅读 2014-01-11 09:58:21
    当我们创建索引时,oracle会为索引创建索引树,表和索引树通过rowid(伪列)来定位数据。当表里的数据发生更新时,oracle会自动维护索引树。但是在索引树中没有更新操作,只有删除和插入操作。 例如在某表id列上...

    转载地址:http://hi.baidu.com/ericfung56/item/5fc3412c43a8b81a2a0f1c0c


    当我们创建索引时,oracle会为索引创建索引树,表和索引树通过rowid(伪列)来定位数据。当表里的数据发生更新时,oracle会自动维护索引树。但是在索引树中没有更新操作,只有删除和插入操作。

    例如在某表id列上创建索引,某表id列上有值“101”,当我将“101”更新为“110”时,oracle同时会来更新索引树,但是oracle先将索引树中的“101”标示为删除(实际并未删除,只是标示一下),然后再将“110”写到索引树中。

    如果表更新比较频繁,那么在索引中删除标示会越来越多,这时索引的查询效率必然降低,所以我们应该定期重建索引。来消除索引中这些删除标记。

    一般不会选择先删除索引,然后再重新创建索引,而是rebuild索引。在rebuild期间,用户还可以使用原来的索引,并且rebuild新的索引时也会利用原来的索引信息,这样重建索引会块一些。


    这个实验来察看索引中的删除标记,并且如何重建索引。

    试验环境:oracle 8.1.7

     

    一、创建表、插入记录和创建索引
    SQL> create table ind (id number,name varchar2(100));

    表已创建。

    SQL> create or replace procedure sp_insert_ind
      2  is
      3  begin
      4  for i in 1..10000 loop
      5  insert into ind values(i,to_char(i)||'aaaaaaaaaa');
      6  end loop;
      7  end;
      8  /

    过程已创建。

    SQL> exec sp_insert_ind

    PL/SQL 过程已成功完成。


    SQL> create index ind_id_idx on ind(id);

    索引已创建。

     

    二、收集索引信息

    --收集信息,没有更新数据字典,所以没有信息
    SQL> select lf_rows,lf_rows_len,del_lf_rows,del_lf_rows_len from index_stats;

    未选定行


    --更新数据字典
    SQL> ANALYZE INDEX ind_id_idx VALIDATE STRUCTURE;

    索引已分析


    --参数含义:
    --LF_ROWS Number of values currently in the index
    --LF_ROWS_LEN Sum in bytes of the length of all values
    --DEL_LF_ROWS Number of values deleted from the index
    --DEL_LF_ROWS_LEN Length of all deleted values

    SQL> select lf_rows,lf_rows_len,del_lf_rows,del_lf_rows_len from index_stats;

       LF_ROWS LF_ROWS_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN                              
    ---------- ----------- ----------- ---------------                              
         10000      149801           0               0     

                            
    --察看索引中已经标示为删除的行除以总共的行的数量,目前为0
    SQL> SELECT (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 AS index_usage  FROM index_stats;

    INDEX_USAGE                                                                     
    -----------                                                                     
              0      

     

    三、更新索引,并且重新察看信息
                                                                 
    --更新表中1000行记录,这时会更新索引树
    SQL> update ind set id=id+1 where id>9000;

    已更新1000行。


    SQL> ANALYZE INDEX ind_id_idx VALIDATE STRUCTURE;

    索引已分析


    --总共行的数量增加了1000行,并且标示为删除了1000行记录
    SQL> select lf_rows,lf_rows_len,del_lf_rows,del_lf_rows_len from index_stats;

       LF_ROWS LF_ROWS_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN                              
    ---------- ----------- ----------- ---------------                              
         11000      164792        1000           14990                             


    --察看索引中已经标示为删除的行除以总共的行的数量,目前为 9.09631536,这个值如果查过20,肯定要重建索引了。
    SQL> SELECT (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 AS index_usage  FROM index_stats;

    INDEX_USAGE                                                                     
    -----------                                                                     
    9.09631536                                                                    

     

    四、重建索引

    --重建索引
    SQL> alter index ind_id_idx rebuild;

    索引已更改。

    SQL> select lf_rows,lf_rows_len,del_lf_rows,del_lf_rows_len from index_stats;

    未选定行

     

    ---以下信息又基本回到从前
    SQL> ANALYZE INDEX ind_id_idx VALIDATE STRUCTURE;

    索引已分析

    SQL> select lf_rows,lf_rows_len,del_lf_rows,del_lf_rows_len from index_stats;

       LF_ROWS LF_ROWS_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN                              
    ---------- ----------- ----------- ---------------                              
         10000      149802           0               0                             

    SQL> SELECT (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 AS index_usage  FROM index_stats;

    INDEX_USAGE                                                                     
    -----------                                                                     
              0     


    展开全文
  • Oracle 重建索引的必要性

    万次阅读 2014-04-15 15:45:07
    索引重建是一个争论不休被不断热烈讨论的议题。当然Oracle官方也有自己...近来Oracle也提出了一些与之相反的观点,就是强烈建议不要定期重建索引。本文是参考了1525787.1并进行相应描述。1、重建索引的理由 a、Oracle
  • Oracle索引重建

    2015-11-27 12:33:00
    有人持不同观点,就是强烈建议不要定期重建索引。索引重建是一个争论不休被不断热烈讨论的议题。 1、重建索引的理由 a、Oracle的B树索引随着时间的推移变得不平衡(误解) b、索引碎片在不断增加 c、索引不断...
  • Oracle 重建索引脚本

    千次阅读 2014-04-21 17:52:32
    索引是提高数据库查询性能的有力武器。没有索引,就好比图书馆没有图书标签...N久以前Oracle建议我们定期重建那些高度为4,已删除的索引条目至少占有现有索引条目总数的20%的这些表重建索引。但Oracle现在强烈建议不要
  • 浅谈oracle重建索引 当我们创建索引时,oracle会为索引创建索引树,表和索引树通过rowid(伪列)来定位数据。当表里的数据发生更新时,oracle会自动维护索引树。但是在索引树中没有更新操作,只有删除和插入操作。 ...
  • 由于目前暂时找不到合适的DBA对数据库进行优化,于是写了一个简单的脚本来定期重建所有的索引。本文提到的脚本创建一张表用来记录索引重建的日志,建立一个存储过程,并建立一个job来每7天调用一次该存储过程。 ...
  • 定期重建索引?maybe

    2017-01-09 19:30:12
    是否需要定期重建索引,是dba们一直争论的话题,从未停止过. 总结下各方意见 ---5.1 支持重建索引的理由 1)oracle的B树索引随着时间的推移变得很不平衡 很多dba认为如果对oracle的B树索引进行大量的DMl...
  • 浅谈oracle重建索引当我们创建索引时,oracle会为索引创建索引树,表和索引树通过rowid(伪列)来定位数据。当表里的数据发生更新时,oracle会自动维护索引树。但是在索引树中没有更新操作,只有删除和插入操作。...
  • 定期重建索引脚本: CREATE OR REPLACE TYPE strsplit_type AS TABLE OF VARCHAR2(32676); CREATE OR REPLACE FUNCTION strsplit(p_value VARCHAR2, p_split VARCHAR2 := ',') --usage: select * from table...
  • 当我们创建索引时,oracle会为索引创建索引树,表和索引树通过rowid(伪列)来定位数据。当表里的数据发生更新时,oracle会自动维护索引树。但是在索引树中没有更新操作,只有删除和插入操作。例如在某表id列上创建...
  • ORACLE数据库中的索引到底要不要定期重建呢? 如果不需要定期重建,那么理由是什么? 如果需要定期重建,那么理由又是什么?另外,如果需要定期重建,那么满足那些条件的索引才需要重建呢?关于这个问题,网上也有很...
  • Oracle重建索引

    千次阅读 2010-06-16 22:02:00
    如果表更新比较频繁,那么在索引中删除标示会越来越多,这时索引的查询效率必然降低,所以我们应该定期重建索引来消除索引中这些删除标记。 一般不会选择先删除索引,然后再重新创建索引,而是rebuild索引。在rebuild...
  • 当然Oracle官方也有自己的观点,我们很多DBA也是遵循这一准则来重建索引,那就是Oracle建议对于索引深度超过4级以及已删除的索引条目至少占有现有索引条目总数的20% 这2种情形下需要重建索引。近来Oracle也提出了...
  • 浅谈Oracle 重建索引

    2015-02-06 11:06:00
     如果表更新比较频繁,那么在索引中删除标示会越来越多,这时索引的查询效率必然降低,所以我们应该定期重建索引。来消除索引中这些删除标记。   一般不会选择先删除索引,然后再重新创建索引,而是rebuild索引...
  • select t.table_name, t.last_analyzedfrom user_all_tables tbegin dbms_stats.gather_schema_stats('DCWORK');end;   DCWORK DCWORK 1 DCWORK begin dbms_stats.gather_schema_stats('DCWORK');...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 5,006
精华内容 2,002
热门标签
关键字:

oracle定期重建索引