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

来源：互联网发布：c语言中的echo 编辑：程序博客网时间：2024/05/16 13:53
这样会走索引范围扫描，因为这个表达式有前导性。SH@ prod> set autotrace onSH@ prod> select max(cust_credit_limit) , count(*) from customers_ne where cust_last_name like 'Vaugh%' ;MAX(CUST_CREDIT_LIMIT)   COUNT(*)---------------------- ----------                 11000         81Execution 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         81Execution 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         81Execution 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
阅读全文
0 0