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

This article introduces the differences between create index online and create index and what points to pay attention to. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

Simulation of CREATE INDEX ONLINE Lock Mode change

SESSION 139

SQL > insert into test123

2 select * from dba_objects

50062 rows inserted

Do not submit

SESSION 148

SQL > create index test123_i on test123 (owner) online

Answer 148 jam

SQL > select * from v$lock where sid in ('139mpg 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 40 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

The blockage is due to

0000000096669CB0 0000000096669CD8 148 TM 53479 0 2 4 42 0

You can see that the TM lock has been acquired in mode 2, that is, SS mode, but it is expected to acquire the TM lock in mode 4, that is, mode S, but on OBJECTS 53479 it is acquired by the 139session in mode 3, that is, SX mode.

SS can be obtained because it is compatible with SX, but if you want to get S mode, S and SX are not compatible, so it is blocked.

By the way, OBJECT_ID=53479 is the table TEST123, and object 53481 is the object SYS_JOURNAL_53480, which ensures that possible changes are recorded in the so-called log table while indexing.

Synchronize to the log after indexing is completed, which is unique to ONLINE indexing.

At this point, we COMMIT answered.

SQL > select * from v$lock where sid in ('139mpg 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

Once submitted, the desired lock is obtained, and once acquired, it is downgraded to 2, that is, SS mode instead of S mode.

Then we do a number of DML operations in session 139 to see if CREATE INDEX ONLINE blocks the subsequent DML

SQL > select * from v$lock where sid in ('139mpg 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

You can see here that it does not actually block the subsequent DML operation, because after the downgrade, you only need to obtain the SS mode for the TEST123, and the SS mode is compatible with the SX mode.

So CREATE INDEX ONLINE will

1. If there is an DML uncommitted on this table, CREATE INDEX ONLINE will wait for it to be submitted, because the TM lock on the table needs to be obtained in S mode initially, and S mode is not compatible with SX mode.

2. If the actual TM S lock on the table has been downgraded to SS after obtaining the S mode lock on the table, that is, during the creation process, the subsequent DML will not be blocked. That's why.

Where CREATE INDEX ONLINE is superior to CREATE INDEX, it will not block the subsequent DML, because the TM lock is in SS mode rather than S mode.

However, it is important to pay attention to the first point, so it is better to close the application and build an index for insurance, especially for large tables, CREATE INDEX ONLINE is not necessarily safe.

Simulation of CREATE INDEX Lock Mode change

Actually, CREATE INDEX has nothing to simulate.

If you still have DML operation on the table, then there must be a TM lock in TX mode. If you set up the index, the error will be as follows.

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

Of course, if you can build an index, you will see the following

Build the index and check that the lock TM is mode 4 and mode S

SQL > select * from v$lock where sid in ('139mpg 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

Answer 139 to perform the operation

Delete test123

Check the lock mode as follows

SQL > select * from v$lock where sid in ('139mpg 148') order by sid

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK

0000000096669DD0 0000000096669DF8 139 TM 53479 00 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

You can see that reply 139 wants to obtain the TM lock in mode 3, that is, SX mode, but because the TM mode of CREATE INDEX is 4, that is, S mode, it is not shared, so it cannot be obtained and can only be blocked.

Wait for create index to complete, so CREATE INDEX must no longer be used without determining that the table has no DML operations, unless you are sure that there are no DML operations on the table

Compatibility matrix

Held/get null ss sx s ssx x

Null 1 1 1

Ss 1 1 1

Sx 1 1 1

S 1 1 1

Ssx 1 1

X 1

On the differences between create index online and create index and what points to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.