create index online 和create index 不同及注意點

CREATE INDEX ONLINE 鎖模式變化模擬

SESSION 139
SQL>  insert into test123
  2  select * from dba_objects;
 
50062 rows inserted
不提交
SESSION 148
SQL> create index test123_i on test123(owner) online;

回話148堵塞

SQL>  select * from v$lock where sid in ('139','148') order by sid;
 
ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
0000000096669B90 0000000096669BB8        139 TM        53479          0          3          0         66          1
00000000966E6578 00000000966E65B0        139 TX       589843        343          6          0         66          0
0000000096669DD0 0000000096669DF8        148 TM        53481          0          4          0         40          0
0000000096669CB0 0000000096669CD8        148 TM        53479          0          2          4         42          0
00000000978E54F0 00000000978E5510        148 DL        53479          0          3          0         42          0
00000000978E53A0 00000000978E53C0        148 DL        53479          0          3          0         42          0

堵塞正是由於
0000000096669CB0 0000000096669CD8        148 TM        53479          0          2          4         42          0
可以看到已經以模式2也就是SS模式獲得TM鎖，但是期望以模式4獲得TM鎖也就是S模式，但是在OBJECTS 53479上被139會話以模式3也就是SX模式獲得
因為SS和SX相容所以可以獲得，但是如果想獲得S模式，S和SX是不相容，所以堵塞
順便提一下OBJECT_ID=53479 就是表TEST123，而物件53481是物件SYS_JOURNAL_53480，就是為了保證在建立索引的同時把可能的更改記錄到所謂的日誌表中
待索引建立完成後同步到日誌中，這也是ONLINE建立索引所獨有的。
此時我們COMMIT回話139

SQL>  select * from v$lock where sid in ('139','148') order by sid;
 
ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
00000000978E53A0 00000000978E53C0        148 DL        53479          0          3          0        821          0
0000000096669DD0 0000000096669DF8        148 TM        53481          0          4          0        819          0
0000000096669CB0 0000000096669CD8        148 TM        53479          0          2          0         14          0
00000000978E54F0 00000000978E5510        148 DL        53479          0          3          0        821          0

一旦提交後期望的鎖即可獲得，而且獲得後會降級為2也就是SS模式而不是S模式

然後我們又在會話139進行多次DML操作，看看CREATE INDEX ONLINE 是否堵塞 隨後的DML

SQL>  select * from v$lock where sid in ('139','148') order by sid;
 
ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
000000009666A250 000000009666A278        139 TM        53479          0          3          0         84          0
00000000966C0868 00000000966C08A0        139 TX       131088        311          6          0         80          0
0000000096669DD0 0000000096669DF8        148 TM        53481          0          4          0        562          0
00000000978E54F0 00000000978E5510        148 DL        53479          0          3          0        563          0
00000000978E53A0 00000000978E53C0        148 DL        53479          0          3          0        563          0
0000000096669CB0 0000000096669CD8        148 TM        53479          0          2          0        549          0

這裡可以看到實際不會堵塞隨後的DML操作，因為降級後只需要對TEST123獲得SS模式即可，SS模式是SX模式是相容的。
所以CREATE INDEX ONLINE會
1、如果在本表上有DML沒有提交，那麼CREATE INDEX ONLINE會等待其提交，因為初始的時候需要以S模式獲得表上TM鎖,S模式和SX模式不相容
2、如果獲得了表上S模式鎖後，也就是進行建立過程中，實際對錶的TM S鎖已經降級為SS，這個時候就不會堵塞隨後的DML了。這也是為什麼
CREATE INDEX ONLINE優於CREATE INDEX的地方，他不會堵塞隨後的DML，因為TM鎖是SS模式而不是S模式。
   但是還是要注意第一點，所以為了保險還是關閉應用建立索引吧，特別是大表，CREATE INDEX ONLINE也不一定保險。

CREATE INDEX  鎖模式變化模擬

其實CREATE INDEX 沒什麼好模擬的，
如果你還有DML操作在表上，那麼一定有TX模式的TM鎖，建立索引會報錯如下
SQL> create index test123_i
  2  on test123(owner);
 
create index test123_i
on test123(owner)
 
ORA-00054: resource busy and acquire with NOWAIT specified

當然如果可以建立索引的話你會看到如下
回話148建立索引，檢視其鎖TM為模式4及模式S
SQL>  select * from v$lock where sid in ('139','148') order by sid;
 
ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
00000000978E53A0 00000000978E53C0        148 DL        53479          0          3          0          6          0
00000000978E54F0 00000000978E5510        148 DL        53479          0          3          0          6          0
00000000966F2BA8 00000000966F2BE0        148 TX       458790        367          6          0          6          0
0000000096669CB0 0000000096669CD8        148 TM           18          0          3          0          6          0
0000000096669B90 0000000096669BB8        148 TM        53479          0          4          0          6          0

回話139進行操作
delete test123;
檢視鎖模式如下
SQL>  select * from v$lock where sid in ('139','148') order by sid;
 
ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
0000000096669DD0 0000000096669DF8        139 TM        53479          0          0          3         15          0
00000000978E54F0 00000000978E5510        148 DL        53479          0          3          0         31          0
0000000096669B90 0000000096669BB8        148 TM        53479          0          4          0         31          1
00000000966F2BA8 00000000966F2BE0        148 TX       458790        367          6          0         31          0
0000000096668868 00000000966688C8        148 TS            4   18509883          6          0         17          0
00000000978E53A0 00000000978E53C0        148 DL        53479          0          3          0         31          0
0000000096669CB0 0000000096669CD8        148 TM           18          0          3          0         31          0

可以看到回話139想以模式3也就是SX模式獲得TM鎖，但是因為CREATE INDEX 的TM模式是4也就是S模式，是不共享的，所以不能獲得，只能堵塞
等待create index 完成，所以CREATE INDEX一定不能再沒有確定這個表沒有DML操作的情況下使用，除非你確定沒有DML操作在這個表上

相容矩陣

held/get  null  ss   sx   s   ssx   x
null          1    1    1   1     1   1 
ss            1    1    1   1     1
sx            1    1    1
s             1    1         1
ssx          1    1
x             1