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的更加优化
