Oracle优化——LIKE与索引(以%开头的LIKE会不走索引或走索引全扫描)
程序员文章站
2022-04-15 11:13:07
这样会走索引范围扫描,因为这个表达式有前导性。
sh@ prod> set autotrace on
sh@ prod> select max(cust_credit_limit)...
这样会走索引范围扫描,因为这个表达式有前导性。
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