Plans and the order of execution(执行计划顺序)
上一篇 /
下一篇 2008-07-06 11:00:11
Plans and the order of execution(执行计划顺序)
整理的内容来自ITPUB和Metalink: Note:46234.1
执行计划顺序理解的核心思想：
understand the PARENT -- CHILD relationships involved:
PARENT
FIRST CHILD
SECOND CHILD
In this example, the FIRST CHILD is executed first followed by the
SECOND CHILD, then the PARENT collates the output in some way.
A more complex case is:
PARENT1
FIRST CHILD
FIRST GRANDCHILD
SECOND CHILD
Here the same principles apply, the FIRST GRANDCHILD is the initial operation
then the FIRST CHILD followed by the SECOND CHILD and finally the PARENT
collates the output.
举例说明：
EXAMPLE 1：
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'USER_INFO'
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'USER_NUM_TABLE'
5 3 INDEX (RANGE SCAN) OF 'PK_USER_INFO' (UNIQUE)
左边的两列数字，第一列表示这条计划的编号，第二列是这条计划的父计划的编号；如果一条计划有子计划，那么先要执行其子计划；
在这个例子中：从第一条编号为0的（SELECT STATEMENT ptimizer=CHOOSE）开始，他有个子计划1（SORT (AGGREGATE)），然后1有个子计划2，2有子计划3， 3 有子计划4和5，4是3的第一个子计划，所以先执行4（TABLE ACCESS (FULL) OF 'USER_NUM_TABLE'），再执行5(INDEX (RANGE SCAN) OF 'PK_USER_INFO' (UNIQUE))，4和5执行完返回到其父计划3（NESTED LOOPS），3把4和5取到的rows进行nested loops，结果再返回到2，再到1排序，再到0select
EXAMPLE 2：
set autotrace traceonly explain
select ename,dname
from emp, dept
where emp.deptno=dept.deptno
and dept.dname in ('ACCOUNTING','RESEARCH','SALES','OPERATIONS');
15 rows selected.
This produces the following explain plan:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=3 Card=8 Bytes=248)
1 0 HASH JOIN (Cost=3 Card=8 Bytes=248)
2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=3 Bytes=36)
3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=16 Bytes=304)
A shortened summary of this is:
Execution starts with ID=0: SELECT STATEMENT but this is dependent on it's child objects
So it executes its first child step: ID=1 PID=0 HASH JOIN but this is dependent on it's child objects
So it executes its first child step: ID=2 PID=1 TABLE ACCESS (FULL) OF 'DEPT'
Then the second child step: ID=3 PID=2 TABLE ACCESS (FULL) OF 'EMP'
Rows are returned to the parent step(s) until finished
EXAMPLE 3
=========
select /*+ RULE */ ename,dname
from dept , emp
where emp.deptno=dept.deptno
and dept.dname in ('ACCOUNTING','RESEARCH','SALES','OPERATIONS');
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=HINT: RULE
1 0 NESTED LOOPS
2 1 TABLE ACCESS (FULL) OF 'EMP'
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
4 3 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
Execution starts with ID=0: SELECT STATEMENT but this is dependent on it's child objects
So it executes it's first child step: ID=1 PID=0 NESTED LOOPS but this is dependent on it's child objects
So it executes it's first child step: ID=2 PID=1 TABLE ACCESS (FULL) OF 'EMP'
Rows are fetched in ID=2 These rows are returned to the parent
to drive the join in the parent: ID=1 PID=0 NESTED LOOPS
ID=1 uses the rows to execute ID=3: ID=3 PID=1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' but this is dependent on it's child objects
So it executes it's first child step: ID=4 PID=3 INDEX (UNIQUE SCAN) OF 'PK_DEPT'
Rows are returned to the parent ID=1
ID=1 returns rows to it's parent ID=0
导入论坛
引用链接
收藏
分享给好友
推荐到圈子
管理
举报
TAG:
