精华内容
下载资源
问答
  • ORACLE数据库重建索引

    2013-09-29 11:06:27
    导致索引失效: 1、表上频繁发生update,delete操作; 2、表上发生了alter table ..move操作(move操作导致了rowid变化)。
  • 主要介绍了Oracle重建索引Shell脚本、SQL脚本分享,索引是提高数据库查询性能的有力武器,定期重建索引是很有必要的事情,需要的朋友可以参考下
  • ORACLE重建索引总结

    2011-12-13 10:32:28
    一、重建索引的前提 1、表上频繁发生update,delete操作; 2、表上发生了alter table ..move操作(move操作导致了rowid变化)。 二、重建索引的标准 1、索引重建是否有必要,一般看索引是否倾斜的...
  • NULL 博文链接:https://hackpro.iteye.com/blog/1845366
  • ORACLE重建索引详解

    千次阅读 2018-01-25 10:12:03
    一、重建索引的前提 1、表上频繁发生update,delete操作; 2、表上发生了alter table ..move操作(move操作导致了rowid变化)。   二、重建索引的标准 1、索引重建是否有必要,一般看索引是否倾斜的...

    一、重建索引的前提

    1、表上频繁发生update,delete操作;

    2、表上发生了alter table ..move操作(move操作导致了rowid变化)。

     

    二、重建索引的标准

    1、索引重建是否有必要,一般看索引是否倾斜的严重,是否浪费了空间, 那应该如何才可以判断索引是否倾斜的严重,是否浪费了空间, 对索引进行结构分析(如下):

    SQL>Analyze index index_name validate structure;

    2、在执行步骤1的session中查询index_stats表,不要到别的session去查询。

    SQL>select height,DEL_LF_ROWS/LF_ROWS from index_stats;

    说明:当 查询出来的 height>=4 或者 DEL_LF_ROWS/LF_ROWS>0.2 的场合 , 该索引考虑重建 。

    举例: (t_gl_assistbalance 26 万多条信息 )

    SQL> select count(*) from t_gl_assistbalance ;

    输出结果:

    COUNT(*)

    ----------

    265788

     

    SQL> Analyze index IX_GL_ASSTBAL_1 validate structure;

    Index analyzed

    SQL> select height,DEL_LF_ROWS/LF_ROWS from index_stats;

    输出结果:

    HEIGHT DEL_LF_ROWS/LF_ROWS

    ---------- -------------------

    4 1

     

    三、重建索引的方式

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

    举例:

    删除索引:drop index IX_PM_USERGROUP;

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

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

     伦理片 http://www.dotdy.com/

    2 、直接重建:

    举例:

    alter index indexname rebuild; 或alter index indexname rebuild online;

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

     

    四、alter index rebuild 内部过程和注意点

    alter index rebuild 和alter index rebuil online的区别

    1、扫描方式不同

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

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

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

     

    举例1

    SQL> explain plan for alter index IX_GL_ASSTBAL_1 rebuild;

    Explained

    SQL> select * from table(dbms_xplan.display);

    PLAN_TABLE_OUTPUT

    ---------------------------------------------------------------------

    | Id | Operation | Name | Rows | Bytes | Cost |

    ---------------------------------------------------------------------

    | 0 | ALTER INDEX STATEMENT | | 999K| 4882K| 3219 |

    | 1 | INDEX BUILD NON UNIQUE| IDX_POLICY_ID2 | | | |

    | 2 | SORT CREATE INDEX | | 999K| 4882K| |

    | 3 | INDEX FAST FULL SCAN | IDX_POLICY_ID2 | 999K| 4882K| |

    ---------------------------------------------------------------------

     

    举例2

    SQL> explain plan for alter index idx_policy_id rebuild;

    Explained

    SQL> select * from table(dbms_xplan.display);

    PLAN_TABLE_OUTPUT

    ---------------------------------------------------------------------

    | Id | Operation | Name | Rows | Bytes | Cost |

    ---------------------------------------------------------------------

    | 0 | ALTER INDEX STATEMENT | | 2072K| 9M| 461 |

    | 1 | INDEX BUILD NON UNIQUE| IDX_POLICY_ID | | | |

    | 2 | SORT CREATE INDEX | | 2072K| 9M| |

    | 3 | TABLE ACCESS FULL | TEST_INDEX | 2072K| 9M| 461 |

     

    举例3 ( 注意和 举例1 比较 )

    Rebuil online 方式 :

    SQL> explain plan for alter index idx_policy_id2 rebuild online;

    Explained

    SQL> select * from table(dbms_xplan.display);

    PLAN_TABLE_OUTPUT

    ---------------------------------------------------------------------

    | Id | Operation | Name | Rows | Bytes | Cost |

    ---------------------------------------------------------------------| 0 | ALTER INDEX STATEMENT | | 999K| 4882K| 3219 |

    | 1 | INDEX BUILD NON UNIQUE| IDX_POLICY_ID2 | | | |

    | 2 | SORT CREATE INDEX | | 999K| 4882K| |

    | 3 | TABLE ACCESS FULL | TEST_INDEX2 | 999K| 4882K| 3219 |

     

    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;

    Partition name 可以从user_ind_partitions查找

    Tablepace 参数允许alter index操作更改索引的存储空间;

     

    六、索引状态描述

    在数据字典中查看索引状态,发现有三种:

    valid:当前索引有效

    N/A :分区索引 有效

    unusable:索引失效

     

    七、术语

    1、高基数:简单理解就是表中列的不同值多。

    2、低基数:建单理解就是表中的列的不同值少。

    3、以删除的叶节点数量:指得是数据行的delete操作从逻辑上删除的索引节点 的数量,要记住oracle在删除数据行后,将 “ 死 “ 节点保留在索引中,这样做可以加快sql删除操作的速度,因此oracle删除数据行后可以不必重新平衡索引。

    4、索引高度:索引高度是指由于数据行的插入操作而产生的索引层数,当表中添加大量数据时,oracle将生成索引的新层次以适应加入的数据行,因此,oracle索引可能有4层,但是这只会出现在索引数中产生大量插入操作的区域。Oracle索引的三层结构可以支持数百万的项目,而具备4层或是更多层的需要重建。

    5、每次索引访问的读取数:是指利用索引读取一数据行时所需要的逻辑I/O操作数,逻辑读取不必是物理读取,因为索引的许多内容已经保存在数据缓冲区,然而,任何数据大于10的索引都需要重建。

    6、什么时候重建呢?

     

    察看 dba_indexes 中的 blevel 。这列是说明索引从根块到叶快的级别,或是深度。如果级别大于等于4。则需要重建,

    如下 :Select index_name,blevel from dba_indexes where blevel>=4.

    另一个从重建中受益的指标显然是当该索引中的被删除项占总的项数的百分比。如果在20%以上时,也应当重建,如下

    SQL>analyze index index_name validate structure

    SQL>select (del_lf_rows_len/lf_rows_len)*100 from index_stats where name= ’ index_name ’

    就能看到是否这个索引被删除的百分比。

     

    7、什么样的重建方式更好?

    (1)建索引的办法:

    1.1、删除并从头开始建立索引。

    1.2 、 使用 alter index index_name rebuild 命令重建索引。

    1.3 、 使用 alter index index_name coalesce 命令重建索引。

     

    (2)、下面讨论一下这三种方法的优缺点:

    2.1、删除并从头开始建索引:方法是最慢的,最耗时的。一般不建议。

    2.2、Alter index index_name rebuild

    快速重建索引的一种有效的办法,因为使用现有索引项来重建新索引,如果客户操作时有其他用户在对这个表操作,尽量使用带online参数来最大限度的减少索引重建时将会出现的任何加锁问题,alter index index_name rebuild online。

     

    但是,由于新旧索引在建立时同时存在,因此,使用这种技巧则需要有额外的磁盘空间可临时使用,当索引建完后把老索引删除,如果没有成功,也不会影响原来的索引。利用这种办法可以用来将一个索引移到新的表空间。

    Alter index index_name rebuild tablespace tablespace_name 。

    这个命令的执行步骤如下:

    首先,逐一读取现有索引,以获取索引的关键字。

    其次,按新的结构填写临时数据段。

    最后,一旦操作成功,删除原有索引树,降临时数据段重命名为新的索引。

    需要注意的是alter index index_name rebuild 命令中必须使用tablespace字句,以保证重建工作是在现有索引相同的表空间进行。

     

    2.3、alter index index_name coalesce

    使用带有coalesce参数时重建期间不需要额外空间,它只是在重建索引时将处于同一个索引分支内的叶块拼合起来,这最大限度的减少了与查询过程中相关的潜在的加锁问题,但是,coalesce选项不能用来将一个索引转移到其他表空间。

     

    八、其他

    1、truncate 分区操作和truncate 普通表的区别?

    1.1、Truncate 分区操作会导致全局索引失效; truncate 普通表对索引没有影响;

    1.2、Truncate 分区操作不会释放全局索引中的空间,而truncate 普通表会释放索引所占空间;

    2、rename 表名操作对索引没有影响,因为rename操作只是更改了数据字典,表中数据行的rowid并没有发生变化

     

    总结:

    1、判断是否需要重建索引:

     

    SQL>analyze index index_name validate structure;

    SQL> select height,DEL_LF_ROWS/LF_ROWS from index_stats;

    ( 或 Select index_name,blevel from dba_indexes where blevel>=4 );

    说明 : 当查询出来的 height>=4 或者 DEL_LF_ROWS/LF_ROWS>0.2 的场合 , 该索引考虑重建 ;

     

    2 、重建索引方法 :

    方法一、

    Alter index index_name rebuild tablespace tablespace_name;

     

    优点:是快速重建索引的一种有效的办法,可以用来将一个索引移到新的表空间。

     

    缺点:重建期间需要额外空间。

     

    方法二、

    alter index index_name coalesce;

     

    优点:重建期间不需要额外空间。

     

    缺点:coalesce选项不能用来将一个索引转移到其他表空间。

    展开全文
  • Oracle 建立索引SQL优化

    千次阅读 多人点赞 2018-02-11 09:53:22
    索引有单列索引和复合索引之说。 建设原则:  1、索引应该经常建在Where 子句经常用到的列上。如果某个大表经常使用某个字段进行查询,并且检索行数小于总表行数的5%。则应该考虑。  2、对于两表连接的字段,...

     

    一、建立数据库索引:

    索引有单列索引和复合索引之说。

    建设原则:

     1、索引应该经常建在Where 子句经常用到的列上。如果某个大表经常使用某个字段进行查询,并且检索行数小于总表行数的5%。则应该考虑。

     2、对于两表连接的字段,应该建立索引。如果经常在某表的一个字段进行Order By 则也经过进行索引。

     3、不应该在小表上建设索引。

    优缺点:
     1、索引主要进行提高数据的查询速度。 当进行DML时,会更新索引。因此索引越多,则DML越慢,其需要维护索引。 因此在创建索引及DML需要权衡。

       2、当一个表的索引达到4个以上时,ORACLE的性能可能还是改善不了,因为OLTP系统每表超过5个索引即会降低性能,而且在一个sql 中, Oracle 从不能使用超过 5个索引

       3、索引可能产生碎片,因为记录从表中删除时,相应也从表的索引中删除.表释放的空间可以再用,而索引释放的空间却不能再用.频繁进行删除操作的被索引的表,应当阶段性地重建索引,以避免在索引中造成空间碎片,影响性能.在许可的条件下,也可以阶段性地truncate表,truncate命令删除表中所有记录,也删除索引碎片. (建立索引影响了删除和更新操作)

    创建索引:
     单一索引:Create Index <Index-Name> On <Table_Name>(Column_Name);

     复合索引:Create Index <Index-Name> On emp(deptno,job); —>在emp表的deptno、job列建立索引。

      select * from emp where deptno=66 and job='sals' ->走索引。

      select * from emp where deptno=66 OR job='sals' ->将进行全表扫描。不走索引

      select * from emp where deptno=66 ->走索引。

      select * from emp where job='sals' ->进行全表扫描、不走索引。

      如果在where 子句中有OR 操作符或单独引用Job 列(索引列的后面列) 则将不会走索引,将会进行全表扫描。

          同时在Oracle里用PL/SQL的F5可以对整个SQL查询来判断没加索引前和加完索引后的用时。

     

    索引失效的情况:
     ① Not Null/Null 如果某列建立索引,当进行Select * from emp where depto is not null/is null。 则会是索引失效。
     ② 索引列上不要使用函数,SELECT Col FROM tbl WHERE substr(name ,1 ,3 ) = 'ABC'  或 SELECT Col FROM tbl WHERE name LIKE '%ABC%'  而 SELECT Col FROM tbl WHERE name LIKE 'ABC%' 会使用索引。

     ③ 索引列上不能进行计算SELECT Col FROM tbl WHERE col / 10 > 10 则会使索引失效,应该改成SELECT Col FROM tbl WHERE col > 10 * 10

     ④ 索引列上不要使用NOT ( != 、 <> )如:SELECT Col FROM tbl WHERE col ! = 10 应该改成:SELECT Col FROM tbl WHERE col > 10 OR col < 10 。

     

    二、关于SQL 性能优化

     

    (一)ORACLE 性能优化主要方法
    ⑴硬件升级(CPU、内存、硬盘):

         CPU:在任何机器中CPU的数据处理能力往往是衡量计算机性能的一个标志,并且ORACLE是一个提供并行能力的数据库系统,如果运行队列数目超过了CPU处理的数目,性能就会下降;
         内存:衡量机器性能的另外一个指标就是内存的多少了,在ORACLE中内存和我们在建数据库中的交换区进行数据的交换,读数据时,磁盘I/O必须等待物理I/O操作完成,在出现ORACLE的内存瓶颈时,我们第一个要考虑的是增加内存,由于I/O的响应时间是影响ORACLE性能的主要参数;
         网络条件:NET*SQL负责数据在网络上的来往,大量的SQL会令网络速度变慢。比如10M的网卡和100的网卡就对NET*SQL有非常明显的影响,还有交换机、集线器等等网络设备的性能对网络的影响很明显,建议在任何网络中不要试图用3个集线器来将网段互联。
     

    ⑵版本及参数设置


    ⑶应用程序设计(框架、调用方式---源代码)

         程序设计中的一个著名定律是20%的代码用去了80%的时间;
         两种方式优化:源代码的优化和SQL语句的优化。源代码的优化在时间成本和风险上代价很高;另一方面,源代码的优化对数据库系统性能的提升收效有限。
         DBMS处理查询计划的过程是这样的:在做完查询语句的词法、语法检查之后,将语句提交给DBMS的查询优化器,优化器做完代数优化和存取路径的优化之后,由预编译模块对语句进行处理并生成查询规划,然后在合适的时间提交给系统处理执行,最后将执行结果返回给用户。 


    ⑷SQL 语句优化:

     

    当Oracle数据库拿到SQL语句时,其会根据查询优化器分析该语句,并根据分析结果生成查询执行计划。
    也就是说,数据库是执行的查询计划,而不是Sql语句。
    查询优化器有rule-based-optimizer(基于规则的查询优化器) 和Cost-Based-optimizer(基于成本的查询优化器)。
    其中基于规则的查询优化器在10g版本中消失。

    对于规则查询,其最后查询的是全表扫描。而CBO则会根据统计信息进行最后的选择。


    先执行From ->Where ->Group By->Order By,所以尽量避免全表扫。

    执行From 字句是从右往左进行执行。因此必须选择记录条数最少的表放在右边。  

    对于Where字句其执行顺序是从后向前执行、因此可以过滤最大数量记录的条件必须写在Where子句的末尾,而对于多表之间的连接,则写在之前。因为这样进行连接时,可以去掉大多不重复的项。  

    ④SELECT子句中避免使用(*)ORACLE在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.但是在count(*)和count(1)的执行中不需要遵守上述内容,速度经过我测试是相同的。

    用UNION替换OR(适用于索引列)

      union:是将两个查询的结果集进行追加在一起,它不会引起列的变化。 由于是追加操作,需要两个结果集的列数应该是相关的,并且相应列的数据类型也应该相当的。union 返回两个结果集,同时将两个结果集重复的项进行消除。 如果不进行消除,用UNOIN ALL.

       通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 
       如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引.

      高效:
      SELECT ID , NAME
      FROM LOCATION
      WHERE ID = 1
      UNION
      SELECT ID , NAME
      FROM LOCATION
      WHERE NAME = “SUQI356”

      低效:
      SELECT ID , NAME 
      FROM LOCATION
      WHERE ID = 1 OR NAME = “SUQI356”
      如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.

    ⑥用EXISTS替代IN、用NOT EXISTS替代NOT IN和用(+)比用NOT IN更有效率
    在许多基于基础表的查询中, 为了满足一个条件, 往往需要对另一个表进行联接. 在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 
    在子查询中, NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下, NOT IN都是最低效的(因为它对子查询中的表执行了一个全表遍历). 
    为了避免使用NOT IN, 我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.

    例子:

    高效: SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’)

    低效: SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB’)

    ⑦ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理,在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表 。

    ⑧避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤,这个处理需要排序、总计等操作; 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.

      ⑨尽可能使用varchar代替char,因为变长字段存储空间小,在一个相对较小的字段内搜索效率要高。

      ⑩使用临时表来存储

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

    万次阅读 2014-04-15 15:45:07
    当然Oracle官方也有自己的观点,我们很多DBA也是遵循这一准则来重建索引,那就是Oracle建议对于索引深度超过4级以及已删除的索引条目至少占有现有索引条目总数的20% 这2种情形下需要重建索引。近来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树索引,建议重建

     

    7、相关参考
        Oracle 聚簇因子(Clustering factor) 

        Oracle 索引监控(monitor index)
        Oracle 索引监控与外键索引 
        收集统计信息导致索引被监控 
        Oracle 监控索引的使用率
        NULL 值与索引(一)
        NULL 值与索引(二)
        函数使得索引列失效

        Oracle 索引质量分析


        Oracle&nbsp;牛鹏社    

    展开全文
  • 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 索引修复 Oracle索引重建

    千次阅读 2018-08-15 21:34:26
    当数据库出现坏块而坏块所涉及对象为索引时,我们一般进行修复索引的方法是重建索引。 相对其它坏块,索引坏块修复起来最容易的。不过在修复前,我们需要确认这个坏块确实来自于某索引。 因此,这里我们会介绍一些...
  • 关于 Oracle 分区索引的失效和重建

    千次阅读 2018-11-07 14:16:40
    SQL&gt; create table t as select object_id,object_name from dba_objects; 表已创建。 SQL&gt; select min(object_id),max(object_id) from t; MIN(OBJECT_ID) MAX(OBJECT_ID) -------------- -------...
  • oracle 重建索引 回收表空间 《PS:留下小赞赞,比心比心》 BEGIN /*################## 收缩表的空余存储空间 把查询出的sql按顺序执行即可 ####################*/ FOR i IN (select 'alter ...
  • 部分内容转载:&nbsp;... 所有执行的结果是脚本命令集合,可以用来创建索引;&nbsp;...a)在plsql中使用execute &...immediate 'alter table J3_...b)sql窗口中,复制出来可直接执行; 1)查询指定表的索引...
  • oracle中,重建普通表上的索引很简单。要重建特定索引,只需执行如下sql命令: ALTER INDEX INDEX_NAME Rebuild; 这里,INDEX_NAME代表索引的名字,下同。 如果重建某个表上的全部索引,执行如下PL/SQL 代码...
  • 上一篇文章中我们了解了oracle普通表转化为分区表的方法的相关内容,接下来的这篇文章,我们将探讨oracle分区索引的失效和重建问题,提供了相关代码示例供大家参考,具体如下。 --创建测试表 SQL> create table t ...
  • Oracle 重建索引脚本

    千次阅读 2014-04-21 17:52:32
    索引是提高数据库查询性能的有力武器。没有索引,就好比图书馆没有图书标签...N久以前Oracle建议我们定期重建那些高度为4,已删除的索引条目至少占有现有索引条目总数的20%的这些表重建索引。但Oracle现在强烈建议不要
  • sqlplus / as sysdba spool c:\rebuild.sql select 'alter index '||owner||'.'||index_name||' rebuild;' from dba_ind...
  • 按用户批量重建索引: 按用户将此用户下面非临时表上面的索引全部重建,此过程建议在SYS用户下面执行:  CREATE OR REPLACE PROCEDURE BATCH_REBUILD_INDEX(USER_NAME IN VARCHAR2) IS  S_SQL VARCHAR2(500);  ...
  • 如果你管理的ORACLE数据库下某些应用项目有大量的修改删除操作, 数据索引是需要周期性的重建的. 它不仅可以提高查询性能, 还能增加索引表空间空闲空间大小. 在ORACLE里大量删除记录后, 表和索引里占用的数据块空间...
  • 数据库使用的oracle数据库,可视化管理工具使用的PLSQL 查看表中的索引 选中表,右键, view -->indexes就可以查看到表中的索引 image.png 这里只能看到表中那些数据列有索引,但是如果先知道查询语句是否...
  • oracle拆分分区表及重建索引

    千次阅读 2020-05-23 12:05:02
    四、重建索引 分区时,语句务必带上“update index”选项,否则拆分以后,索引不可用,报ORA-01502错误,新记录会插不进去!(错误ORA-01502: 索引或这类索引的分区处于不可用状态)。但网上许多教程,根本没提这...
  • oracle重建索引

    2017-04-01 13:59:59
    数据库的索引如果有比较频繁的Delete操作,将可能导致索引产生很多碎片, 所以,在有的时候,需要对所有的索引重新REBUILD,以便合并索引块,减少碎 片,提高查询速度。 SQL>setheadingoff SQL>setfeedbackoff...
  • 今天在手工配置Oracle11g的EM时总是报如下错误: WARNING:ORA-00001:uniqueconstraint(SYSMAN.PARAMETERS_PRIMARY_KEY)violated ORA-06512:at”SYSMAN.EMD_LOADER”,line4986 ORA-06512:atline1 以Metalink上也没有...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 26,751
精华内容 10,700
关键字:

oracle重建索引sql