- 最后登录
- 2015-1-5
- 在线时间
- 463 小时
- 威望
- 6
- 金钱
- 960
- 注册时间
- 2012-6-27
- 阅读权限
- 10
- 帖子
- 80
- 精华
- 0
- 积分
- 6
- UID
- 538
|
2#
发表于 2013-10-15 10:03:50
本帖最后由 dla001 于 2013-10-15 10:07 编辑
Maclean Liu(刘相兵 发表于 2013-10-14 22:07 ![](static/image/common/back.gif)
请给出测试用例
11.2.0.3.7 x64
OEL5.8 x64
执行计划显示的是先执行子查询,但子查询中的条件是主查询传过的才对,应该是先主再子。- create table zwh_main (id number, c1 varchar2(50));
- create table zwh_sub (id number, id2 number);
- insert into zwh_main select rownum, 'hello' from dual connect by level <10000;
- insert into zwh_sub select rownum,rownum from dual connect by level <1000;
- create index idx_zwh_main on zwh_main(id);
- create index idx_zwh_sub on zwh_sub(id);
- set linesize 150
- set long 999
- set pagesize 99
- select zwh_main.*,(select sum(id2) from zwh_sub where zwh_sub.id=zwh_main.id) s
- from zwh_main
- where zwh_main.id=100;
- select * from table(dbms_xplan.display_cursor());
- SQL_ID 56aha300yf6gt, child number 0
- -------------------------------------
- select zwh_main.*,(select sum(id2) from zwh_sub where
- zwh_sub.id=zwh_main.id) s from zwh_main where zwh_main.id=100
- Plan hash value: 4037143566
- ---------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 2 (100)| |
- | 1 | SORT AGGREGATE | | 1 | 26 | | |
- | 2 | TABLE ACCESS BY INDEX ROWID| ZWH_SUB | 10 | 260 | 2 (0)| 00:00:01 |
- |* 3 | INDEX RANGE SCAN | IDX_ZWH_SUB | 4 | | 1 (0)| 00:00:01 |
- | 4 | TABLE ACCESS BY INDEX ROWID | ZWH_MAIN | 1 | 40 | 2 (0)| 00:00:01 |
- |* 5 | INDEX RANGE SCAN | IDX_ZWH_MAIN | 1 | | 1 (0)| 00:00:01 |
- ---------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 3 - access("ZWH_SUB"."ID"=:B1)
- 5 - access("ZWH_MAIN"."ID"=100)
- Note
- -----
- - dynamic sampling used for this statement (level=2)
- 28 rows selected.
- SQL> @sqlorder
- Enter value for macsqlid: 56aha300yf6gt
- old 1: select 'Input SQL_ID : ',lower('&&macsqlid') macsqlid from dual
- new 1: select 'Input SQL_ID : ',lower('56aha300yf6gt') macsqlid from dual
- Input SQL_ID : 56aha300yf6gt
- Enter value for child_number: 0
- old 1: select 'Input Child_number : ',lower('&&child_number') child_number from dual
- new 1: select 'Input Child_number : ',lower('0') child_number from dual
- Input Child_number : 0
- old 2: sql_id='&&macsqlid' and child_number=&&child_number
- new 2: sql_id='56aha300yf6gt' and child_number=0
- Table created.
- Table created.
- PL/SQL procedure successfully completed.
- old 2: sql_id='&&macsqlid' and child_number=&&child_number
- new 2: sql_id='56aha300yf6gt' and child_number=0
- 6 rows created.
- Commit complete.
- OO OBJECT_NAME Execution Order
- ------------------------------ ------------------------------ ---------------
- SELECT STATEMENT 6
- SORT AGGREGATE 3
- TABLE ACCESS BY INDEX ROWID ZWH_SUB 2
- INDEX RANGE SCAN IDX_ZWH_SUB 1
- TABLE ACCESS BY INDEX ROWID ZWH_MAIN 5
- INDEX RANGE SCAN IDX_ZWH_MAIN 4
- 6 rows selected.
- Commit complete.
- Table dropped.
- Table dropped.
复制代码 |
|