溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

create index和create index online的區(qū)別是什么

發(fā)布時(shí)間：2021-11-12 14:36:26 來源：億速云 閱讀：179 作者：柒染 欄目：關(guān)系型數(shù)據(jù)庫

今天就跟大家聊聊有關(guān)create index和create index online的區(qū)別是什么，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結(jié)了以下內(nèi)容，希望大家根據(jù)這篇文章可以有所收獲。

create index/create index online

此實(shí)例需要3個(gè)會(huì)話，會(huì)話1創(chuàng)建索引，會(huì)話2修改索引鍵字段的值，會(huì)話3查看鎖的情況。

創(chuàng)建測試表

  1. create table t_test

  2. (

  3.   col1 number,

  4.   col2 number

  5. );


造測試數(shù)據(jù)（根據(jù)自己機(jī)器具體情況估計(jì)需要的數(shù)據(jù)量，使創(chuàng)建索引的時(shí)間大概在20-30秒）

  1. insert into t_test

  2. select rownum col1, rownum col2 from dual

  3. connect by rownum<10000000;

  4. commit;


create index

會(huì)話1：

  1. SQL> set time on

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

  3. 10:22:02 SQL>


  5. --獲取 會(huì)話1 sid

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


  8.        SID

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

  10.        144


  12. Elapsed: 00:00:00.01


會(huì)話2：

  1. SQL> set time on

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

  3. 10:22:06 SQL>


  5. --獲取 會(huì)話2 sid

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


  8.        SID

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

  10.        147


  12. Elapsed: 00:00:00.01


會(huì)話3：

  1. SQL> set time on

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

  3. 10:22:11 SQL>


  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>


會(huì)話1：

  1. --創(chuàng)建索引，不使用online（因?yàn)橐?會(huì)話2、會(huì)話3 中做其它操作，所以表中數(shù)據(jù)要量要足夠大）

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


  4. Index created.


  6. Elapsed: 00:00:59.73


會(huì)話2：

  1. --修改指定行的索引字段，此時(shí)update語句會(huì)hang住，等待索引創(chuàng)建，從會(huì)話3 中的鎖的情況可以看到 會(huì)話2 在等待 會(huì)話1

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


  4. 1 row updated.


  6. Elapsed: 00:01:02.63


會(huì)話3：

  1. --查看此時(shí)鎖的情況

  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);


  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


  16. 6 rows selected.


  18. Elapsed: 00:00:02.12

  19. 10:25:52 SQL>/


  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


  31. 7 rows selected.


  33. Elapsed: 00:00:00.39

  34. 10:26:16 SQL>/


  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


  40. Elapsed: 00:00:00.21

  41. 10:26:20 SQL>/


  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


  48. Elapsed: 00:00:00.77


會(huì)話2：

  1. --回滾修改

  2. 10:26:21 SQL> rollback;


  4. Rollback complete.


  6. Elapsed: 00:00:00.01


會(huì)話3：

  1. 10:26:26 SQL> /


  3. no rows selected


  5. Elapsed: 00:00:00.03

  6. 10:26:36 SQL> /


  8. no rows selected


  10. Elapsed: 00:00:00.01


create index online

會(huì)話1：

  1. --刪除索引，并加online選項(xiàng)重建

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


  4. Index dropped.


  6. Elapsed: 00:00:00.35

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


  9. Index created.


  11. Elapsed: 00:02:47.07


會(huì)話2：

  1. --修改指定行的索引字段，此時(shí)update不會(huì)待索引創(chuàng)建，而是很快結(jié)束

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


  4. 1 row updated.


  6. Elapsed: 00:00:09.21


會(huì)話3：

  1. --查看鎖的情況

  2. 10:26:53 SQL> /


  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


  13. 6 rows selected.


  15. Elapsed: 00:00:02.49

  16. 10:27:26 SQL>/


  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


  28. 7 rows selected.


  30. Elapsed: 00:00:02.16

  31. 10:27:38 SQL>/


  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


  44. 8 rows selected.


  46. Elapsed: 00:00:00.17

  47. 10:28:29 SQL>/


  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


  60. 8 rows selected.


  62. Elapsed: 00:00:00.14


  64. --從鎖的情況中看到創(chuàng)建索引過程中出現(xiàn)了表SYS_JOURNAL_5180670，查看表的相關(guān)信息

  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\';


  69. TABLE_NAME                     IOT_TYPE     PARTITIONED          TEMPORARY

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

  71. SYS_JOURNAL_5180670            IOT          NO                   N


  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


  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);


  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


  100. 8 rows selected.


  102. Elapsed: 00:00:00.13


會(huì)話2：

  1. --回滾update

  2. 10:27:28 SQL> rollback;


  4. Rollback complete.


  6. Elapsed: 00:00:00.01

  7. 10:30:04 SQL>


會(huì)話3：

  1. --查看鎖的情況，沒有記錄，索引創(chuàng)建已結(jié)束

  2. 10:29:52 SQL> /


  4. no rows selected


  6. Elapsed: 00:00:00.08

  7. 10:30:07 SQL> /


  9. no rows selected


  11. Elapsed: 00:00:00.01


結(jié)論：
1.create index 會(huì)阻塞其它會(huì)話修改索引字段，直到索引創(chuàng)建結(jié)束；
2.create index online 允許其它會(huì)話修改索引字段，但如果修改索引字段的會(huì)話沒有commit或是rollbak，則索引創(chuàng)建會(huì)被阻塞；
3.online創(chuàng)建索引時(shí)會(huì)臨時(shí)創(chuàng)建一個(gè)IOT的表，索引創(chuàng)建結(jié)束后刪除IOT表（IOT表的使用方法暫時(shí)還不清楚）

看完上述內(nèi)容，你們對(duì)create index和create index online的區(qū)別是什么有進(jìn)一步的了解嗎？如果還想了解更多知識(shí)或者相關(guān)內(nèi)容，請(qǐng)關(guān)注億速云行業(yè)資訊頻道，感謝大家的支持。

向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手