通常来说，全文索引大多用在OLAP环境当中，全文索引擅长于词汇的快速搜索。
一、全文索引和普通b_tree索引对比
SQL> create tablet1 (id int,name varchar(10));
Table created.
SQL> create indext1_ind on t1(name);
Index created.
SQL> create tablet2 as select * from t1;
Table created.
SQL> create indext2_ind on t2(name) indextype is ctxsys.context;
Index created.
SQL> select *from t1 where name like ‘%tom%‘;
ID NAME
--------------------
1 tom
2 tom tom
2 tom tom
Execution Plan
----------------------------------------------------------
Plan hash value:3589342044
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 10 | 2 (0)| 00:00:01 |
|* 2 | INDEX FULL SCAN | T1_IND| 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
2 - filter("NAME" LIKE ‘%tom%‘ AND"NAME" IS NOT NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
676 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
SQL> select *from t2 where contains(name,‘tom‘)>0;
ID NAME
--------------------
1 tom
2 tom tom
2 tom tom
Execution Plan
----------------------------------------------------------
Plan hash value:785228215
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 30 | 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 3 | 30 | 7 (0)| 00:00:01 |
|* 2 | DOMAIN INDEX | T2_IND| | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
2 -access("CTXSYS"."CONTAINS"("NAME",‘tom‘)>0)
Statistics
----------------------------------------------------------
10 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
676 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
SQL> selectobject_name,object_type from user_objects order by 1;
OBJECT_NAME OBJECT_TYPE
-------------------------------------------------
--DR开头的四张表为全文索引的基表
DR$T2_IND$X INDEX
DRC$T2_IND$R INDEX
SYS_IL0000236119C00006$$ INDEX
SYS_IL0000236124C00002$$ INDEX
SYS_IOT_TOP_236122 INDEX
SYS_IOT_TOP_236128 INDEX
SYS_LOB0000236119C00006$$ LOB
OBJECT_NAME OBJECT_TYPE
-------------------------------------------------
SYS_LOB0000236124C00002$$ LOB
T1 TABLE
T1_IND INDEX
T2 TABLE
T2_IND INDEX
二、DML操作对全文索引的影响
以context方式创建的全文索引并不是基于事务的，默认情况下，即使一个dml操作提交，信息也不会更新到全文索引中。
1、insert 操作
SQL> create tablet(name varchar2(30));
Table created.
SQL> create indext_ind on t(name) indextype is ctxsys.context;
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,‘dba‘) >0;
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