서브 쿼리와 조인
메인 쿼리와 서브 쿼리 간에는 부모와 자식이라는 종속적이고 계층적인 관계가 존재한다 서브 쿼리는 메인 쿼리에 종속되므로 단독으로 실행할 수 없으며 메인 쿼리 건수만큼 값을 받아 반복적으로 필터링하는 방식으로 실행해야 한다
스크립트
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
-- EMP 테이블 생성
CREATE TABLE EX_20_EMP (
EMP_ID NUMBER PRIMARY KEY,
EMP_NAME VARCHAR2(50)
);
-- BONUS 테이블 생성
CREATE TABLE EX_20_BONUS (
BONUS_ID NUMBER PRIMARY KEY,
EMP_ID NUMBER,
BONUS_AMOUNT NUMBER
);
-- 데이터 삽입
INSERT INTO EX_20_EMP VALUES (1, 'Kim');
INSERT INTO EX_20_EMP VALUES (2, 'Lee');
INSERT INTO EX_20_EMP VALUES (3, 'Park');
INSERT INTO EX_20_EMP VALUES (4, 'Choi');
-- BONUS: 일부 직원만 보너스를 받음
INSERT INTO EX_20_BONUS VALUES (101, 1, 1000);
INSERT INTO EX_20_BONUS VALUES (102, 3, 1500);
COMMIT;
EXPLAIN PLAN FOR
SELECT EMP_ID, EMP_NAME
FROM EX_20_EMP E
WHERE EXISTS (
SELECT /*+ no_unnest*/ 1 FROM EX_20_BONUS B
WHERE B.EMP_ID = E.EMP_ID
)
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
----------------------------------------------------------------------------------+
Plan hash value: 4255771625 |
|
----------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
----------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 4 | 160 | 6 (0)| 00:00:01 ||
|* 1 | FILTER | | | | | ||
| 2 | TABLE ACCESS FULL| EX_20_EMP | 4 | 160 | 3 (0)| 00:00:01 ||
|* 3 | TABLE ACCESS FULL| EX_20_BONUS | 1 | 13 | 3 (0)| 00:00:01 ||
----------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "EX_20_BONUS" "B" |
WHERE "B"."EMP_ID"=:B1)) |
3 - filter("B"."EMP_ID"=:B1) |
|
Note |
----- |
- dynamic statistics used: dynamic sampling (level=2) |
no_unnest 힌트
이는 옵티마이저에게 서브 쿼리를 메인 쿼리로 변환하지 말고 원래 형태로 그대로 실행을 해라 하는 뜻입니다 앞에서 보았듯이 옵티마이저 성능이 발달하면서 자연스럽게 쿼리 최적화를 하게 되는데요 지금 같은 쿼리는 no_unnest 힌트가 없다면 다음처럼 옵티마이저가 쿼리를 최적화하고 이때 실행계획도 변하게 됩니다
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
EXPLAIN PLAN FOR
SELECT DISTINCT E.EMP_ID, E.EMP_NAME
FROM EX_20_EMP E JOIN EX_20_BONUS B ON B.EMP_ID = E.EMP_ID;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
---------------------------------------------------------------------------------------------+
Plan hash value: 2585269812 |
|
---------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
---------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 2 | 106 | 6 (17)| 00:00:01 ||
| 1 | HASH UNIQUE | | 2 | 106 | 6 (17)| 00:00:01 ||
| 2 | NESTED LOOPS | | 2 | 106 | 5 (0)| 00:00:01 ||
| 3 | NESTED LOOPS | | 2 | 106 | 5 (0)| 00:00:01 ||
| 4 | TABLE ACCESS FULL | EX_20_BONUS | 2 | 26 | 3 (0)| 00:00:01 ||
|* 5 | INDEX UNIQUE SCAN | SYS_C008195 | 1 | | 0 (0)| 00:00:01 ||
| 6 | TABLE ACCESS BY INDEX ROWID| EX_20_EMP | 1 | 40 | 1 (0)| 00:00:01 ||
---------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
5 - access("B"."EMP_ID"="E"."EMP_ID") |
|
Note |
----- |
- dynamic statistics used: dynamic sampling (level=2) |
- this is an adaptive plan |
EXPLAIN PLAN FOR
SELECT EMP_ID, EMP_NAME
FROM EX_20_EMP E
WHERE EXISTS (
SELECT 1 FROM EX_20_BONUS B
WHERE B.EMP_ID = E.EMP_ID
)
PLAN_TABLE_OUTPUT |
--------------------------------------------------------------------------------------------+
Plan hash value: 3499804270 |
|
--------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
--------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 2 | 106 | 5 (20)| 00:00:01 ||
| 1 | NESTED LOOPS | | 2 | 106 | 5 (20)| 00:00:01 ||
| 2 | NESTED LOOPS | | 2 | 106 | 5 (20)| 00:00:01 ||
| 3 | SORT UNIQUE | | 2 | 26 | 3 (0)| 00:00:01 ||
| 4 | TABLE ACCESS FULL | EX_20_BONUS | 2 | 26 | 3 (0)| 00:00:01 ||
|* 5 | INDEX UNIQUE SCAN | SYS_C008195 | 1 | | 0 (0)| 00:00:01 ||
| 6 | TABLE ACCESS BY INDEX ROWID| EX_20_EMP | 1 | 40 | 1 (0)| 00:00:01 ||
--------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
5 - access("B"."EMP_ID"="E"."EMP_ID") |
|
Note |
----- |
- dynamic statistics used: dynamic sampling (level=2) |
- this is an adaptive plan |
쿼리 최적화는 이런 모양이 되고 실행계획도 변화게 됩니다 실제로 거의 비슷하게 가는 것을 볼 수 있습니다
NO_UNNEST 사용하는 이유
서브 쿼리를 JOIN으로 바꾸면 성능이 나빠지는 경우
특정 필터 구조를 유지하고 싶은 경우
EXISTS, IN 등의 서브 쿼리 최적화가 잘 안될 때 사용하게 됩니다
하지만 NO_UNNEST 힌트는 옵티마이저가 하는 자동 최적화 기회를 차단하기 때문에 항상 성능이 좋아지는 것은 아닙니다
지금 같은 쿼리에서는 FULL SCAN 을 하는 NO_UNNEST 가 장기적으로 데이터가 많아지면 불리할 수 있습니다
쿼리 최적화 UNNEST 을 하는 이유
조인 힌트를 쓸 수 있습니다 서브 쿼리가 그대로 있으면 앞에서 보았던 USE_NL , USE_HASH, MERGE 같은 조인 힌트 방식을 사용할 수 없습니다
서브 쿼리 방식은 조인되는 순서를 영향을 줄 수 없지만 최적화가 된 이후에는 조인 순서를 제어할 수 있습니다
최적화된 쿼리는 인덱스를 활용할 수 있습니다 하지만 그렇지 않은 서브 쿼리 상태에서는 인덱스를 활용할 수 없습니다