执行计划的执行顺序精解

阅读执行计划的规则：

1. 缩进最深的，最先执行

2. 缩进深度相同的，先上后下

举例说明：

需要注意的是，这里使用解释执行计划来举例说明

解释执行计划

explain plan for

select * from hr.employees a,hr.departments b,hr.jobs c,hr.locations d

where a.department_id = b.department_id

and   a.job_id = c.job_id

and   b.location_id = d.location_id

and   b.department_id = 30;

已解释。

获得解释执行计划

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 706249465

---------------------------------------------------------------------| Id  | Operation                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT               |             |     6 |  1032 |     9  (12)| 00:00:01 |

|*  1 |  HASH JOIN                     |             |     6 |  1032 |     9  (12)| 00:00:01 |

|   2 |   NESTED LOOPS                 |             |     6 |   834 |     5   (0)| 00:00:01 |

|   3 |    NESTED LOOPS                |             |     1 |    70 |     2   (0)| 00:00:01 |

|   4 |     TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |     1 |    21 |     1   (0)| 00:00:01 |

|*  5 |      INDEX UNIQUE SCAN         | DEPT_ID_PK  |     1 |       |     0   (0)| 00:00:01 |

|   6 |     TABLE ACCESS BY INDEX ROWID| LOCATIONS   |    23 |  1127 |     1   (0)| 00:00:01 |

|*  7 |      INDEX UNIQUE SCAN         | LOC_ID_PK   |     1 |       |     0   (0)| 00:00:01 |

|*  8 |    TABLE ACCESS FULL           | EMPLOYEES   |     6 |   414 |     3   (0)| 00:00:01 |

|   9 |   TABLE ACCESS FULL            | JOBS        |    19 |   627 |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - access("A"."JOB_ID"="C"."JOB_ID")

   5 - access("B"."DEPARTMENT_ID"=30)

   7 - access("B"."LOCATION_ID"="D"."LOCATION_ID")

   8 - filter("A"."DEPARTMENT_ID"=30)

已选择24行。

为了看清缩进关系，我特地把这ID,Operation,Name列单独拿出来显示。

Id  | Operation                      | Name       

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

   0 | SELECT STATEMENT               |            

*  1 |  HASH JOIN                     |            

   2 |   NESTED LOOPS                 |            

   3 |    NESTED LOOPS                |            

   4 |     TABLE ACCESS BY INDEX ROWID| DEPARTMENTS

*  5 |      INDEX UNIQUE SCAN         | DEPT_ID_PK 

   6 |     TABLE ACCESS BY INDEX ROWID| LOCATIONS  

*  7 |      INDEX UNIQUE SCAN         | LOC_ID_PK  

*  8 |    TABLE ACCESS FULL           | EMPLOYEES  

   9 |   TABLE ACCESS FULL            | JOBS