Nested Loops Join
선행 테이블 결정 후 후행 테이블에 Loop 돌면서 join
Physical IO(Disk→Buffer Cache→Read)가 Logical IO(Buffer Cache→Read)보다 cost가 크다
Oracle 11g부터는 NLJ Batch를 지원한다
Buffer Cache에 읽는 데이터를 먼저 읽어 NESTED LOOP를 수행하고, 없는 row id는 따로 저장해 다시 nested loop를 수행한다.
단, index된 데이터를 읽을 때 정렬이 깨진다는 단점이 있다.
select
d.deptno, d.dname, e.empno, e.ename, e.sal
from cp_dept d, cp_emp e
where d.deptno = e.deptno;
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 6 (100)| 14 |00:00:00.01 | 6 |
| 1 | NESTED LOOPS | | 1 | | | 14 |00:00:00.01 | 6 |
| 2 | NESTED LOOPS | | 1 | 14 | 6 (0)| 14 |00:00:00.01 | 5 |
| 3 | TABLE ACCESS FULL | CP_DEPT | 1 | 4 | 3 (0)| 4 |00:00:00.01 | 3 |
|* 4 | INDEX RANGE SCAN | CP_EMP_DEPTNO | 4 | 5 | 0 (0)| 14 |00:00:00.01 | 2 |
| 5 | TABLE ACCESS BY INDEX ROWID| CP_EMP | 14 | 4 | 1 (0)| 14 |00:00:00.01 | 1 |
---------------------------------------------------------------------------------------------------------------------위와 같이 Join을 한 번 수행했지만 2번의 Nested Loop를 수행한다
Sort Merge Join
정렬 시간복잡도 O(nlogn), merge 시간복잡도 O(n)이기 때문에 NLJ보다 더 빠르다.
하지만 정렬이 될때까지 부분 결과를 도출하지 못한다
-- indexes in both cp_dept and cp_emp
select
d.deptno, d.dname, e.empno, e.ename, e.sal
from cp_dept d, cp_emp e
where d.deptno = e.deptno;
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 6 (100)| 14 |00:00:00.01 | 5 | | | |
| 1 | MERGE JOIN | | 1 | 14 | 6 (17)| 14 |00:00:00.01 | 5 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| CP_EMP | 1 | 14 | 2 (0)| 14 |00:00:00.01 | 2 | | | |
| 3 | INDEX FULL SCAN | CP_EMP_DEPTNO | 1 | 14 | 1 (0)| 14 |00:00:00.01 | 1 | | | |
|* 4 | SORT JOIN | | 14 | 4 | 4 (25)| 14 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)|
| 5 | TABLE ACCESS FULL | CP_DEPT | 1 | 4 | 3 (0)| 4 |00:00:00.01 | 3 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------Index된 데이터는 이미 정렬이 되어있기 때문에 driving table은 Sort를 하지 않고 인덱스를 활용했다
(driven 테이블은 데이터가 별로 없어서 Table Access Full한 것 같다)
Hash Join
해시 테이블을 통해 join
equi join에만 적용 가능
테이블이 클 때 좋다
인덱스가 없을 때 좋다
속도가 빨라도 PGA에 hash table이 생성되기 떄문에 전체적으로 Hash Join을 쓰면 성능이 안 좋을 수 있다
select /*+ leading(c) use_hash(s)*/
count(*)
from customers c, sales s
where c.cust_id = s.cust_id;
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 533 (100)| 1 |00:00:00.26 | 1992 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.26 | 1992 | | | |
|* 2 | HASH JOIN | | 1 | 41259 | 533 (2)| 28131 |00:00:00.26 | 1992 | 1517K| 1517K| 1543K (0)|
| 3 | INDEX FULL SCAN | CUSTOMERS_IX01 | 1 | 319 | 1 (0)| 319 |00:00:00.01 | 1 | | | |
| 4 | INDEX FAST FULL SCAN| SALES_IX02 | 1 | 913K| 529 (1)| 913K|00:00:00.07 | 1991 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------
서브쿼리
서브쿼리 수행 시 Join으로 변경되기도 한다
이를 subquery unnesting이라고 한다
select *
from dept
where deptno in (select deptno from emp);
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 (100)| 3 |00:00:00.01 | 4 |
| 1 | NESTED LOOPS SEMI | | 1 | 3 | 3 (0)| 3 |00:00:00.01 | 4 |
| 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 3 (0)| 4 |00:00:00.01 | 2 |
|* 3 | INDEX RANGE SCAN | EMP_DEPTNO_IX | 4 | 9 | 0 (0)| 3 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------------Semi 조인은 Join은 하지만 Join된 결과를 출력하지는 않는다.
Complex view
group by를 포함하는 join이나 서브쿼리문이다
select d.dname, avg_sal_dept
from dept d
,(select deptno, avg(sal) avg_sal_dept from emp group by deptno) e
where d.deptno = e.deptno
and d.loc='CHICAGO'
;
@xplan
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 (100)| 1 |00:00:00.01 | 4 | | | |
| 1 | HASH GROUP BY | | 1 | 3 | 4 (25)| 1 |00:00:00.01 | 4 | 833K| 833K| 463K (0)|
| 2 | NESTED LOOPS | | 1 | | | 6 |00:00:00.01 | 4 | | | |
| 3 | NESTED LOOPS | | 1 | 5 | 3 (0)| 6 |00:00:00.01 | 3 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 2 | | | |
|* 5 | INDEX RANGE SCAN | DEPT_LOC_IX | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 1 | | | |
|* 6 | INDEX RANGE SCAN | EMP_DEPTNO_IX | 1 | 5 | 0 (0)| 6 |00:00:00.01 | 1 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID | EMP | 6 | 5 | 1 (0)| 6 |00:00:00.01 | 1 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("D"."LOC"='CHICAGO')
6 - access("D"."DEPTNO"="DEPTNO")group by를 먼저할 것 같지만, 변환하여 5번, where절을 먼저 수행한다
Subquery Factoring
With절에서 반복되는 쿼리 정의
with sum_sal as (
select deptno, sum(Sal) as sum
from emp
group by deptno
)
select *
from sum_sal
where sum >(select avg(sum) from sum_sal);
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 8 (100)| 1 |00:00:00.01 | 16 | 7 | 1 | | | |
| 1 | TEMP TABLE TRANSFORMATION | | 1 | | | 1 |00:00:00.01 | 16 | 7 | 1 | | | |
| 2 | LOAD AS SELECT | | 1 | | | 0 |00:00:00.01 | 6 | 6 | 1 | 266K| 266K| 266K (0)|
| 3 | HASH GROUP BY | | 1 | 3 | 4 (25)| 3 |00:00:00.01 | 2 | 6 | 0 | 948K| 948K| 1232K (0)|
| 4 | TABLE ACCESS FULL | EMP | 1 | 14 | 3 (0)| 14 |00:00:00.01 | 2 | 6 | 0 | | | |
|* 5 | VIEW | | 1 | 3 | 2 (0)| 1 |00:00:00.01 | 7 | 1 | 0 | | | |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6600_81582 | 1 | 3 | 2 (0)| 3 |00:00:00.01 | 5 | 1 | 0 | | | |
| 7 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 2 | 0 | 0 | | | |
| 8 | VIEW | | 1 | 3 | 2 (0)| 3 |00:00:00.01 | 2 | 0 | 0 | | | |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6600_81582 | 1 | 3 | 2 (0)| 3 |00:00:00.01 | 2 | 0 | 0 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
With 쿼리는 임시 데이터에 저장하기 때문에 물리적 IO가 증가한다. INLINE 힌트를 줄 수 있다
with sum_sal as (
select /*+ inline */ deptno, sum(Sal) as sum
from emp
group by deptno
)
select *
from sum_sal
where sum >(select avg(sum) from sum_sal);
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 (100)| 1 |00:00:00.01 | 4 | | | |
|* 1 | FILTER | | 1 | | | 1 |00:00:00.01 | 4 | | | |
| 2 | HASH GROUP BY | | 1 | 1 | 4 (25)| 3 |00:00:00.01 | 2 | 948K| 948K| 1225K (0)|
| 3 | TABLE ACCESS FULL | EMP | 1 | 14 | 3 (0)| 14 |00:00:00.01 | 2 | | | |
| 4 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 2 | | | |
| 5 | VIEW | | 1 | 3 | 4 (25)| 3 |00:00:00.01 | 2 | | | |
| 6 | SORT GROUP BY | | 1 | 3 | 4 (25)| 3 |00:00:00.01 | 2 | 2048 | 2048 | 2048 (0)|
| 7 | TABLE ACCESS FULL| EMP | 1 | 14 | 3 (0)| 14 |00:00:00.01 | 2 | | | |
--------------------------------------------------------------------------------------------------------------------------------
강의정보
멀티캠퍼스
삼성계열 기업교육 전문기업, HRD컨설팅, 러닝플랫폼, 법정의무, IT업스킬링, 외국어, MBA, 메타버스러닝
www.multicampus.com
'오라클(ORACLE) 강의' 카테고리의 다른 글
| 그룹 및 집계 함수 (0) | 2023.09.15 |
|---|---|
| Order By (0) | 2023.09.15 |
| 인덱스 (0) | 2023.09.12 |
| Optimizer / 실행 계획 (0) | 2023.09.12 |