Join operation
Nested Loops Join
- 선행 테이블 (Outer Table) 결정 후 후행 테이블(Inner Table)에 반복적인 접근
- 후행 테이블의 조인 컬럼에 인덱스 필요
- 소량의 데이터를 조인 시 사용
- 부분 범위 (first_rows) 처리에 최적화
- 많은 양의 데이터 조인 시 Random Access 증가
- 8i
- Driving outer
- 이것을 선택하는게 가장 중요하다
- 작은 table 우선으로 선택
- 1건만 찾아낼 수 있다면 걔가 무조건 outer
- 추가조건
- Drived inner
- 인덱스가 존재하면 좋다.
- optiomizer_mode 가 all_rows면 이 방법을 잘 사용하지 않으므로, first_rows로 둔다.
- 가장 일반적인 실행 계획
- Nested Loop
- Outer table
- Inner table
- index(Inner table)
9i
- Prefetching
11g
USER01@orcl2> alter session set optimizer_mode=first_rows_1;
Session altered.
USER01@orcl2> SELECT d.department_id, d.department_name, e.last_name, e.salary
FROM departments d, employees e
WHERE d.department_id = e.department_id ;
USER01@orcl2> @xplan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2m4pj9uqtvxb2, child number 0
-------------------------------------
SELECT d.department_id, d.department_name, e.last_name, e.salary
FROM departments d, employees e WHERE d.department_id =
e.department_id
Plan hash value: 1835612793
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 106 |00:00:00.01 | 16 | 5 |
| 1 | NESTED LOOPS | | 1 | | 106 |00:00:00.01 | 16 | 5 |
| 2 | NESTED LOOPS | | 1 | 1 | 106 |00:00:00.01 | 11 | 3 |
| 3 | TABLE ACCESS FULL(OUTER) | DEPARTMENTS | 1 | 14 | 27 |00:00:00.01 | 5 | 2 |
|* 4 | INDEX RANGE SCAN(INNER) | EMPL_DEPTNO_IX | 27 | 3 | 106 |00:00:00.01 | 6 | 1 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 106 | 1 | 106 |00:00:00.01 | 5 | 2 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
====================================================================================================
USER01@orcl2> SELECT /*+ leading(d) use_nl(e) */
d.department_id, d.department_name, e.last_name, e.salary
FROM departments d, employees e
WHERE d.department_id = e.department_id ;
USER01@orcl2> @xplan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID b10j8n11jc7nx, child number 0
-------------------------------------
SELECT /*+ leading(d) use_nl(e) */ d.department_id, d.department_name,
e.last_name, e.salary FROM departments d, employees e WHERE
d.department_id = e.department_id
Plan hash value: 1835612793
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 106 |00:00:00.01 | 16 |
| 1 | NESTED LOOPS | | 1 | | 106 |00:00:00.01 | 16 |
| 2 | NESTED LOOPS | | 1 | 106 | 106 |00:00:00.01 | 11 |
| 3 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 5 |
|* 4 | INDEX RANGE SCAN | EMPL_DEPTNO_IX | 27 | 10 | 106 |00:00:00.01 | 6 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 106 | 4 | 106 |00:00:00.01 | 5 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
USER01@orcl2> SELECT /*+ leading(e) use_nl(d) */
d.department_id, d.department_name, e.last_name, e.salary
FROM departments d, employees e
WHERE d.department_id = e.department_id ;
USER01@orcl2> @xplan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0mjhy9abrx8n7, child number 0
-------------------------------------
SELECT /*+ leading(e) use_nl(d) */ d.department_id, d.department_name,
e.last_name, e.salary FROM departments d, employees e WHERE
d.department_id = e.department_id
Plan hash value: 1054253669
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 106 |00:00:00.01 | 117 | 2 |
| 1 | NESTED LOOPS | | 1 | | 106 |00:00:00.01 | 117 | 2 |
| 2 | NESTED LOOPS | | 1 | 106 | 106 |00:00:00.01 | 11 | 2 |
| 3 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 6 | 1 |
|* 4 | INDEX UNIQUE SCAN | DEPT_DEPTID_IX | 107 | 1 | 106 |00:00:00.01 | 5 | 1 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 106 | 1 | 106 |00:00:00.01 | 106 | 0 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
USER01@orcl2> SELECT /*+ optimizer_features_enable('8.1.7') use_nl( d e) */
d.department_id, d.department_name, e.last_name, e.salary
FROM departments d, employees e
WHERE d.department_id = e.department_id ;
USER01@orcl2> @xplan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 297kfb11b2rtq, child number 0
-------------------------------------
SELECT /*+ optimizer_features_enable('8.1.7') use_nl( d e) */
d.department_id, d.department_name, e.last_name, e.salary FROM
departments d, employees e WHERE d.department_id = e.department_id
Plan hash value: 2968905875
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 106 |00:00:00.01 | 115 |
| 1 | NESTED LOOPS | | 1 | 106 | 106 |00:00:00.01 | 115 |
| 2 | TABLE ACCESS FULL| DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 5 |
|* 3 | TABLE ACCESS FULL| EMPLOYEES | 27 | 4 | 106 |00:00:00.01 | 110 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
Note
-----
- cpu costing is off (consider enabling it)
- NLJ_PREFETCH (9i)
USER01@orcl2> SELECT /*+ optimizer_features_enable('9.2.0')
leading(d) use_nl(e) index(e empl_deptno_ix) nlj_prefetch(e) */
d.department_id, d.department_name, e.last_name, e.salary
FROM departments d, employees e
WHERE d.department_id = e.department_id ;
USER01@orcl2> @xplan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6vjkwd9zf5mwx, child number 0
-------------------------------------
SELECT /*+ optimizer_features_enable('9.2.0') leading(d) use_nl(e)
index(e empl_deptno_ix) nlj_prefetch(e) */ d.department_id,
d.department_name, e.last_name, e.salary FROM departments d, employees
e WHERE d.department_id = e.department_id
Plan hash value: 5884908
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 0 | | 0 |00:00:00.01 | 0 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 4 | 106 |00:00:00.01 | 16 |
| 2 | NESTED LOOPS | | 1 | 106 | 134 |00:00:00.01 | 11 |
| 3 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 5 |
|* 4 | INDEX RANGE SCAN | EMPL_DEPTNO_IX | 27 | 10 | 106 |00:00:00.01 | 6 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
Note
-----
- cpu costing is off (consider enabling it)
- NLJ_BATCHING (11g)
USER01@orcl2> SELECT /*+ leading(d) use_nl(e) */
d.department_id, d.department_name, e.last_name, e.salary
FROM departments d, employees e
WHERE d.department_id = e.department_id ;
USER01@orcl2> SELECT /*+ leading(d) use_nl(e) nlj_batching(e) */
d.department_id, d.department_name, e.last_name, e.salary
FROM departments d, employees e
WHERE d.department_id = e.department_id ;
USER01@orcl2> SELECT /*+ leading(d) use_nl(e) */
d.department_id, d.department_name, e.last_name, e.salary
FROM departments d, employees e
WHERE d.department_id = e.department_id
and d.location_id=1700;
@xplan
Sort Merge Join
- 각각의 집합을 조인 컬럽으로 정렬 후 조인 수행
- 정렬 작업이 완료되기 전 부분적인 조인 결과 도출 불가능
- 정렬해야 할 데이터가 많은 경우 부담이 가장 큰 조인 방법
- Non equi join 시 사용 가능
USER01@orcl2> SELECT /*+ leading(d) use_merge(e) */
d.department_id, d.department_name, e.last_name, e.salary
FROM departments d, employees e
WHERE d.department_id = e.department_id ;
USER01@orcl2> @xplan
USER01@orcl2> SELECT /*+ leading(e) use_merge(d) */
d.department_id, d.department_name, e.last_name, e.salary
FROM departments d, employees e
WHERE d.department_id = e.department_id ;
USER01@orcl2> @xplan
USER01@orcl2> SELECT /*+ leading(d) use_merge(e) no_index(d) */
d.department_id, d.department_name, e.last_name, e.salary
FROM departments d, employees e
WHERE d.department_id = e.department_id ;
USER01@orcl2> @xplan
USER01@orcl2> SELECT /*+ leading(e) use_merge(d) no_index(d) */
d.department_id, d.department_name, e.last_name, e.salary
FROM departments d, employees e
WHERE d.department_id = e.department_id ;
USER01@orcl2> @xplan
USER01@orcl2> SELECT /*+ leading(e) use_merge(s) no_index(e) */
e.empno, e.ename, e.sal, s.grade
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal ;
USER01@orcl2> @xplan
USER01@orcl2> SELECT /*+ leading(e) use_merge(s) */
e.empno, e.ename, e.sal, s.grade
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal ;
USER01@orcl2> @xplan
Hash Join
- PGA memory를 사용
- 작은 행 소스를 사용하여 해시 테이블을 생성
- hash table 생성
- 전체범위 처리
- 대신에 만들어진다면 굉장히 빨라진다.
- 두 집합 중 크기가 작은 테이블을 선행 테이블(hash table)로 결정 후 후행 테이블(Probe Table)을 엑세스하여 조인
- Equi Join 에서만 사용 가능
- 대량의 데이터를 조인 시 사용
- 전체 범위 (all_rows)에 최적화
- 소량의 데이터를 조인할 때 Hash Join 이 사용되면 불필요한 I/O 증가
- 각 집합의 비조인 조건 컬럼을 선행으로 하는 결합 인덱스 사용 고려
USER01@orcl2> SELECT /*+ USE_HASH(d e)*/ d.department_id, d.department_name, e.last_name, e.salary
FROM departments d, employees e
WHERE d.department_id = e.department_id ;
USER01@orcl2> @xplan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6cmbk9xyygu6j, child number 0
-------------------------------------
SELECT /*+ USE_HASH(d e)*/ d.department_id, d.department_name,
e.last_name, e.salary FROM departments d, employees e
WHERE d.department_id = e.department_id
Plan hash value: 2052257371
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 106 |00:00:00.01 | 9 | | | |
|* 1 | HASH JOIN | | 1 | 1 | 106 |00:00:00.01 | 9 | 862K| 862K| 1179K (0)|
| 2 | TABLE ACCESS FULL| DEPARTMENTS | 1 | 14 | 27 |00:00:00.01 | 3 | | | |
| 3 | TABLE ACCESS FULL| EMPLOYEES | 1 | 28 | 107 |00:00:00.01 | 6 | | | |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
Join 순서 조정
- ORDERED 힌트 사용 : FROM 절의 나열된 테이블의 순서대로 조인 수행
- LEADING 힌트 사용 : 선행 테이블 지정
USER01@orcl2> SELECT /*+ ordered */
d.department_id, d.department_name, e.last_name, e.salary
FROM employees e, departments d
WHERE d.department_id = e.department_id ;
USER01@orcl2>@xplan
USER01@orcl2> SELECT /*+ ordered use_nl(d e) */
d.department_id, d.department_name, e.last_name, e.salary
FROM employees e, departments d
WHERE d.department_id = e.department_id ;
USER01@orcl2> @xplan
USER01@orcl2> SELECT /*+ ordered use_nl(e d) */
d.department_id, d.department_name, e.last_name, e.salary
FROM departments d, employees e
WHERE d.department_id = e.department_id ;
USER01@orcl2> @xplan
-- leading 힌트 10g~ leading(c a b )
USER01@orcl2> SELECT /*+ leading(e) use_nl(d) */
d.department_id, d.department_name, e.last_name, e.salary
FROM departments d, employees e
WHERE d.department_id = e.department_id ;
USER01@orcl2> @xplan
Join 관련 힌트
- USE_NL(d e) - Nested Loop Join
- USE_MERGE(d e) - Sort Merge Join
- USE_HASH(d e) - Hash Join
- ORDERED - FROM 절의 나영된 테이블 순서대로 조인 수행
- LEADING(d e) - 지시된 순서로 조인 수행
USER01@orcl2> SELECT /*+ ordered use_nl(d e) */ d.department_id, d.department_name, e.last_name, e.salary
FROM employees e, departments d
WHERE d.department_id = e.department_id ;
USER01@orcl2> SELECT /*+ leading(d) use_nl(d e) */ d.department_id, d.department_name, e.last_name, e.salary
FROM employees e, departments d
WHERE d.department_id = e.department_id ;
JOIN 연습
USER01@orcl2> show parameter statistics_level
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string ALL
USER01@orcl2> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
USER01@orcl2> create table cp_emp as select * from emp;
USER01@orcl2> create table cp_dept as select * from dept;
USER01@orcl2> select d.deptno, d.dname, e.empno, e.ename, e.sal
from cp_dept d, cp_emp e
where d.deptno = e.deptno;
USER01@orcl2> @xplan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0wyhganuksjac, child number 0
-------------------------------------
select d.deptno, d.dname, e.empno, e.ename, e.sal from cp_dept d,
cp_emp e where d.deptno = e.deptno
Plan hash value: 444267005
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 14 |00:00:00.01 | 7 | | | |
|* 1 | HASH JOIN | | 1 | 14 | 14 |00:00:00.01 | 7 | 888K| 888K| 708K (0)|
| 2 | TABLE ACCESS FULL| CP_DEPT | 1 | 4 | 4 |00:00:00.01 | 3 | | | |
| 3 | TABLE ACCESS FULL| CP_EMP | 1 | 14 | 14 |00:00:00.01 | 4 | | | |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO")
Note
-----
- dynamic sampling used for this statement (level=2)
-- 아무런 인덱스가 존재하지 않을때, hash join을 수행
-- 통계정보가 없기 때문에, dynamic sampling을 진행
USER01@orcl2> select /*+ use_nl(d e) */ d.deptno, d.dname, e.empno, e.ename, e.sal
from cp_dept d, cp_emp e
where d.deptno = e.deptno;
USER01@orcl2> @xplan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4dj3m0wazhh36, child number 0
-------------------------------------
select/*+ use_nl(d e) */ d.deptno, d.dname, e.empno, e.ename, e.sal
from cp_dept d, cp_emp e where d.deptno = e.deptno
Plan hash value: 2301270000
----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 14 |00:00:00.01 | 17 |
| 1 | NESTED LOOPS | | 1 | 14 | 14 |00:00:00.01 | 17 |
| 2 | TABLE ACCESS FULL| CP_DEPT | 1 | 4 | 4 |00:00:00.01 | 4 |
|* 3 | TABLE ACCESS FULL| CP_EMP | 4 | 4 | 14 |00:00:00.01 | 13 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("D"."DEPTNO"="E"."DEPTNO")
Note
-----
- dynamic sampling used for this statement (level=2)
USER01@orcl2> create index cp_emp_deptno on cp_emp(deptno);
Index created.
USER01@orcl2> create index cp_dept_deptno on cp_dept(deptno);
Index created.
USER01@orcl2> select d.deptno, d.dname, e.empno, e.ename, e.sal
from cp_dept d, cp_emp e
where d.deptno = e.deptno;
USER01@orcl2> @xplan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0wyhganuksjac, child number 0
-------------------------------------
select d.deptno, d.dname, e.empno, e.ename, e.sal from cp_dept d,
cp_emp e where d.deptno = e.deptno
Plan hash value: 570090882
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 14 |00:00:00.01 | 7 | | | |
| 1 | MERGE JOIN | | 1 | 14 | 14 |00:00:00.01 | 7 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| CP_EMP | 1 | 14 | 14 |00:00:00.01 | 4 | | | |
| 3 | INDEX FULL SCAN | CP_EMP_DEPTNO | 1 | 14 | 14 |00:00:00.01 | 2 | | | |
|* 4 | SORT JOIN | | 14 | 4 | 14 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)|
| 5 | TABLE ACCESS FULL | CP_DEPT | 1 | 4 | 4 |00:00:00.01 | 3 | | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
Note
-----
- dynamic sampling used for this statement (level=2)
-- 양쪽에 인덱스가 있을때, merge join을 수행
USER01@orcl2> alter session set optimizer_mode=all_rows;
Session altered.
USER01@orcl2> select /*+ leading(d) use_nl(e) */ d.deptno, d.dname, e.empno, e.ename, e.sal
from cp_dept d, cp_emp e
where d.deptno = e.deptno;
USER01@orcl2> @xplan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID brsmyayq7q7q2, child number 0
-------------------------------------
select /*+ leading(d) use_nl(e) */ d.deptno, d.dname, e.empno, e.ename,
e.sal from cp_dept d, cp_emp e where d.deptno = e.deptno
Plan hash value: 2101345790
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 14 |00:00:00.01 | 9 |
| 1 | NESTED LOOPS | | 1 | | 14 |00:00:00.01 | 9 |
| 2 | NESTED LOOPS | | 1 | 14 | 14 |00:00:00.01 | 7 |
| 3 | TABLE ACCESS FULL | CP_DEPT | 1 | 4 | 4 |00:00:00.01 | 4 |
|* 4 | INDEX RANGE SCAN | CP_EMP_DEPTNO | 4 | 5 | 14 |00:00:00.01 | 3 |
| 5 | TABLE ACCESS BY INDEX ROWID| CP_EMP | 14 | 4 | 14 |00:00:00.01 | 2 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
Note
-----
- dynamic sampling used for this statement (level=2)
USER01@orcl2> select /*+ leading(e) use_nl(d) */ d.deptno, d.dname, e.empno, e.ename, e.sal
from cp_dept d, cp_emp e
where d.deptno = e.deptno;
USER01@orcl2> @xplan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3vctux5jpnddp, child number 0
-------------------------------------
select /*+ leading(e) use_nl(d) */ d.deptno, d.dname, e.empno, e.ename,
e.sal from cp_dept d, cp_emp e where d.deptno = e.deptno
Plan hash value: 425837721
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 14 |00:00:00.01 | 10 |
| 1 | NESTED LOOPS | | 1 | | 14 |00:00:00.01 | 10 |
| 2 | NESTED LOOPS | | 1 | 14 | 14 |00:00:00.01 | 8 |
| 3 | TABLE ACCESS FULL | CP_EMP | 1 | 14 | 14 |00:00:00.01 | 4 |
|* 4 | INDEX RANGE SCAN | CP_DEPT_DEPTNO | 14 | 1 | 14 |00:00:00.01 | 4 |
| 5 | TABLE ACCESS BY INDEX ROWID| CP_DEPT | 14 | 1 | 14 |00:00:00.01 | 2 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
Note
-----
- dynamic sampling used for this statement (level=2)
USER01@orcl2> select /*+ leading(c) use_hash(s) */ count(*)
from customers c, sales s
where c.cust_id = s.cust_id;
USER01@orcl2> @xplan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bt6gqnkgfz3pn, child number 0
-------------------------------------
select /*+ leading(c) use_hash(s) */ count(*) from customers c,
sales s where c.cust_id = s.cust_id
Plan hash value: 447420574
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:07.08 | 2086 | 2070 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:07.08 | 2086 | 2070 | | | |
|* 2 | HASH JOIN | | 1 | 918K| 918K|00:00:06.00 | 2086 | 2070 | 1812K| 1381K| 2222K (0)|
| 3 | INDEX FAST FULL SCAN| CUSTS_ID_IX | 1 | 55500 | 55500 |00:00:00.08 | 123 | 117 | | | |
| 4 | INDEX FAST FULL SCAN| SALES_CUST_IX | 1 | 918K| 918K|00:00:01.31 | 1963 | 1953 | | | |
--------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C"."CUST_ID"="S"."CUST_ID")
-- hash table을 읽은게 아니라, cust_id만 읽었다 그것도 ffs로 이유는 순서가 중요하지 않은 count이기 때문
USER01@orcl2> select /*+ leading(s) use_hash(c) */ count(*)
from customers c, sales s
where c.cust_id = s.cust_id;
USER01@orcl2> @xplan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 27dyhty0brzvf, child number 0
-------------------------------------
select /*+ leading(s) use_hash(c) */ count(*) from customers c,
sales s where c.cust_id = s.cust_id
Plan hash value: 806849744
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:07.02 | 2086 | 1267 | 1260 | | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:07.02 | 2086 | 1267 | 1260 | | | | |
|* 2 | HASH JOIN | | 1 | 918K| 918K|00:00:05.95 | 2086 | 1267 | 1260 | 16M| 2762K| 29M (1)| 11264 |
| 3 | INDEX FAST FULL SCAN| SALES_CUST_IX | 1 | 918K| 918K|00:00:01.12 | 1963 | 0 | 0 | | | | |
| 4 | INDEX FAST FULL SCAN| CUSTS_ID_IX | 1 | 55500 | 55500 |00:00:00.07 | 123 | 0 | 0 | | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C"."CUST_ID"="S"."CUST_ID")
-- 메모리가 어마어마하게 쓰인다.
-- Hash Join은 대량의 조인을 수행할 때 사용되므로 가급적 Multi Block I/O를 지원하는 액세스 방법을 사용
USER01@orcl2> drop table cp_dept purge;
USER01@orcl2> drop table cp_emp purge;
USER01@orcl2> SELECT /*+ leading(d) use_hash(e) */
d.department_id, d.department_name, e.last_name, e.salary
FROM departments d, employees e
WHERE d.department_id (+) = e.department_id
AND d.location_id (+) = 1700 ;
USER01@orcl2> @xplan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0ghvbscztc5bn, child number 0
-------------------------------------
SELECT /*+ leading(d) use_hash(e) */ d.department_id,
d.department_name, e.last_name, e.salary FROM departments d,
employees e WHERE d.department_id (+) = e.department_id AND
d.location_id (+) = 1700
Plan hash value: 2185598595
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 107 |00:00:00.01 | 8 | | | |
|* 1 | HASH JOIN OUTER | | 1 | 107 | 107 |00:00:00.01 | 8 | 870K| 870K| 846K (0)|
| 2 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 4 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 4 | 21 |00:00:00.01 | 4 | | | |
|* 4 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 1 | 4 | 21 |00:00:00.01 | 2 | | | |
--------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
4 - access("D"."LOCATION_ID"=1700)
====================================================================================================
Leading 힌트를 사용했지만 DEPARTMENTS 테이블이 먼저 액세스 되지 않았다. 필터링 조건이 뛰어난 조건식이 있을 때 DEPARTMENTS 테이블이 먼저 액세스 되면 성능상 유리한 경우도 있지 않을까? 이점을 보완할 수 있도록 Oracle Database 10g 버전부터는 swap_join_inputs 힌트를 사용하여 Outer Join 에서 데이터가 없는 쪽을 Driving Table로 사용 가능하게 한다. 단, Hash Join 에서만 사용 가능하다.
====================================================================================================
USER01@orcl2> SELECT /*+ swap_join_inputs(d) leading(d) use_hash(e) */
d.department_id, d.department_name, e.last_name, e.salary
FROM departments d, employees e
WHERE d.department_id (+) = e.department_id
AND d.location_id (+) = 1700 ;
SER01@orcl2> @xplan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID cp51xzgbn56rf, child number 0
-------------------------------------
SELECT /*+ swap_join_inputs(d) leading(d) use_hash(e) */
d.department_id, d.department_name, e.last_name, e.salary FROM
departments d, employees e WHERE d.department_id (+) =
e.department_id AND d.location_id (+) = 1700
Plan hash value: 169013385
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 107 |00:00:00.01 | 8 | | | |
|* 1 | HASH JOIN RIGHT OUTER | | 1 | 107 | 107 |00:00:00.01 | 8 | 862K| 862K| 1207K (0)|
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 4 | 21 |00:00:00.01 | 2 | | | |
|* 3 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 1 | 4 | 21 |00:00:00.01 | 1 | | | |
| 4 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 6 | | | |
--------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
3 - access("D"."LOCATION_ID"=1700)
'DATABASE(oracleDB 11g) > Tuning' 카테고리의 다른 글
| [SQL Tuning]Partitioned table (0) | 2019.05.20 |
|---|---|
| [SQL Tuning]Subquery operation(서브쿼리 오퍼레이션) (0) | 2019.05.20 |
| [SQL Tuning]인덱스(Index)의 기초 (0) | 2019.05.20 |
| [SQL Tuning]인덱스를 사용하지 못하는 사례 6가지와 해결책 (0) | 2019.05.03 |
| [SQL Tuning]옵티마이저 연산자 (0) | 2019.05.03 |