lastwinner 发表于 2014-11-30 11:22
where 中文字段A like '%关键词%'
这样的查询是铁定用不到索引的
SQL> create table td(a varchar(10) primary key);
表已创建。
已用时间: 00: 00: 00.08
SQL> insert into td values('已用时间');
已创建 1 行。
已用时间: 00: 00: 00.01
SQL> insert into td values('表已创建。');
已创建 1 行。
SQL> select index_name from user_indexes where table_name='TD';
INDEX_NAME
------------------------------
SYS_C0067929
已用时间: 00: 00: 00.09
SQL> set autot on
SQL> select /*+index(td SYS_C0067929)*/ * from td where a like '%已%';
A
----------
表已创建。
已用时间
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 2699856662
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 14 | 2 (0)| 00:00:01 |
|* 1 | INDEX FULL SCAN | SYS_C0067929 | 2 | 14 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A" LIKE '%已%')
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
588 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)
2 rows processed