Index created.
SQL> insert intot values('i am an oracle dba');
1 row created.
SQL> commit；
insert数据已提交，我们看看全文索引是否已更新
SQL> setautotrace on
SQL> select *from t where name like '%dba%';
NAME
------------------------------
i am an oracle dba
Execution Plan
----------------------------------------------------------
Plan hash value:1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 17 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
1 - filter("NAME" IS NOT NULL AND"NAME" LIKE '%dba%')
Note
-----
- dynamic sampling used for this statement(level=2)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
538 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set line 200
SQL> select *from t where contains(name,'dba') >0;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value:315187259
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 29 | 4 (0)| 00:00:01 |
|* 2 | DOMAIN INDEX | T_IND| | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
2 -access("CTXSYS"."CONTAINS"("NAME",'dba')>0)
Note
-----
- dynamic sampling used for this statement(level=2)
Statistics
----------------------------------------------------------
1829 recursive calls
0 db block gets
2696 consistent gets
30 physical reads
0 redo size
332 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
164 sorts (memory)
0 sorts (disk)
0 rows processed
以上发现，全文索引并没有自动更新，而是把记录存放在线面的ctxsys.dr$pending表中。
SQL> setautotrace off
SQL> select *from ctxsys.dr$pending;
PND_CID PND_PID PND_ROWID PND_TIMESTAMP P
-------------------- ------------------ ------------------ -
1084 0 AABGmVAAEAAAADmAAA 03-APR-14 N
SQL> insert intot values('he is an oracle dba');
1 row created.
SQL> commit;
Commit complete.
SQL> select *from ctxsys.dr$pending;
PND_CID PND_PID PND_ROWID PND_TIMESTAMP P
-------------------- ------------------ ------------------ -
1084 0 AABGmVAAEAAAADmAAA 03-APR-14 N
1084 0 AABGmVAAEAAAADmAAB 03-APR-14 N
SQL> select *from t where contains(name,'dba') >0;
no rows selected
为了把信息同步到全文索引中，我们需要手工同步：
SQL> alter indext_ind rebuild parameters ('sync');
Index altered.
SQL> select *from t where contains(name,'