Oracle: Entendendo o SQL Explain Plan

Referência https://docs.oracle.com/cd/B10500_01/server.920/a96533/optimops.htm

Coletando o Explain

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;

Gerando o output com o comando

select * from table(dbms_xplan.display)

Output

-----------------------------------------------------------------------------------
| 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")

Termos:

• Operation: TABLE ACCESS FULL
• Operation: INDEX UNIQUE SCAN

Ver também

• Mais Artigos sobre Oracle