Re: Interesting hint
From: Nimish Garg <nimishsoft_at_gmail.com>
Date: Mon, 7 Mar 2011 00:39:17 -0800 (PST)
Message-ID: <ce150608-acf0-4575-8b9a-e55de4ccd133_at_a21g2000prj.googlegroups.com>
On Mar 7, 3:16 am, Mladen Gogala <gogala.mla..._at_gmail.com> wrote:
> 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
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Date: Mon, 7 Mar 2011 00:39:17 -0800 (PST)
Message-ID: <ce150608-acf0-4575-8b9a-e55de4ccd133_at_a21g2000prj.googlegroups.com>
On Mar 7, 3:16 am, Mladen Gogala <gogala.mla..._at_gmail.com> wrote:
> 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
is it applicable on 10r2 also??
i tried it but not working
SQL> set autot trace SQL> set lines 200 SQL> select ename from scott.emp where deptno in (select /*+PRECOMPUTE_SUBQUERY */ deptno from scott.dept);
14 rows selected.
Execution Plan
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
| 0 | SELECT STATEMENT | | 14 | 168 | 4 (0)| | 1 | NESTED LOOPS | | 14 | 168 | 4 (0)| | 2 | TABLE ACCESS FULL| EMP | 14 | 126 | 3 (0)| | 3 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 3 | 1 (0)| -------------------------------------------------------------------
Note
- 'PLAN_TABLE' is old version
Statistics
615 recursive calls
0 db block gets
120 consistent gets
9 physical reads
0 redo size
705 bytes sent via SQL*Net to client
488 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
14 rows processed
Received on Mon Mar 07 2011 - 02:39:17 CST