Invalid Hints are Silently Ignored? An Invalid USE_HASH Hint Transforms a Sort-Merge Join into a Nested Loops Join

September 30, 2011

How many times have you heard or read that the Oracle Database query optimizer may freely ignore hints if it so chooses?  How many times have you heard or read that the query optimizer silently ignores hints that are invalid?  I was recently reminded of both of these concepts while reading the book “Oracle Database 11g Performance Tuning Recipes“.

Page 494 of the book states:

“Tip: Hints with incorrect or improper syntax are ignored by the optimizer.”

Page 496 of the book states:

“Note: Hints influence the optimizer, but the optimizer may still choose to ignore any hints specified in the query.”

Hints with incorrect syntax may cause the optimizer to consider the hints as invalid, but the changed outcome is potentially much worse (or better) than the hint being simply ignored, as demonstrated in this blog article.  As described in another blog article, hints are directives and must be obeyed unless…

I thought that I would craft an interesting, yet simple test case to see if invalid hints are silently ignored.  Here is the test case script:

SET AUTOTRACE OFF

DROP TABLE T1 PURGE;
DROP TABLE T2 PURGE;

CREATE TABLE T1 AS
SELECT
  ROWNUM RN
FROM
  DUAL
CONNECT BY
  LEVEL<=100;

CREATE TABLE T2 AS
SELECT
  ROWNUM RN
FROM
  DUAL
CONNECT BY
  LEVEL<=100;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>NULL,TABNAME=>'T1')
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>NULL,TABNAME=>'T2')

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'USE_HASH_10053';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT
  *
FROM
  T1,
  T2
WHERE
  T1.RN>T2.RN;

SELECT /*+ USE_HASH(T1 T2) */
  *
FROM
  T1,
  T2
WHERE
  T1.RN>T2.RN;

SET AUTOTRACE OFF

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

The following shows the output that I received with Oracle Database 11.2.0.2:

SQL> SELECT
  2    *
  3  FROM
  4    T1,
  5    T2
  6  WHERE
  7    T1.RN>T2.RN;

Execution Plan
----------------------------------------------------------
Plan hash value: 412793182

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  4950 | 29700 |     9  (34)| 00:00:01 |
|   1 |  MERGE JOIN         |      |  4950 | 29700 |     9  (34)| 00:00:01 |
|   2 |   SORT JOIN         |      |   100 |   300 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |   100 |   300 |     3   (0)| 00:00:01 |
|*  4 |   SORT JOIN         |      |   100 |   300 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| T2   |   100 |   300 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access(INTERNAL_FUNCTION("T1"."RN")>INTERNAL_FUNCTION("T2"."RN"))
       filter(INTERNAL_FUNCTION("T1"."RN")>INTERNAL_FUNCTION("T2"."RN"))

SQL> SELECT /*+ USE_HASH(T1 T2) */
  2    *
  3  FROM
  4    T1,
  5    T2
  6  WHERE
  7    T1.RN>T2.RN;

Execution Plan
----------------------------------------------------------
Plan hash value: 1967407726

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  4950 | 29700 |   144   (5)| 00:00:01 |
|   1 |  NESTED LOOPS      |      |  4950 | 29700 |   144   (5)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |   100 |   300 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |    50 |   150 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("T1"."RN">"T2"."RN")

So, the USE_HASH hint which is invalid because a hash join cannot be used when the join condition is an inequality, causes the optimizer to switch from a sort-merge join to a nested loops join.  Does this mean that invalid hints are potentially NOT silently ignored?