模糊查询与索引

通配符（%）在搜寻词首出现，一般会导致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获取的字段包含了模糊查询字段以外的，就很难用上索引了。