通常来说，全文索引大多用在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.
SQ