b*tree index的那些事(四):index rebuild
创建测试表和索引
SQL> create table t1 as select * from dba_objects;
Table created.
SQL> create index i1 on t1(object_id);
Index created.
SQL> select object_id,object_name from dba_objects where object_name in('T1','I1');
OBJECT_ID OBJECT_NAM
---------- ----------
75024 T1
75025 I1
执行计划的区别：
REBUILD:
SQL> explain plan for alter index i1 rebuild;
Explained.
SQL> set linesize 200 pagesize 2000
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2094272848
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 86255 | 1095K| 288 (1)| 00:00:04 |
| 1 | INDEX BUILD NON UNIQUE| I1 | | | | |
| 2 | SORT CREATE INDEX | | 86255 | 1095K| | |
| 3 | INDEX FAST FULL SCAN| I1 | | | | |
-------------------------------------------------------------------------------
10 rows selected.
REBUILD ONLINE:
SQL> explain plan for alter index i1 rebuild online;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3918846387
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 86255 | 1095K| 288 (1)| 00:00:04 |
| 1 | INDEX BUILD NON UNIQUE| I1 | | | | |
| 2 | SORT CREATE INDEX | | 86255 | 1095K| | |
| 3 | TABLE ACCESS FULL | T1 | 86255 | 1095K| 288 (1)| 00:00:04 |
-------------------------------------------------------------------------------
10 rows selected.
从执行计划上来看REBUILD为IFFS,REBUILD ONLINE为TFS,通过10046验证确实如此。
REBUILD:
WAIT #1: nam='db file scattered read' ela= 39 file#=4 block#=11872 blocks=8 obj#=75025 tim=1441955219010547
WAIT #1: nam='db file scattered read' ela= 15 file#=4 block#=11881 blocks=7 obj#=75025 tim=1441955219014057
WAIT #1: nam='db file scattered read' ela= 22 file#=4 block#=11888 blocks=8 obj#=75025 tim=1441955219016963
WAIT #1: nam='db file scattered read' ela= 14 file#=4 block#=11897 blocks=7 obj#=75025 tim=1441955219020119
WAIT #1: nam='db file scattered read' ela= 18 file#=4 block#=12032 blocks=8 obj#=75025 tim=1441955219023176
WAIT #1: nam='db file scattered read' ela= 16 file#=4 block#=12041 blocks=7 obj#=75025 tim=1441955219026068
WAIT #1: nam='db file scattered read' ela= 19 file#=4 block#=12048 blocks=8 obj#=75025 tim=1441955219029012
REBUILD ONLINE:
WAIT #1: nam='db file scattered read' ela= 45 file#=4 block#=10625 blocks=7 obj#=75024 tim=1441955695348021
WAIT #1: nam='db file scattered read' ela= 13 file#=4 block#=10632 blocks=8 obj#=75024 tim=1441955695348166
WAIT #1: nam='db file scattered read' ela= 10 file#=4 block#=10641 blocks=7 obj#=75024 tim=1441955695348331
WAIT #1: nam='db file scattered read' ela= 14 file#=4 block#=10648 blocks=8 obj#=75024 tim=1441955695348676
WAIT #1: nam='db file scattered read' ela= 12 file#=4 block#=10657 blocks=7 obj#=75024 tim=1441955695348989
WAIT #1: nam='db file scattered read' ela= 13 file#=4 block#=10664 blocks=8 obj#=75024 tim=1441955695349447
WAIT #1: nam='db file scattered read' ela= 10 file#=4 block#=10673 blocks=7 obj#=75024 tim=1441955695349593
都是等待多块读，因为IFFS和TFS都是多块读，不同之处在REBUILD读的是75025 ，正好是I1的object_id，也就是索引的OBJECT_ID。而REBUILD ONLINE读的是75024 正好是T1的object_id，也就是表的OBJECT_ID。
note：其实rebuild的方式是根据cost来选择TFS还是IFFS的，但是rebuild online只能选择TFS。持有锁的区别：
REBUILD:
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
37 OD 75025 0 6 0 0
37 DL 75024 0 3 0 0
37 DL 75024 0 3 0 0
37 TM 75024 0 4 0 0
LOCK_TYPE:TM
LOCK_MODE:4
通过10046也可以验证，在重建索引之前执行了S FOR INDEX "SCOTT"."I1" IN SHARE MODE NOWAIT；即持有S模式的锁。
REBUILD ONLINE:
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
37 OD 75025 0 6 0 0
37 DL 75024 0 3 0 0
37 DL 75024 0 3 0 0
37 OD 75024 0 4 0 0
37 TM 75024 0 2 0 0
37 TM 75040 0 4 0 0
37 TX 458764 905 6 0 0
LOCK_TYPE:TM
LOCK_MODE:2
通过10046也可以验证，发现在重建索引之前执行了LOCK TABLE FOR INDEX "SCOTT"."I1" IN ROW SHARE MODE；即持有RS模式的锁。
锁：
结论与上表的对应是一样的。
兼容表：
通过兼容表发现S模式的锁与SX模式的锁是不兼容的，这就是为何用REBUILD重建索引时，不能DML的原因，因为DML首先要申请一个3号TM锁即模式为SX，再去申请X的6号行级锁，这里由于申请的SX模式的锁与S不兼容，所以会进入TM等待。而REBUILD ONLINE持有的是2号锁即RS模式的锁与DML需要申请的SX锁兼容，所以REBUILD ONLINE期间是可以DML的。
那么有两个疑问：
1.为什么ONLINE会是全表扫描呢？
2.从10046的过程来看ONLINE创建了一个IOT，有什么作用呢？
1.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
2.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
IOT在这里充当journal table，以便实现online rebuild
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31347199/viewspace-2121394/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
一次DBA_FREE_SPACE的优化
下一篇：
Push Subquery
请登录后发表评论
登录
全部评论
