Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about the difference between create index and create index online. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

Create index/create index online

This example requires three sessions, session 1 creates an index, session 2 modifies the value of the index key field, and session 3 looks at the lock.

Create a test table

Create table t_test

(

Col1 number

Col2 number

);

Create test data (estimate the amount of data needed according to the specific conditions of your machine, so that the time to create an index is about 20-30 seconds)

Insert into t_test

Select rownum col1, rownum col2 from dual

Connect by rownum set time on

10:22:01 SQL > set timing on

10:22:02 SQL >

-- get session 1 sid

10:22:04 SQL > select sid from v$mystat where rownum=1

SID

-

144

Elapsed: 00:00:00.01

Session 2:

SQL > set time on

10:22:06 SQL > set timing on

10:22:06 SQL >

-- get session 2 sid

10:22:06 SQL > select sid from v$mystat where rownum=1

SID

-

147

Elapsed: 00:00:00.01

Session 3:

SQL > set time on

10:22:11 SQL > set timing on

10:22:11 SQL >

-formatted output

10:22:13 SQL > set line 200

10:23:03 SQL > col addr for A10

10:23:03 SQL > col kaddr for A10

10:23:03 SQL > col sid for 999999

10:23:03 SQL > col type for A10

10:23:03 SQL > col id1 for 99999999999

10:23:03 SQL > col id2 for 99999999999

10:23:03 SQL > col lmod for 99

10:23:03 SQL > col request for 99

10:23:03 SQL > col ctime for 999999

10:23:03 SQL > col block for 99

10:23:03 SQL > col table_name for A30

10:23:03 SQL >

Session 1:

-- create an index without using online (the amount of data in the table should be large enough because you have to do other operations in session 2 and session 3)

10:25:08 SQL > create index ix_test_col1 on t_test (col1)

Index created.

Elapsed: 00:00:59.73

Session 2:

Modify the index field of the specified row. At this time, the update statement will hang and wait for the index to be created. You can see that session 2 is waiting for session 1 from the lock in session 3.

10:25:04 SQL > update t_test set col1=102400 where col2=102400

1 row updated.

Elapsed: 00:01:02.63

Session 3:

-- check the lock condition at this time.

10:24:29 SQL > select a.mom, decode (a.type,\'TM\', b.object_name) table_name

10:24:32 2 from v$lock a, dba_objects b

10:24:32 3 where a.id1=b.object_id (+)

10:24:32 4 and a.sid in (144,147)

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK TABLE_NAME

315C65FC 315C6614 144 TM 18 0 3 0 1 0 OBJ$

315C66A8 315C66C0 147 TM 5180637 0 0 3 0 0 T_TEST

315C6550 315C6568 144 TM 5180637 0 4 0 31 T_TEST

3203444C 32034460 144 DL 5180637 03 03 0

32034394 320343A8 144 DL 5180637 03 03 0

31627F54 31627F78 144 TX 655384 57423 6 0 3 0

6 rows selected.

Elapsed: 00:00:02.12

10:25:52 SQL > /

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK TABLE_NAME

315C5A88 315C5AD0 144 TS 6 23571 6 0 18 0

315C65FC 315C6614 144 TM 18 0 3 0 35 0 OBJ$

315C66A8 315C66C0 147 TM 5180637 0 0 3 34 0 T_TEST

315C6550 315C6568 144 TM 5180637 0 4 0 37 1 T_TEST

3203444C 32034460 144 DL 5180637 03 0 37 0

32034394 320343A8 144 DL 5180637 03 0 37 0

31627F54 31627F78 144 TX 655384 57423 6 0 37 0

7 rows selected.

Elapsed: 00:00:00.39

10:26:16 SQL > /

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK TABLE_NAME

32034394 320343A8 147 CU 754675352 0 6 0 0 0

Elapsed: 00:00:00.21

10:26:20 SQL > /

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK TABLE_NAME

315C6550 315C6568 147 TM 5180637 0 3 0 4 0 T_TEST

31616060 31616084 147 TX 393221 56619 60 4 0

Elapsed: 00:00:00.77

Session 2:

-- Roll back changes

10:26:21 SQL > rollback

Rollback complete.

Elapsed: 00:00:00.01

Session 3:

10:26:26 SQL > /

No rows selected

Elapsed: 00:00:00.03

10:26:36 SQL > /

No rows selected

Elapsed: 00:00:00.01

Create index online

Session 1:

-- delete the index and add the online option to rebuild

10:26:46 SQL > drop index ix_test_col1

Index dropped.

Elapsed: 00:00:00.35

10:26:59 SQL > create index ix_test_col1 on t_test (col1) online

Index created.

Elapsed: 00:02:47.07

Session 2:

-- modify the index field of the specified row. At this point, update will not wait for the index to be created, but will finish soon.

10:26:50 SQL > update t_test set col1=102400 where col2=102400

1 row updated.

Elapsed: 00:00:09.21

Session 3:

-- check the lock condition

10:26:53 SQL > /

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK TABLE_NAME

315C66A8 315C66C0 147 TM 5180637 0 3 0 0 0 T_TEST

315C6550 315C6568 144 TM 5180637 0 2 0 1 0 T_TEST

3203444C 32034460 144 DL 5180637 03 0 20

32034394 320343A8 144 DL 5180637 03 0 20

315C65FC 315C6614 144 TM 5180671 0 4 0 1 0 SYS_JOURNAL_5180670

31627F54 31627F78 144 TX 327692 57125 6 0 2 0

6 rows selected.

Elapsed: 00:00:02.49

10:27:26 SQL > /

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK TABLE_NAME

315C66A8 315C66C0 147 TM 5180637 0 3 06 0 T_TEST

315C6550 315C6568 144 TM 5180637 0 2 0 7 0 T_TEST

3203444C 32034460 144 DL 5180637 03 0 80

32034394 320343A8 144 DL 5180637 03 0 80

315C65FC 315C6614 144 TM 5180671 0 4 0 70 SYS_JOURNAL_5180670

31627F54 31627F78 144 TX 327692 57125 6 0 8 0

31616060 31616084 147 TX 655370 57432 6060

7 rows selected.

Elapsed: 00:00:02.16

10:27:38 SQL > /

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK TABLE_NAME

315C5A88 315C5AD0 144 TS 6 23579 6 0 3 0

315C66A8 315C66C0 147 TM 5180637 0 3 0 19 0 T_TEST

315C6550 315C6568 144 TM 5180637 0 20 20 0 T_TEST

3203444C 32034460 144 DL 5180637 03 0 21 0

32034394 320343A8 144 DL 5180637 03 0 21 0

315C65FC 315C6614 144 TM 5180671 0 4 0 20 0 SYS_JOURNAL_5180670

31627F54 31627F78 144 TX 327692 57125 6 0 21 0

31616060 31616084 147 TX 655370 57432 60 19 0

8 rows selected.

Elapsed: 00:00:00.17

10:28:29 SQL > /

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK TABLE_NAME

315C5A88 315C5AD0 144 TS 6 23579 6 0 52 0

315C66A8 315C66C0 147 TM 5180637 0 3 0 68 1 T_TEST

315C6550 315C6568 144 TM 5180637 0 2 4 69 0 T_TEST

3203444C 32034460 144 DL 5180637 03 0 70 0

32034394 320343A8 144 DL 5180637 03 0 70 0

315C65FC 315C6614 144 TM 5180671 0 4 0 69 0 SYS_JOURNAL_5180670

31627F54 31627F78 144 TX 327692 57125 6 0 70 0

31616060 31616084 147 TX 655370 57432 60 68 0

8 rows selected.

Elapsed: 00:00:00.14

-- from the case of the lock, you can see that the table SYS_JOURNAL_5180670 appears in the process of creating the index. View the relevant information of the table.

10:28:52 SQL > col partitioned for A20

10:29:02 SQL > col temporary for A20

10:29:08 SQL > select table_name, iot_type, partitioned, temporary from user_tables where table_name=\ 'SYS_JOURNAL_5180670\'

TABLE_NAME IOT_TYPE PARTITIONED TEMPORARY

SYS_JOURNAL_5180670 IOT NO N

Elapsed: 00:00:00.00

10:29:10 SQL > set line 100

10:29:15 SQL > desc SYS_JOURNAL_5180670

Name Null? Type

-

C0 NOT NULL NUMBER

OPCODE CHAR (1)

PARTNO NUMBER

RID NOT NULL ROWID

10:29:19 SQL > set line 200

10:29:37 SQL > select a.mom, decode (a.type,\'TM\', b.object_name) table_name

10:29:48 2 from v$lock a, dba_objects b

10:29:48 3 where a.id1=b.object_id (+)

10:29:48 4 and a.sid in (144,147)

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK TABLE_NAME

315C5A88 315C5AD0 144 TS 6 23579 6 0 132 0

315C66A8 315C66C0 147 TM 5180637 0 3 0 148 1 T_TEST

315C6550 315C6568 144 TM 5180637 0 2 4 149 0 T_TEST

3203444C 32034460 144 DL 5180637 03 0 150 0

32034394 320343A8 144 DL 5180637 03 0 150 0

315C65FC 315C6614 144 TM 5180671 0 4 0 149 0 SYS_JOURNAL_5180670

31627F54 31627F78 144 TX 327692 57125 6 0 150 0

31616060 31616084 147 TX 655370 57432 60 148 0

8 rows selected.

Elapsed: 00:00:00.13

Session 2:

-- rollback update

10:27:28 SQL > rollback

Rollback complete.

Elapsed: 00:00:00.01

10:30:04 SQL >

Session 3:

-- check the situation of the lock. There is no record. Index creation is finished.

10:29:52 SQL > /

No rows selected

Elapsed: 00:00:00.08

10:30:07 SQL > /

No rows selected

Elapsed: 00:00:00.01

Conclusion:

1.create index blocks other sessions to modify index fields until index creation is complete

2.create index online allows other sessions to modify the index field, but if the session that modified the index field does not have commit or rollbak, index creation will be blocked

When 3.online creates an index, it temporarily creates an IOT table, and deletes the IOT table after index creation. (the usage of the IOT table is not clear.)

After reading the above, do you have any further understanding of the difference between create index and 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.