CONTEXT索引對COMMIT操作的影響 （ZT）

ORACLE的CONTEXT索引不是實時同步的，Oracle為了保證索引的碎片程度以及普通DML的執行效率，並沒有在進行DML操作的同時進行索引的維護。而是選擇了定期或根據需要時進行維護的方法。

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

[@more@]今天在論壇上看到了有人遇到了這個問題：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操作執行時間變得很長。