模糊查询与索引

模糊查询与索引

参考 http://tech.ccidnet.com/art/3089/20080405/1410979_1.html。

通配符（%）在搜寻词首出现，一般会导致Oracle系统不使用索引。因此，要尽量避免在模糊查询中使用通配符开头，或者是开头结尾都有通配符，这样会导致降低查询速度。

--创建一个name字段的索引

create index IDX_B$L_INTEREST_INFO_NAME on B$L_INTEREST_INFO (NAME);

    以下语句不能使用name字段索引：

select * from b$l_interest_info where name like '%瑞德工业园%';

2 ----------------------------------------------------------------------------

3 | Id | Operation         | Name              | Rows | Bytes | Cost (%CPU)|

4 ----------------------------------------------------------------------------

5 |   0 | SELECT STATEMENT |                   |   141K| 8139K| 5426   (4)|

6 |   1 | TABLE ACCESS FULL| B$L_INTEREST_INFO |   141K| 8139K| 5426   (4)|

7      ----------------------------------------------------------------------------

如果遇到模糊查询的例子，尽量将通配符放在末尾，以常量开头，那么可以使用上索引，如下语句所示：

select * from b$l_interest_info where name like '瑞德工业园%';

2       -----------------------------------------------------------------------------------------------

3       | Id | Operation                   | Name                       | Rows | Bytes | Cost (%CPU)|

4       -----------------------------------------------------------------------------------------------

5       |   0 | SELECT STATEMENT            |                            | 1278 | 75402 |   360   (1)|

6       |   1 | TABLE ACCESS BY INDEX ROWID| B$L_INTEREST_INFO          | 1278 | 75402 |   360   (1)|

7       |   2 |   INDEX RANGE SCAN          | IDX_B$L_INTEREST_INFO_NAME | 1278 |       |     6   (0)|

8       -----------------------------------------------------------------------------------------------

 

如果必须将通配符放在开头，以常量结束，那么可以创建一个反向键索引

--在name字段创建一个反向键索引

create index idx_interest_info_name_re on b$l_interest_info(reverse(name));

analyze table b$l_interest_info compute statistics for table for all indexes;

--没有用到反向键索引

select * from b$l_interest_info where name like '%瑞德工业园';

2      ----------------------------------------------------------------------------

3      | Id | Operation         | Name              | Rows | Bytes | Cost (%CPU)|

4      ----------------------------------------------------------------------------

5      |   0 | SELECT STATEMENT |                   |   141K| 8139K| 5426  (4)|

6      |   1 | TABLE ACCESS FULL| B$L_INTEREST_INFO |   141K| 8139K| 5426   (4)|

7      ----------------------------------------------------------------------------

 

因此，要使用反向键索引还必须加上reverse关键字

--用上了反向键索引IDX_INTEREST_INFO_NAME_RE

select * from b$l_interest_info where reverse(name) like reverse('%瑞德工业园');

2       ----------------------------------------------------------------------------------------------

3       | Id | Operation                   | Name                      | Rows | Bytes | Cost (%CPU)|

4       ----------------------------------------------------------------------------------------------

5       |   0 | SELECT STATEMENT            |                           |   141K| 8139K| 5233   (1)|

6       |   1 | TABLE ACCESS BY INDEX ROWID| B$L_INTEREST_INFO         |   141K|  8139K| 5233   (1)|

7       |   2 |   INDEX RANGE SCAN          | IDX_INTEREST_INFO_NAME_RE | 25429 |       |    77   (2)|

8       ----------------------------------------------------------------------------------------------

 

如果开头结尾都要用到通配符，且select获取的字段只有该模糊查询字段，则可以用上索引：

--用到了name字段的一般索引IDX_B$L_INTEREST_INFO_NAME

select name from b$l_interest_info where name like '%瑞德工业园%';

2       ----------------------------------------------------------------------------------------

3       | Id | Operation            | Name                       | Rows | Bytes | Cost (%CPU)|

4       ----------------------------------------------------------------------------------------

5       |   0 | SELECT STATEMENT     |                            |   141K| 1379K| 1915   (7)|

6       |   1 | INDEX FAST FULL SCAN| IDX_B$L_INTEREST_INFO_NAME |   141K| 1379K| 1915   (7)|

7       ----------------------------------------------------------------------------------------

但是对于使用%%这种查询且select获取的字段包含了模糊查询字段以外的，就很难用上索引了。