create index/create index online區別

kunlunzhiying發表於2018-02-06
Index
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>

  5. --獲取 會話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

會話2：
  1. SQL> set time on
  2. 10:22:06 SQL> set timing on
  3. 10:22:06 SQL>

  5. --獲取 會話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

會話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>

會話1：
  1. --建立索引，不使用online（因為要在 會話2、會話3 中做其它操作，所以表中資料要量要足夠大）
  2. 10:25:08 SQL> create index ix_test_col1 on t_test(col1);

  4. Index created.

  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;

  4. 1 row updated.

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

  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

會話2：
  1. --回滾修改
  2. 10:26:21 SQL> rollback;

  4. Rollback complete.

  6. Elapsed: 00:00:00.01

會話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

會話1：
  1. --刪除索引，並加online選項重建
  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

會話2：
  1. --修改指定行的索引欄位，此時update不會待索引建立，而是很快結束
  2. 10:26:50 SQL> update t_test set col1=102400 where col2=102400;

  4. 1 row updated.

  6. Elapsed: 00:00:09.21

會話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. --從鎖的情況中看到建立索引過程中出現了表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\';

  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

會話2：
  1. --回滾update
  2. 10:27:28 SQL> rollback;

  4. Rollback complete.

  6. Elapsed: 00:00:00.01
  7. 10:30:04 SQL>

會話3：
  1. --檢視鎖的情況，沒有記錄，索引建立已結束
  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

結論：
1.create index 會阻塞其它會話修改索引欄位，直到索引建立結束；
2.create index online 允許其它會話修改索引欄位，但如果修改索引欄位的會話沒有commit或是rollbak，則索引建立會被阻塞；
3.online建立索引時會臨時建立一個IOT的表，索引建立結束後刪除IOT表（IOT表的使用方法暫時還不清楚）

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28211342/viewspace-2150891/，如需轉載，請註明出處，否則將追究法律責任。

相關文章