创建了索引但是ref为null 创建索引加online

转载

daleiwang 2024-04-28 14:17:51

文章标签 创建了索引但是ref为null SQL SYS ico 文章分类 数据仓库大数据

在 oracle中创建索引时
有用create index online与不带online创建索引的区别是什么？
online状态创建索引有何好处？谢谢！

解决方案 »

You can create and rebuild indexes online. This enables you to update base tables at the same time you are building or rebuilding indexes on that table. You can perform DML operations while the index build is taking place, but DDL operations are not allowed.

online不索引到当前SQL的执行不带online的话，ORACLE会在创建索引时加上相关的锁，会阻塞其它对此表进行写操作的session

一般建立索引会引起锁表，不允许进行数据更新等DML操作；
如果建立索引时指定关键字online，那么只会在表上加一个共享锁，仅不允许变更表的定义。
但可以对数据进行修改。

online 会在创建索引和rebuilt索引的的时候允许DML操作，不设置online的话会影响DML操作影响并发

之前写了一篇《创建索引的两种方式比对》，介绍了创建索引的两种方法，即online和非online，当时给出的结论，如下所示，

(1) online和非online方式创建索引，效果相同。
(2) online方式创建索引，由于使用了一张临时表，以ROW SHARE锁表，不会阻塞原表DML的语句，非online方式创建索引，则会以SHARE NOWAIT锁表，阻塞原表DML语句。
(3) 由于online方式创建索引，Oracle执行工作复杂，因此比非online方式创建索引用时要久。
(4) 一句话“不能什么便宜均占着”，要么选择可以快速创建索引的非online方式但创建期间会锁表阻塞DML语句，要么选择不会阻塞原表DML语句的online方式创建索引但用时较久。从实际来看，我理解，若小表选择任何一种均可，大表，尤其是生产系统，找不着非高峰时间，选择online更合理一些，若不关注是否影响DML操作，则两种方式均可以了。

我们再做下实验，创建测试表，

SQL> create table tbl(id number, name varchar2(1));
 Table created.

场景一：非online创建索引

session 1：插入一条数据，但不提交，

SQL> select sid from v$mystat where rownum = 1;
  SID
 ----------
   1

 SQL> insert into tbl values(2, 'b');        
 1 row created.

此时会出现一个表级锁，和一个行级锁，

SQL> select sid, type, id1, lmode, request, block from v$lock where sid in (1, 40)
    and type in ('TX', 'TM'); 
 SID    TYPE    ID1    LMODE    REQUEST   BLOCK
 ----- -------- ---------- ---------- ---------- ----------
   1    TM    14338     3        0      0
   1    TX    131079    6        0      0

 SQL> select object_name from dba_objects 
    where object_id = 14338;
 OBJECT_NAME
 --------------------------------------------------------------------------------
 TBL

关于锁的模式，

0：none
1：null 空
2：Row-S 行共享(RS)：共享表锁
3：Row-X 行专用(RX)：用于行的修改
4：Share 共享锁(S)：阻止其他DML操作
5：S/Row-X 共享行专用(SRX)：阻止其他事务操作
6：exclusive 专用(X)：独立访问使用

此时session 2创建索引，注意未加online关键字，提示ORA-00054错误，表示资源繁忙，

SQL> select sid from v$mystat where rownum = 1;     
  SID
 --------
   40

 SQL> create index idx_tbl on tbl(id);
 create index idx_tbl on tbl(id)
                        *
 ERROR at line 1:
 ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

从现象上看，说明使用非online创建索引，如果表上存在未提交事务，则无法执行，提示错误ORA-00054，直到所有事务已经提交。

如果上述操作反向来，即先create index创建索引，创建过程中，执行DML操作，此时DML操作会被hang，相当于创建索引阻碍了DML。

从锁的情况看，此时create index的session会请求mode=4的TM锁，而insert未提交的session未释放锁，block=1，LMODE为2、3、4不影响DML(insert,delete,update,select)操作, 但DDL(alter、drop等)操作会提示ORA-00054错误，这和现象一致，

 SID   TY     ID1     LMODE   REQUEST   BLOCK
 ------ ------- ---------- --------- --------- ---------
  40    TM    21504     0      4      0
   1    TM    21504     3      0      1
   1    TX    524310    6      0      0

另外，我们从10046的trace(BISAL_ora_52965.trc，大小117K)，发现非online方式创建索引，一共SELECT数据字典共计23次，UPDATE了2次，INSERT了4次，总共对数据字典，执行31次操作，才完成了创建索引的工作，

grep 'select ' BISAL_ora_52965.trc | wc -l
 23

 grep 'update ' BISAL_ora_52965.trc | wc -l
 2

 grep 'delete ' BISAL_ora_52965.trc | wc -l
 0

 grep 'insert ' BISAL_ora_52965.trc | wc -l
 4

其中所有的DML操作如下所示，使用SHARE MODE NOWAIT方式，LOCK表，对obj$、seg$、icol、ind$等数据字典，进行增删改操作，

LOCK TABLE 'TBL' IN SHARE MODE  NOWAIT
 insert into obj$
 insert into seg$
 update seg$
 insert into icol$
 insert into ind$
 update obj$

场景二：online创建索引

同样，session 1插入一条记录，但不提交，session 2执行create index ... online操作，不会像场景一抛出错误，而是等待状态，直到session 1提交，

SQL> create index idx_tbl on tbl(id) online;

从现象上看，说明使用online来创建索引，如果表上存在未提交事务，则无法执行，不会报错，但会等待，直到所有事务已经提交。

如果上述操作反向来，先执行了create index ... online，创建过程中，执行DML操作，此时这些DML能执行，不会影响。

等待状态期间，检索锁的信息，除了ID1=14338对应TBL表存在TM锁的持有和等待，还对ID1=14341对应SYS_JOURNAL_14340表有TM锁，同时这两个会话，对TBL表的行，存在TX锁的持有和等待，另外一个TX就是对SYS_JOURNAL_14340的行持锁，

SQL> select sid, type, id1, lmode, request, block from v$lock where sid in (1, 40)
    and type in ('TX', 'TM');
  SID   TYPE   ID1    LMODE    REQUEST   BLOCK
 ------ ------- -------- ---------- ---------- ----------
  40    TX   131072    0        4      0
  40    TM   14338     2        0      0
  40    TM   14341     4        0      0
   1    TM   14338     3        0      0
  40    TX   327682    6        0      0
   1    TX   131072    6        0      1

 SQL> select object_name from dba_objects where object_id = 14341;
 OBJECT_NAME
 --------------------------------------------------------------------------------
 SYS_JOURNAL_14340

 SQL> select object_name from dba_objects where object_id = 14338;
 OBJECT_NAME
 --------------------------------------------------------------------------------
 TBL

注：SYS_JOURNAL_14340相当于中间表，之所以online不影响正常DML，就是因为他用了中间表，类似物化视图的机制，对中间表进行的操作，

从10046的trace(BISAL_ora_53117.trc，大小371K)来看，发现使用online方式创建索引，一共SELECT数据字典共计54次，INSERT了12次，UPDATE了5次，DELETE了46次，总共对数据字典，执行117次操作，才完成了创建索引的工作，相比非online，onilne方式创建索引，做了更多的操作，

grep 'select ' BISAL_ora_53117.trc | wc -l
 54

 grep 'insert ' BISAL_ora_53117.trc | wc -l
 12

 grep 'update ' BISAL_ora_53117.trc | wc -l
 5

 grep 'delete ' BISAL_ora_53117.trc | wc -l
 46

其中所有DML操作的调用栈，如下所示，第一步操作是LOCK TABLE，模式ROW SHARE，接下来创建了中间表，并创建唯一索引，以及各种DML数据字典的操作，

LOCK TABLE 'TBL' IN ROW SHARE MODE
 create table 'BISAL'.'SYS
 update obj$
 update con$
 CREATE UNIQUE INDEX 'BISAL'.'SYS_IOT_TOP_14344' on 'BISAL'.'SYS_JOURNAL_14343'('C0','RID') INDEX ONLY TOPLEVEL TABLESPACE 'TB' STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) NOPARALLEL
 insert into seg$
 insert into icol$
 insert into ind$
 insert into tab$
 insert into col$
 insert into ccol$
 insert into cdef$
 insert into icol$
 insert into ind_online$
 update seg$
 delete from ind_stats$ where obj#=:1
 update ind$
 UPDATE ind_online$ SET flags=:2 WHERE obj#=:1
 insert into sys.wri$_optstat_ind_history
 delete from ind_stats$ where obj#=:1
 delete from object_usage
 delete from sys.cache_stats_1$
 delete com$
 delete from hist_head$
 delete from compression$
 DELETE FROM sys.sumdelta$
 DELETE FROM sys.sumpartlog$
 DELETE FROM sys.snap_loadertime$
 BEGIN aw_drop_proc(ora_dict_obj_type, ora_dict_obj_name, ora_dict_obj_owner);
 delete from ind_online$
 delete from ind_stats$
 delete from idl_ub1$
 delete from idl_ub2$
 delete from idl_sb4$
 delete from ncomp_dll$
 delete from idl_char$
 delete from idl_ub2$
 delete from idl_sb4$
 delete from ncomp_dll$
 delete from idl_ub1$
 delete from idl_char$
 delete from idl_ub2$
 delete from idl_sb4$
 delete from ncomp_dll$
 delete from objauth$
 delete from col$
 delete from icol$
 delete from icoldep$
 delete from jijoin$
 delete from jirefreshsql$
 delete from ccol$
 delete from ind$
 delete from cdef$
 delete ecol$  -- 语句缺少from关键字
 delete from tab$
 delete coltype$  -- 语句缺少from关键字
 delete from subcoltype$
 delete ntab$
 delete lob$  -- 语句缺少from关键字
 delete from opqtype$
 delete from cdef$
 delete from objauth$
 delete from obj$
 update seg$
 delete from con$
 delete from seg$

总结来讲，

1. 非online方式创建索引，不能存在正在执行的DML，否则create index就会抛出ORA-00054。如果非online创建索引过程中，有正在执行的DML，则这些操作会hang，直到create index执行完成。换句话说，对于频繁DML的表，因为并发操作，有可能一直无法创建索引，也有可能创建索引的过程中，影响其他DML操作。

2. online方式创建的索引，不能存在正在执行的DML，否则create index ... online不会抛出错误，但会一直等待，直到所有DML完成。如果使用online创建索引过程中，有正在执行的DML，则不会影响。换句话说，对于频繁DML的表，因为并发操作，有可能一直无法创建索引，但是在创建索引的过程中，不会影响其他DML操作。

3. 无论online还是非online，如果创建的时候，有其他的DML操作，则都不会成功，不同的是，online是等待，非online是抛出错误。

4. 对于非online，创建索引过程中，会阻碍其他的DML，对于online，创建过程中，不会阻碍其他DML。

5. 相对来说，online比非online对现有DML的影响较小，但是二者，都存在无法创建的可能。

6. 针对无法创建的可能，一种方法，就是暂停业务，暂停所有DML操作，这样可以顺利地创建完成，此时选择非online，相比使用online，可以更快创建

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。