精华内容
下载资源
问答
  • oracle重建索引

    2014-05-08 15:27:47
    oracle重建索引  (2011-08-12 16:48:16) 转载▼ 标签:  杂谈 分类: oracle 一:考虑重建索引的场合 1:表上频繁发生update,delete操作 2:表上发生了alter table ..move...

    一:考虑重建索引的场合
    1:表上频繁发生update,delete操作
    2:表上发生了alter table ..move操作(move操作导致了rowid变化)


    二:判断重建索引的标准
      索引重建是否有必要,一般看索引是否倾斜的严重,是否浪费了空间;
      那应该如何才可以判断索引是否倾斜的严重,是否浪费了空间,如下:
    1,        对索引进行结构分析
    Analyze index indexname validate structure;
    2, 在执行步骤1的session中查询index_stats表,不要到别的session去查询
       select height,DEL_LF_ROWS/LF_ROWS from index_stats;
    3, 在步骤2查询出来的height>=4或者DEL_LF_ROWS/LF_ROWS>0.2的场合,该索引考虑重建;
    Example:
       SQL> select count(*) from test_index;
             COUNT(*)
    ----------
                       2072327
    SQL> analyze index pk_t_test validate structure;
    Index analyzed
            SQL> select height,DEL_LF_ROWS/LF_ROWS from index_stats;
                         HEIGHT         DEL_LF_ROWS/LF_ROWS
    ---------- -------------------
                               0
    SQL> delete from test_index where rownum<250000;
              249999 rows deleted
            SQL> select height,DEL_LF_ROWS/LF_ROWS from index_stats;
                        HEIGHT                 DEL_LF_ROWS/LF_ROWS
    ---------- -------------------
                                               0
            SQL> analyze index pk_t_test validate structure;
                    Index analyzed
    SQL> select height,DEL_LF_ROWS/LF_ROWS from index_stats;
                        HEIGHT                  DEL_LF_ROWS/LF_ROWS
    ---------- -------------------
                     3                           0.0777430939338362


    三:重建索引的方式
        1:drop 原来的索引,然后再创建索引;
        2:alter index indexname rebuild (online);
        方式一:耗时间,无法在24*7环境中实现
        方式二:比较快,可以在24*7环境中实现
        建议使用方式二


    四:alter index rebuid内部过程和注意点
        1:alter index rebuild 和alter index rebuild online的区别
    (1)        扫描方式不同
    Rebuild以index fast full scan(or table full scan)方式读取原索引中的数据来构建一个新的索引,有排序的操作; rebuild online 执行表扫描获取数据,有排序的操作;
    Rebuild  方式 (index fast full scan  or  table full scan  取决于统计信息的cost)
    Eg1:
    SQL> explain plan for alter index idx_policy_id2 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|       |
    ---------------------------------------------------------------------
    Eg2:
    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 |


    Eg3: (注意和Eg1比较)
    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操作,rebuil online 不会阻塞dml操作;
    (3)        rebuild online时系统会产生一个SYS_JOURNAL_xxx的IOT类型的系统临时日志表,所有rebuild online时索引的变化都记录在这个表中,当新的索引创建完成后,把这个表的记录维护到新的索引中去,然后drop掉旧的索引,rebuild online就完成了。

    注意点:
    1,        执行rebuild操作时,需要检查表空间是否足够;
    2,        虽然说rebuild online操作允许dml操作,但是还是建议在业务不繁忙时间段进行;
    3,        Rebuild操作会产生大量redo log ;

    五:重建分区表上的分区索引
       1:重建分区索引方法:
         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   

    valid:当前索引有效
    N/A :分区索引有效
    unusable:索引失效

     

    七:术语

      高基数:简单理解就是表中列的不同值多
      低基数:建单理解就是表中的列的不同值少
      以删除的叶节点数量:指得是数据行的delete操作从逻辑上删除的索引节点的数量,要记住oracle在删除数据行后,将“死“节点保留在索引中,这样做可以加快sql删除操作的速度,因此oracle删除数据行后可以不必重新平衡索引。
       索引高度:索引高度是指由于数据行的插入操作而产生的索引层数,当表中添加大量数据时,oracle将生成索引的新层次以适应加入的数据行,因此,oracle索引可能有4层,但是这只会出现在索引数中产生大量插入操作的区域。Oracle索引的三层结构可以支持数百万的项目,而具备4层或是更多层的需要重建。
        每次索引访问的读取数:是指利用索引读取一数据行时所需要的逻辑I/O操作数,逻辑读取不必是物理读取,因为索引的许多内容已经保存在数据缓冲区,然而,任何数据大于10的索引都需要重建。

        那么什么时候重建呢?我们可以利用analyze index …….. compute statistics 对表进行分析。然后察看dba_indexes中的blevel。这列是说明索引从根块到叶快的级别,或是深度。如果级别大于等于4。则需要重建,如下:
    Select index_name,blevel from dba_indexes where blevel>=4.
       另一个从重建中受益的指标显然是当该索引中的被删除项占总的项数的百分比。如果在20%以上时,也应当重建,如下
    SQL>anlyze index ------ validate structure
    SQL>select (del_lf_rows_len/lf_rows_len)*100 from index_stats where name=’------‘
    就能看到是否这个索引被删除的百分比。
    上面只是判断,那么,怎样重建会更好呢?
    建索引的办法:
    a.        删除并从头开始建立索引。
    b.        使用alter index -------- rebuild 命令重建索引
    c.        使用alter index -------- coalesce命令重建索引。
    下面讨论一下这三种方法的优缺点:
    1).删除并从头开始建索引:方法是最慢的,最耗时的。一般不建议。
    2).Alter index ---- rebuild 快速重建索引的一种有效的办法,因为使用现有索引项来重建新索引,如果客户操作时有其他用户在对这个表操作,尽量使用带online参数来最大限度的减少索引重建时将会出现的任何加锁问题,alter index ------- rebuild online.但是,由于新旧索引在建立时同时存在,因此,使用这种技巧则需要有额外的磁盘空间可临时使用,当索引建完后把老索引删除,如果没有成功,也不会影响原来的索引。利用这种办法可以用来将一个索引以到新的表空间。
    Alter index ------ rebuild  tablespace -----。
      这个命令的执行步骤如下:
       首先,逐一读取现有索引,以获取索引的关键字。
       其次,按新的结构填写临时数据段。
       最后,一旦操作成功,删除原有索引树,降临时数据段重命名为新的索引。
       需要注意的是alter index ---rebuild 命令中必须使用tablespace字句,以保证重建工作是在现有索引相同的表空间进行。
    3).alter index ----- coalesce 使用带有coalesce参数时重建期间不需要额外空间,它只是在重建索引时将处于同一个索引分支内的叶块拼合起来,这最大限度的减少了与查询过程中相关的潜在的加锁问题,但是,coalesce选项不能用来讲一个索引转移到其他表空间。

    八:其他
       1:truncate 分区操作和truncate 普通表的区别
          Truncate 分区操作会导致全局索引失效; truncate 普通表对索引没有影响;
          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重建索引

    万次阅读 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 重建索引

    千次阅读 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重建索引Shell脚本、SQL脚本分享,索引是提高数据库查询性能的有力武器,定期重建索引是很有必要的事情,需要的朋友可以参考下
  • oracle 重建索引初学者oracle6-oracle 重建索引www.myexceptions.net网友分享于:2013-07-23浏览:3次菜鸟oracle6--oracle 重建索引转自:http://database.51cto.com/art/201011/233500.htm 谢谢Oracle重建索引操作...

    当前位置:我的异常网» 数据库 » 初学者oracle6-oracle 重建索引

    初学者oracle6-oracle 重建索引

    www.myexceptions.net  网友分享于:2013-07-23  浏览:3次

    菜鸟oracle6--oracle 重建索引

    转自:http://database.51cto.com/art/201011/233500.htm  谢谢

    Oracle重建索引操作大家经常会用到,下面就为您详细介绍Oracle重建索引方面的知识,供您参考,如果您对此方面感兴趣的话,不妨一看。

    如果你管理的Oracle数据库下某些应用项目有大量的修改删除操作, 数据索引是需要周期性的重建的.

    它不仅可以提高查询性能, 还能增加索引表空间空闲空间大小. 在ORACLE里大量删除记录后, 表和索引里占用的数据块空间并没有释放. Oracle重建索引可以释放已删除记录索引占用的数据块空间. 转移数据, 重命名的方法可以重新组织表里的数据.

    下面是可以按ORACLE用户名生成Oracle重建索引的SQL脚本:

    SET ECHO OFF;

    SET FEEDBACK OFF;

    SET VERIFY OFF;

    SET PAGESIZE 0;

    SET TERMOUT ON;

    SET HEADING OFF;

    ACCEPT username CHAR PROMPT 'Enter the index username: ';

    spool /oracle/rebuild_&username.sql;

    SELECT

    'REM +-----------------------------------------------+' || chr(10) ||

    'REM | INDEX NAME : ' || owner || '.' || segment_name

    || lpad('|', 33 - (length(owner) + length(segment_name)) )

    || chr(10) ||

    'REM | BYTES : ' || bytes

    || lpad ('|', 34-(length(bytes)) ) || chr(10) ||

    'REM | EXTENTS : ' || extents

    || lpad ('|', 34-(length(extents)) ) || chr(10) ||

    'REM +-----------------------------------------------+' || chr(10) ||

    'ALTER INDEX ' || owner || '.' || segment_name || chr(10) ||

    'REBUILD ' || chr(10) ||

    'TABLESPACE ' || tablespace_name || chr(10) ||

    'STORAGE ( ' || chr(10) ||

    ' INITIAL ' || initial_extent || chr(10) ||

    ' NEXT ' || next_extent || chr(10) ||

    ' MINEXTENTS ' || min_extents || chr(10) ||

    ' MAXEXTENTS ' || max_extents || chr(10) ||

    ' PCTINCREASE ' || pct_increase || chr(10) ||

    ');' || chr(10) || chr(10)

    FROM dba_segments

    WHERE segment_type = 'INDEX'

    AND owner='&username'

    ORDER BY owner, bytes DESC;

    spool off;

    如果你用的是WINDOWS系统, 想改变输出文件的存放目录, 修改spool后面的路径成:

    spool c:\oracle\rebuild_&username.sql;

    如果你只想对大于max_bytes的索引重建索引, 可以修改上面的SQL语句:

    在AND owner='&username' 后面加个限制条件 AND bytes> &max_bytes

    如果你想修改索引的存储参数, 在Oracle重建索引rebuild_&username.sql里改也可以. 比如把pctincrease不等于零的值改成是零. 生成的rebuild_&username.sql文件我们需要来分析一下, 它们是否到了需要重建的程度:

    分析索引,看是否碎片严重

    SQL>ANALYZE INDEX &index_name VALIDATE STRUCTURE;

    col name heading 'Index Name' format a30

    col del_lf_rows heading 'Deleted|Leaf Rows' format 99999999

    col lf_rows_used heading 'Used|Leaf Rows' format 99999999

    col ratio heading '% Deleted|Leaf Rows' format 999.99999

    SELECT name,

    del_lf_rows,

    lf_rows - del_lf_rows lf_rows_used,

    to_char(del_lf_rows / (lf_rows)*100,'999.99999') ratio

    FROM index_stats where name = upper('&index_name');

    当删除的比率大于15 - 20% 时,肯定是需要索引重建的. 经过删改后的rebuild_&username.sql文件我们可以放到ORACLE的定时作业里:

    比如一个月或者两个月在非繁忙时间运行. 如果遇到ORA-00054错误, 表示索引在的表上有锁信息, 不能重建索引. 那就忽略这个错误, 看下次是否成功. 对那些特别忙的表要区别对待, 不能用这里介绍的方法, 还要把它们的索引从rebuild_&username.sql里删去。

    文章评论

    展开全文
  • Oracle 重建索引

    千次阅读 2012-12-26 16:23:16
    // 如果查询为空,那么索引失效. select max(索引字段) from 表名; // 根据表名查询对应的索引名. select index_name,table_name from user_indexes ...// 重建索引 alter index 索引名 rebuild;
  • Oracle重建索引

    千次阅读 2010-06-16 22:02:00
    在rebuild期间,用户还可以使用原来的索引,并且rebuild新的索引时也会利用原来的索引信息,这样重建索引会快一些。 索引在重建时,查询仍然可以使用旧索引。实际上,oracle在rebuild时,在创建新索引过程中,并不...
  • oracle重建索引(二)

    2019-07-21 14:39:02
    重建索引有多种方式,如drop and re-create、rebuild、rebuild online等。下面简单比较这几种方式异同以及优缺点:相关文章:oracle重建索引(一)二、rebuild 和rebuild onli...
  • ORACLE重建索引总结

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

    2018-11-07 14:52:53
    一、重建索引的前提 1、表上频繁发生update,delete操作; 2、表上发生了alter table ..move操作(move操作导致了rowid变化)。 &nbsp; 二、重建索引的标准 1、索引重建是否有必要,一般看索引是否倾斜的...
  • Oracle重建索引操作大家经常会用到,下面就为您详细介绍Oracle重建索引方面的知识,供您参考,如果您对此方面感兴趣的话,不妨一看。 如果你管理的Oracle数据库下某些应用项目有大量的修改删除操作, 数据索引是...
  • Oracle 重建索引脚本

    千次阅读 2014-04-21 17:52:32
    索引是提高数据库查询性能的有力武器。没有索引,就好比图书馆没有图书标签...N久以前Oracle建议我们定期重建那些高度为4,已删除的索引条目至少占有现有索引条目总数的20%的这些表重建索引。但Oracle现在强烈建议不要
  • Oracle 重建索引的必要性

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

    千次阅读 2008-04-08 17:46:00
    下面简单比较这几种方式异同以及优缺点: 相关文章: oracle重建索引(一) oracle重建索引(二) 三、rebuild和rebuild online的数据源 网上一直有这样一个说法:重建索引是以原索引作为数据源的。那么,这种说法...

空空如也

空空如也

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

oracle重建索引