create index和create index online的区别是什么

今天就跟大家聊聊有关create index和create index online的区别是什么，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。

create index/create index online

此实例需要3个会话，会话1创建索引，会话2修改索引键字段的值，会话3查看锁的情况。

创建测试表

1. create table t_test
   

2. (
   

3. col1 number,
   

4. col2 number
   

5. );

造测试数据（根据自己机器具体情况估计需要的数据量，使创建索引的时间大概在20-30秒）

1. insert into t_test
   

2. select rownum col1, rownum col2 from dual
   

3. connect by rownum<10000000;
   

4. commit;

create index

会话1：

1. SQL> set time on
   

2. 10:22:01 SQL> set timing on
   

3. 10:22:02 SQL>
   

4. 
   

5. --获取 会话1 sid
   

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

7. 
   

8. SID
   

9. ----------
   

10. 144
    

11. 
    

12. Elapsed: 00:00:00.01

会话2：

1. SQL> set time on
   

2. 10:22:06 SQL> set timing on
   

3. 10:22:06 SQL>
   

4. 
   

5. --获取 会话2 sid

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

7. 
   

8. SID
   

9. ----------
   

10. 147
    

11. 
    

12. Elapsed: 00:00:00.01

会话3：

1. SQL> set time on
   

2. 10:22:11 SQL> set timing on
   

3. 10:22:11 SQL>
   

4. 
   

5. --格式化输出
   

6. 10:22:13 SQL> set line 200
   

7. 10:23:03 SQL> col addr for a10
   

8. 10:23:03 SQL> col kaddr for a10
   

9. 10:23:03 SQL> col sid for 999999
   

10. 10:23:03 SQL> col type for a10
    

11. 10:23:03 SQL> col id1 for 99999999999
    

12. 10:23:03 SQL> col id2 for 99999999999
    

13. 10:23:03 SQL> col lmod for 99
    

14. 10:23:03 SQL> col request for 99
    

15. 10:23:03 SQL> col ctime for 999999
    

16. 10:23:03 SQL> col block for 99
    

17. 10:23:03 SQL> col table_name for a30
    

18. 10:23:03 SQL>

会话1：

1. --创建索引，不使用online（因为要在 会话2、会话3 中做其它操作，所以表中数据要量要足够大）
   

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

3. 
   

4. Index created.
   

5. 
   

6. Elapsed: 00:00:59.73

会话2：

1. --修改指定行的索引字段，此时update语句会hang住，等待索引创建，从会话3 中的锁的情况可以看到 会话2 在等待 会话1
   

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

3. 
   

4. 1 row updated.
   

5. 
   

6. Elapsed: 00:01:02.63

会话3：

1. --查看此时锁的情况
   

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

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

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

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

6. 
   

7. ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK TABLE_NAME
   

8. ---------- ---------- ------- ---------- ------------ ------------ ---------- ------- ------- ----- ------------------------------
   

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

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

11. 315C6550 315C6568 144 TM 5180637 0 4 0 3 1 T_TEST
    

12. 3203444C 32034460 144 DL 5180637 0 3 0 3 0
    

13. 32034394 320343A8 144 DL 5180637 0 3 0 3 0
    

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

15. 
    

16. 6 rows selected.
    

17. 
    

18. Elapsed: 00:00:02.12
    

19. 10:25:52 SQL>/

20. 
    

21. ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK TABLE_NAME
    

22. ---------- ---------- ------- ---------- ------------ ------------ ---------- ------- ------- ----- ------------------------------
    

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

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

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

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

27. 3203444C 32034460 144 DL 5180637 0 3 0 37 0
    

28. 32034394 320343A8 144 DL 5180637 0 3 0 37 0
    

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

30. 
    

31. 7 rows selected.
    

32. 
    

33. Elapsed: 00:00:00.39
    

34. 10:26:16 SQL>/

35. 
    

36. ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK TABLE_NAME
    

37. ---------- ---------- ------- ---------- ------------ ------------ ---------- ------- ------- ----- ------------------------------
    

38. 32034394 320343A8 147 CU 754675352 0 6 0 0 0
    

39. 
    

40. Elapsed: 00:00:00.21
    

41. 10:26:20 SQL>/

42. 
    

43. ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK TABLE_NAME
    

44. ---------- ---------- ------- ---------- ------------ ------------ ---------- ------- ------- ----- ------------------------------
    

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

46. 31616060 31616084 147 TX 393221 56619 6 0 4 0
    

47. 
    

48. Elapsed: 00:00:00.77

会话2：

1. --回滚修改
   

2. 10:26:21 SQL> rollback;
   

3. 
   

4. Rollback complete.
   

5. 
   

6. Elapsed: 00:00:00.01

会话3：

1. 10:26:26 SQL> /
   

2. 
   

3. no rows selected
   

4. 
   

5. Elapsed: 00:00:00.03
   

6. 10:26:36 SQL> /
   

7. 
   

8. no rows selected
   

9. 
   

10. Elapsed: 00:00:00.01

create index online

会话1：

1. --删除索引，并加online选项重建
   

2. 10:26:46 SQL> drop index ix_test_col1;
   

3. 
   

4. Index dropped.
   

5. 
   

6. Elapsed: 00:00:00.35
   

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

8. 
   

9. Index created.
   

10. 
    

11. Elapsed: 00:02:47.07

会话2：

1. --修改指定行的索引字段，此时update不会待索引创建，而是很快结束
   

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

3. 
   

4. 1 row updated.
   

5. 
   

6. Elapsed: 00:00:09.21

会话3：

1. --查看锁的情况
   

2. 10:26:53 SQL> /
   

3. 
   

4. ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK TABLE_NAME
   

5. ---------- ---------- ------- ---------- ------------ ------------ ---------- ------- ------- ----- ------------------------------
   

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

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

8. 3203444C 32034460 144 DL 5180637 0 3 0 2 0
   

9. 32034394 320343A8 144 DL 5180637 0 3 0 2 0
   

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

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

12. 
    

13. 6 rows selected.
    

14. 
    

15. Elapsed: 00:00:02.49
    

16. 10:27:26 SQL>/

17. 
    

18. ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK TABLE_NAME
    

19. ---------- ---------- ------- ---------- ------------ ------------ ---------- ------- ------- ----- ------------------------------
    

20. 315C66A8 315C66C0 147 TM 5180637 0 3 0 6 0 T_TEST
    

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

22. 3203444C 32034460 144 DL 5180637 0 3 0 8 0
    

23. 32034394 320343A8 144 DL 5180637 0 3 0 8 0
    

24. 315C65FC 315C6614 144 TM 5180671 0 4 0 7 0 SYS_JOURNAL_5180670
    

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

26. 31616060 31616084 147 TX 655370 57432 6 0 6 0
    

27. 
    

28. 7 rows selected.
    

29. 
    

30. Elapsed: 00:00:02.16
    

31. 10:27:38 SQL>/

32. 
    

33. ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK TABLE_NAME
    

34. ---------- ---------- ------- ---------- ------------ ------------ ---------- ------- ------- ----- ------------------------------
    

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

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

37. 315C6550 315C6568 144 TM 5180637 0 2 0 20 0 T_TEST
    

38. 3203444C 32034460 144 DL 5180637 0 3 0 21 0
    

39. 32034394 320343A8 144 DL 5180637 0 3 0 21 0
    

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

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

42. 31616060 31616084 147 TX 655370 57432 6 0 19 0
    

43. 
    

44. 8 rows selected.
    

45. 
    

46. Elapsed: 00:00:00.17
    

47. 10:28:29 SQL>/

48. 
    

49. ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK TABLE_NAME
    

50. ---------- ---------- ------- ---------- ------------ ------------ ---------- ------- ------- ----- ------------------------------
    

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

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

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

54. 3203444C 32034460 144 DL 5180637 0 3 0 70 0
    

55. 32034394 320343A8 144 DL 5180637 0 3 0 70 0
    

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

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

58. 31616060 31616084 147 TX 655370 57432 6 0 68 0
    

59. 
    

60. 8 rows selected.
    

61. 
    

62. Elapsed: 00:00:00.14
    

63. 
    

64. --从锁的情况中看到创建索引过程中出现了表SYS_JOURNAL_5180670，查看表的相关信息
    

65. 10:28:52 SQL> col partitioned for a20
    

66. 10:29:02 SQL> col temporary for a20
    

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

68. 
    

69. TABLE_NAME IOT_TYPE PARTITIONED TEMPORARY
    

70. ------------------------------ ------------ -------------------- --------------------
    

71. SYS_JOURNAL_5180670 IOT NO N
    

72. 
    

73. Elapsed: 00:00:00.00
    

74. 10:29:10 SQL> set line 100
    

75. 10:29:15 SQL> desc SYS_JOURNAL_5180670
    

76. Name Null? Type
    

77. ----------------------------------------------------- -------- ------------------------------------
    

78. C0 NOT NULL NUMBER
    

79. OPCODE CHAR(1)
    

80. PARTNO NUMBER
    

81. RID NOT NULL ROWID
    

82. 
    

83. 10:29:19 SQL> set line 200
    

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

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

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

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

88. 
    

89. ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK TABLE_NAME
    

90. ---------- ---------- ------- ---------- ------------ ------------ ---------- ------- ------- ----- ------------------------------
    

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

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

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

94. 3203444C 32034460 144 DL 5180637 0 3 0 150 0
    

95. 32034394 320343A8 144 DL 5180637 0 3 0 150 0
    

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

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

98. 31616060 31616084 147 TX 655370 57432 6 0 148 0
    

99. 
    

100. 8 rows selected.
     

101. 
     

102. Elapsed: 00:00:00.13

会话2：

1. --回滚update
   

2. 10:27:28 SQL> rollback;
   

3. 
   

4. Rollback complete.
   

5. 
   

6. Elapsed: 00:00:00.01
   

7. 10:30:04 SQL>

会话3：

1. --查看锁的情况，没有记录，索引创建已结束
   

2. 10:29:52 SQL> /
   

3. 
   

4. no rows selected
   

5. 
   

6. Elapsed: 00:00:00.08
   

7. 10:30:07 SQL> /
   

8. 
   

9. no rows selected
   

10. 
    

11. Elapsed: 00:00:00.01

结论：
1.create index 会阻塞其它会话修改索引字段，直到索引创建结束；
2.create index online 允许其它会话修改索引字段，但如果修改索引字段的会话没有commit或是rollbak，则索引创建会被阻塞；
3.online创建索引时会临时创建一个IOT的表，索引创建结束后删除IOT表（IOT表的使用方法暂时还不清楚）

看完上述内容，你们对create index和create index online的区别是什么有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注行业资讯频道，感谢大家的支持。