Shulou(Shulou.com)06/01 Report--

Today, I will talk to you about the example analysis of CREATE INDEX. Online. many people may not understand it very well. in order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

Here we discuss how CREATE INDEX.ONLINE creates indexes online:

Database version:

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

Create simulated users and data tables:

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 (111Magneto AAA')

1 row created.

SQL > commit

Commit complete.

Session 1:

SQL > connect xiaoyang/xiaoyang

Connected.

SQL > select sid from v$mystat where rownum=1

SID

-

one hundred and thirty six

SQL >

SQL > insert into test values (222 million BBB')

1 row created.

Session 1 has a SID of 136 and inserts a piece of data into the XIAOYANG.TEST table but is not committed.

Session 2:

Sqlplus xiaoyang/xiaoyang

.

SQL > select sid from v$mystat where rownum=1

SID

-

one hundred and forty seven

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

The error is reported directly without adding the ONLINE keyword.

SQL > create index idx_test_name on test (name) online

After adding the ONLINE keyword, it is found that the execution of the statement is stuck.

Session 3:

Sqlplus / as sysdba

.

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

SID BLOCKING_SESSION

--

147 136

It was found that 136 sessions blocked 147 sessions.

SQL > SELECT sid,type,id1,id2,lmode,request,block FROM V$LOCK WHERE SID IN (147136) 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 40

147 DL 115640 0 3 0 0

147 DL 115640 0 3 0 0

6 rows selected.

The query lock found that the TM lock corresponding to the 147session had two records, one was successful when requesting the lock with mode 4, and the other was not successful, only the lock with mode 2 was obtained. Because it is 136 sessions that block 147 sessions, it is said that the locks acquired by 136 with mode 3 are not compatible with locks with mode 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 and 115643)

OWNER OBJECT_ID OBJECT_NAME OBJECT_TYPE

-

XIAOYANG 115640 TEST TABLE

XIAOYANG 115643 SYS_JOURNAL_115642 TABLE

115640 is the TEST table, and the table that successfully acquired the lock with schema 4 is SYS_JOURNAL_115642, which should be the intermediate table that performs ONLINE indexing.

If a new DML operation is generated at this time:

Session 4:

Sqlplus xiaoyang/xiaoyang

.

SQL > select sid from v$mystat where rownum=1

SID

-

one hundred and forty eight

SQL > insert into test values (333 cccc')

The transaction is also blocked.

Session 3:

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

SID BLOCKING_SESSION

--

147 136

148 147

It is found that session 147 is blocking session 148.

SQL > SELECT sid,type,id1,id2,lmode,request,block FROM V$LOCK WHERE SID IN (147136148) 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 40

148 TM 115640 0 0 3 0

Session 148 failed to request a lock with table mode 3 before executing the DML statement. This should be caused by a chain reaction.

Session 1 will be submitted.

Session 4 executes successfully, but session 3 is still blocked.

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

SID BLOCKING_SESSION

--

147 148

This shows that session 2 (sid=147) is blocked by session 4 (sid=148).

Submit session 4, session 2 online created index successfully! All the locks are gone.

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

OWNER OBJECT_ID OBJECT_NAME OBJECT_TYPE

-

XIAOYANG 115640 TEST TABLE

The intermediate table generated when ONLINE creates the index also disappears.

Please note:

Execute ALTER INDEX. REBUILD ONLINE; will also appear similar to CREATE INDEX. The problem with 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

It's stuck.

After reading the above, do you have any further understanding of the sample analysis of CREATE INDEX. Online? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.