1. 중첩된 루프
1.1 중첩된 루프를 이용한 자연 조인
sample 1)
select ename, dname
from emp, dept
where emp.deptno = dept.deptno;
- 위의 쿼리는 결과적으로 아래와 처리방식이 동일함.
sample 2)
For x in (select * from emp)
Loop
Index lookup the ROWID for X.DEPTNO
select * from dept where ROWID = that ROWID
Output joined record
End loop
- => 처리방식
- 1. EMP테이블의 첫번째 데이터를 읽는다.
- 2. 읽은 첫번째 데이터의 DEPTNO 값을 사용한다.
- 3. 찾은 DEPTNO 값을 가지고 EMP테이블을 읽는다.(ename 가져옴)
- 4. 참조된 블록을 DEPT 테이블에서 읽는다.
1.2 중첩된 루프를 이용한 외부 조인
sample 1)
select /*+ first_rows */ ename, dname
from emp, dept
where emp.deptno(+) = dept.deptno;
- 자연 조인과 비교하여 조인 순서가 바뀌었음을 유의할 것. (직접 테스트 해본 결과 조인순서는 동일하였음. 10g)
- 외부조인을 사용하면 최적화기가 사용할 수 있는 옵션이 제한되기 때문에 정말로 외부 조인이 필요한지 검토해 봐야함.(불필요한 외부조인은 사용하지 말 것)
- => 중첩된 루프 조인은 결과의 마지막 집합의 행을 얻는데는 효율이 떨어진다. (부분범위 처리일 경우 매우 효과적임)
2. 해시 조인
중첩된 루프 조인과 해시 조인의 비교
중첩된 루프 조인
해시조인
- 논리적인 I/O가 중첩된 루프 조인일 경우 36106 이던 것이 4022 밖에 사용되지 않음.
- (책에서는 0.04%로 나왔으나 직접 테스트해 본 결과 11.14% 나옴)
- 참고
2.1 해시 자연 조인
- 두개의 테이블 중 작은 것을 취하여 메모리 내에 해시 테이블을 생성한다.(T1)
- 해시함수에 적용된 조인키는 해시 테이블의 인덱스에 대응됨.
- T2로 부터 OBJECT_ID값을 취하여 해시 함수에 적용암으로써 메모리 내에서 T1의 해당 행을 찾음.
- 이런 해시 테이블은 전용 메모리에 존재하므로 일반적인 논리적인 I/O와 달리 래칭행위를 유발하지 않음.
- 어차피 아무도 이들 행을 액세스 할 수 없기 때문에 래치를 이용하여 보호할 필요가 없음.
- 첫번째 행을 얻는데는 시간이 걸리지만 모든행을 얻는데에는 효과적임.
- 해시테이블이 메모리에 모두 수용되지 않을 경우에는 TEMP 공간을 사용한다.
2.2 해시 외부 조인
- 행이 보존되지 않는 테이블 => 해시 함수를 적용.
- 행이 보존되는 테이블 => 해시 테이블 생성.
- 해시 조인은 두개의 큰 집합을 조인하거나 작은 집합을 큰 집합과 조인하는데 매우 뛰어나다.
- 첫번째 행을 얻기 까지는 시간이 소요되지만 나머지 행을 얻는데는 효율이 매우 좋다.
- (메모리 혹은 메모리와 디스크에 적재하기 때문)
3. 정렬 병합 조인
- 각 테이블을 정렬한 후 결과를 병합하는 조인.
- 두 입력 집합을 모두 스캔 한 후 정렬을 하여야 하기 때문에 해시조인보다 효율적이지 못함. (해시조인은 하나의 입력집합만 처리한 후 데이터가 출력되기 시작함)
- 일반적으로 비동등조인작업(범위비교)에 유용하다.
정렬 병합 조인과 중첩된 루프 조인과의 비교
정렬 병합 조인
중첩된 루프 조인
해시조인
- use_hash 힌트를 주었음에도 해시 조인을 하지 않고 nl 조인을 함을 볼 수 있음.
- 참고
- 정렬 병합 조인일 경우 논리적인 I/O가 14 이나 중첩된 루프 조인은 논리적인 I/O가 263으로 비동등 조인작업에서는 정렬 병합 조인이 효과적인 것을 볼 수 있음.
- (비동등조인작업에서는 해시 조인을 할 수 없음)
4. 데카르트 조인 (Cartesion Product)
- 조인되는 테이블 간의 연관성이 없는 경우 조인.
- 조인되는 테이블들의 모든 행들 끼리 조인 된다. (A테이블 행수 * B테이블행수 = 결과 행수)
- 실제로는 데카르트 조인이 포함되어 있지 않으나 경우에 따라서 데카르트 조인을 사용하도록 쿼리를 다시 쓰기도 함.
5. 반 조인 (Anti Join)
- 값이 동일하지 않은, 즉 존재하지 않는 Row를 탐색한다.
- Not Exists 와 Not In Operation이 이런 역할을 한다.
select /*+ gather_plan_statistics */
t1.c1, t1.c2
from
t1
where
t1.c1 not in (select t2.c1 from t2)
;
@stat
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------
|* 1 | FILTER | | 1 | | | 9000 |00:00:01.03 | 69483 | 36 |
| 2 | TABLE ACCESS FULL| T1 | 1 | 10000 | 11 (0)| 10000 |00:00:00.06 | 638 | 30 |
|* 3 | TABLE ACCESS FULL| T2 | 10000 | 1 | 3 (0)| 1000 |00:00:00.96 | 68845 | 6 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NULL)
3 - filter(LNNVL("T2"."C1"<>:B1))
Subquery Unesting이 이루어 지지 않는다. Filter Operation이 사용 됨.
- 왜? Anti Join은 NULL 값이 존재하지 않는다는 것이 보장될 때만 사용가능하다.
- 조건에 정확하게 IS NOT NULL을 부여하거나 Column 속성에 NOT NULL을 부여해야 한다.
select /*+ gather_plan_statistics */
t1.c1, t1.c2
from
t1
where
t1.c1 not in (select t2.c1 from t2 where t2.c1 is not null) and
t1.c1 is not null
;
@stat
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN RIGHT ANTI | | 1 | 9001 | 14 (8)| 9000 |00:00:00.07 | 645 | 1517K| 1517K| 1473K (0)|
|* 2 | INDEX FAST FULL SCAN| T2_N1 | 1 | 1000 | 2 (0)| 1000 |00:00:00.01 | 7 | | | |
|* 3 | TABLE ACCESS FULL | T1 | 1 | 10000 | 11 (0)| 10000 |00:00:00.05 | 638 | | | |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."C1"="T2"."C1")
2 - filter("T2"."C1" IS NOT NULL)
3 - filter("T1"."C1" IS NOT NULL)
IS NOT NULL 조건이 부여된 경우 Hash Anti Join이 선택되었으며 일량이 645 Block으로 개선되었다.
Table Full Scan 대신 Index Fast Full Scan이 선택되었다.(B*Tree Index가 NULL값을 저장하지 않는다는 기본적인 전제 조건 때문이다.)
-- 11.1.0.6 에서 실행
select /*+ gather_plan_statistics */
t1.c1, t1.c2
from
t1
where
t1.c1 not in (select t2.c1 from t2)
;
@stat
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN RIGHT ANTI NA| | 1 | 9000 | 15 (7)| 9000 |00:00:00.04 | 645 | 1517K| 1517K| 1514K (0)|
| 2 | TABLE ACCESS FULL | T2 | 1 | 1000 | 3 (0)| 1000 |00:00:00.01 | 7 | | | |
| 3 | TABLE ACCESS FULL | T1 | 1 | 10000 | 11 (0)| 10000 |00:00:00.03 | 638 | | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."C1"="T2"."C1")
- Oracle 11g의 Optimizer는 Null Aware Anti Join(ANTI NA)이라는 새로운 Join Operation을 추가했다.
- 말 그대로 Anti Join을 적용하되 NULL값을 인식해서 효과적으로 처리하겠다는 의미이다.