NO_QUERY_TRANSFORMATION Hint is Ignored… Well, Almost Ignored

January 3, 2011

A couple of days ago I used a NO_QUERY_TRANSFORMATION hint to permit a query (provided by Martin Berger) to execute, which on the surface appeared to be quite sane, but without hints the query would usually fail to execute.  A test case version of the query worked on 11.2.0.1 running on 64 bit Windows on Dec 31, 2010, but suffered from the first known year 2011 bug ( 😉 ), and failed to execute on Jan 1, 2011 in the same database.  

A test case version of that sane looking query looks like this: 

select
  owner,
  view_name
from
  (select
    v.owner,
    v.view_name,
    o.object_type
  from
    dba_views v,
    dba_objects o
  where
    v.owner = o.owner
    AND v.view_name = o.object_name
    AND o.object_type='VIEW'
    AND o.status='VALID'
    AND v.owner ='SYS'  ) vv
where
  dbms_metadata.get_ddl(VV.OBJECT_TYPE, vv.view_name, vv.owner) like '%TEST%'; 

If you think that the DBMS_METADATA.GET_DDL call in the WHERE clause is at fault, you might try this query instead:

select
  owner,
  view_name
from
  (SELECT
    dbms_metadata.get_ddl('VIEW', vv.view_name, vv.owner) DDL,
    OWNER,
    VIEW_NAME
  FROM
    (select
      v.owner,
      v.view_name,
      o.object_type
    from
      dba_views v,
      dba_objects o
    where
      v.owner = o.owner
      AND v.view_name = o.object_name
      AND o.object_type='VIEW'
      AND o.status='VALID'
      AND v.owner ='SYS'  ) vv
  )
WHERE
  DDL like '%TEST%'; 

So, the NO_QUERY_TRANSFORMATION hint was not ignored when the above queries were hinted, and the hint allowed both of the above queries to execute when that hint was added immediately after the SELECT.  Just to make certain that there is no magic involved with this hint, let’s take a look at the description from the Oracle Database 11.2 documentation:

“The NO_QUERY_TRANSFORMATION hint instructs the optimizer to skip all query transformations, including but not limited to OR-expansion, view merging, subquery unnesting, star transformation, and materialized view rewrite. For example:

SELECT /*+ NO_QUERY_TRANSFORMATION */ employee_id, last_name
  FROM (SELECT * FROM employees e) v
  WHERE v.last_name = 'Smith';         "

I think that the above explanation is easy to understand – essentially: leave my query alone!

So, what led to this blog article?  I saw a statement in the “Pro Oracle SQL” book on page 329 that I wanted to verify since I had never used the ANY keyword in a SQL statement:

 “There isn’t much to say about the =ANY version [of the SQL statement] since it is merely an alternate way of writing IN.”

I set up a simple test case script with two tables to see if I could demonstrate to myself that a query with the ANY keyword is handled exactly the same as a query using the IN syntax:

CREATE TABLE T1 AS
SELECT
  ROWNUM C1,
  LPAD('A',100,'A') PADDING
FROM
  DUAL
CONNECT BY
  LEVEL<=100;

CREATE TABLE T2 AS
SELECT
  ROWNUM C0,
  TRUNC((MOD(ROWNUM-1,80)+1)*1.2) C1,
  LPAD('A',100,'A') PADDING
FROM
  DUAL
CONNECT BY
  LEVEL<=1000;

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

Let’s assume that table T1 is a list of items in a hardware store, and table T2 is a list of items that were purchased in the last month.  We just need a quick way to determine the list of items that were purchased in the last month (we might also need to select another column, such as the product description from table T1, thus the need to access both tables T1 and T2).  So, we put together the following two SQL statements to see if the ANY syntax really is equivalent to the IN syntax:

SELECT
  T1.C1
FROM
  T1
WHERE
  T1.C1 IN (
    SELECT
      T2.C1
    FROM
      T2);

SELECT
  T1.C1
FROM
  T1
WHERE
  T1.C1 = ANY (
    SELECT
      T2.C1
    FROM
      T2); 

First, let’s check the execution plans:

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT
  T1.C1
FROM
  T1
WHERE
  T1.C1 IN (
    SELECT
      T2.C1
    FROM
      T2);

Execution Plan
----------------------------------------------------------
Plan hash value: 1713220790

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    80 |   480 |    11  (10)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |      |    80 |   480 |    11  (10)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |   100 |   300 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |  1000 |  3000 |     7   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."C1"="T2"."C1")

-

SELECT
  T1.C1
FROM
  T1
WHERE
  T1.C1 = ANY (
    SELECT
      T2.C1
    FROM
      T2);

Execution Plan
----------------------------------------------------------
Plan hash value: 1713220790

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    80 |   480 |    11  (10)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |      |    80 |   480 |    11  (10)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |   100 |   300 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |  1000 |  3000 |     7   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."C1"="T2"."C1") 

The execution plans appear to be the same.  But wait, through the magic of automatic query transformations, Oracle’s optimizer might have actually rewritten both SQL statements so that the actual SQL executed for both queries is identical.  I recall experiencing performance problems in Oracle Database 8.1.7.x with certain IN type queries, while the equivalent EXISTS queries were fast.  Let’s use the magic NO_QUERY_TRANSFORMATION hint that allowed the SQL statement at the start of this blog article to execute, so that we may prevent the optimizer from rewriting the SQL statement.  That hint should help indicate whether or not the SQL statement using the ANY syntax really is equivalent to the query using the IN syntax.  Note that this test is performed on Oracle Database 11.2.0.1 and you might receive different results on different Oracle release versions.  The hinted test queries and their execution plans:

SELECT /*+ NO_QUERY_TRANSFORMATION */
  T1.C1
FROM
  T1
WHERE
  T1.C1 IN (
    SELECT
      T2.C1
    FROM
      T2);

Execution Plan
----------------------------------------------------------
Plan hash value: 895956251

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |   103   (0)| 00:00:02 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T1   |   100 |   300 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     2 |     6 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( EXISTS (SELECT 0 FROM "T2" "T2" WHERE "T2"."C1"=:B1))
   3 - filter("T2"."C1"=:B1)

-

SELECT /*+ NO_QUERY_TRANSFORMATION */
  T1.C1
FROM
  T1
WHERE
  T1.C1 = ANY (
    SELECT
      T2.C1
    FROM
      T2);

Execution Plan
----------------------------------------------------------
Plan hash value: 895956251

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |   103   (0)| 00:00:02 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T1   |   100 |   300 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     2 |     6 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( EXISTS (SELECT 0 FROM "T2" "T2" WHERE "T2"."C1"=:B1))
   3 - filter("T2"."C1"=:B1) 

In you look closely, you will see that the execution plans changed from their unhinted versions, and both of the hinted execution plans are identical.  But wait!  Take a close look at the Predicate Information section of the execution plan, specifically the line for plan ID 1.  Where did that EXISTS entry come from – the hint should have told Oracle’s optimizer to not transform the query?  It appears that both queries were transformed into EXISTS queries.  Let’s take a look at the 10053 trace file for a slightly modified version of the queries (so that we will force a hard parse):

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

SELECT /*+ NO_QUERY_TRANSFORMATION */
  T1.C1,
  1
FROM
  T1
WHERE
  T1.C1 IN (
    SELECT
      T2.C1
    FROM
      T2);

SELECT /*+ NO_QUERY_TRANSFORMATION */
  T1.C1,
  1
FROM
  T1
WHERE
  T1.C1 = ANY (
    SELECT
      T2.C1
    FROM
      T2);

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

Taking a look in the trace file:

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "SYS_ALIAS_1"."C1" "C1",1 "1" FROM "TESTUSER"."T1" "SYS_ALIAS_1" WHERE  EXISTS (SELECT 0 FROM "TESTUSER"."T2" "T2" WHERE "T2"."C1"="SYS_ALIAS_1"."C1")
...
sql=SELECT /*+ NO_QUERY_TRANSFORMATION */
  T1.C1,
  1
FROM
  T1
WHERE
  T1.C1 IN (
    SELECT
      T2.C1
    FROM
      T2)
----- Explain Plan Dump -----
----- Plan Table -----

============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |
--------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |         |       |       |   103 |           |
| 1   |  FILTER             |         |       |       |       |           |
| 2   |   TABLE ACCESS FULL | T1      |   100 |   300 |     3 |  00:00:01 |
| 3   |   TABLE ACCESS FULL | T2      |     2 |     6 |     2 |  00:00:01 |
--------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter( IS NOT NULL)
3 - filter("T2"."C1"=:B1)

----------------------------------------------------------------------------------

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "SYS_ALIAS_1"."C1" "C1",1 "1" FROM "TESTUSER"."T1" "SYS_ALIAS_1" WHERE  EXISTS (SELECT 0 FROM "TESTUSER"."T2" "T2" WHERE "T2"."C1"="SYS_ALIAS_1"."C1")
...
sql=SELECT /*+ NO_QUERY_TRANSFORMATION */
  T1.C1,
  1
FROM
  T1
WHERE
  T1.C1 = ANY (
    SELECT
      T2.C1
    FROM
      T2)
----- Explain Plan Dump -----
----- Plan Table -----

============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |
--------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |         |       |       |   103 |           |
| 1   |  FILTER             |         |       |       |       |           |
| 2   |   TABLE ACCESS FULL | T1      |   100 |   300 |     3 |  00:00:01 |
| 3   |   TABLE ACCESS FULL | T2      |     2 |     6 |     2 |  00:00:01 |
--------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter( IS NOT NULL)
3 - filter("T2"."C1"=:B1) 

From the above, we are able to see that despite the NO_QUERY_TRANSFORMATION hint, both queries were written identically to use an EXISTS clause.  Maybe the NO_QUERY_TRANSFORMATION hint was ignored?  From the 10053 trace file:

End of Optimizer State Dump
Dumping Hints
=============
  atom_hint=(@=000007FFA41C2E00 err=0 resol=0 used=1 token=986 org=1 lvl=1 txt=NO_QUERY_TRANSFORMATION ())
====================== END SQL Statement Dump ====================== 

The above shows that the hint did not result in an error, and was used.  Well, maybe we should just say that the NO_QUERY_TRANSFORMATION hint was almost ignored.