Using EXPLAIN PLAN

EXPLAIN PLAN FOR

SELECT

e.employee_id,

j.job_title,

e.salary,

d.department_name

FROM employees e, jobs j, departments d

WHERE e.employee_id < 103

AND e.job_id = j.job_id

AND e.department_id = d.department_id;

 

 

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

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

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

| 0 | SELECT STATEMENT | | 3 | 189 | 10 (10)|

| 1 | NESTED LOOPS | | 3 | 189 | 10 (10)|

| 2 | NESTED LOOPS | | 3 | 141 | 7 (15)|

 |* 3 | TABLE ACCESS FULL | EMPLOYEES | 3 | 60 | 4 (25)|

| 4 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 513 | 2 (50)|

|* 5 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | |

 | 6 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 27 | 432 | 2 (50)|

 |* 7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | |

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

 Predicate Information (identified by operation id):

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

3 - filter("E"."EMPLOYEE_ID"<103)

5 - access("E"."JOB_ID"="J"."JOB_ID")

7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")  

 

Steps in the Execution Plan:The following steps physically retrieve data from an object in the database:

Step 3 reads all rows of the employees table.

Step 5 looks up each job_id in JOB_ID_PK index and finds the rowids of the associated rows in the jobs table.

Step 4 retrieves the rows with rowids that were returned by Step 5 from the jobs table.

Step 7 looks up each department_id in DEPT_ID_PK index and finds the rowids of the associated rows in the departments table.

Step 6 retrieves the rows with rowids that were returned by Step 7 from the departments table.

 

The following steps operate on rows returned by the previous row source:

Step 2 performs the nested loop operation on job_id in the jobs and employees tables, accepting row sources from Steps 3 and 4, joining each row from Step 3 source to its corresponding row in Step 4, and returning the resulting rows to Step 2.

 

Step 1 performs the nested loop operation, accepting row sources from Step 2 and Step 6, joining each row from Step 2 source to its corresponding row in Step 6, and returning the resulting rows to Step 1.

 

 

Outer loop

In the execution plan , the outer loop and the equivalent statement are as follows:

3 | TABLE ACCESS FULL | EMPLOYEES SELECT e.employee_id, e.salary FROM employees e WHERE e.employee_id < 103 

Inner loop

The execution plan , shows the inner loop being iterated for every row fetched from the outer loop, as follows:

4 | TABLE ACCESS BY INDEX ROWID| JOBS 5 | INDEX UNIQUE SCAN | JOB_ID_PK SELECT j.job_title FROM jobs j WHERE e.job_id = j.job_id

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20945761/viewspace-558016/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20945761/viewspace-558016/