Hint 强制使用索引有时失灵？

执行以下两组语句，查看执行计划

1. SELECT * from oe.customers;

Execution Plan
----------------------------------------------------------
Plan hash value: 2008213504

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   319 | 53911 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| CUSTOMERS |   319 | 53911 |     5   (0)| 00:00:01 |
-------------------------------------------------------------------------------

2. SELECT /*+ INDEX(customers CUSTOMERS_PK)*/ * from oe.customers;

Execution Plan
----------------------------------------------------------
Plan hash value: 2921505881

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |   319 | 53911 |     9   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMERS    |   319 | 53911 |     9   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN           | CUSTOMERS_PK |   319 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

通过对比 1 和 2 ，hint 起到强制使用索引的目的了。

继续对比两组语句

3.  select c.customer_id, count(o.order_id) as orders_ct
from oe.customers c
join oe.orders o
on c.customer_id = o.customer_id
where c.gender = 'F'
group by c.customer_id
having count(o.order_id) > 4
order by orders_ct, c.customer_id
/

Execution Plan
----------------------------------------------------------
Plan hash value: 290339559

-----------------------------------------------------------------------------------------
| Id  | Operation             | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |     1 |    10 |     7  (29)| 00:00:01 |
|   1 |  SORT ORDER BY        |                 |     1 |    10 |     7  (29)| 00:00:01 |
|*  2 |   FILTER              |                 |       |       |            |          |
|   3 |    HASH GROUP BY      |                 |     1 |    10 |     7  (29)| 00:00:01 |
|   4 |     NESTED LOOPS      |                 |   105 |  1050 |     5   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL| CUSTOMERS       |   160 |   960 |     5   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN | ORD_CUSTOMER_IX |     1 |     4 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(COUNT(*)>4)
   5 - filter("C"."GENDER"='F')
   6 - access("C"."CUSTOMER_ID"="O"."CUSTOMER_ID")
       filter("O"."CUSTOMER_ID">0)

4. 使用 hints

SELECT /*+ INDEX(customers CUSTOMERS_PK)*/c.customer_id,count(o.order_id) as orders_ct
from oe.orders o
join oe.customers c
on o.customer_id = c.customer_id
where c.gender = 'F'
group by c.customer_id
having count(o.order_id) > 4
order by orders_ct, c.customer_id
/

Execution Plan
----------------------------------------------------------
Plan hash value: 290339559

-----------------------------------------------------------------------------------------
| Id  | Operation             | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |     1 |    10 |     7  (29)| 00:00:01 |
|   1 |  SORT ORDER BY        |                 |     1 |    10 |     7  (29)| 00:00:01 |
|*  2 |   FILTER              |                 |       |       |            |          |
|   3 |    HASH GROUP BY      |                 |     1 |    10 |     7  (29)| 00:00:01 |
|   4 |     NESTED LOOPS      |                 |   105 |  1050 |     5   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL| CUSTOMERS       |   160 |   960 |     5   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN | ORD_CUSTOMER_IX |     1 |     4 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(COUNT(*)>4)
   5 - filter("C"."GENDER"='F')
   6 - access("O"."CUSTOMER_ID"="C"."CUSTOMER_ID")
       filter("O"."CUSTOMER_ID">0)

对比 3 和 4， 4 中加了hint，并没有强制使用索引。。。留个疑问，有时间多想想。