Oracle: Entendendo o SQL Explain Plan - Revision history

Ebasso at 15:44, 12 February 2022

2022-02-12T15:44:14Z

@@ Line 37: / Line 37: @@
 Termos:
-* Operation: '''TABLE ACCESS FULL'''
+Índices e acesso à tabela
-* Operation: '''INDEX UNIQUE SCAN'''

Ebasso at 14:46, 12 February 2022

2022-02-12T14:46:40Z

← Older revision		Revision as of 14:46, 12 February 2022
Line 39:		Line 39:
	* Operation: '''TABLE ACCESS FULL'''		* Operation: '''TABLE ACCESS FULL'''
	* Operation: '''INDEX UNIQUE SCAN'''		* Operation: '''INDEX UNIQUE SCAN'''



	== Ver também ==		== Ver também ==

Ebasso at 14:33, 12 February 2022

2022-02-12T14:33:50Z

← Older revision		Revision as of 14:33, 12 February 2022
Line 1:		Line 1:
			Referência https://docs.oracle.com/cd/B10500_01/server.920/a96533/optimops.htm

	Coletando o Explain		Coletando o Explain

Line 8:		Line 10:
	AND e.department_id = d.department_id;		AND e.department_id = d.department_id;

			Gerando o output com o comando

			select * from table(dbms_xplan.display)

	Output		Output

Ebasso: Criou a página com "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 <..."

2022-02-12T14:30:35Z

Criou a página com "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 <..."

New page

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;

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 ==

*[[Oracle| Mais Artigos sobre Oracle]]

[[Category:Oracle]]
[[Category:Databases]]
[[Category:Tecnologias]]