前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >【DB笔试面试572】在Oracle中,模糊查询可以使用索引吗?

【DB笔试面试572】在Oracle中,模糊查询可以使用索引吗?

作者头像
AiDBA宝典
发布2019-09-29 15:48:40
9.8K0
发布2019-09-29 15:48:40
举报
文章被收录于专栏:小麦苗的DB宝专栏

题目部分

在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也可以用到域索引。

示例代码如下所示:

代码语言:javascript
复制
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程序员面试笔试宝典》,作者:李华荣。

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2019-06-05,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 DB宝 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档