Ignoring Hints

Here’s a whimsical, but very telling, example of Oracle “ignoring” hints.

I have the following query, which includes a hint to use a specific index when visiting a certain table. It’s the primary key index, so has no issues relating to null values making the hint invalid – yet Oracle does not use this index. Has it ignored the hint ?

 
select 
	/*+ index(prd prd_pk) */ 
	dep.dept_name, 
	grp.group_name, 
	sum(sal.value)	value 
from 
	sales			sal, 
	products		prd, 
	groups			grp, 
	departments		dep 
where 
	prd.product_id = sal.product_id 
and	grp.product_group = prd.product_group 
and	dep.product_dept = grp.product_dept 
group by 
	dep.dept_name, 
	grp.group_name 
; 

The answer is No! Oracle does not ignore hints. But here’s the execution plan from 10gR2 autotrace, with CPU costing disabled.

 
Execution Plan (10.2.0.1) 
---------------------------------------------------------- 
Plan hash value: 3176690131           

------------------------------------------------------------------------------- 
| Id  | Operation                       | Name        | Rows  | Bytes | Cost  | 
------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT                |             |    12 |   420 |    25 | 
|   1 |  HASH GROUP BY                  |             |    12 |   420 |    25 | 
|   2 |   NESTED LOOPS                  |             |   585 | 20475 |     4 | 
|   3 |    NESTED LOOPS                 |             |   585 | 11700 |     3 | 
|   4 |     MAT_VIEW REWRITE ACCESS FULL| SALES_SUM   |   585 |  4095 |     2 | 
|   5 |     TABLE ACCESS BY INDEX ROWID | GROUPS      |     1 |    13 |     1 | 
|*  6 |      INDEX UNIQUE SCAN          | GRP_PK      |     1 |       |       | 
|   7 |    TABLE ACCESS BY INDEX ROWID  | DEPARTMENTS |     1 |    15 |     1 | 
|*  8 |     INDEX UNIQUE SCAN           | DPT_PK      |     1 |       |       | 
-------------------------------------------------------------------------------           

Predicate Information (identified by operation id): 
--------------------------------------------------- 
   6 - access("GRP"."PRODUCT_GROUP"="SALES_SUM"."PRODUCT_GROUP") 
   8 - access("DEP"."PRODUCT_DEPT"="GRP"."PRODUCT_DEPT") 

It’s a cheat – the optimizer rewrote the query to use a materialized view that allowed it to exclude the products table, so the hint became irrelevant.

The hint was not ignored, but you can only see this if you examine the 10053 trace file where you discover that, despite choosing the rewrite, the optimizer did cost the query without the rewrite and did consider the prd_pk index during that pass of the code – even when I made the the index appear so undesirable so that it would not be considered without the hint.

Here’s the execution plan when I add the no_rewrite (norewrite for earlier versions) hint to the SQL (note line 7 particularly):

Execution Plan 
---------------------------------------------------------- 
Plan hash value: 1041076401          

------------------------------------------------------------------------------- 
| Id  | Operation                       | Name        | Rows  | Bytes | Cost  | 
------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT                |             |    12 |   480 |   221 | 
|   1 |  HASH GROUP BY                  |             |    12 |   480 |   221 | 
|   2 |   NESTED LOOPS                  |             | 46477 |  1815K|    29 | 
|   3 |    NESTED LOOPS                 |             | 46477 |  1134K|    28 | 
|   4 |     NESTED LOOPS                |             | 46477 |   544K|    27 | 
|   5 |      TABLE ACCESS FULL          | SALES       | 46477 |   272K|    26 | 
|   6 |      TABLE ACCESS BY INDEX ROWID| PRODUCTS    |     1 |     6 |     1 | 
|*  7 |       INDEX UNIQUE SCAN         | PRD_PK      |     1 |       |       | 
|   8 |     TABLE ACCESS BY INDEX ROWID | GROUPS      |     1 |    13 |     1 | 
|*  9 |      INDEX UNIQUE SCAN          | GRP_PK      |     1 |       |       | 
|  10 |    TABLE ACCESS BY INDEX ROWID  | DEPARTMENTS |     1 |    15 |     1 | 
|* 11 |     INDEX UNIQUE SCAN           | DPT_PK      |     1 |       |       | 
-------------------------------------------------------------------------------  

Predicate Information (identified by operation id): 
--------------------------------------------------- 
   7 - access("PRD"."PRODUCT_ID"="SAL"."PRODUCT_ID") 
   9 - access("GRP"."PRODUCT_GROUP"="PRD"."PRODUCT_GROUP") 
  11 - access("DEP"."PRODUCT_DEPT"="GRP"."PRODUCT_DEPT") 

[Further reading on “ignoring hints”]