在Oracle中，模糊查询可以使用索引吗?

DB宝 2019-06-05
1951
♣题目部分在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也可以用到域索引。
示例代码如下所示：
  1LHR@orclasm > CREATE TABLE TB_LHR_20160518_02 AS  SELECT * FROM DBA_TABLES;
  2Table created.
  3LHR@orclasm > CREATE INDEX IDX_TB_LHR_20160518_02 ON TB_LHR_20160518_02(TABLE_NAME);
  4Index created. 
  5LHR@orclasm > SET AUTOT ON 
  6LHR@orclasm > SELECT OWNER,TABLE_NAME FROM TB_LHR_20160518_02 WHERE TABLE_NAME LIKE 'AA%';
  7------------------------------------------------------------------------------------------------------
  8| Id  | Operation                   | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
  9------------------------------------------------------------------------------------------------------
 10|   0 | SELECT STATEMENT            |                        |     1 |    34 |     1   (0)| 00:00:01 |
 11|   1 |  TABLE ACCESS BY INDEX ROWID| TB_LHR_20160518_02     |     1 |    34 |     1   (0)| 00:00:01 |
 12|*  2 |   INDEX RANGE SCAN          | IDX_TB_LHR_20160518_02 |     1 |       |     1   (0)| 00:00:01 |
 13------------------------------------------------------------------------------------------------------
 14LHR@orclasm > SELECT OWNER,TABLE_NAME FROM TB_LHR_20160518_02 WHERE TABLE_NAME LIKE '%AA%';
 15----------------------------------------------------------------------------------------
 16| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
 17----------------------------------------------------------------------------------------
 18|   0 | SELECT STATEMENT  |                    |     1 |    34 |    31   (0)| 00:00:01 |
 19|*  1 |  TABLE ACCESS FULL| TB_LHR_20160518_02 |     1 |    34 |    31   (0)| 00:00:01 |
 20----------------------------------------------------------------------------------------
 21
 22--如果只查询索引字段，那么必然走索引：
 23LHR@orclasm > SELECT  TABLE_NAME FROM TB_LHR_20160518_02 WHERE TABLE_NAME LIKE '%AA%';
 24-----------------------------------------------------------------------------------------------
 25| Id  | Operation            | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
 26-----------------------------------------------------------------------------------------------
 27|   0 | SELECT STATEMENT     |                        |     1 |    17 |     7   (0)| 00:00:01 |
 28|*  1 |  INDEX FAST FULL SCAN| IDX_TB_LHR_20160518_02 |     1 |    17 |     7   (0)| 00:00:01 |
 29-----------------------------------------------------------------------------------------------
 30
 31--创建REVERSE函数索引：
 32LHR@orclasm > CREATE INDEX IDX_TB_LHR_20160518_03 ON TB_LHR_20160518_02(REVERSE(TABLE_NAME));
 33Index created.
 34LHR@orclasm > SELECT OWNER,TABLE_NAME FROM TB_LHR_20160518_02 WHERE REVERSE(TABLE_NAME) LIKE REVERSE('%AA');
 35------------------------------------------------------------------------------------------------------
 36| Id  | Operation                   | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
 37------------------------------------------------------------------------------------------------------
 38|   0 | SELECT STATEMENT            |                        |   152 |  7752 |    27   (0)| 00:00:01 |
 39|   1 |  TABLE ACCESS BY INDEX ROWID| TB_LHR_20160518_02     |   152 |  7752 |    27   (0)| 00:00:01 |
 40|*  2 |   INDEX RANGE SCAN          | IDX_TB_LHR_20160518_03 |    27 |       |     2   (0)| 00:00:01 |
 41------------------------------------------------------------------------------------------------------
 42
 43--如果被检索的字符串总是从第3个位置开始，那么可以先创建SUBSTR函数索引，再使用LIKE 'ABC%'，如下所示：
 44LHR@orclasm > CREATE INDEX IDX_TB_LHR_20160518_04 ON TB_LHR_20160518_02(SUBSTR(TABLE_NAME,3));
 45Index created.
 46LHR@orclasm > SELECT OWNER,TABLE_NAME FROM TB_LHR_20160518_02 WHERE SUBSTR(TABLE_NAME,3) LIKE 'AA%';
 47------------------------------------------------------------------------------------------------------
 48| Id  | Operation                   | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
 49------------------------------------------------------------------------------------------------------
 50|   0 | SELECT STATEMENT            |                        |   152 |  9728 |    18   (0)| 00:00:01 |
 51|   1 |  TABLE ACCESS BY INDEX ROWID| TB_LHR_20160518_02     |   152 |  9728 |    18   (0)| 00:00:01 |
 52|*  2 |   INDEX RANGE SCAN          | IDX_TB_LHR_20160518_04 |    27 |       |     2   (0)| 00:00:01 |
 53------------------------------------------------------------------------------------------------------
 54
 55--如果字符串ABC始终从原字符串结尾的某个固定位置出现，那么可以创建函数组合索引进行优化。假如ABC从字符串倒数第五位出现，如下所示：
 56LHR@orclasm > CREATE INDEX IDX_TB_LHR_20160518_05 ON TB_LHR_20160518_02(REVERSE(SUBSTR(TABLE_NAME,1,LENGTH(TABLE_NAME)-4)));
 57Index created.
 58LHR@orclasm > SELECT OWNER,TABLE_NAME FROM TB_LHR_20160518_02 WHERE REVERSE(SUBSTR(TABLE_NAME,1,LENGTH(TABLE_NAME)-4)) LIKE REVERSE('%AA');
 59------------------------------------------------------------------------------------------------------
 60| Id  | Operation                   | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
 61------------------------------------------------------------------------------------------------------
 62|   0 | SELECT STATEMENT            |                        |   159 |  9381 |    27   (0)| 00:00:01 |
 63|   1 |  TABLE ACCESS BY INDEX ROWID| TB_LHR_20160518_02     |   159 |  9381 |    27   (0)| 00:00:01 |
 64|*  2 |   INDEX RANGE SCAN          | IDX_TB_LHR_20160518_05 |    29 |       |     2   (0)| 00:00:01 |
 65------------------------------------------------------------------------------------------------------
 66Predicate Information (identified by operation id):
 67---------------------------------------------------
 68   2 - access(REVERSE(SUBSTR("TABLE_NAME",1,LENGTH("TABLE_NAME")-4)) LIKE 'AA%')
 69       filter(REVERSE(SUBSTR("TABLE_NAME",1,LENGTH("TABLE_NAME")-4)) LIKE 'AA%')
 70
 71--如果字符串ABC在原字符串中位置不固定，那么可以通过改写SQL进行优化。这种情况需要在LIKE的字段上存在普通索引的情况下，先使用子查询查询出需要的字段，然后在外层嵌套，这样就可以使用到索引了。如下所示：
 72LHR@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%');
 73-------------------------------------------------------------------------------------------------------
 74| Id  | Operation                    | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
 75-------------------------------------------------------------------------------------------------------
 76|   0 | SELECT STATEMENT             |                        |     1 |    51 |    10  (10)| 00:00:01 |
 77|   1 |  NESTED LOOPS                |                        |       |       |            |          |
 78|   2 |   NESTED LOOPS               |                        |     1 |    51 |    10  (10)| 00:00:01 |
 79|   3 |    SORT UNIQUE               |                        |     1 |    17 |     7   (0)| 00:00:01 |
 80|*  4 |     INDEX FAST FULL SCAN     | IDX_TB_LHR_20160518_02 |     1 |    17 |     7   (0)| 00:00:01 |
 81|*  5 |    INDEX RANGE SCAN          | IDX_TB_LHR_20160518_02 |     1 |       |     1   (0)| 00:00:01 |
 82|   6 |   TABLE ACCESS BY INDEX ROWID| TB_LHR_20160518_02     |     1 |    34 |     2   (0)| 00:00:01 |
 83-------------------------------------------------------------------------------------------------------
 84Predicate Information (identified by operation id):
 85---------------------------------------------------
 86   4 - filter("TABLE_NAME" LIKE '%AA%')
 87   5 - access("TABLE_NAME"="TABLE_NAME")
 88       filter("TABLE_NAME" LIKE '%AA%')
 89Note
 90-----
 91   - dynamic sampling used for this statement (level=2)
 92Statistics
 93----------------------------------------------------------
 94        131  recursive calls
 95          2  db block gets
 96        221  consistent gets
 97        111  physical reads
 98          0  redo size
 99        671  bytes sent via SQL*Net to client
100        519  bytes received via SQL*Net from client
101          2  SQL*Net roundtrips to/from client
102          7  sorts (memory)
103          0  sorts (disk)
104          3  rows processed

& 说明：
有关模糊查询LIKE的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2139039/

本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。