分享
 
 
 

关于PUSH_SUBQ提示的说明

王朝other·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

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.*

2from emp a,dept b where a.deptno = b.deptno and exists (select 1 from dept1 where dept1.dname = b.dname);

EMPNO ENAMEJOBMGR HIREDATE SAL COMM DEPTNO

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

7369SMITHCLERK 7902 17-DEC-8080020

7499ALLENSALESMAN7698 20-FEB-81 1600300 30

7521WARD SALESMAN7698 22-FEB-81 1250500 30

7566JONESMANAGER 7839 02-APR-81 297520

7654MARTIN SALESMAN7698 28-SEP-81 1250 1400 30

7698BLAKEMANAGER 7839 01-MAY-81 285030

7782CLARKMANAGER 7839 09-JUN-81 245010

7788SCOTTANALYST 7566 19-APR-87 300020

7839KING PRESIDENT17-NOV-81 500010

7844TURNER SALESMAN7698 08-SEP-81 15000 30

7876ADAMSCLERK 7788 23-MAY-87 110020

EMPNO ENAMEJOBMGR HIREDATE SAL COMM DEPTNO

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

7900JAMESCLERK 7698 03-DEC-8195030

7902FORD ANALYST 7566 03-DEC-81 300020

7934MILLER CLERK 7782 23-JAN-82 130010

14 rows selected.

Execution Plan

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

0SELECT STATEMENT Optimizer=CHOOSE

10 FILTER ---------------'最初push_subq的使命是为了消除/提高这个filter的效率的'

21 NESTED LOOPS

32 TABLE Access (FULL) OF 'EMP'

42 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'

54 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

61 TABLE ACCESS (FULL) OF 'DEPT1'

Statistics

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

0recursive calls

52db block gets

21consistent gets

0physical reads

0redo size

2715bytes sent via SQL*Net to client

751bytes received via SQL*Net from client

4SQL*Net roundtrips to/from client

1sorts (memory)

0sorts (disk)

14rows processed

用于push_subq是个CBO hints,这里我们可以看到COST的出现:

SQL select /*+ push_subq */ a.*

2from emp a,dept b where a.deptno = b.deptno and exists (select 1 from dept1 where dept1.dname = b.dname);

EMPNO ENAMEJOBMGR HIREDATE SAL COMM DEPTNO

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

7782CLARKMANAGER 7839 09-JUN-81 245010

7839KING PRESIDENT17-NOV-81 500010

7934MILLER CLERK 7782 23-JAN-82 130010

7369SMITHCLERK 7902 17-DEC-8080020

7566JONESMANAGER 7839 02-APR-81 297520

7788SCOTTANALYST 7566 19-APR-87 300020

7876ADAMSCLERK 7788 23-MAY-87 110020

7902FORD ANALYST 7566 03-DEC-81 300020

7499ALLENSALESMAN7698 20-FEB-81 1600300 30

7521WARD SALESMAN7698 22-FEB-81 1250500 30

7654MARTIN SALESMAN7698 28-SEP-81 1250 1400 30

EMPNO ENAMEJOBMGR HIREDATE SAL COMM DEPTNO

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

7698 BLAKEMANAGER 7839 01-MAY-81 285030

7844 TURNER SALESMAN7698 08-SEP-81 15000 30

7900 JAMESCLERK 7698 03-DEC-8195030

14 rows selected.

Execution Plan

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

0SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=122)

10 NESTED LOOPS (Cost=3 Card=1 Bytes=122)

21 TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=2 Bytes=44)

32 TABLE ACCESS (FULL) OF 'DEPT1' (Cost=1 Card=1 Bytes=9)--------'这里消除了之前的filter'

41 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=21 Bytes=2100)

Statistics

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

204recursive calls

85db block gets

38consistent gets

0physical reads

0redo size

2706bytes sent via SQL*Net to client

768bytes received via SQL*Net from client

4SQL*Net roundtrips to/from client

5sorts (memory)

0sorts (disk)

14rows processed

再看CBO下:

正常情况下,没什么好说的:

SQL set autotrace traceonly

SQL exec dbms_stats.gather_schema_stats('SCOTT')

PL/SQL procedure successfully completed.

SQL select a.*

2from 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

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

0SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=5 Bytes=275)

10 FILTER

21 NESTED LOOPS (Cost=2 Card=5 Bytes=275)

32 TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=1 Bytes=18)

42 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=518)

51 TABLE ACCESS (FULL) OF 'DEPT1' (Cost=1 Card=1 Bytes=9)

Statistics

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

39recursive calls

68db block gets

21consistent gets

0physical reads

0redo size

2708bytes sent via SQL*Net to client

751bytes received via SQL*Net from client

4SQL*Net roundtrips to/from client

1sorts (memory)

0sorts (disk)

14rows processed

在CBO下push_subq发挥了同样的作用:

SQL select /*+ push_subq */ a.*

2from 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

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

0SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=5 Bytes=275)

10 NESTED LOOPS (Cost=2 Card=5 Bytes=275)

21 TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=1 Bytes=18)

32 TABLE ACCESS (FULL) OF 'DEPT1' (Cost=1 Card=1 Bytes=9)---'注重这里'

41 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=518)

Statistics

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

0recursive calls

84db block gets

11consistent gets

0physical reads

0redo size

2709bytes sent via SQL*Net to client

768bytes received via SQL*Net from client

4SQL*Net roundtrips to/from client

1sorts (memory)

0sorts (disk)

14rows 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.*

2from 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

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

0SELECT STATEMENT Optimizer=CHOOSE

10 FILTER

21 NESTED LOOPS

32 TABLE ACCESS (FULL) OF 'EMP'

42 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'

54 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

61 TABLE ACCESS (FULL) OF 'DEPT1'

Statistics

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

0recursive calls

0db block gets

26consistent gets

0physical reads

0redo size

1164bytes sent via SQL*Net to client

503bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

11rows processed

SQL select /*+ push_subq */ a.*

2from 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

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

0SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=82 Bytes=9676)

10 HASH JOIN (SEMI) (Cost=8 Card=82 Bytes=9676)

21 HASH JOIN (Cost=5 Card=82 Bytes=8938)

32 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=7134)

42 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=82 Bytes=1804)

51 TABLE ACCESS (FULL) OF 'DEPT1' (Cost=2 Card=82 Bytes=738)

Statistics

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

0recursive calls

0db block gets

10consistent gets

0physical reads

0redo size

1195bytes sent via SQL*Net to client

503bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

11rows processed

SQL exec dbms_stats.gather_schema_stats('scott')

PL/SQL procedure successfully completed.

SQL select a.*

2from 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

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

0SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=11 Bytes=682)

10 HASH JOIN (SEMI) (Cost=8 Card=11 Bytes=682)

21 HASH JOIN (Cost=5 Card=11 Bytes=572)

32 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=52)

42 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=11 Bytes=429)

51 TABLE ACCESS (FULL) OF 'DEPT1' (Cost=2 Card=4 Bytes=40)

Statistics

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

172recursive calls

0db block gets

60consistent gets

0physical reads

0redo size

1196bytes sent via SQL*Net to client

503bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

6sorts (memory)

0sorts (disk)

11rows processed

SQL select /*+ push_subq */ a.*

2from 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

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

0SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=11 Bytes=682)

10 HASH JOIN (SEMI) (Cost=8 Card=11 Bytes=682)

21 HASH JOIN (Cost=5 Card=11 Bytes=572)

32 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=52)

42 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=11 Bytes=429)

51 TABLE ACCESS (FULL) OF 'DEPT1' (Cost=2 Card=4 Bytes=40)

Statistics

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

0recursive calls

0db block gets

10consistent gets

0physical reads

0redo size

1196bytes sent via SQL*Net to client

503bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

11rows processed

SQL

可以肯定的是push_subq从8i到9i的作用发生了变化

这个变化可能来自于CBO的更加优化

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有