精华内容
下载资源
问答
  • ORACLE重建索引总结

    2011-12-13 10:32:28
    使用带有coalesce参数时重建期间不需要额外空间,它只是在重建索引时将处于同一个索引分支内的叶块拼合起来,这最大限度的减少了与查询过程中相关的潜在的加锁问题,但是,coalesce选项不能用来将一个索引转移到其他...
  • 主要介绍了Oracle重建索引Shell脚本、SQL脚本分享,索引是提高数据库查询性能的有力武器,定期重建索引是很有必要的事情,需要的朋友可以参考下
  • 主要介绍了oracle分区的失效和重建代码示例,小编觉得挺不错的,这里分享给大家,供需要的朋友参考。
  • ORACLE数据库重建索引

    2013-09-29 11:06:27
    导致索引失效: 1、表上频繁发生update,delete操作; 2、表上发生了alter table ..move操作(move操作导致了rowid变化)。
  • 主要介绍了Oracle关于重建索引争论的总结,本文总结了重建索引的理由、重建索引的本质、反对重建索引的理由等内容,需要的朋友可以参考下
  • oracle 重建索引

    千次阅读 2019-03-21 15:33:26
    -- Create table ...需要重建索引: alter index PK_PHONEDICT rebuild tablespace USERS ; 注: PK_PHONEDICT 为索引名, USERS 为表空间;不要把列名当成索引名 如图所示:
    -- Create table 
    create table PHONEDICT
    (
      ID        INTEGER not null,
      DICTVALUE VARCHAR2(200) not null,
      TIME      DATE default sysdate,
      DICTTYPE  INTEGER,
      USERID    VARCHAR2(60),
      STATUS    NUMBER(2)
    )
    tablespace TEST_DATA
      pctfree 10
      initrans 1
      maxtrans 255
      storage
      (
        initial 128K
        next 1M
        minextents 1
        maxextents unlimited
      );
    -- Create/Recreate primary, unique and foreign key constraints (一下是三个索引)
    alter table PHONEDICT
      add constraint PK_PHONEDICT primary key (ID)
      using index 
      tablespace USERS
      pctfree 10
      initrans 2
      maxtrans 255
      storage
      (
        initial 64K
        next 1M
        minextents 1
        maxextents unlimited
      );
    -- Create/Recreate indexes 
    create index IDX_DICTTYPE on PHONEDICT (DICTTYPE)
      tablespace USERS
      pctfree 10
      initrans 2
      maxtrans 255
      storage
      (
        initial 64K
        next 1M
        minextents 1
        maxextents unlimited
      );
    create index IDX_DICTVALUE on PHONEDICT (DICTVALUE)
      tablespace USERS
      pctfree 10
      initrans 2
      maxtrans 255
      storage
      (
        initial 128K
        next 1M
        minextents 1
        maxextents unlimited
      );

    当我在执行insert into 的时候发现 PK_PHONEDICT 索引失效了,

    需要重建索引:

    alter index PK_PHONEDICT rebuild tablespace USERS;

    注:PK_PHONEDICT 为索引名,USERS 为表空间;不要把列名当成索引名

    如图所示:

    展开全文
  • ORACLE重建索引详解

    千次阅读 2018-07-09 22:22:12
    一、重建索引的前提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环境中实现,不建议使用。

    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选项不能用来将一个索引转移到其他表空间。
    转自 http://ftc007.blog.163.com/blog/static/196571650201211272380962/
    展开全文
  • ORACLE重建索引

    万次阅读 2018-04-23 18:42:27
    重建索引 alter index 索引名称 rebuild tablespace DELL_SPACE online; 索引分析 analyze index 索引名称 validate structure; select (del_lf_rows_len/lf_rows_len) from index_stats where name='索引名称';

    重建索引(重建索引方法很多,不同方法的适用条件不通,此处只列出一种)

    alter index 索引名称 rebuild tablespace DELL_SPACE online;

    索引分析

    analyze index 索引名称 validate structure;
    select (del_lf_rows_len/lf_rows_len) from index_stats where name='索引名称';

    查询索引深度

    Select index_name,blevel from dba_indexes where blevel>=4; //大于等于4时,需要重建索引
    Select index_name,blevel from dba_indexes where index_name='索引名称';

    查询索引

    //表上有哪些索引,状态如何
    select status,T.* from user_indexes T where table_name=upper('表名');
    //查询某个索引的状态
    select status,index_name from user_indexes s where index_name=upper('索引名称');
    //查询分区索引
    select status,index_name from user_ind_partitions s where index_name=upper('索引名称');
    
    展开全文
  • 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 牛鹏社    

    展开全文
  • oracle 索引修复 Oracle索引重建

    千次阅读 2018-08-15 21:34:26
    当数据库出现坏块而坏块所涉及对象为索引时,我们一般进行修复索引的方法是重建索引。 相对其它坏块,索引坏块修复起来最容易的。不过在修复前,我们需要确认这个坏块确实来自于某索引。 因此,这里我们会介绍一些...
  • Oracle ~ 重建索引(包括分区)尽量不要重建索引真正需要重建索引的情形如何重建索引1、drop 原来的索引,然后再创建索引2 、直接重建2.1 alter index rebuild 和alter index rebuil online的区别注意点:重建分区表...
  • NULL 博文链接:https://hackpro.iteye.com/blog/1845366
  • 作为DBA,针对数据库索引创建及重建需要了解一下索引创建的原则及标准,判断是否需要停止应用以及检查创建索引的表大小,选择合适的创建方式。 一、索引创建前的检查 1、检查表段大小 select segment_name, bytes/...
  • Create unique index idx1_col on table_NAME... ---对于大的索引,注意空间问题. ALTER INDEX NMOSDB.IX1_ALARM_CLR REBUILD ONLINE parallel 5 compute statistics; ALTER INDEX NMOSDB.IX1_ALARM_CLR noparallel;
  • 关于 Oracle 分区索引的失效和重建

    千次阅读 2018-11-07 14:16:40
    ADD (HASH) COALESCE (HASH) DROP EXCHANGE MERGE MOVE SPLIT TRUNCATE 之后需要对失效索引进行重建,也可以在删除分区表的时候指定 UPDATE INDEXES 直接进行索引重建。 来自 “ ITPUB博客 ” ,链接:...
  • oracle 重建索引 回收表空间 《PS:留下小赞赞,比心比心》 BEGIN /*################## 收缩表的空余存储空间 把查询出的sql按顺序执行即可 ####################*/ FOR i IN (select 'alter ...
  • 所有执行的结果是脚本命令集合,可以用来创建索引;  a)在plsql中使用execute  immediate 'alter table J3_nsrxx NOLOGGING';  b)sql窗口中,复制出来可直接执行; 1)查询指定表的索引...
  • oracle在线创建索引和重组索引
  • Oracle表与索引的分析及索引重建

    千次阅读 2018-07-13 15:05:19
    Oracle表与索引的分析及索引重建备忘:Oracle表与索引的分析及索引重建
  • oracle中,重建普通表上的索引很简单。要重建特定索引,只需执行如下sql命令: ALTER INDEX INDEX_NAME Rebuild; 这里,INDEX_NAME代表索引的名字,下同。 如果重建某个表上的全部索引,执行如下PL/SQL 代码...
  • 表数据经过重新抽取或者大量导入的时候会破坏索引,下列方法会修复所有索引 create or replace procedure p_rebuild_all_index (tablespace_name in varchar2,--这里是表空间名,如果不改变表空间,可以传入null ...
  • 浅谈oracle重建索引

    2011-12-28 11:09:30
    索引操作的完整,特别对于索引的相关验证介绍非常实用,在重建索引时可以提供给使用者完整的操作方法。
  • oracle 重建分区索引

    2017-07-21 16:13:00
    重建分区表的索引回报:  ORA-14086:不能将分区索引作为整体重建。 so,重建语音必须指定分区。 alter index index_name rebuild partition partition_name; 当然,主键索引可以直接重建。 不过,如果你....

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 30,001
精华内容 12,000
关键字:

oracle重建索引需要多久