ITPub博客

首页 > 数据库 > Oracle > create index/create index online区别

create index/create index online区别

Oracle 作者:kunlunzhiying 时间:2018-02-06 16:01:18 0 删除 编辑
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. --获取 会话1 sid
  5. 10:22:04 SQL> select sid from v$mystat where rownum=1;

  6.        SID
  7. ----------
  8.        144

  9. 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. --获取 会话2 sid
  5. 10:22:06 SQL> select sid from v$mystat where rownum=1;

  6.        SID
  7. ----------
  8.        147

  9. 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. 10:22:13 SQL> set line 200
  6. 10:23:03 SQL> col addr for a10
  7. 10:23:03 SQL> col kaddr for a10
  8. 10:23:03 SQL> col sid for 999999
  9. 10:23:03 SQL> col type for a10
  10. 10:23:03 SQL> col id1 for 99999999999
  11. 10:23:03 SQL> col id2 for 99999999999
  12. 10:23:03 SQL> col lmod for 99
  13. 10:23:03 SQL> col request for 99
  14. 10:23:03 SQL> col ctime for 999999
  15. 10:23:03 SQL> col block for 99
  16. 10:23:03 SQL> col table_name for a30
  17. 10:23:03 SQL>

会话1:
  1. --创建索引,不使用online(因为要在 会话2、会话3 中做其它操作,所以表中数据要量要足够大)
  2. 10:25:08 SQL> create index ix_test_col1 on t_test(col1);

  3. Index created.

  4. 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. 1 row updated.

  4. 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. ADDR       KADDR      SID     TYPE       ID1          ID2          LMODE      REQUEST CTIME   BLOCK TABLE_NAME
  7. ---------- ---------- ------- ---------- ------------ ------------ ---------- ------- ------- ----- ------------------------------
  8. 315C65FC   315C6614   144     TM         18           0            3          0       1       0     OBJ$
  9. 315C66A8   315C66C0   147     TM         5180637      0            0          3       0       0     T_TEST
  10. 315C6550   315C6568   144     TM         5180637      0            4          0       3       1     T_TEST
  11. 3203444C   32034460   144     DL         5180637      0            3          0       3       0
  12. 32034394   320343A8   144     DL         5180637      0            3          0       3       0
  13. 31627F54   31627F78   144     TX         655384       57423        6          0       3       0

  14. 6 rows selected.

  15. Elapsed: 00:00:02.12
  16. 10:25:52 SQL> /

  17. ADDR       KADDR      SID     TYPE       ID1          ID2          LMODE      REQUEST CTIME   BLOCK TABLE_NAME
  18. ---------- ---------- ------- ---------- ------------ ------------ ---------- ------- ------- ----- ------------------------------
  19. 315C5A88   315C5AD0   144     TS         6            23571        6          0       18      0
  20. 315C65FC   315C6614   144     TM         18           0            3          0       35      0     OBJ$
  21. 315C66A8   315C66C0   147     TM         5180637      0            0          3       34      0     T_TEST
  22. 315C6550   315C6568   144     TM         5180637      0            4          0       37      1     T_TEST
  23. 3203444C   32034460   144     DL         5180637      0            3          0       37      0
  24. 32034394   320343A8   144     DL         5180637      0            3          0       37      0
  25. 31627F54   31627F78   144     TX         655384       57423        6          0       37      0

  26. 7 rows selected.

  27. Elapsed: 00:00:00.39
  28. 10:26:16 SQL> /

  29. ADDR       KADDR      SID     TYPE       ID1          ID2          LMODE      REQUEST CTIME   BLOCK TABLE_NAME
  30. ---------- ---------- ------- ---------- ------------ ------------ ---------- ------- ------- ----- ------------------------------
  31. 32034394   320343A8   147     CU         754675352    0            6          0       0       0

  32. Elapsed: 00:00:00.21
  33. 10:26:20 SQL> /

  34. ADDR       KADDR      SID     TYPE       ID1          ID2          LMODE      REQUEST CTIME   BLOCK TABLE_NAME
  35. ---------- ---------- ------- ---------- ------------ ------------ ---------- ------- ------- ----- ------------------------------
  36. 315C6550   315C6568   147     TM         5180637      0            3          0       4       0     T_TEST
  37. 31616060   31616084   147     TX         393221       56619        6          0       4       0

  38. Elapsed: 00:00:00.77

会话2:
  1. --回滚修改
  2. 10:26:21 SQL> rollback;

  3. Rollback complete.

  4. Elapsed: 00:00:00.01

会话3:
  1. 10:26:26 SQL> /

  2. no rows selected

  3. Elapsed: 00:00:00.03
  4. 10:26:36 SQL> /

  5. no rows selected

  6. Elapsed: 00:00:00.01

create index online

会话1:
  1. --删除索引,并加online选项重建
  2. 10:26:46 SQL> drop index ix_test_col1;

  3. Index dropped.

  4. Elapsed: 00:00:00.35
  5. 10:26:59 SQL> create index ix_test_col1 on t_test(col1) online;

  6. Index created.

  7. Elapsed: 00:02:47.07

会话2:
  1. --修改指定行的索引字段,此时update不会待索引创建,而是很快结束
  2. 10:26:50 SQL> update t_test set col1=102400 where col2=102400;

  3. 1 row updated.

  4. Elapsed: 00:00:09.21

会话3:
  1. --查看锁的情况
  2. 10:26:53 SQL> /

  3. ADDR       KADDR      SID     TYPE       ID1          ID2          LMODE      REQUEST CTIME   BLOCK TABLE_NAME
  4. ---------- ---------- ------- ---------- ------------ ------------ ---------- ------- ------- ----- ------------------------------
  5. 315C66A8   315C66C0   147     TM         5180637      0            3          0       0       0     T_TEST
  6. 315C6550   315C6568   144     TM         5180637      0            2          0       1       0     T_TEST
  7. 3203444C   32034460   144     DL         5180637      0            3          0       2       0
  8. 32034394   320343A8   144     DL         5180637      0            3          0       2       0
  9. 315C65FC   315C6614   144     TM         5180671      0            4          0       1       0     SYS_JOURNAL_5180670
  10. 31627F54   31627F78   144     TX         327692       57125        6          0       2       0

  11. 6 rows selected.

  12. Elapsed: 00:00:02.49
  13. 10:27:26 SQL> /

  14. ADDR       KADDR      SID     TYPE       ID1          ID2          LMODE      REQUEST CTIME   BLOCK TABLE_NAME
  15. ---------- ---------- ------- ---------- ------------ ------------ ---------- ------- ------- ----- ------------------------------
  16. 315C66A8   315C66C0   147     TM         5180637      0            3          0       6       0     T_TEST
  17. 315C6550   315C6568   144     TM         5180637      0            2          0       7       0     T_TEST
  18. 3203444C   32034460   144     DL         5180637      0            3          0       8       0
  19. 32034394   320343A8   144     DL         5180637      0            3          0       8       0
  20. 315C65FC   315C6614   144     TM         5180671      0            4          0       7       0     SYS_JOURNAL_5180670
  21. 31627F54   31627F78   144     TX         327692       57125        6          0       8       0
  22. 31616060   31616084   147     TX         655370       57432        6          0       6       0

  23. 7 rows selected.

  24. Elapsed: 00:00:02.16
  25. 10:27:38 SQL> /

  26. ADDR       KADDR      SID     TYPE       ID1          ID2          LMODE      REQUEST CTIME   BLOCK TABLE_NAME
  27. ---------- ---------- ------- ---------- ------------ ------------ ---------- ------- ------- ----- ------------------------------
  28. 315C5A88   315C5AD0   144     TS         6            23579        6          0       3       0
  29. 315C66A8   315C66C0   147     TM         5180637      0            3          0       19      0     T_TEST
  30. 315C6550   315C6568   144     TM         5180637      0            2          0       20      0     T_TEST
  31. 3203444C   32034460   144     DL         5180637      0            3          0       21      0
  32. 32034394   320343A8   144     DL         5180637      0            3          0       21      0
  33. 315C65FC   315C6614   144     TM         5180671      0            4          0       20      0     SYS_JOURNAL_5180670
  34. 31627F54   31627F78   144     TX         327692       57125        6          0       21      0
  35. 31616060   31616084   147     TX         655370       57432        6          0       19      0

  36. 8 rows selected.

  37. Elapsed: 00:00:00.17
  38. 10:28:29 SQL> /

  39. ADDR       KADDR      SID     TYPE       ID1          ID2          LMODE      REQUEST CTIME   BLOCK TABLE_NAME
  40. ---------- ---------- ------- ---------- ------------ ------------ ---------- ------- ------- ----- ------------------------------
  41. 315C5A88   315C5AD0   144     TS         6            23579        6          0       52      0
  42. 315C66A8   315C66C0   147     TM         5180637      0            3          0       68      1     T_TEST
  43. 315C6550   315C6568   144     TM         5180637      0            2          4       69      0     T_TEST
  44. 3203444C   32034460   144     DL         5180637      0            3          0       70      0
  45. 32034394   320343A8   144     DL         5180637      0            3          0       70      0
  46. 315C65FC   315C6614   144     TM         5180671      0            4          0       69      0     SYS_JOURNAL_5180670
  47. 31627F54   31627F78   144     TX         327692       57125        6          0       70      0
  48. 31616060   31616084   147     TX         655370       57432        6          0       68      0

  49. 8 rows selected.

  50. Elapsed: 00:00:00.14

  51. --从锁的情况中看到创建索引过程中出现了表SYS_JOURNAL_5180670,查看表的相关信息
  52. 10:28:52 SQL> col partitioned for a20
  53. 10:29:02 SQL> col temporary for a20
  54. 10:29:08 SQL> select table_name, iot_type, partitioned, temporary from user_tables where table_name=\'SYS_JOURNAL_5180670\';

  55. TABLE_NAME                     IOT_TYPE     PARTITIONED          TEMPORARY
  56. ------------------------------ ------------ -------------------- --------------------
  57. SYS_JOURNAL_5180670            IOT          NO                   N

  58. Elapsed: 00:00:00.00
  59. 10:29:10 SQL> set line 100
  60. 10:29:15 SQL> desc SYS_JOURNAL_5180670
  61.  Name                                                  Null?    Type
  62.  ----------------------------------------------------- -------- ------------------------------------
  63.  C0                                                    NOT NULL NUMBER
  64.  OPCODE                                                         CHAR(1)
  65.  PARTNO                                                         NUMBER
  66.  RID                                                   NOT NULL ROWID

  67. 10:29:19 SQL> set line 200
  68. 10:29:37 SQL> select a.*, decode(a.type, \'TM\', b.object_name) table_name
  69. 10:29:48 2 from v$lock a, dba_objects b
  70. 10:29:48 3 where a.id1=b.object_id(+)
  71. 10:29:48 4 and a.sid in(144, 147);

  72. ADDR       KADDR      SID     TYPE       ID1          ID2          LMODE      REQUEST CTIME   BLOCK TABLE_NAME
  73. ---------- ---------- ------- ---------- ------------ ------------ ---------- ------- ------- ----- ------------------------------
  74. 315C5A88   315C5AD0   144     TS         6            23579        6          0       132     0
  75. 315C66A8   315C66C0   147     TM         5180637      0            3          0       148     1     T_TEST
  76. 315C6550   315C6568   144     TM         5180637      0            2          4       149     0     T_TEST
  77. 3203444C   32034460   144     DL         5180637      0            3          0       150     0
  78. 32034394   320343A8   144     DL         5180637      0            3          0       150     0
  79. 315C65FC   315C6614   144     TM         5180671      0            4          0       149     0     SYS_JOURNAL_5180670
  80. 31627F54   31627F78   144     TX         327692       57125        6          0       150     0
  81. 31616060   31616084   147     TX         655370       57432        6          0       148     0

  82. 8 rows selected.

  83. Elapsed: 00:00:00.13

会话2:
  1. --回滚update
  2. 10:27:28 SQL> rollback;

  3. Rollback complete.

  4. Elapsed: 00:00:00.01
  5. 10:30:04 SQL>

会话3:
  1. --查看锁的情况,没有记录,索引创建已结束
  2. 10:29:52 SQL> /

  3. no rows selected

  4. Elapsed: 00:00:00.08
  5. 10:30:07 SQL> /

  6. no rows selected

  7. 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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
IT搬砖

注册时间:2014-07-28

  • 博文量
    665
  • 访问量
    344657