♣
题目部分
在Oracle中,模糊查询可以使用索引吗?
♣
答案部分
分为以下几种情况:
(1)若SELECT子句只检索索引字段,那么模糊查询可以使用索引,例如,“SELECT ID FROM TB WHERE ID LIKE '%123%';”可以使用索引。
(2)若SELECT子句不只检索索引字段还检索其它非索引字段,那么分为以下几种情况:
① 模糊查询形如“WHERE COL_NAME LIKE 'ABC%';”可以用到索引。
② 模糊查询形如“WHERE COL_NAME LIKE '%ABC';”不能使用索引,但是可以通过REVERSE函数来创建函数索引才能使用到索引。
③ 模糊查询形如“WHERE COL_NAME LIKE '%ABC%';”不能使用索引,但是,如果所查询的字符串有一定的规律的的话,那么还是可以使用到索引的,分以下几种情况:
a. 如果字符串ABC始终从原字符串的某个固定位置出现,那么可以创建SUBSTR函数索引进行优化。
b. 如果字符串ABC始终从原字符串结尾的某个固定位置出现,那么可以创建函数组合索引进行优化。
c. 如果字符串ABC在原字符串中位置不固定,那么可以通过改写SQL进行优化。改写的方法主要是通过先使用子查询查询出需要的字段,然后在外层嵌套,这样就可以使用到索引了。
④ 建全文索引后使用CONTAINS也可以用到域索引。
示例代码如下所示:
LHR@orclasm > CREATE TABLE TB_LHR_20160518_02 AS SELECT * FROM DBA_TABLES;
Table created.
LHR@orclasm > CREATE INDEX IDX_TB_LHR_20160518_02 ON TB_LHR_20160518_02(TABLE_NAME);
Index created.
LHR@orclasm > SET AUTOT ON
LHR@orclasm > SELECT OWNER,TABLE_NAME FROM TB_LHR_20160518_02 WHERE TABLE_NAME LIKE 'AA%';
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_LHR_20160518_02 | 1 | 34 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TB_LHR_20160518_02 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
LHR@orclasm > SELECT OWNER,TABLE_NAME FROM TB_LHR_20160518_02 WHERE TABLE_NAME LIKE '%AA%';
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 31 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TB_LHR_20160518_02 | 1 | 34 | 31 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
--如果只查询索引字段,那么必然走索引:
LHR@orclasm > SELECT TABLE_NAME FROM TB_LHR_20160518_02 WHERE TABLE_NAME LIKE '%AA%';
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 7 (0)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IDX_TB_LHR_20160518_02 | 1 | 17 | 7 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
--创建REVERSE函数索引:
LHR@orclasm > CREATE INDEX IDX_TB_LHR_20160518_03 ON TB_LHR_20160518_02(REVERSE(TABLE_NAME));
Index created.
LHR@orclasm > SELECT OWNER,TABLE_NAME FROM TB_LHR_20160518_02 WHERE REVERSE(TABLE_NAME) LIKE REVERSE('%AA');
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 152 | 7752 | 27 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_LHR_20160518_02 | 152 | 7752 | 27 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TB_LHR_20160518_03 | 27 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
--如果被检索的字符串总是从第3个位置开始,那么可以先创建SUBSTR函数索引,再使用LIKE 'ABC%',如下所示:
LHR@orclasm > CREATE INDEX IDX_TB_LHR_20160518_04 ON TB_LHR_20160518_02(SUBSTR(TABLE_NAME,3));
Index created.
LHR@orclasm > SELECT OWNER,TABLE_NAME FROM TB_LHR_20160518_02 WHERE SUBSTR(TABLE_NAME,3) LIKE 'AA%';
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 152 | 9728 | 18 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_LHR_20160518_02 | 152 | 9728 | 18 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TB_LHR_20160518_04 | 27 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
--如果字符串ABC始终从原字符串结尾的某个固定位置出现,那么可以创建函数组合索引进行优化。假如ABC从字符串倒数第五位出现,如下所示:
LHR@orclasm > CREATE INDEX IDX_TB_LHR_20160518_05 ON TB_LHR_20160518_02(REVERSE(SUBSTR(TABLE_NAME,1,LENGTH(TABLE_NAME)-4)));
Index created.
LHR@orclasm > SELECT OWNER,TABLE_NAME FROM TB_LHR_20160518_02 WHERE REVERSE(SUBSTR(TABLE_NAME,1,LENGTH(TABLE_NAME)-4)) LIKE REVERSE('%AA');
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 159 | 9381 | 27 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_LHR_20160518_02 | 159 | 9381 | 27 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TB_LHR_20160518_05 | 29 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(REVERSE(SUBSTR("TABLE_NAME",1,LENGTH("TABLE_NAME")-4)) LIKE 'AA%')
filter(REVERSE(SUBSTR("TABLE_NAME",1,LENGTH("TABLE_NAME")-4)) LIKE 'AA%')
--如果字符串ABC在原字符串中位置不固定,那么可以通过改写SQL进行优化。这种情况需要在LIKE的字段上存在普通索引的情况下,先使用子查询查询出需要的字段,然后在外层嵌套,这样就可以使用到索引了。如下所示:
LHR@orclasm > SELECT OWNER,TABLE_NAME FROM TB_LHR_20160518_02 WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM TB_LHR_20160518_02 WHERE TABLE_NAME LIKE '%AA%');
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 51 | 10 (10)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 51 | 10 (10)| 00:00:01 |
| 3 | SORT UNIQUE | | 1 | 17 | 7 (0)| 00:00:01 |
|* 4 | INDEX FAST FULL SCAN | IDX_TB_LHR_20160518_02 | 1 | 17 | 7 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_TB_LHR_20160518_02 | 1 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| TB_LHR_20160518_02 | 1 | 34 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("TABLE_NAME" LIKE '%AA%')
5 - access("TABLE_NAME"="TABLE_NAME")
filter("TABLE_NAME" LIKE '%AA%')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
131 recursive calls
2 db block gets
221 consistent gets
111 physical reads
0 redo size
671 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
3 rows processed
& 说明:
有关模糊查询LIKE的更多内容可以参考我的BLOG:http://blog.itpub.net/26736162/viewspace-2139039/
本文选自《Oracle程序员面试笔试宝典》,作者:李华荣。