舉例一個比較好的表連接的執(zhí)行計劃

SQL> var loc varchar2(30)
SQL> exec :loc:='South San Francisco'

PL/SQL procedure successfully completed.

SQL> SELECT 
 2 emp.last_name,emp.first_name,j.job_title,d.department_name,l.city,l.state_province,l.postal_code,l.street_address,
 3 emp.email,emp.phone_number,emp.hire_date,emp.salary,mgr.last_name
 4 from hr.employees emp,hr.employees mgr,hr.departments d,hr.locations
 5 l,hr.jobs j
 6 where l.city =:loc
 7 and emp.manager_id=mgr.employee_id
 8 and emp.department_id=d.department_id
 9 and d.location_id=l.location_id
 10 and emp.job_id=j.job_id;
--每次都是以嵌套循環(huán)來完成整個的查詢流程，這就是一個最好的執(zhí)行計劃
45 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4121168346

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 2580 | 8 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 15 | 2580 | 8 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 15 | 2400 | 6 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 15 | 1995 | 5 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 4 | 268 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| LOCATIONS | 1 | 48 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | LOC_CITY_IX | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 4 | 76 | 1 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 4 | | 0 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 4 | 264 | 1 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | JOBS | 1 | 27 | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | 0 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 1 | 12 | 1 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 6 - access("L"."CITY"=:LOC)
 8 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
 10 - access("EMP"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
 12 - access("EMP"."JOB_ID"="J"."JOB_ID")
 14 - access("EMP"."MANAGER_ID"="MGR"."EMPLOYEE_ID")


Statistics
----------------------------------------------------------
 2035 recursive calls
 0 db block gets
 669 consistent gets
 19 physical reads
 0 redo size
 3923 bytes sent via SQL*Net to client
 407 bytes received via SQL*Net from client
 4 SQL*Net roundtrips to/from client
 24 sorts (memory)
 0 sorts (disk)
 45 rows processed