Interesting hint

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Sun, 6 Mar 2011 22:16:51 +0000 (UTC)
Message-ID: <pan.2011.03.06.22.16.50_at_gmail.com>

I was digging through Tanel's articles and came across the following: http://blog.tanelpoder.com/2009/01/23/multipart-cursor-subexecution-and- precompute_subquery-hint/#more-194

Basically, there is a hint which tells Oracle to pre-compute the in subquery during the optimization phase and expand it into an "or" list. Below is an example. Pay attention to the fact that DEPT table is not mentioned in the execution plan, the subquery was expanded during the query optimization phase. Thanks, Tanel!

SQL> set autotrace on explain
SQL> select ename from emp
  2 where deptno in (select /*+ PRECOMPUTE_SUBQUERY */

  3                   deptno from dept);

ENAME

---

SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER 14 rows selected.

Elapsed: 00:00:00.13

Execution Plan

---

Plan hash value: 3956160932

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---

|   0 | SELECT STATEMENT  |      |    14 |   126 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |    14 |   126 |     3   (0)| 00:00:01 |


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

Predicate Information (identified by operation id):

---

   1 - filter("DEPTNO"=10 OR "DEPTNO"=20 OR "DEPTNO"=30 OR "DEPTNO"=40)

SQL>

-- 
http://mgogala.byethost5.com

Received on Sun Mar 06 2011 - 16:16:51 CST