create index online 和create index不同及有哪些注意点

这篇文章给大家介绍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

关于create index online 和create index不同及有哪些注意点就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。