create index和create index online的区别是什么
今天就跟大家聊聊有关create index和create index online的区别是什么，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。
create index/create index online
此实例需要3个会话，会话1创建索引，会话2修改索引键字段的值，会话3查看锁的情况。
创建测试表
create table t_test
(
col1 number,
col2 number
);
造测试数据（根据自己机器具体情况估计需要的数据量，使创建索引的时间大概在20-30秒）
insert into t_test
select rownum col1, rownum col2 from dual
connect by rownum<10000000;
commit;
create index
会话1：
SQL> set time on
10:22:01 SQL> set timing on
10:22:02 SQL>
--获取 会话1 sid
10:22:04 SQL> select sid from v$mystat where rownum=1;
SID
----------
144
Elapsed: 00:00:00.01
会话2：
SQL> set time on
10:22:06 SQL> set timing on
10:22:06 SQL>
--获取 会话2 sid
10:22:06 SQL> select sid from v$mystat where rownum=1;
SID
----------
147
Elapsed: 00:00:00.01
会话3：
SQL> set time on
10:22:11 SQL> set timing on
10:22:11 SQL>
--格式化输出
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>
会话1：
--创建索引，不使用online（因为要在 会话2、会话3 中做其它操作，所以表中数据要量要足够大）
10:25:08 SQL> create index ix_test_col1 on t_test(col1);
Index created.
Elapsed: 00:00:59.73
会话2：
--修改指定行的索引字段，此时update语句会hang住，等待索引创建，从会话3 中的锁的情况可以看到 会话2 在等待 会话1
10:25:04 SQL> update t_test set col1=102400 where col2=102400;
1 row updated.
Elapsed: 00:01:02.63
会话3：
--查看此时锁的情况
10:24:29 SQL> select a.*, 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 3 1 T_TEST
3203444C 32034460 144 DL 5180637 0 3 0 3 0
32034394 320343A8 144 DL 5180637 0 3 0 3 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 0 3 0 37 0
32034394 320343A8 144 DL 5180637 0 3 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 6 0 4 0
Elapsed: 00:00:00.77
会话2：
--回滚修改
10:26:21 SQL> rollback;
Rollback complete.
Elapsed: 00:00:00.01
会话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
会话1：
--删除索引，并加online选项重建
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
会话2：
--修改指定行的索引字段，此时update不会待索引创建，而是很快结束
10:26:50 SQL> update t_test set col1=102400 where col2=102400;
1 row updated.
Elapsed: 00:00:09.21
会话3：
--查看锁的情况
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 0 3 0 2 0
32034394 320343A8 144 DL 5180637 0 3 0 2 0
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 0 6 0 T_TEST
315C6550 315C6568 144 TM 5180637 0 2 0 7 0 T_TEST
3203444C 32034460 144 DL 5180637 0 3 0 8 0
32034394 320343A8 144 DL 5180637 0 3 0 8 0
315C65FC 315C6614 144 TM 5180671 0 4 0 7 0 SYS_JOURNAL_5180670
31627F54 31627F78 144 TX 327692 57125 6 0 8 0
31616060 31616084 147 TX 655370 57432 6 0 6 0
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 2 0 20 0 T_TEST
3203444C 32034460 144 DL 5180637 0 3 0 21 0
32034394 320343A8 144 DL 5180637 0 3 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 6 0 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 0 3 0 70 0
32034394 320343A8 144 DL 5180637 0 3 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 6 0 68 0
8 rows selected.
Elapsed: 00:00:00.14
--从锁的情况中看到创建索引过程中出现了表SYS_JOURNAL_5180670，查看表的相关信息
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.*, 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 0 3 0 150 0
32034394 320343A8 144 DL 5180637 0 3 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 6 0 148 0
8 rows selected.
Elapsed: 00:00:00.13
会话2：
--回滚update
10:27:28 SQL> rollback;
Rollback complete.
Elapsed: 00:00:00.01
10:30:04 SQL>
会话3：
--查看锁的情况，没有记录，索引创建已结束
10:29:52 SQL> /
no rows selected
Elapsed: 00:00:00.08
10:30:07 SQL> /
no rows selected
Elapsed: 00:00:00.01
结论：
1.create index 会阻塞其它会话修改索引字段，直到索引创建结束；
2.create index online 允许其它会话修改索引字段，但如果修改索引字段的会话没有commit或是rollbak，则索引创建会被阻塞；
3.online创建索引时会临时创建一个IOT的表，索引创建结束后删除IOT表（IOT表的使用方法暂时还不清楚）
看完上述内容，你们对create index和create index online的区别是什么有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注亿速云行业资讯频道，感谢大家的支持。
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
