Oracle ~ 重建索引（包括分区）

最新推荐文章于 2024-09-11 08:43:55 发布
最新推荐文章于 2024-09-11 08:43:55 发布
阅读量4.9k 收藏 23
点赞数 6
分类专栏： Oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/cai_and_luo/article/details/110920213
版权

Oracle ~ 重建索引（包括分区）

尽量不要重建索引

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重建索引详解
weixin_39789327的博客
04-03 1805
一、重建索引的前提1、表上频繁发生update,delete操作；2、表上发生了alter table ..move操作(move操作导致了rowid变化)。二、重建索引的标准1、索引重建是否有必要，一般看索引是否倾斜的严重，是否浪费了空间， 那应该如何才可以判断索引是否倾斜的严重，是否浪费了空间， 对索引进行结构分析(如下)：SQL>Analyze index index_name val...
Oracle 分区索引介绍和实例演示
12-16
当对表进行分区操作时，无需重建索引。本地索引可以是B树索引或位图索引，通常用于数据仓库环境。 - 全局分区索引：全局索引的分区方式与表不同，需要明确指定分区键的范围或散列值。全局索引必须是前缀索引，包含...
Oracle重建所有表和索引
weixin_33901843的博客
03-30 322
定义两个存储过程，先执行表移动，再执行索引重建。（如果记录很多，执行时间可能会很长，几个小时也有可能，建议在系统空闲时运行）：create or replace procedure p_remove_all_table (tablespace_name in varchar2)--这里是表空间名，如果不改变表空间，可以传入nullas  sqlt varchar(200);begin    --取...
ORACLE数据库重建索引
09-29
导致索引失效： 1、表上频繁发生update,delete操作； 2、表上发生了alter table ..move操作（move操作导致了rowid变化）。
Oracle索引常用操作详解
最新发布
promise524的博客
09-11 2388
在 Oracle 中，索引是一种提升查询性能的强大工具，但使用不当可能会对写操作带来负面影响。因此，了解如何创建、维护和删除索引，以及适当选择索引类型（如 B-Tree、位图、唯一索引等）至关重要。通过查看索引的使用情况，及时重建索引或删除不必要的索引，能更好地管理数据库的性能。
重建索引来释放索引所占的表空间
haiyang_tian20140616的专栏
11-15 1万+
重建索引来释放索引所占的表空间： "alter index F_HR_BENFIT_INX rebuild" 试了 一下确实可以，心得下回再写。 ============================================= 一、重建索引的前提 1、表上频繁发生update,delete操作； 2、表上发生了alter table ..move操作（move操作导致
索引重建的必要性与影响 (文档 ID 1525787.1)
cn_mos的专栏
10-25 703
文档内容   用途   适用范围   详细信息   参考 适用于: Oracle Database - Enterprise Edition - 版本 8.1.7.0 和更高版本 本文档所含信息适用于所有平台 用途 本文章将重点概述重建索引的各种影响。我们通
oracle重建索引(一)
08-05 233
导读： 　　重建索引有多种方式，如drop and re-create、rebuild、rebuild online等。下面简单比较这几种方式异同以及优缺点： 　　首先建立测试表及数据： 　　SQL> CREATE TAB...
oracle 重建分区索引
anfuyi5792的博客
07-21 2059
分区表的所有分区相当于一个单独的表。 创建在分区表上的索引，就相当于在所有分区上单独创建的索引（主键索引除外）。 重建分区表的索引回报： 　　ORA-14086：不能将分区索引作为整体重建。 so，重建语音必须指定分区。 alter index index_name rebuild partition partition_name; 当然，主键索引可以直接重建。 不...
oracle分区索引的失效和重建代码示例
09-09
Oracle分区索引是一种...综上所述，Oracle分区索引的管理包括创建、删除、重建等操作，这些操作对于维持数据库性能和正确性至关重要。在处理大型数据集时，理解和熟练掌握这些技巧能帮助DBA更有效地维护数据库系统。
深入oracle分区索引的详解
09-10
局部索引支持索引分区消除，这意味着如果查询条件包含索引分区键，数据库可以仅搜索相关的分区，提高查询效率。然而，局部索引不能提供全局的唯一性约束，约束必须包含分区键。此外，位图索引只能是局部的，并且通常...
ORACLE重建索引总结
12-13
一、重建索引的前提 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重建索引
喝醉酒的小白
04-08 9844
重建索引 查看信息 conn hr/hr select table_name from tabs; col segment_name format a40 select segment_name, bytes/1024 from user_segments; col object_name format a40 select owner, object_name, object_type from dba_objects where owner='HR' order by object_type; c
ORACLE重建索引
热门推荐
sayyy的专栏
04-23 1万+
重建索引 alter index 索引名称 rebuild tablespace DELL_SPACE online; 索引分析 analyze index 索引名称 validate structure; select (del_lf_rows_len/lf_rows_len) from index_stats where name='索引名称';
oracle中重建索引
weixin_34071713的博客
04-01 129
数据库的索引如果有比较频繁的Delete操作，将可能导致索引产生很多碎片， 所以，在有的时候，需要对所有的索引重新REBUILD，以便合并索引块，减少碎 片，提高查询速度。 SQL>setheadingoff SQL>setfeedbackoff SQL>spoold:index.sql SQL>SELE...
Oracle篇—分区索引的重建和管理（第三篇，总共五篇）
奈斯DBA的博客
01-26 3357
oracle分区索引的重建和管理。分区索引必须对每个分区重建，不能作为整体重建。如果将分区索引作为整体重建会报：ORA-14086: a partitioned index may not be rebuilt as a whole
Oracle 深入理解重建索引
袈裟本无清净，红尘不染性空。幽幽古刹千年钟，都是痴人说梦
09-17 246
什么时候需要重建索引  索引在普遍意义上能够给数据库带来带来提升，但索引的额外开销也是不容小视的，而索引的重建也是维护索引的重要工作之一。 经过维护的索引可带来以下好处：1、CBO对于索引的使用可能会产生一个较小的成本值，从而在执行计划中选择使用索引。2、使用索引扫描的查询扫描的物理索引块会减少，从而提高效率。3、于需要缓存的索引块减少了，从而让出了内存以供其他组件使用。 重建索引的原因主要包...
oracle重建分区索引语句
ZHANG123HUO的博客
05-08 1403
如果索引的状态为 unusable，则说明索引需要重建。这将对分区索引重新进行索引构建。
Oracle数据库：索引与表分区详解
重建索引可以优化索引结构，而删除索引则需要注意可能对查询性能的影响。 表分区是另一种提升性能的技术，尤其是对大型表来说。通过将大表分割成逻辑上相关的小块（分区），可以实现更高效的查询和管理。例如，可以...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值