首先创建两个表:
SQL> create table t1(id int,name varchar(10),value varchar(10));
表已创建。
SQL> insert into t1 values(1,'a','aa a');
已创建 1 行。
SQL> insert into t1 values(1,'b','bb b');
已创建 1 行。
SQL> commit;
提交完成。
SQL> create table t2 as select * from t1;
表已创建。
SQL> create index ind_t1_value on t1(value); --普通索引
索引已创建。
SQL> create index ind_t2_value on t2(value) indextype is ctxsys.context; --全文索引
索引已创建。
SQL> select * from t1;
ID NAME VALUE
---------- ---------- ----------
2 a aa a
2 b bb b
SQL> select * from t2;
ID NAME VALUE
---------- ---------- ----------
1 a aa a
1 b bb b
这样的操作,对于普通索引是不可能走索引的:
SQL> select * from t1 where value like '%a%';
ID NAME VALUE
---------- ---------- ----------
2 a aa a
执行计划
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 27 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
全文索引:找到value中所有包含a字符的记录。
SQL> select * from t2 where contains(value,'a') > 0;
ID NAME VALUE
---------- ---------- ----------
1 a aa a
执行计划
----------------------------------------------------------
Plan hash value: 2648594605
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 39 | 4 (0)| 00:00:01 |
|* 2 | DOMAIN INDEX | IND_T2_VALUE | | | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
--contains函数相当于上面的like '%a%'效果是一样的。
注意:
全文索引占用的空间非常大。
全文索引和DML操作:
insert操作不会更新全文索引信息。可以手工同步:
SQL> alter index ind_t2_value rebuild parameters('sync');
索引已更改。
delete操作会更新全文索引信息。
update操作相当于先delete,然后在insert。
可以使用如下的语法创建索引,在commit的时候更新全文索引信息:
SQL> create index ind_t2_name on t2(name) indextype is ctxsys.context parameters('sync (on commit)');
索引已创建。
总而言之:如果要对字段使用模糊查询,使用普通索引,只有全表扫描。想要使用索引,就只有使用全文索引。