重建索引 REBUILD和REBUILD ONLINE的一些疑问_dbdao吧_百度贴吧
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&签到排名：今日本吧第个签到，本吧因你更精彩，明天继续来努力！
本吧签到人数：0可签7级以上的吧50个
本月漏签0次！成为超级会员，赠送8张补签卡连续签到：天&&累计签到：天超级会员单次开通12个月以上，赠送连续签到卡3张
关注：1,153贴子：
重建索引 REBUILD和REBUILD ONLINE的一些疑问
自己做了测试，由于等级不够不能发附件所以只能写结论和疑问通过10046和执行计划得到的测试结果：1.执行计划上来看REBUILD为IFFS,REBUILD ONLINE为TFS2.持有锁REBUILD持有的锁:LOCK_TYPE:TMLOCK_MODE:4通过10046看到，发现在重建索引之前执行了LOCK TABLE
FOR INDEX &SCOTT&.&I1& IN SHARE MODE
NOWAIT；即持有S模式的锁。REBUILD ONLINE持有的锁:LOCK_TYPE:TMLOCK_MODE:2通过10046看到，发现在重建索引之前执行了LOCK TABLE
FOR INDEX &SCOTT&.&I1& IN ROW SHARE MODE；即持有RS模式。3.能否DMLREBUILD不能，REBUILD ONLINE可以。因为DML首先要申请一个3号TM锁即模式为SX，再去申请X的6号行级锁。REBUILDE持有的是S模式的锁与SX模式的锁是不兼容的，所以会进入TM等待。REBUILD ONLINE持有的是2号锁即RS模式的锁与DML需要申请的SX锁兼容，所以REBUILD ONLINE期间是可以DML的。4.疑问：1.为什么ONLINE会是全表扫描呢？2.从10046的过程来看ONLINE创建了一个IOT，后面又在IOT上创建了索引，这些有什么作用呢？不明白
投资上玖富网，注册即可获得300元红包！3000万+注册用户，银行及风控体系
Oracle 8.1中引入index online build • Restartable online indexbuild, means that online build can proceed after the databaserestarts. It is hard to implement because current Oracle implementation does not supportrestartable sort.(it is a future ehancement)• Build the new indexby scanning fromthe old indexsegment. Because the indexrowsmove fromone block to another block in the indexblock split, reading from indexisdifficult to implement than reading from table.在设计文档中描述了基于索引做rebuild online ，要比 从表读取 技术上难很多， 所以Oracle开发人员选择了基于表读取来实现index online rebuild
When we create an index, we allow updates to the table. These changes are recorded in ajournal table. At the end of indexcreation, we merge the journal into the new index. Based onthe observation, we have the following simple algorithm(the locking schema follows 3-stepoperation):IOT在这里充当
journal table，以便实现 online rebuild
贴吧热议榜
使用签名档&&
保存至快速回贴查看: 2687|回复: 7
关于alter index rebuild
(online)的疑问
论坛徽章:24
google了些资料，说得都是alter index rebuild会使用index fast full scan进行建立索引，指定online就会走full table scan，当然可能不同的版本不一样。我记得好像是有一个在建立index时会加锁的，阻止DML操作？
这两天电脑中毒了 ，没有办法测试。
杀了两天了还没有搞定，重装了2次了，
那位帮我看看，难道是我记错了
论坛徽章:56
rebuild online不会阻塞DML
论坛徽章:136
google了些资料，说得都是alter index rebuild会使用index fast full scan进行建立索引
前段时间不是就有人说过,走index fast full index和full table都有可能的,要看表和索引哪个大,你自己测试下就知道了
论坛徽章:136
SQL& create table a(a char(1));
表已创建。
SQL& begin
&&2& & for i in 1..100 loop
&&3& &&&insert into a values('a');
PL/SQL 过程已成功完成。
SQL& create index ind_a on a(a);
索引已创建。
SQL& explain plan
&&2& &for alter index ind_
SQL& select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value:
--------------------------------------------------------------------------------
| Id&&| Operation& && && && &&&| Name&&| Rows&&| Bytes | Cost (%CPU)| Time& &&&|
--------------------------------------------------------------------------------
|& &0 | ALTER INDEX STATEMENT&&|& && & |& & 82 |& &246 |& &&&2& &(0)| 00:00:01 |
|& &1 |&&INDEX BUILD NON UNIQUE| IND_A |& && & |& && & |& && && && &|& && && & |
|& &2 |& &SORT CREATE INDEX& & |& && & |& & 82 |& &246 |& && && && &|& && && & |
|& &3 |& & TABLE ACCESS FULL& &| A& &&&|& & 82 |& &246 |& &&&2& &(0)| 00:00:01 |
--------------------------------------------------------------------------------
论坛徽章:24
你测试的就是我说得啊！
online会走fts
否则会走ffs
论坛徽章:136
你说的是online走full table,rebuild index 没加online会在表上加tm锁
论坛徽章:4
关键在于online 创建一个iot 表存储临时数据，原表上的lock 为tm2
而没online lock 为tm4 ，造成enqueue
论坛徽章:24
原帖由 棉花糖ONE 于
21:01 发表
你说的是online走full table,rebuild index 没加online会在表上加tm锁
你的例子不是full table么？
itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有　　　　
　北京市公安局海淀分局网监中心备案编号：　广播电视节目制作经营许可证：编号（京）字第1149号查看: 7318|回复: 1
关于ALTER INDEX rebuild和rebuild online的区别
论坛徽章:3
关于ALTER INDEX rebuild和rebuild online的区别
今天看到帖子上
最初由 biti_rainy 发布
如果是索引坏了…… 建议是 rebuild online ，这样数据重新来自 table
只是 rebuild的话 还是来自索引可能还有问题。
实在是不是很理解，做试验验证一下：
建立试验表：
SQL& create table t as select rownum a from all_objects where rownum & 101;
Table created
SQL& create index t_ind on t(a);
Index created
SQL& explain plan for alter index t_
SQL& select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value:
--------------------------------------------------------------------------------
| Id&&| Operation& && && && &&&| Name&&| Rows&&| Bytes | Cost (%CPU)| Time& &&&|
--------------------------------------------------------------------------------
|& &0 | ALTER INDEX STATEMENT&&|& && & |& &327 |&&4251 |& &&&3& &(0)| 00:00:01 |
|& &1 |&&INDEX BUILD NON UNIQUE| T_IND |& && & |& && & |& && && && &|& && && & |
|& &2 |& &SORT CREATE INDEX& & |& && & |& &327 |&&4251 |& && && && &|& && && & |
|& &3 |& & INDEX FAST FULL SCAN| T_IND |& && & |& && & |& && && && &|& && && & |
--------------------------------------------------------------------------------
这里可以看到是对索引进行扫描。
SQL& explain plan for alter index t_
SQL& select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value:
--------------------------------------------------------------------------------
| Id&&| Operation& && && && &&&| Name&&| Rows&&| Bytes | Cost (%CPU)| Time& &&&|
--------------------------------------------------------------------------------
|& &0 | ALTER INDEX STATEMENT&&|& && & |& &327 |&&4251 |& &&&3& &(0)| 00:00:01 |
|& &1 |&&INDEX BUILD NON UNIQUE| T_IND |& && & |& && & |& && && && &|& && && & |
|& &2 |& &SORT CREATE INDEX& & |& && & |& &327 |&&4251 |& && && && &|& && && & |
|& &3 |& & TABLE ACCESS FULL& &| T& &&&|& &327 |&&4251 |& &&&3& &(0)| 00:00:01 |
--------------------------------------------------------------------------------
已选择10行。
这里可以看到在rebuild online的时候，是对表进行扫描。
这两者为什么有这样的区别哪？
我不是很理解为什么要这样：
可能的原因
在使用online的时候，是允许对表进行DML操作的，所有的索引的变化是放置在一个sys.ind_online$表中，当有修改表的时候，会将记录记录在这个表中，当新的索引建立完成后会将这个修改更新到新的索引上。
由于在只是rebuild的时候，需要对表加上share锁。这样就没有数据的更新，就不需要修改记录。索引反应的数据和实际的数据是一样的？
1、而为什么一个搜索表，而另外一个却是只是scan索引哪？
2、在online的时候，我的理解对吗，如果我的理解是对的话，也不能够解释？
不是很理解，高手解释一下。
论坛徽章:151
你的理解基本上是对的，他们的区别主要就在于是否阻塞DML操作和扫描数据的不同。由于REBUILD的时候为了维护索引本身，会阻塞DML操作，在DML操作频繁的OLTP系统中经常用rebuild online会比较合适
itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有　　　　
　北京市公安局海淀分局网监中心备案编号：　广播电视节目制作经营许可证：编号（京）字第1149号2181人阅读
Oracle性能优化（16）
alter index coalesce和alter index rebuild的区别：
alter index coalesce是合并同一branch的leaf block。
而alter index rebuild是重新建立一个indexes，一般需要2倍的index大小的。而且需要排序。
它讲到”coalesce index”比”rebuild index”消耗更少的资源。在我的测试中，coalesce index比rebuild index产生更多的redo size；如果对一些GB级别的索引进行coalesce，过多的redo会严重影响系统性能，且会很快把归档目录填满。还是在产品库上少用的为好。
以下是证明alter index coalesce的过程：
×××××××× ×××××××××××××××××alter index coalesce××××××××××××××××××××××
SQL& select * from v$
--------------------------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE&&& 9.2.0.6.0&&&&&& Production
TNS for Solaris: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production
SQL& drop&&t2;
Table dropped.
SQL& create table t2(col1 number);
Table created.
SQL& create unique index t2_inx on t2(col1);
Index created.
SQL& alter table t2 add constraint t2_pk primary key(col1);
Table altered.
SQL& begin
& 2& for i in& 1000 loop
& 3&&&&& insert into t2 values(i);
PL/SQL procedure successfully completed.
Commit complete.
SQL& begin
& 2& sys.dbms_stats.gather_table_stats('SYSTEM', 'T2', ESTIMATE_PERCENT =& 20, c
ascade=& true);
PL/SQL procedure successfully completed.
SQL& col index_name for a10;
SQL& select index_name, blevel, leaf_blockS, distinct_keys
& 2&&&&&& clustering_factor, num_rows
& 3&&&&&& from user_indexes
& 4&&&&&& where table_name LIKE 'T2%';
INDEX_NAME&&&& BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR&& NUM_ROWS
---------- ---------- ----------- ----------------- ----------
T2_INX&&&&&&&&&&&&& 1&&&&&&&& 190&&&&&&&&&&& 101000&&&& 101000
SQL& declare
& 2& cursor mm is select col1 from t2;
& 3& begin
& 4& for cur_1 in mm loop
& 5&&&& if mod(cur_1.col1, 2) = 0 then
& 6&&&&&& delete from t2 where col1=cur_1.col1;
PL/SQL procedure successfully completed.
SQL& begin
& 2& sys.dbms_stats.gather_table_stats('SYSTEM', 'T2', ESTIMATE_PERCENT =& 20, c
ascade=& true);
PL/SQL procedure successfully completed.
SQL& select index_name, blevel, leaf_blockS, distinct_keys
& 2&&&&&& clustering_factor, num_rows
& 3&&&&&& from user_indexes
& 4&&&&&& where table_name LIKE 'T2%';
INDEX_NAME&&&& BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR&& NUM_ROWS
---------- ---------- ----------- ----------------- ----------
T2_INX&&&&&&&&&&&&& 1&&&&&&&& 190&&&&&&&&&&&& 50500&&&&& 50500
SQL& alter index t2_
Index altered.
SQL& begin
& 2& sys.dbms_stats.gather_table_stats('SYSTEM', 'T2', ESTIMATE_PERCENT =& 20, c
ascade=& true);
PL/SQL procedure successfully completed.
SQL& select index_name, blevel, leaf_blockS, distinct_keys
& 2&&&&&& clustering_factor, num_rows
& 3&&&&&& from user_indexes
& 4&&&&&& where table_name LIKE 'T2%';
INDEX_NAME&&&& BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR&& NUM_ROWS
---------- ---------- ----------- ----------------- ----------
T2_INX&&&&&&&&&&&&& 1&&&&&&&& 189&&&&&&&&&&&& 50500&&&&& 50500
SQL& declare
& 2& cursor mm is select col1 from t2;
& 3& begin
& 4& for cur_1 in mm loop
& 5&&&& if mod(cur_1.col1, 3) = 0 then
& 6&&&&&& delete from t2 where col1=cur_1.col1;
PL/SQL procedure successfully completed.
SQL& alter index t2_
Index altered.
SQL& begin
& 2& sys.dbms_stats.gather_table_stats('SYSTEM', 'T2', ESTIMATE_PERCENT =& 20, c
ascade=& true);
PL/SQL procedure successfully completed.
SQL& select index_name, blevel, leaf_blockS, distinct_keys
& 2&&&&&& clustering_factor, num_rows
& 3&&&&&& from user_indexes
& 4&&&&&& where table_name LIKE 'T2%';
INDEX_NAME&&&& BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR&& NUM_ROWS
---------- ---------- ----------- ----------------- ----------
T2_INX&&&&&&&&&&&&& 1&&&&&&&&& 95&&&&&&&&&&&& 33666&&&&& 33666
×××××××× ×××××××××××××××××alter index coalesce××××××××××××××××××××××
&redo size 检验
它讲到”coalesce index”比”rebuild index”消耗更少的资源。在我的测试中，coalesce index比rebuild index产生更多的redo size；如果对一些GB级别的索引进行coalesce，过多的redo会严重影响系统性能，且会很快把归档目录填满。还是在产品库上少用的为好。
先在一个7M的表上进行测试
create table binzhang(id number not null,creation date not null,last_modified
date not null);
insert into binzhang select binzhang_seq.nextval id,created,created
from dba_objects
insert into binzhang select binzhang_seq.nextval id,created,created
from dba_objects;
SQL& select bytes from user_segments where segment_name=’BINZHANG’;
create index binzhangidx1 on binzhang(creation) ;
create index binzhangidx2 on binzhang(last_modified) ;
update binzhang set creation=creation+124,last_modified=last_modified+124
where mod(id,12)in
(1,3,5,7,9,11);
analyze index binzhangidx1 validate structure;
select name,value from v$statname s,v$mystat v where s.STATISTIC#=v.STATISTIC#
and name=’redo size’;
NAME VALUE
—————————–
SQL& alter index binzhangidx1 coalesce;
Index altered.
select name,value from v$statname s,v$mystat v where s.STATISTIC#=v.STATISTIC#
and name=’redo size’;
NAME VALUE
———————— ———-
SQL& alter index binzhangidx2 rebuild tablespace cr_data;
Index altered.
select name,value from v$statname s,v$mystat v where s.STATISTIC#=v.STATISTIC#
and name=’redo size’;
NAME VALUE
———————— ———-
Redo size used by coalesce -=6760052
Redo size used by rebuild -=4473520
OK. We can see that coalesce index generate more redo than rebuildfor
a 7M table.
...............................
再在一个200M的表上进行测试。
SQL& select bytes from user_segments where segment_name=’BINZHANG’;
SQL& update binzhang set creation=creation+124,last_modified=last_modified+124
where mod(id,12)in
(1,3,5,7,9,11);
3083428 rows updated.
select name,value from v$statname s,v$mystat v where s.STATISTIC#=v.STATISTIC#
and name=’redo size’;
NAME VALUE
——————————- ———-
redo size 568
SQL& alter index binzhangidx2 rebuild;
Index altered.
select name,value from v$statname s,v$mystat v where s.STATISTIC#=v.STATISTIC#
and name=’redo size’;
NAME VALUE
————————— ———-
SQL& alter index binzhangidx1 coalesce;
Index altered.
select name,value from v$statname s,v$mystat v where s.STATISTIC#=v.STATISTIC#
and name=’redo size’;
NAME VALUE
———————— ———-
............................................
非常明显，coalesce产生过多的redo size.
结论：coalesce index is more resource intensive than rebuild index.
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：329323次
积分：4197
积分：4197
排名：第5582名
原创：85篇
转载：79篇
评论：45条
(13)(2)(4)(4)(1)(7)(1)(3)(2)(6)(5)(1)(1)(4)(7)(12)(1)(2)(1)(1)(5)(1)(1)(2)(1)(1)(1)(11)(15)(1)(1)(1)(1)(6)(9)(1)(1)(5)(22)博客访问： 583469
博文数量： 824
注册时间：
5年ABAP转BASIS
ITPUB论坛APP
ITPUB论坛APP
APP发帖 享双倍积分
IT168企业级官微
微信号：IT168qiye
系统架构师大会
微信号：SACC2013
分类： Oracle
两者重建索引时的扫描方式不同，rebuild用的是“INDEX FAST FULL SCAN”，rebuild online用的是“TABLE ACCESS FULL”; 即rebuild index是扫描索引块,而rebuild index online是扫描全表的数据块.
SQL> explain plan for alter index ind_test_ SQL> select * from table(dbms_xplan.display);
SQL> explain plan for alter index ind_test_
SQL> select * from table(dbms_xplan.display);&
rebuild index online在执行期间不会阻塞DML操作，但在开始和结束阶段，需要请求模式为4的TM锁。因此
，如果在rebuild index online开始前或结束时，有其它长时间的事物在运行，很有可能就造成大量的锁等
待。也就是说在执行前仍会产生阻塞, 应该避免排他锁.
而rebuild index在执行期间会阻塞DML操作, 但速度较快.
阅读(423) | 评论(0) | 转发(0) |
相关热门文章
给主人留下些什么吧！~~
请登录后评论。