소트가 발생하지 않도록 SQL 을 작성
우리는 앞에서 여러 가지 Sort 오퍼레이션을 보았다 SQL 을 작성할 때 불필요한 소트가 발생하지 않아야 한다 앞에서 소트에 대한 성능 처리도 만만치 않다는 것을 보았다
Union VS Union ALL
이 둘의 차이는 근본적으로 다르다 Union 은 중복을 제거하고 결과 집합을 만드는 반면 Union ALL 은 중복제거 없이 그냥 결과 집합을 위아래로 합치기만 하는 것이다 따라서 될 수 있으면 Union All 을 사용해야 한다 만약 다음의 쿼리를 보자
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
EXPLAIN PLAN FOR
SELECT *
FROM EMP WHERE DEPTNO = 10
UNION
SELECT *
FROM EMP WHERE DEPTNO = 20
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
----------------------------------------------------------------------------+
Plan hash value: 3774834881 |
|
----------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
----------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 8 | 304 | 8 (25)| 00:00:01 ||
| 1 | SORT UNIQUE | | 8 | 304 | 8 (25)| 00:00:01 ||
| 2 | UNION-ALL | | | | | ||
|* 3 | TABLE ACCESS FULL| EMP | 3 | 114 | 3 (0)| 00:00:01 ||
|* 4 | TABLE ACCESS FULL| EMP | 5 | 190 | 3 (0)| 00:00:01 ||
----------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
3 - filter("DEPTNO"=10) |
4 - filter("DEPTNO"=20) |
EXPLAIN PLAN FOR
SELECT *
FROM EMP WHERE DEPTNO = 10
UNION ALL
SELECT *
FROM EMP WHERE DEPTNO = 20
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
---------------------------------------------------------------------------+
Plan hash value: 1301082189 |
|
---------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
---------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 8 | 304 | 6 (0)| 00:00:01 ||
| 1 | UNION-ALL | | | | | ||
|* 2 | TABLE ACCESS FULL| EMP | 3 | 114 | 3 (0)| 00:00:01 ||
|* 3 | TABLE ACCESS FULL| EMP | 5 | 190 | 3 (0)| 00:00:01 ||
---------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
2 - filter("DEPTNO"=10) |
3 - filter("DEPTNO"=20) |
우리는 동일한 두 결과 집합에 합치는 방법을 Union 과 Union All 을 사용했다 사실 두 쿼리는 절대로 중복이 있을 수 없다 만약 중복이 없는데도 지레 겁을 먹고 Union 을 사용했다면 불필요한 소트 연산을 붙여준 꼴이 되는 것이다 두 집합의 결과는 같지만 비용은 소트 연산이 들어간 union 이 비용을 더 지불한 것을 볼 수 있다 그럼 두 결과 집합이 중복이 발생할지 안 할지는 어떻게 알 수 있을까? 사실 그것은 단번에 알 수 없다 테이블의 논리적인 정보와 쿼리 작성의 경험이 쌓이면 비로소 보이게 되는 것이다 즉 어떠한 조건이 상호 배타적인 경우에는 Union All 을 사용해서 비용을 줄여야 한다
Distinct
https://gitlab.com/kimdongy1000/sqltuningproject 예제 소스 EX24 참조
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
SELECT DISTINCT
P.PRODUCT_ID ,
P.PRODUCT_NAME ,
P.PRODUCT_PRICE ,
P.PRODUCT_TYPE_CODE
FROM EX24_PRODUCT P , EX24_CONTRACT C
WHERE P.PRODUCT_TYPE_CODE = 'C03'
AND P.PRODUCT_ID = C.PRODUCT_ID
AND C.CONTRACT_DATE BETWEEN '20250101' AND '20250502'
AND C.CONTRACT_TYPE_CODE = 'R'
ORDER BY P.PRODUCT_ID
SELECT P.PRODUCT_ID ,
P.PRODUCT_NAME ,
P.PRODUCT_PRICE ,
P.PRODUCT_TYPE_CODE
FROM EX24_PRODUCT P
WHERE P.PRODUCT_TYPE_CODE = 'C03'
AND EXISTS (
SELECT 1 FROM EX24_CONTRACT C
WHERE P.PRODUCT_ID = C.PRODUCT_ID
AND C.CONTRACT_DATE BETWEEN '20250101' AND '20250502'
AND C.CONTRACT_TYPE_CODE = 'R'
)
ORDER BY P.PRODUCT_ID
이 두 개의 쿼리는 결과는 동일하게 나옵니다 왜 같은지 설명을 하겠습니다
조인 쿼리는 주어진 조건을 만족하는 여러 개의 계약 데이터가 하나의 상품에 연결될 수 있습니다. 이때 조인 결과는 상품 기준으로 중복된 행이 발생할 수 있으며, 우리는 오직 상품 단위로만 결과를 보고 싶기 때문에, SELECT DISTINCT를 사용해 상품 중복을 제거합니다.
EXISTS는 단지 조건을 만족하는 행이 존재하는지만 판단합니다. 서브 쿼리의 조건을 만족하는 행이 하나라도 존재하면 TRUE를 반환하고, 존재하지 않으면 FALSE를 반환합니다. 이 결과에 따라, TRUE 이면 해당 상품 행을 결과 셋에 포함시키고, FALSE 이면 그 상품은 제외한 채 다음 행으로 넘어가게 됩니다.
그럼 결과는 같은지 살펴보았고 성능을 같이 보자
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
PLAN_TABLE_OUTPUT |
-------------------------------------------------------------------------------------------------------------+
Plan hash value: 1109110706 |
|
-------------------------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
-------------------------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 13 | 468 | 57 (4)| 00:00:01 ||
| 1 | SORT ORDER BY | | 13 | 468 | 57 (4)| 00:00:01 ||
| 2 | HASH UNIQUE | | 13 | 468 | 56 (2)| 00:00:01 ||
|* 3 | FILTER | | | | | ||
| 4 | NESTED LOOPS SEMI | | 13 | 468 | 55 (0)| 00:00:01 ||
|* 5 | TABLE ACCESS FULL | EX24_PRODUCT | 13 | 286 | 3 (0)| 00:00:01 ||
|* 6 | TABLE ACCESS BY INDEX ROWID BATCHED| EX24_CONTRACT | 5632 | 78848 | 4 (0)| 00:00:01 ||
|* 7 | INDEX RANGE SCAN | EX24_CONTRACT_IDX | 3 | | 1 (0)| 00:00:01 ||
-------------------------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
3 - filter(TO_DATE('20250502')>=TO_DATE('20250101')) |
5 - filter("P"."PRODUCT_TYPE_CODE"='C03') |
6 - filter("C"."CONTRACT_TYPE_CODE"='R') |
7 - access("P"."PRODUCT_ID"="C"."PRODUCT_ID" AND "C"."CONTRACT_DATE">='20250101' AND |
"C"."CONTRACT_DATE"<='20250502') |
PLAN_TABLE_OUTPUT |
------------------------------------------------------------------------------------------------------------+
Plan hash value: 3461279481 |
|
------------------------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
------------------------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 13 | 468 | 56 (2)| 00:00:01 ||
| 1 | SORT ORDER BY | | 13 | 468 | 56 (2)| 00:00:01 ||
|* 2 | FILTER | | | | | ||
| 3 | NESTED LOOPS SEMI | | 13 | 468 | 55 (0)| 00:00:01 ||
|* 4 | TABLE ACCESS FULL | EX24_PRODUCT | 13 | 286 | 3 (0)| 00:00:01 ||
|* 5 | TABLE ACCESS BY INDEX ROWID BATCHED| EX24_CONTRACT | 5632 | 78848 | 4 (0)| 00:00:01 ||
|* 6 | INDEX RANGE SCAN | EX24_CONTRACT_IDX | 3 | | 1 (0)| 00:00:01 ||
------------------------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
2 - filter(TO_DATE('20250502')>=TO_DATE('20250101')) |
4 - filter("P"."PRODUCT_TYPE_CODE"='C03') |
5 - filter("C"."CONTRACT_TYPE_CODE"='R') |
6 - access("P"."PRODUCT_ID"="C"."PRODUCT_ID" AND "C"."CONTRACT_DATE">='20250101' AND |
"C"."CONTRACT_DATE"<='20250502') |
비용은 약 1차이가 발생하고 있습니다 서브 쿼리는 실제로 조건을 만족하는 데이터를 모두 읽지 않지만 위에 조인 조건은 모든 데이터를 읽고 그것을 중복 처리해야 하기 때문에 더 많은 데이터 블록을 읽어야 할 수 있습니다
조인 방식 변경으로 인한 소트 연산 생략
1
2
3
4
5
6
7
8
9
10
11
12
13
14
EXPLAIN PLAN FOR
SELECT *
FROM EX24_CONTRACT C , EX24_PRODUCT P
WHERE C.CONTRACT_TYPE_CODE = 'R'
AND C.PRODUCT_ID = P.PRODUCT_ID
ORDER BY C.CONTRACT_DATE
EXPLAIN PLAN FOR
SELECT /*+ leading(C) use_nl(P) INDEX(C ex24_contract_idx2)*/ *
FROM EX24_CONTRACT C , EX24_PRODUCT P
WHERE C.CONTRACT_TYPE_CODE = 'R'
AND C.PRODUCT_ID = P.PRODUCT_ID
ORDER BY C.CONTRACT_DATE
이 두 개의 쿼리에서 위에 쿼리는 SORT ORDER BY 가 나타난다 그러는 이유는 위의 조인은 해시 조인이기 때문이다 하지만 이를 NL 조인으로 변경하면 소트 연산을 생략할 수 있다 바로 아래처럼 말이다 이때 중요한 것은 인덱스인데 정렬 기준이 조인 키 칼럼이면 소트 머지 조인도 Sort Order By 연산을 생략할 수 있다