`

ORACLE重建索引详解

阅读更多

一、重建索引的前提

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选项不能用来将一个索引转移到其他表空间。

分享到:
评论

相关推荐

    Oracle解决索引碎片功能.txt

    在Oracle中,通常使用`ALTER INDEX`命令来重建索引,从而消除碎片。具体命令格式如下: ```sql ALTER INDEX ind_1 REBUILD [ONLINE] [TABLESPACE name]; ``` - **ind_1**:待重建的索引名称。 - **ONLINE**:表示...

    Oracle Index 索引介绍

    **Oracle索引详解** Oracle索引是数据库管理系统中用于提高查询效率的重要数据结构。它的工作原理类似于书籍的目录,允许数据库系统快速定位到所需的数据行,而无需扫描整个表。索引的存在使得对大量数据的查找、...

    Oracle重构索引

    - **定义**:在线重建索引是一种高级的技术手段,可以在不影响业务正常运行的情况下对索引进行重构。 - **特点**: - 在重建过程中,索引仍然是可读可写的,不会影响到业务操作。 - 适用于大型生产环境中的索引...

    oracle索引使用样例

    ### Oracle索引使用样例详解 #### 一、索引并行创建 在Oracle数据库中,并行创建索引可以显著提高创建索引的速度,尤其是在处理大量数据时。下面的SQL语句展示了如何并行创建一个索引: ```sql CREATE INDEX IDX_GD...

    oracle 索引创建

    ### Oracle索引创建详解 #### 一、Oracle索引的重要性及基本概念 在数据库系统中,索引是一种用于提高数据检索速度的数据结构。对于大型数据库系统而言,索引的合理设计与使用对于提升查询性能至关重要。Oracle...

    深入oracle分区索引的详解

    重建索引是一个重要的维护任务,可以通过`ALTER INDEX`语句完成。对于分区索引,可以逐个分区进行重建,如`ALTER INDEX idx_name REBUILD PARTITION index_partition_name [ONLINE NOLOGGING]`,对于非分区索引,...

    Oracle_sqlplus详解.

    1. `startup nomount`:不加载数据文件,仅启动实例,适合重建控制文件。 2. `startup mount dbname`:挂载数据库,但不打开数据文件,可以进行日志归档和介质恢复。 3. `startup open dbname`:完全打开数据库,...

    linux下oracle手动建实例

    ### Linux 下 Oracle 手动建实例详解 #### 一、概述 在Linux环境中手动构建Oracle实例是一项高级且复杂的任务,通常适用于需要定制化配置或特殊需求的场景。本文档将根据给定的信息,详细介绍如何在Linux环境下手动...

    高级oracle sql详解

    【Oracle SQL 高级知识详解】 Oracle SQL 是一种强大的数据库查询和编程语言,它提供了许多高级特性,使得数据库管理、数据分析以及应用开发更为高效。在Oracle数据库系统中,其层次结构与其他数据库略有不同,主要...

    oracle索引说明

    ### Oracle索引详解 在Oracle数据库管理中,索引是一种重要的数据结构,它能显著提升数据检索的速度。本文将从B-tree索引入手,详细解析其结构与特性,并结合具体的图解与实例来帮助读者更好地理解和掌握Oracle索引...

    手动创建Oracle数据库详解

    定期执行数据库维护任务,如备份、统计信息收集、索引重建,能确保数据库高效运行。 最后,理解Oracle的网络配置是必要的。Oracle使用Net Services(也称为Oracle Net或TNS)来连接数据库实例和客户端应用。配置...

    ORACLE检查找出损坏索引(Corrupt Indexes)的方法详解

    3. **修复**:根据损坏的严重程度,可能选择重建索引、使用RMAN的修复功能或通过DBMS_REPAIR包。 4. **验证**:修复后,应再次运行分析和检查操作,以确保索引现在是健康的。 5. **监控**:设置监控机制,定期检查...

    Oracle表空间操作详解.docx

    移动表空间可能需要重建索引。 4. **删除表空间中的数据文件** 删除数据文件需谨慎,通常先将其设为离线: ```sql ALTER DATABASE DATAFILE 'fullpath_of_removed_DF' OFFLINE DROP; ``` 数据文件不会立即被...

    ORACLE学习手册和案例

    #### 1.2 索引详解 **1.2.1 索引介绍** 索引是一种特殊的数据库结构,用于提高数据检索的效率。在Oracle中,合理的索引设计对于提升系统的性能至关重要。 **1.2.1.1 索引的创建语法** 创建索引的基本语法如下: ...

    oracle查询表碎片

    2. **截断表后重建索引**:使用`TRUNCATE TABLE;`命令清空表数据,再通过`INSERT INTO ... SELECT FROM;`方式重新加载数据,并重建索引。这种方式可以彻底解决碎片问题,但需谨慎操作。 #### 七、结论 通过对给定...

    jf_Oracle_经典笔记.docx

    这篇文档主要涵盖了Oracle数据库的常见问题处理、环境安装配置、索引详解以及SQL优化等多个方面,对于理解和操作Oracle数据库非常有帮助。 一、常见问题记录 在数据库管理中,密码过期是常见的安全管理问题。为避免...

Global site tag (gtag.js) - Google Analytics