oracle全文索引之commit與DML操作

我們知道，無論對多大的資料做DML操作，執行commit都可以很快完成，但如何刪除建有全文索引的記錄，在commit時可能會很慢。
根據推斷可以知道是由於域索引造成的，那麼在有域索引的情況下，commit時，oracle還做了那些額外工作呢？

一、建立測試資料

SQL> create table t_domain(id number,doc varchar2(100));

Table created

SQL> create index idx_domain on t_domain(doc) indextype is ctxsys.context;

Index created

SQL>

SQL> insert into t_domain values(1,'this is beijing');

1 row inserted

SQL> insert into t_domain values(2,'this is shanghai');

1 row inserted

SQL> commit;

Commit complete

SQL> exec ctx_ddl.sync_index('idx_domain');

PL/SQL procedure successfully completed

二、測試

1、delete資料
--重新整理共享池
SQL> alter system flush shared_pool;

System altered

SQL> @d:sqlgettrace

TRACE_FILE_NAME
--------------------------------------------------------------------------------
E:oracleadminoracle9iudumporacle9i_ora_2580.trc

--刪除前可以查出資料
SQL> select * from t_domain where contains(doc,'shanghai')>0;

ID DOC
---------- --------------------------------------------------------------------------------
2 this is shanghai

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered

--刪除資料
SQL> delete from t_domain where id=2;

1 row deleted
--刪除後執行同樣的sql不能查出資料
SQL> select * from t_domain where contains(doc,'shanghai')>0;

ID DOC
---------- --------------------------------------------------------------------------------

SQL> commit;

Commit complete

SQL> alter session set events '10046 trace name context off';

Session altered
--提交後也不能查出資料
SQL> select * from t_domain where contains(doc,'shanghai')>0;

ID DOC
---------- --------------------------------------------------------------------------------

SQL>

E:oracleadminoracle9iudump>tkprof oracle9i_ora_2580.trc 2580.txt
摘取過程的DML如下：
--發出刪除命令
delete from t_domain
where
id=2

--刪除$K表的資料
DELETE "SUK"."DR$IDX_DOMAIN$K"
WHERE
TEXTKEY = :rid RETURNING DOCID INTO :docid
--向$N表插入資料
INSERT INTO "SUK"."DR$IDX_DOMAIN$N" (NLT_DOCID,NLT_MARK)
VALUES
(:docid,'U')
--向DR$DELETE表插入資料
INSERT INTO DR$DELETE (DEL_IDX_ID,DEL_IXP_ID,DEL_DOCID)
VALUES
(:idxid,:ixpid,:docid)
--提交
commit
--同步索引
begin ctxsys.syncrn(:idxid, :ixpid, :rtabnm); end;
--刪除DR$DELETE表資料
delete from ctxsys.dr$delete
where
del_idx_id = :idxid and del_ixp_id = :ixpid

根據《oracle全文索引之如何實現查詢》，提交前，oracle會在得到的結果中排除DR$DELETE存在的docid，所以在提交前不能查詢到剛被更新的資料。
在commit時，oracle會同步索引，把$R表中已經被刪除的docid在DATA欄位對應的資訊刪除，所以，提交後，雖然$I表的資訊沒有被刪除，但根據$R表的資訊已經被刪除，被update的資料在commit後是不會立刻能被查出來的，直到索引被同步。
同時可以看到，在commit後，還要執行一些delete操作來維護域索引，並且在執行commit同時同步全文索引，所以，可以推測commit不會立刻完成，什麼時候能完成視刪除的資料量而定。

2、插入記錄
--重新整理共享池
SQL> alter system flush shared_pool;

System altered

SQL> @d:sqlgettrace

TRACE_FILE_NAME
--------------------------------------------------------------------------------
E:oracleadminoracle9iudumporacle9i_ora_3712.trc

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered

SQL> insert into t_domain values(5,'this is shenzhen');

1 row inserted

SQL> commit;

Commit complete

SQL> alter session set events '10046 trace name context off';

Session altered
--插入資料後不能立刻查詢新資料
SQL> select * from t_domain where contains(doc,'shenzhen')>0;

ID DOC
---------- --------------------------------------------------------------------------------

E:oracleadminoracle9iudump>tkprof oracle9i_ora_3712.trc 3712.txt
摘錄trace檔案中的DML語句如下：
--發出插入記錄命令
insert into t_domain
values
(5,'this is shenzhen')

--把新記錄資訊插入到DR$PENDING中
INSERT INTO CTXSYS.DR$PENDING (PND_CID, PND_PID, PND_ROWID, PND_TIMESTAMP,
PND_LOCK_FAILED)
VALUES
(:1, :2, :3, SYSDATE, 'N')

commit

從以上的sql可以看出，在插入一條記錄時，只是把新加記錄的資訊記錄到DR$PENDING中，在commit前後沒有同步索引，也就是在$I表中並沒有新增的記錄的資訊，所以新增資料在commit前後都不能被查詢到。
同時可以看出，，在commit後資料庫並沒有做其他工作，所以，可以推測無論插入多少資料，commit操作都可以很快完成。

3、更新非索引欄位

--重新整理共享遲
SQL> alter system flush shared_pool;

System altered

--得到當前會話對應的trace檔名
SQL> @d:sqlgettrace

TRACE_FILE_NAME
--------------------------------------------------------------------------------
E:oracleadminoracle9iudumporacle9i_ora_2032.trc

--更新前可以查詢資料
SQL> select * from t_domain where contains(doc,'shanghai')>0;

ID DOC
---------- --------------------------------------------------------------------------------
2 this is shanghai

--設定10046
SQL> alter session set events '10046 trace name context forever,level 12';

Session altered

SQL> update t_domain set id=2 where id=2;

1 row updated

SQL> select * from t_domain where contains(doc,'shanghai')>0;

ID DOC
---------- --------------------------------------------------------------------------------
2 this is shanghai

SQL> commit;

Commit complete

SQL> alter session set events '10046 trace name context off';

Session altered

SQL> select * from t_domain where contains(doc,'shanghai')>0;

ID DOC
---------- --------------------------------------------------------------------------------
2 this is shanghai

--更新後和commit後都可以查詢原來的資料

格式化trace檔案
E:oracleadminoracle9iudump>tkprof oracle9i_ora_2032.trc 2032.txt

把trace檔案中執行過的DML sql（select除外）摘錄如下：
update t_domain set id=2
where
id=2

commit

可見，在只更新非域索引欄位的情況下，commit過程並沒有做其他特別的事情，可以很快的完成。
被更新記錄對應域索引的值沒有發生變化，域索引也不需要維護，原來能查詢出來的記錄仍然可以被查出來。

4、更新索引欄位
--重新整理共享池
SQL> alter system flush shared_pool;

System altered
--得到trace檔名
SQL> @d:sqlgettrace

TRACE_FILE_NAME
--------------------------------------------------------------------------------
E:oracleadminoracle9iudumporacle9i_ora_3412.trc
--更新索引欄位前可以查詢資料
SQL> select * from t_domain where contains(doc,'beijing')>0;

ID DOC
---------- --------------------------------------------------------------------------------
11 this is beijing

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered

SQL> update t_domain set doc='this is beijing' where doc='this is beijing';

1 row updated

SQL> select * from t_domain where contains(doc,'beijing')>0;

ID DOC
---------- --------------------------------------------------------------------------------

SQL> commit;

Commit complete

SQL> alter session set events '10046 trace name context off';

Session altered

SQL> select * from t_domain where contains(doc,'beijing')>0;

ID DOC
---------- --------------------------------------------------------------------------------

--在更新索引欄位後，無論是否commit都無法查詢查詢更新的內容

我們看看，在這段過程中到底執行了什麼DML操作：
update t_domain set doc='this is beijing'
where
doc='this is beijing'

--刪除$K表的docid相關資訊
DELETE "SUK"."DR$IDX_DOMAIN$K"
WHERE
TEXTKEY = :rid RETURNING DOCID INTO :docid

--把刪除的docid記錄到$N表中
INSERT INTO "SUK"."DR$IDX_DOMAIN$N" (NLT_DOCID,NLT_MARK)
VALUES
(:docid,'U')
--把刪除的docid記錄到DR$DELETE中，為了預防使用者在同一個會話中查詢沒有提交資訊
INSERT INTO DR$DELETE (DEL_IDX_ID,DEL_IXP_ID,DEL_DOCID)
VALUES
(:idxid,:ixpid,:docid)
--把更新後的資訊插入到DR$PENDING中
INSERT INTO CTXSYS.DR$PENDING (PND_CID, PND_PID, PND_ROWID, PND_TIMESTAMP,
PND_LOCK_FAILED)
VALUES
(:1, :2, :3, SYSDATE, 'N')

--提交
commit
--同步索引（只同步被刪除的docid？）
begin ctxsys.syncrn(:idxid, :ixpid, :rtabnm); end;
這個過程第三個引數是$R表的表名。我猜測這一步是刪除$R表中已經被刪除的docid在DATA欄位對應的資訊。

--刪除DR$DELETE中已經被同步的docid
delete from ctxsys.dr$delete
where
del_idx_id = :idxid and del_ixp_id = :ixpid

從上面sql可以看出，update索引列實際上就是先delete，再insert的過程。
這個例子中，被更新的值在更新前後是一樣的，屬於比較特殊的情況，但原理是一樣的。
參看《oracle全文索引之如何實現查詢》我們可以知道，提交前，oracle會在得到的結果中排除DR$DELETE存在的docid，所以在提交前不能查詢到剛被更新的資料。
在commit時，oracle會同步索引，把$R表中已經被刪除的docid在DATA欄位對應的資訊刪除，
所以，提交後，雖然$I表的舊資訊沒有被刪除，但由於$R表的資訊已經被刪除，所以更新前的資料是不會被查出來的，這樣也避免了使用者查到錯誤的資料。
以上是update過程中的delete部分；對於insert部分，其產生的資訊僅存在於DR$PENDING中，$I還沒有任何與之相關的資訊，所以提交後更新後的資料也不會馬上被查詢出來。
同時可以看到，在commit後，還要執行一些delete操作來維護域索引，所以，可以推測，在update索引列後，執行commit並不會能立刻完成，什麼時候能完成，視被更新的索引的記錄多少而定。

總結：
1、基於域索引的insert執行commit可以很快完成，但在同步索引前不能查詢新資料。
2、基於域索引的update操作，如果更新的是域索引列，在執行commit時還需要做其他工作，commit完成時間視更新資料的多少而定。但commit時不會同步索引，在同步索引前不能查詢新資料。
3、基於域索引的update操作，如果更新的不是索引列，那麼整個update過程與域索引無關，commit可以很快完成。
4、基於域索引的delete操作，在執行commit時需要同步索引和刪除資料，故commit不能很快完成。commit的完成時間視刪除的資料量而定。由於commit時同步了索引，故commit後被刪除的資料也立刻不會再被查到。