CONTEXT索引对COMMIT操作的影响

http://yangtingkun.itpub.net/post/468/231000

 

ORACLE的CONTEXT索引不是实时同步的，Oracle为了保证索引的碎片程度以及普通DML的执行效率，并没有在进行DML操作的同时进行索引的维护。而是选择了定期或根据需要时进行维护的方法。

但是Oracle在处理INSERT操作和处理DELETE操作上又有所差异。

今天在论坛上看到了有人遇到了这个问题：http://www.itpub.net/670998.html。这里就简单分析一下。

看下面的例子：

SQL> CREATE TABLE T (ID NUMBER, DOCS VARCHAR2(1000));

表已创建。

SQL> INSERT INTO T SELECT ROWNUM, OBJECT_NAME || ' ' || OBJECT_TYPE FROM USER_OBJECTS;

已创建96行。

SQL> COMMIT;

提交完成。

SQL> CREATE INDEX IND_T_DOCS ON T (DOCS) INDEXTYPE IS CTXSYS.CONTEXT;

索引已创建。

SQL> SELECT * FROM T WHERE CONTAINS(DOCS, 'INSERT') > 0;

未选定行

SQL> SELECT * FROM T WHERE CONTAINS(DOCS, 'DUMMY') > 0;

ID DOCS
---------- ------------------------------------------------------------
12 DUMMY TABLE

SQL> INSERT INTO T VALUES (100, 'INSERT EXAMPLE');

已创建 1 行。

SQL> DELETE T WHERE ID = 12;

已删除 1 行。

SQL> SELECT * FROM T WHERE CONTAINS(DOCS, 'INSERT') > 0;

未选定行

SQL> SELECT * FROM T WHERE CONTAINS(DOCS, 'DUMMY') > 0;

未选定行

SQL> ROLLBACK;

回退已完成。

从这里已经可以很清晰的看到INSERT和DELETE的区别了，对于CONTEXT索引，INSERT操作必须等待索引同步的时候才会将修改同步到索引中，而对于DELETE操作，删除完成之后，就发现删除的数据已经无法从索引中查询到了。

Oracle这样处理是有道理的，INSERT的数据没有插入，只会导致当前查询不到数据，并不会造成很大问题，但是如果DELETE了数据，而索引没有同步的话，就会在索引中看到已经从数据库中删除了的数据。这样通过索引中的ROWID去访问表的时候就会出现错误。

但是上面的例子只是说明了INSERT和DELETE操作的不同，并没有说明这些和COMMIT操作有什么关系。

下面看一下实际上Oracle在处理两个不同的COMMIT时有何区别：

SQL> CONN YANGTK/YANGTK@YTK已连接。
SQL> INSERT INTO T VALUES (100, 'INSERT');

已创建 1 行。

SQL> ALTER SESSION SET SQL_TRACE = TRUE;

会话已更改。

SQL> COMMIT;

提交完成。

SQL> ALTER SESSION SET SQL_TRACE = FALSE;

会话已更改。

SQL> CONN YANGTK/YANGTK@YTK已连接。
SQL> DELETE T WHERE ID = 12;

已删除 1 行。

SQL> ALTER SESSION SET SQL_TRACE = TRUE;

会话已更改。

SQL> COMMIT;

提交完成。

SQL> ALTER SESSION SET SQL_TRACE = FALSE;

会话已更改。

上面分别对INSERT操作后面的COMMIT和DELETE操作后面的COMMIT进行了SQL TRACE，而采用重新连接再进行SQL TRACE是为了避免Oracle重用会话缓存的游标，从而使最终的结果更加清晰。

下面的事情就比较简单了，对比二者产生的TRACE信息就可以了。

INSERT语句后面的TRACE很简单：

*** ACTION NAME:() 2006-11-21 16:39:03.953
*** MODULE NAME:(SQL*Plus) 2006-11-21 16:39:03.953
*** SERVICE NAME:(ytk) 2006-11-21 16:39:03.953
*** SESSION ID:(142.3993) 2006-11-21 16:39:03.953
=====================
PARSING IN CURSOR #14 len=34 dep=0 uid=56 oct=42 lid=56 tim=1844493262 hv=3913151867 ad='1bdd93a4'
ALTER SESSION SET SQL_TRACE = TRUE
END OF STMT
EXEC #14:c=0,e=517,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1844493257
=====================
PARSING IN CURSOR #2 len=6 dep=0 uid=56 oct=44 lid=56 tim=1844592400 hv=255718823 ad='0'
COMMIT
END OF STMT
PARSE #2:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1844592396
XCTEND rlbk=0, rd_only=0
EXEC #2:c=0,e=243,p=0,cr=0,cu=1,mis=0,r=0,dep=0,og=0,tim=1844593182
=====================
PARSING IN CURSOR #14 len=35 dep=0 uid=56 oct=42 lid=56 tim=1845133086 hv=4067503723 ad='18536584'
ALTER SESSION SET SQL_TRACE = FALSE
END OF STMT
PARSE #14:c=0,e=459,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1845133082
EXEC #14:c=0,e=518,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1845134155

可以清晰的看到，Oracle所进行的仅仅是一个COMMIT操作而已。

下面看看DELETE操作后面的COMMIT，由于TRACE文件包含的内容很多，这里仅仅包含用户执行的命令和系统第一次调用，将其他的内容忽略掉：

*** ACTION NAME:() 2006-11-21 16:39:33.953
*** MODULE NAME:(SQL*Plus) 2006-11-21 16:39:33.953
*** SERVICE NAME:(ytk) 2006-11-21 16:39:33.953
*** SESSION ID:(142.3995) 2006-11-21 16:39:33.953
=====================
PARSING IN CURSOR #2 len=34 dep=0 uid=56 oct=42 lid=56 tim=1874497064 hv=3913151867 ad='1bdd93a4'
ALTER SESSION SET SQL_TRACE = TRUE
END OF STMT
EXEC #2:c=0,e=529,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1874497059
=====================
PARSING IN CURSOR #2 len=6 dep=0 uid=56 oct=44 lid=56 tim=1874545681 hv=255718823 ad='0'
COMMIT
END OF STMT
PARSE #2:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1874545677
XCTEND rlbk=0, rd_only=0
=====================
PARSING IN CURSOR #11 len=72 dep=1 uid=0 oct=47 lid=0 tim=1874555307 hv=232792201 ad='184f6b90'
begin ctxsys.syncrn(:idxownid, :idxoname, :idxid, :ixpid, :rtabnm); end;
END OF STMT
PARSE #11:c=0,e=588,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1874555301
=====================
.
.
.
=====================
PARSING IN CURSOR #17 len=28 dep=1 uid=0 oct=7 lid=0 tim=1874786281 hv=1514546928 ad='1878fe28'
delete from ctxsys.dr$delete
END OF STMT
PARSE #17:c=0,e=793,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1874786277
EXEC #17:c=0,e=338,p=0,cr=12,cu=2,mis=0,r=1,dep=1,og=4,tim=1874787280
STAT #17 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE DR$DELETE (cr=12 pr=0 pw=0 time=289 us)'
STAT #17 id=2 cnt=1 pid=1 pos=1 obj=40927 op='INDEX FULL SCAN DRC$DEL_KEY (cr=12 pr=0 pw=0 time=162 us)'
EXEC #2:c=203125,e=235124,p=0,cr=476,cu=7,mis=0,r=0,dep=0,og=0,tim=1874787781
=====================
PARSING IN CURSOR #15 len=35 dep=0 uid=56 oct=42 lid=56 tim=1874872051 hv=4067503723 ad='18536584'
ALTER SESSION SET SQL_TRACE = FALSE
END OF STMT
PARSE #15:c=0,e=60,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1874872046
EXEC #15:c=0,e=531,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1874873142

很明显Oracle在执行COMMIT的时候，还运行了ctxsys.syncrn过程，从名称上判断，这个就是一个索引同步的过程。还同时运行了ctxsys.dr$delete的删除操作。这个表保存的就是DELETE操作产生影响的记录信息。在DELETE操作后，DELETE影响的数据马上被保存到这个表中，而Oracle查询的时候会根据这个表中的信息过滤掉已经删除的数据，当COMMIT操作时，Oracle才使用上面的同步过程来真正清除索引中的记录，同时删除这张表的信息。

这就是为什么一个大的DELETE操作过后COMMIT操作的时间会变得很长。

上面仅分析了INSERT和DELETE，对于CONTEXT索引字段的UPDATE操作，等效于一个INSERT加上一个DELETE操作。

因此，如果建立了CONTEXT索引后，对索引字段进行大批量的UPDATE操作或对表进行大量的DELETE操作，很可能导致COMMIT操作运行时间变得很长。