关于PUSH_SUBQ提示的说明

　　PUSH_SUBQ 可以用来控制子查询的执行这个是PUSH_SUBQ 的本意
　　
　　我那个例子的意思是说:
　　
　　PUSH_SUBQ 本质上是个CBO的hints(当然RBO也提不上hints)
　　
　　由于PUSH_SUBQ 的引入就是为了来解决unnesting的某些不足
　　
　　所以在不同的版本上,这个hints发挥的作用也有所不同了.
　　
　　8i上这个提示的作用更接近本原:
　　
　　$ sqlplus "/ as sysdba"
　　
　　SQL*Plus: Release 8.1.5.0.0 - ProdUCtion on Sun Sep 12 20:51:21 2004
　　
　　(c) Copyright 1999 Oracle Corporation.　All rights reserved.
　　
　　Connected to:
　　Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
　　With the Partitioning and Java options
　　PL/SQL Release 8.1.5.0.0 - Production
　　
　　SQL> connect scott/tiger
　　Connected.
　　SQL> create table dept1 as select * from dept;
　　
　　Table created.
　　
　　SQL> set linesize 120
　　SQL> select a.*
　　　2　from emp a,dept b where a.deptno = b.deptno and exists (select 1 from dept1 where dept1.dname = b.dname);
　　
　　EMPNO ENAME　　　JOB　　　　　　　MGR HIREDATE　　　　 SAL　　　 COMM　　 DEPTNO
　　----- ---------- --------- ---------- --------- ---------- ---------- ----------
　　7369　SMITH　　　CLERK　　　　　 7902 17-DEC-80　　　　800　　　　　　　　　　20
　　7499　ALLEN　　　SALESMAN　　　　7698 20-FEB-81　　　 1600　　　　300　　　　 30
　　7521　WARD　　　 SALESMAN　　　　7698 22-FEB-81　　　 1250　　　　500　　　　 30
　　7566　JONES　　　MANAGER　　　　 7839 02-APR-81　　　 2975　　　　　　　　　　20
　　7654　MARTIN　　 SALESMAN　　　　7698 28-SEP-81　　　 1250　　　 1400　　　　 30
　　7698　BLAKE　　　MANAGER　　　　 7839 01-MAY-81　　　 2850　　　　　　　　　　30
　　7782　CLARK　　　MANAGER　　　　 7839 09-JUN-81　　　 2450　　　　　　　　　　10
　　7788　SCOTT　　　ANALYST　　　　 7566 19-APR-87　　　 3000　　　　　　　　　　20
　　7839　KING　　　 PRESIDENT　　　　　　17-NOV-81　　　 5000　　　　　　　　　　10
　　7844　TURNER　　 SALESMAN　　　　7698 08-SEP-81　　　 1500　　　　　0　　　　 30
　　7876　ADAMS　　　CLERK　　　　　 7788 23-MAY-87　　　 1100　　　　　　　　　　20
　　
　　EMPNO ENAME　　　JOB　　　　　　　MGR HIREDATE　　　　 SAL　　　 COMM　　 DEPTNO
　　----- ---------- --------- ---------- --------- ---------- ---------- ----------
　　7900　JAMES　　　CLERK　　　　　 7698 03-DEC-81　　　　950　　　　　　　　　　30
　　7902　FORD　　　 ANALYST　　　　 7566 03-DEC-81　　　 3000　　　　　　　　　　20
　　7934　MILLER　　 CLERK　　　　　 7782 23-JAN-82　　　 1300　　　　　　　　　　10
　　
　　14 rows selected.
　　
　　Execution Plan
　　----------------------------------------------------------
　　　 0　　　SELECT STATEMENT Optimizer=CHOOSE
　　　 1　　0　 FILTER ---------------'最初push_subq的使命是为了消除/提高这个filter的效率的'
　　　 2　　1　　 NESTED LOOPS
　　　 3　　2　　　 TABLE Access (FULL) OF 'EMP'
　　　 4　　2　　　 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
　　　 5　　4　　　　 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
　　　 6　　1　　 TABLE ACCESS (FULL) OF 'DEPT1'
　　
　　
　　
　　
　　Statistics
　　----------------------------------------------------------
　　　　　　　0　recursive calls
　　　　　　 52　db block gets
　　　　　　 21　consistent gets
　　　　　　　0　physical reads
　　　　　　　0　redo size
　　　　　 2715　bytes sent via SQL*Net to client
　　　　　　751　bytes received via SQL*Net from client
　　　　　　　4　SQL*Net roundtrips to/from client
　　　　　　　1　sorts (memory)
　　　　　　　0　sorts (disk)
　　　　　　 14　rows processed
　　
　　用于push_subq是个CBO hints，这里我们可以看到COST的出现:
　　
　　SQL> select /*+ push_subq */ a.*
　　　2　from emp a,dept b where a.deptno = b.deptno and exists (select 1 from dept1 where dept1.dname = b.dname);

　　
　　EMPNO ENAME　　　JOB　　　　　　　MGR HIREDATE　　　　 SAL　　　 COMM　　 DEPTNO
　　----- ---------- --------- ---------- --------- ---------- ---------- ----------
　　7782　CLARK　　　MANAGER　　　　 7839 09-JUN-81　　　 2450　　　　　　　　　　10
　　7839　KING　　　 PRESIDENT　　　　　　17-NOV-81　　　 5000　　　　　　　　　　10
　　7934　MILLER　　 CLERK　　　　　 7782 23-JAN-82　　　 1300　　　　　　　　　　10
　　7369　SMITH　　　CLERK　　　　　 7902 17-DEC-80　　　　800　　　　　　　　　　20
　　7566　JONES　　　MANAGER　　　　 7839 02-APR-81　　　 2975　　　　　　　　　　20
　　7788　SCOTT　　　ANALYST　　　　 7566 19-APR-87　　　 3000　　　　　　　　　　20
　　7876　ADAMS　　　CLERK　　　　　 7788 23-MAY-87　　　 1100　　　　　　　　　　20
　　7902　FORD　　　 ANALYST　　　　 7566 03-DEC-81　　　 3000　　　　　　　　　　20
　　7499　ALLEN　　　SALESMAN　　　　7698 20-FEB-81　　　 1600　　　　300　　　　 30
　　7521　WARD　　　 SALESMAN　　　　7698 22-FEB-81　　　 1250　　　　500　　　　 30
　　7654　MARTIN　　 SALESMAN　　　　7698 28-SEP-81　　　 1250　　　 1400　　　　 30
　　
　　EMPNO ENAME　　　JOB　　　　　　　MGR HIREDATE　　　　 SAL　　　 COMM　　 DEPTNO
　　------ ---------- --------- ---------- --------- ---------- ---------- ----------
　　7698 BLAKE　　　MANAGER　　　　 7839 01-MAY-81　　　 2850　　　　　　　　　　30
　　7844 TURNER　　 SALESMAN　　　　7698 08-SEP-81　　　 1500　　　　　0　　　　 30
　　7900 JAMES　　　CLERK　　　　　 7698 03-DEC-81　　　　950　　　　　　　　　　30
　　14 rows selected.
　　
　　Execution Plan
　　----------------------------------------------------------
　　　 0　　　SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=122)
　　　 1　　0　 NESTED LOOPS (Cost=3 Card=1 Bytes=122)
　　　 2　　1　　 TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=2 Bytes=44)
　　　 3　　2　　　 TABLE ACCESS (FULL) OF 'DEPT1' (Cost=1 Card=1 Bytes=9)--------'这里消除了之前的filter'
　　　 4　　1　　 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=21 Bytes=2100)
　　
　　Statistics
　　----------------------------------------------------------
　　　　　　204　recursive calls
　　　　　　 85　db block gets
　　　　　　 38　consistent gets
　　　　　　　0　physical reads
　　　　　　　0　redo size
　　　　　 2706　bytes sent via SQL*Net to client
　　　　　　768　bytes received via SQL*Net from client
　　　　　　　4　SQL*Net roundtrips to/from client
　　　　　　　5　sorts (memory)
　　　　　　　0　sorts (disk)
　　　　　　 14　rows processed
　　
　　
　　再看CBO下:
　　
　　正常情况下,没什么好说的:
　　
　　SQL> set autotrace traceonly
　　SQL> exec dbms_stats.gather_schema_stats('SCOTT')
　　
　　PL/SQL procedure successfully completed.
　　
　　SQL> select a.*
　　　2　from emp a,dept b where a.deptno = b.deptno and exists (select 1 from dept1 where dept1.dname = b.dname);
　　
　　14 rows selected.
　　
　　Execution Plan
　　----------------------------------------------------------
　　　 0　　　SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=5 Bytes=275)
　　　 1　　0　 FILTER
　　　 2　　1　　 NESTED LOOPS (Cost=2 Card=5 Bytes=275)
　　　 3　　2　　　 TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=1 Bytes=18)
　　　 4　　2　　　 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=518)
　　　 5　　1　　 TABLE ACCESS (FULL) OF 'DEPT1' (Cost=1 Card=1 Bytes=9)
　　
　　Statistics
　　----------------------------------------------------------
　　　　　　 39　recursive calls
　　　　　　 68　db block gets
　　　　　　 21　consistent gets
　　　　　　　0　physical reads
　　　　　　　0　redo size
　　　　　 2708　bytes sent via SQL*Net to client
　　　　　　751　bytes received via SQL*Net from client
　　　　　　　4　SQL*Net roundtrips to/from client
　　　　　　　1　sorts (memory)
　　　　　　　0　sorts (disk)
　　　　　　 14　rows processed
　　
　　在CBO下push_subq发挥了同样的作用:
　　
　　SQL> select /*+ push_subq */ a.*
　　　2　from emp a,dept b where a.deptno = b.deptno and exists (select 1 from dept1 where dept1.dname = b.dname);

　　
　　14 rows selected.
　　
　　Execution Plan
　　----------------------------------------------------------
　　　 0　　　SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=5 Bytes=275)
　　　 1　　0　 NESTED LOOPS (Cost=2 Card=5 Bytes=275)
　　　 2　　1　　 TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=1 Bytes=18)
　　　 3　　2　　　 TABLE ACCESS (FULL) OF 'DEPT1' (Cost=1 Card=1 Bytes=9)---'注重这里'
　　　 4　　1　　 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=518)
　　
　　Statistics
　　----------------------------------------------------------
　　　　　　　0　recursive calls
　　　　　　 84　db block gets
　　　　　　 11　consistent gets
　　　　　　　0　physical reads
　　　　　　　0　redo size
　　　　　 2709　bytes sent via SQL*Net to client
　　　　　　768　bytes received via SQL*Net from client
　　　　　　　4　SQL*Net roundtrips to/from client
　　　　　　　1　sorts (memory)
　　　　　　　0　sorts (disk)
　　　　　　 14　rows processed
　　
　　而在Oracle9i之中:
　　
　　[oracle@jumper oracle]$ sqlplus "/ as sysdba"
　　
　　SQL*Plus: Release 9.2.0.3.0 - Production on Sun Sep 12 21:42:57 2004
　　
　　Copyright (c) 1982, 2002, Oracle Corporation.　All rights reserved.
　　
　　Connected to:
　　Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
　　With the Partitioning, OLAP and Oracle Data Mining options
　　JServer Release 9.2.0.3.0 - Production
　　
　　SQL> connect scott/tiger
　　Connected.
　　SQL> set linesize 120
　　SQL> set autotrace traceonly
　　SQL> select a.*
　　　2　from emp a,dept b where a.deptno = b.deptno and exists (select 1 from dept1 where dept1.dname = b.dname);
　　
　　11 rows selected.
　　
　　Execution Plan
　　----------------------------------------------------------
　　　 0　　　SELECT STATEMENT Optimizer=CHOOSE
　　　 1　　0　 FILTER
　　　 2　　1　　 NESTED LOOPS
　　　 3　　2　　　 TABLE ACCESS (FULL) OF 'EMP'
　　　 4　　2　　　 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
　　　 5　　4　　　　 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
　　　 6　　1　　 TABLE ACCESS (FULL) OF 'DEPT1'
　　
　　Statistics
　　----------------------------------------------------------
　　　　　　　0　recursive calls
　　　　　　　0　db block gets
　　　　　　 26　consistent gets
　　　　　　　0　physical reads
　　　　　　　0　redo size
　　　　　 1164　bytes sent via SQL*Net to client
　　　　　　503　bytes received via SQL*Net from client
　　　　　　　2　SQL*Net roundtrips to/from client
　　　　　　　0　sorts (memory)
　　　　　　　0　sorts (disk)
　　　　　　 11　rows processed
　　
　　SQL> select /*+ push_subq */ a.*
　　　2　from emp a,dept b where a.deptno = b.deptno and exists (select 1 from dept1 where dept1.dname = b.dname);
　　
　　11 rows selected.
　　
　　Execution Plan
　　----------------------------------------------------------
　　　 0　　　SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=82 Bytes=9676)
　　　 1　　0　 HASH JOIN (SEMI) (Cost=8 Card=82 Bytes=9676)
　　　 2　　1　　 HASH JOIN (Cost=5 Card=82 Bytes=8938)
　　　 3　　2　　　 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=7134)
　　　 4　　2　　　 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=82 Bytes=1804)
　　　 5　　1　　 TABLE ACCESS (FULL) OF 'DEPT1' (Cost=2 Card=82 Bytes=738)
　　
　　Statistics
　　----------------------------------------------------------
　　　　　　　0　recursive calls
　　　　　　　0　db block gets
　　　　　　 10　consistent gets
　　　　　　　0　physical reads
　　　　　　　0　redo size
　　　　　 1195　bytes sent via SQL*Net to client
　　　　　　503　bytes received via SQL*Net from client
　　　　　　　2　SQL*Net roundtrips to/from client
　　　　　　　0　sorts (memory)
　　　　　　　0　sorts (disk)
　　　　　　 11　rows processed
　　
　　SQL> exec dbms_stats.gather_schema_stats('scott')
　　
　　PL/SQL procedure successfully completed.
　　
　　SQL> select a.*
　　　2　from emp a,dept b where a.deptno = b.deptno and exists (select 1 from dept1 where dept1.dname = b.dname);

　　
　　11 rows selected.
　　
　　Execution Plan
　　----------------------------------------------------------
　　　 0　　　SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=11 Bytes=682)
　　　 1　　0　 HASH JOIN (SEMI) (Cost=8 Card=11 Bytes=682)
　　　 2　　1　　 HASH JOIN (Cost=5 Card=11 Bytes=572)
　　　 3　　2　　　 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=52)
　　　 4　　2　　　 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=11 Bytes=429)
　　　 5　　1　　 TABLE ACCESS (FULL) OF 'DEPT1' (Cost=2 Card=4 Bytes=40)
　　
　　Statistics
　　----------------------------------------------------------
　　　　　　172　recursive calls
　　　　　　　0　db block gets
　　　　　　 60　consistent gets
　　　　　　　0　physical reads
　　　　　　　0　redo size
　　　　　 1196　bytes sent via SQL*Net to client
　　　　　　503　bytes received via SQL*Net from client
　　　　　　　2　SQL*Net roundtrips to/from client
　　　　　　　6　sorts (memory)
　　　　　　　0　sorts (disk)
　　　　　　 11　rows processed
　　
　　SQL> select /*+ push_subq */ a.*
　　　2　from emp a,dept b where a.deptno = b.deptno and exists (select 1 from dept1 where dept1.dname = b.dname);
　　
　　11 rows selected.
　　
　　Execution Plan
　　----------------------------------------------------------
　　　 0　　　SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=11 Bytes=682)
　　　 1　　0　 HASH JOIN (SEMI) (Cost=8 Card=11 Bytes=682)
　　　 2　　1　　 HASH JOIN (Cost=5 Card=11 Bytes=572)
　　　 3　　2　　　 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=52)
　　　 4　　2　　　 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=11 Bytes=429)
　　　 5　　1　　 TABLE ACCESS (FULL) OF 'DEPT1' (Cost=2 Card=4 Bytes=40)
　　
　　Statistics
　　----------------------------------------------------------
　　　　　　　0　recursive calls
　　　　　　　0　db block gets
　　　　　　 10　consistent gets
　　　　　　　0　physical reads
　　　　　　　0　redo size
　　　　　 1196　bytes sent via SQL*Net to client
　　　　　　503　bytes received via SQL*Net from client
　　　　　　　2　SQL*Net roundtrips to/from client
　　　　　　　0　sorts (memory)
　　　　　　　0　sorts (disk)
　　　　　　 11　rows processed
　　
　　SQL>
　　
　　可以肯定的是push_subq从8i到9i的作用发生了变化
　　
　　这个变化可能来自于CBO的更加优化