There was a question about PRECOMPUTE_SUBQUERY hint in an Oracle Forums thread.

Here I will post the answer I gave there and also elaborate it more as it explains a little known interesting fact about Oracle cursor management. Also it allows me to introduce few advanced Oracle troubleshooting scripts by example. This is a fairly long post, but if you are interested in some Oracle cursor management and SQL execution internals, keep on reading ;)

Consider the following test case with two tables, T1 and T2:

SQL> create table t1 as select rownum a from dual connect by level < 10;

Table created.

SQL> create table t2 as select rownum+10 b from dual connect by level < 10;

Table created.

SQL>
SQL> select * from t1;

         A
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9

9 rows selected.

SQL>
SQL> select * from t2;

         B
----------
        11
        12
        13
        14
        15
        16
        17
        18
        19

9 rows selected.

Now lets run a query with a simple subquery in it:

SQL> select a
  2  from   t1
  3  where  a in (select b from t2);

no rows selected

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID  aucw6byq3d5q8, child number 0
-------------------------------------
select a from   t1 where  a in (select b from t2)

Plan hash value: 561629455

----------------------------------------------------------------------------
| Id  | Operation           | Name     | E-Rows |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |        |       |       |          |
|*  1 |  HASH JOIN SEMI     |          |      1 |  1066K|  1066K| 1056K (0)|
|   2 |   TABLE ACCESS FULL | T1       |      9 |       |       |          |
|   3 |   VIEW              | VW_NSO_1 |      9 |       |       |          |
|   4 |    TABLE ACCESS FULL| T2       |      9 |       |       |          |
----------------------------------------------------------------------------

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

   1 - access("A"="B")

Note that a hash semijoin was performed which semijoined two of its child rowsources with join condition “A=B”.

Now lets run exactly the same query with PRECOMPUTE_SUBQUERY hint in subquery block:

SQL> select a
  2  from   t1
  3  where  a in (select /*+ PRECOMPUTE_SUBQUERY */b from t2);

no rows selected

SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID  fvnqhjkcjnybx, child number 0
-------------------------------------
select a from   t1 where  a in (select /*+ PRECOMPUTE_SUBQUERY */b from
t2)

Plan hash value: 3617692013

-------------------------------------------
| Id  | Operation         | Name | E-Rows |
-------------------------------------------
|   0 | SELECT STATEMENT  |      |        |
|*  1 |  TABLE ACCESS FULL| T1   |      5 |
-------------------------------------------

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

   1 - filter(("A"=11 OR "A"=12 OR "A"=13 OR "A"=14 OR "A"=15 OR "A"=16
              OR "A"=17 OR "A"=18 OR "A"=19))

See what happened! The join is gone and it looks like table T2 is not accessed at all (as there is only one TABLE ACCESS rowsource which reads from table T1 ).

However, there has appeared a filter condition which has all the values from T2 in it! How are these values retrieved?

A simple sql_trace reveals this:

[Read more...]