在oracle9i中执行计划如下分析：
SQL> select ename,dname from emp, dept where emp.deptno=dept.deptno and dept.dname in ('ACCOUNTING','RESEARCH','SALES','OPERATIONS');
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
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)
在oracle9i中执行计划如下分析：
SQL> select ename,dname from emp, dept where emp.deptno=dept.deptno and dept.dname in ('ACCOUNTING','RESEARCH','SALES','OPERATIONS');
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
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)
关于前面的两个数字，第一个是状态ID,第二个是父ID。
就是如下所示：0-->1-->2
|
|-->3-->4
在上图里，0的执行依靠1，1的执行又依赖2和3，2是没有子ID的，所以2最先执行，然后是4，在然后是3；然后2和3的结果传回1。
在oracle10g中执行计划的显示结果发生了改变：
SQL> set linesize 200
SQL> select count(*) from site_connect_detail;
COUNT(*)
----------
16250151
执行计划
----------------------------------------------------------
Plan hash value: 2918895406
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15290 (4)| 00:03:04 | | |
| 1 | SORT AGGREGATE | | 1 | | | | |
| 2 | PARTITION RANGE ALL| | 14M| 15290 (4)| 00:03:04 | 1 | 44 |
| 3 | TABLE ACCESS FULL | SITE_CONNECT_DETAIL | 14M| 15290 (4)| 00:03:04 | 1 | 44 |
----------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
74696 consistent gets
74052 physical reads
0 redo size
411 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
我们可以按照the rightmost-uppermost(最右最上) operation of an explain 规则来分析执行计划。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/34329/viewspace-914977/ ，如需转载，请注明出处，否则将追究法律责任。
