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