NAME
------------------------------
i am an oracle dba
he is an oracle dba
SQL> select *from ctxsys.dr$pending;
no rows selected
2、delete操作
SQL> select *from t;
NAME
------------------------------
i am an oracle dba
he is an oracle dba
SQL> delete fromt where name='he is an oracle dba';
1 row deleted.
SQL> select *from t where contains(name,'dba') >0;
NAME
------------------------------
i am an oracle dba
SQL> select *from ctxsys.dr$pending;
no rows selected
SQL> select *from ctxsys.dr$delete;
DEL_IDX_IDDEL_IXP_ID DEL_DOCID
-------------------- ----------
1084 0 2
这里我们看到全文索引立即生效了，至于ctxsys.dr$delete里面的数据是delete操作的中间状态，用来维护一个事物，无论事物提交或者回滚。
SQL> rollback;
Rollback complete.
SQL> select *from t where contains(name,'dba') >0;
NAME
------------------------------
i am an oracle dba
he is an oracle dba
SQL> select *from ctxsys.dr$delete;
no rows selected
3、update操作
update操作相当于delete+insert操作，所以默认情况下需要手动刷新全文索引。
SQL> update t setname='oracle dba' where name='i am an oracle dba';
1 row updated.
SQL> select *from ctxsys.dr$delete;
DEL_IDX_IDDEL_IXP_ID DEL_DOCID
-------------------- ----------
1084 0 1
SQL> select *from ctxsys.dr$pending;
PND_CID PND_PID PND_ROWID PND_TIMESTAMP P
-------------------- ------------------ ------------------ -
1084 0 AABGmVAAEAAAADmAAA 03-APR-14 N
SQL> select *from t where contains(name,'dba') > 0;
NAME
------------------------------
he is an oracle dba
SQL> alter indext_ind rebuild parameters ('sync');
Index altered.
SQL> select *from t where contains(name,'dba') > 0;
NAME
------------------------------
he is an oracle dba
oracle dba
由于全文索引创建对象大多是海量数据的表，dml操作如果实时更新会影响到系统性能。创建全文索引同步的三个选项：
manual：默认选项
every：在一个时间段后更新索引
on commitdml：在事务提交后更新索引
语法如下：
create index t_indon t(name) indextype is ctxsys.context parameters('sync (on commit)');
查看全文索引信息和性能的工具包ctx_report