« Dataguard配置Step by Step | Blog首页 | 如何获得创建控制文件的脚本并重建控制文件 »
关于PUSH_SUBQ提示的说明
作者:eygle | 【转载请注出处】|【云和恩墨 领先的zData数据库一体机 | zCloud PaaS云管平台 | SQM SQL审核平台 | ZDBM 数据库备份一体机】
链接:https://www.eygle.com/archives/2004/10/hins_push_subq.html
链接:https://www.eygle.com/archives/2004/10/hins_push_subq.html
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的更加优化
这个变化可能来自于CBO的更加优化
历史上的今天...
>> 2010-10-15文章:
>> 2008-10-15文章:
>> 2006-10-15文章:
By eygle on 2004-10-15 21:07 | Comments (0) | SQL.PLSQL | 67 |