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:
Índices e acesso à tabela
TABLE ACCESS FULL Lê a tabela inteira — todas as linhas e colunas — conforme armazenada no disco. É uma das operações mais caras.
INDEX UNIQUE SCAN O INDEX UNIQUE SCAN executa apenas uma B-tree traversal . O BD usa essa operação se uma restrição exclusiva garantir que os critérios de pesquisa não correspondam a mais de uma entrada.
INDEX RANGE SCAN O INDEX RANGE SCAN realiza a uma B-tree traversal e segue a "leaf node chain" para encontrar todas as entradas correspondentes.
INDEX FULL SCAN Lê todas as linhas do índice. Dependendo de várias estatísticas do sistema, o BD pode executar essa operação se precisar de todas as linhas na ordem do índice - por exemplo, devido a uma cláusula correspondente de ordem por. Em vez disso, o otimizador também pode usar um INDEX FAST FULL SCAN e executar uma operação de classificação adicional.
INDEX FAST FULL SCAN Lê todas as linhas do índice, conforme armazenado no disco. Essa operação geralmente é executada em vez de uma verificação completa da tabela se todas as colunas necessárias estiverem disponíveis no índice. Semelhante ao TABLE ACCESS FULL, o INDEX FAST FULL SCAN pode se beneficiar de operações de leitura de vários blocos.
TABLE ACCESS BY INDEX ROWID Recupera uma linha da tabela usando o ROWID recuperado da pesquisa de índice anterior.
Associations
operações de join, processam apenas duas tabelas por vez.
NESTED LOOPS JOIN Une duas tabelas buscando o resultado de uma tabela e consultando a outra tabela para cada linha da primeira.
HASH JOIN A junção de hash carrega os registros candidatos de um lado da junção em uma tabela de hash que é então sondada para cada linha do outro lado da junção.
MERGE JOIN A junção de join combina duas listas classificadas como um zíper. Ambos os lados da junção devem ser pré-classificados.
Classificando e Agrupamento
SORT ORDER BY Classifica o resultado de acordo com a cláusula order by. Utiliza muita memória para gerar o resultado intermediário.
SORT ORDER BY STOPKEY Classifica um subconjunto do resultado de acordo com a cláusula order by. Usado para consultas top-N se a execução em pipeline não for possível.
SORT GROUP BY Classifica o conjunto de resultados no grupo por colunas e agrega o resultado classificado em uma segunda etapa. Utiliza muita memória para gerar o resultado intermediário (não em pipeline).
SORT GROUP BY NOSORT Agrega um conjunto pré-classificado de acordo com a cláusula group by. Esta operação não armazena em buffer o resultado intermediário: ela é executada em pipeline.
HASH GROUP BY Agrupa o resultado usando uma tabela de hash. Utiliza muita memória para gerar o resultados intermediário (não em pipeline). A saída não é ordenada de forma significativa.
COUNT STOPKEY Aborta as operações subjacentes quando o número desejado de linhas foi buscado.
WINDOW NOSORT STOPKEY Usa uma função de janela (cláusula over) para abortar a execução quando o número desejado de linhas foi buscado.