CREATE INDEX ......ONLINE的示例分析

今天就跟大家聊聊有关CREATE INDEX ......ONLINE的示例分析，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。

这里我们讨论一下CREATE INDEX......ONLINE在线创建索引的情况：
数据库版本：
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

创建模拟用户和数据表：
SQL> create user xiaoyang identified by xiaoyang default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource to xiaoyang;

Grant succeeded.

SQL> grant select on "SYS"."V_$MYSTAT" to xiaoyang;

Grant succeeded.

SQL> connect xiaoyang/xiaoyang
Connected.

SQL> create table test(id number primary key,
  2  name varchar2(20));

Table created.

SQL> insert into test values (111,'aaa');

1 row created.

SQL> commit;

Commit complete.

会话1：
SQL> connect xiaoyang/xiaoyang
Connected.
SQL> select sid from v$mystat where rownum=1;

       SID
----------
       136

SQL>
SQL> insert into test values (222,'bbb');

1 row created.
会话1的SID为136，向XIAOYANG.TEST表插入一条数据，但未提交。

会话2：
sqlplus xiaoyang/xiaoyang
......
SQL> select sid from v$mystat where rownum=1;

       SID
----------
       147

SQL> create index idx_test_name on test(name);
create index idx_test_name on test(name)
                              *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

不加ONLINE关键字直接报错。
SQL> create index idx_test_name on test(name) online;
加上ONLINE关键字之后发现执行该语句卡住了。

会话3：
sqlplus / as sysdba
......
SQL> SELECT SID,BLOCKING_SESSION FROM V$SESSION WHERE BLOCKING_SESSION IS NOT NULL;

       SID BLOCKING_SESSION
---------- ----------------
       147              136
发现是136会话阻塞了147会话。

SQL> SELECT sid,type,id1,id2,lmode,request,block FROM V$LOCK WHERE SID IN(147,136) ORDER BY SID;

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       136 TM     115640          0          3          0          1
       136 TX     393262      17362          6          0          0
       147 TM     115643          0          4          0          0
       147 TM     115640          0          2          4          0
       147 DL     115640          0          3          0          0
       147 DL     115640          0          3          0          0

6 rows selected.

查询锁发现，147会话对应的TM锁有两条记录，在请求模式为4的锁时一条是成功的，另外一条并未成功，只获得了模式为2的锁。因为是136会话阻塞了147会话，所以说136获得的模式为3的锁和模式为4的锁并不兼容。

• 0 - none

• 1 - null (NULL)

• 2 - row-S (SS)

• 3 - row-X (SX)

• 4 - share (S)

• 5 - S/Row-X (SSX)

• 6 - exclusive (X)

SQL> SELECT owner,object_id,object_name,object_type FROM DBA_OBJECTS WHERE OBJECT_ID in (115640,115643);

OWNER       OBJECT_ID OBJECT_NAME                    OBJECT_TYPE
---------- ---------- ------------------------------------------------ -------------------
XIAOYANG       115640 TEST                                                TABLE
XIAOYANG       115643 SYS_JOURNAL_115642             TABLE
115640是TEST表，而获取模式为4的锁成功的表为 SYS_JOURNAL_115642，它应该是执行ONLINE创建索引的中间表。

如果这个时候又有新的DML操作产生：
会话4：
sqlplus xiaoyang/xiaoyang
......
SQL> select sid from v$mystat where rownum=1;

       SID
----------
       148

SQL> insert into test values(333,'ccc');
该事务同样被阻塞。

会话3：
SQL> SELECT SID,BLOCKING_SESSION FROM V$SESSION WHERE BLOCKING_SESSION IS NOT NULL;

       SID BLOCKING_SESSION
---------- ----------------
       147              136
       148              147
发现是会话147阻塞了会话148。
SQL> SELECT sid,type,id1,id2,lmode,request,block FROM V$LOCK WHERE SID IN(147,136,148) ORDER BY SID;

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       136 TM     115640          0          3          0          1
       136 TX     393262      17362          6          0          0
       147 TM     115643          0          4          0          0
       147 DL     115640          0          3          0          0
       147 DL     115640          0          3          0          0
       147 TM     115640          0          2          4          0
       148 TM     115640          0          0          3          0

会话148在执行DML语句前请求表模式为3的锁失败。这个应该是连锁反应造成的。

将会话1提交。
会话4执行成功，但是会话3依然被阻塞。

SQL> SELECT SID,BLOCKING_SESSION FROM V$SESSION WHERE BLOCKING_SESSION IS NOT NULL;

       SID BLOCKING_SESSION
---------- ----------------
       147              148
这个时候显示会话2(sid=147)是被会话4(sid=148)阻塞。
提交会话4，会话2 online创建索引成功！所有的锁消失。


SQL> SELECT owner,object_id,object_name,object_type FROM DBA_OBJECTS WHERE OBJECT_ID in (115640,115643);

OWNER       OBJECT_ID OBJECT_NAME                    OBJECT_TYPE
---------- ---------- ------------------------------ -------------------
XIAOYANG       115640 TEST                           TABLE

ONLINE创建索引时产生的中间表也消失了。

请注意：
执行ALTER INDEX ..... REBUILD ONLINE;同样会出现类似CREATE INDEX...... ONLINE的问题。
SQL> alter index idx_test_name rebuild;
alter index idx_test_name rebuild
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

SQL> alter index idx_test_name rebuild online;
卡住......

看完上述内容，你们对CREATE INDEX ......ONLINE的示例分析有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注亿速云行业资讯频道，感谢大家的支持。