当前位置： 移动技术网 > IT编程>数据库>Oracle > Oracle优化——LIKE与索引（以%开头的LIKE会不走索引或走索引全扫描）

Oracle优化——LIKE与索引（以%开头的LIKE会不走索引或走索引全扫描）

2018年10月25日  | 移动技术网IT编程  | 我要评论
这样会走索引范围扫描，因为这个表达式有前导性。 
sh@ prod> set autotrace on
sh@ prod> select max(cust_credit_limit) , count(*) from customers_ne where cust_last_name like 'vaugh%' ;

max(cust_credit_limit)   count(*)
---------------------- ----------
                 11000         81


execution plan
----------------------------------------------------------
plan hash value: 3473995898

---------------------------------------------------------------------------------------------
| id  | operation                    | name         | rows  | bytes | cost (%cpu)| time     |
---------------------------------------------------------------------------------------------
|   0 | select statement             |              |     1 |    16 |    13   (0)| 00:00:01 |
|   1 |  sort aggregate              |              |     1 |    16 |            |          |
|   2 |   table access by index rowid| customers_ne |    61 |   976 |    13   (0)| 00:00:01 |
|*  3 |    index range scan          | last_idx1    |    61 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

predicate information (identified by operation id):
---------------------------------------------------

   3 - access("cust_last_name" like 'vaugh%')
       filter("cust_last_name" like 'vaugh%')


statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
        614  bytes sent via sql*net to client
        523  bytes received via sql*net from client
          2  sql*net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

这样不会走索引，因为这个表达式没有前导性，无法找到索引的开始点。
sh@ prod> select max(cust_credit_limit) , count(*) from customers_ne where cust_last_name like '%aughn' ;

max(cust_credit_limit)   count(*)
---------------------- ----------
                 11000         81


execution plan
----------------------------------------------------------
plan hash value: 3963802310

-----------------------------------------------------------------------------------
| id  | operation          | name         | rows  | bytes | cost (%cpu)| time     |
-----------------------------------------------------------------------------------
|   0 | select statement   |              |     1 |    16 |   405   (1)| 00:00:05 |
|   1 |  sort aggregate    |              |     1 |    16 |            |          |
|*  2 |   table access full| customers_ne |  2775 | 44400 |   405   (1)| 00:00:05 |
-----------------------------------------------------------------------------------

predicate information (identified by operation id):
---------------------------------------------------

   2 - filter("cust_last_name" like '%aughn')


statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
       1460  consistent gets
          0  physical reads
          0  redo size
        614  bytes sent via sql*net to client
        523  bytes received via sql*net from client
          2  sql*net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

强制使用索引，走的时索引全扫描，还远不如全表扫描。
sh@ prod> select /*+ index(customers_ne) */ max(cust_credit_limit) , count(*) from customers_ne where cust_last_name like '%aughn' ;

max(cust_credit_limit)   count(*)
---------------------- ----------
                 11000         81


execution plan
----------------------------------------------------------
plan hash value: 359032907

---------------------------------------------------------------------------------------------
| id  | operation                    | name         | rows  | bytes | cost (%cpu)| time     |
---------------------------------------------------------------------------------------------
|   0 | select statement             |              |     1 |    16 |   636   (1)| 00:00:08 |
|   1 |  sort aggregate              |              |     1 |    16 |            |          |
|   2 |   table access by index rowid| customers_ne |  2775 | 44400 |   636   (1)| 00:00:08 |
|*  3 |    index full scan           | last_idx1    |  2775 |       |   143   (1)| 00:00:02 |
---------------------------------------------------------------------------------------------

predicate information (identified by operation id):
---------------------------------------------------

   3 - filter("cust_last_name" like '%aughn')


statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
        155  consistent gets
        139  physical reads
          0  redo size
        614  bytes sent via sql*net to client
        523  bytes received via sql*net from client
          2  sql*net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

您可能感兴趣的文章:

如对本文有疑问， 点击进行留言回复！！

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网