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

这样会走索引范围扫描，因为这个表达式有前导性。

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