홈 SQL_튜닝_14
포스트
취소

SQL_튜닝_14

게시 2023/09/14 업데이트 2025/03/09
By
8 분읽는 시간

소트연산 생략

지난 시간과 마찬가지로 계속해서 소트 연산의 생략을 보도록 하겠습니다

java 소스예제

https://gitlab.com/kimdongy1000/sqltuningproject

이곳에서 Ex4_custom 를 찾자 아래 .sql 파일도 스키마도 마련해두었습니다 예제는 계속해서 Ex4_Custom 를 사용하겠습니다

간단한 테이블 스키마

1
2
3
4
5
6
7
8
9
10
11
12
13

DROP TABLE Ex4_Custom 

CREATE TABLE Ex4_Custom(
	
	customApplyDate varchar(8), 
	customName      varchar(11),
	customSex       varchar(2),
	customAge       number(2)
	
)

CREATE INDEX Ex4_Custom_idx ON Ex4_Custom (customApplyDate , customName , customSex , customAge);

집계함수에 따른 실행계획변화

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

EXPLAIN PLAN FOR 
SELECT MAX(A.customAge) customAge
FROM Ex4_Custom A
WHERE A.customApplyDate > '20010101'
   

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT                                                                   |
------------------------------------------------------------------------------------+
Plan hash value: 3022444818                                                         |
                                                                                    |
------------------------------------------------------------------------------------|
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     ||
------------------------------------------------------------------------------------|
|   0 | SELECT STATEMENT  |                |     1 |    12 |    11   (0)| 00:00:01 ||
|   1 |  SORT AGGREGATE   |                |     1 |    12 |            |          ||
|*  2 |   INDEX RANGE SCAN| EX4_CUSTOM_IDX |  1862 | 22344 |    11   (0)| 00:00:01 ||
------------------------------------------------------------------------------------|
                                                                                    |
Predicate Information (identified by operation id):                                 |
---------------------------------------------------                                 |
                                                                                    |
   2 - access("A"."CUSTOMAPPLYDATE">'20010101' AND "A"."CUSTOMAPPLYDATE" IS         |
              NOT NULL)                                                             |

먼저 이 쿼리를 보자 선두 컬럼이 where 절에 있으니 INDEX RANGE SCAN 이 일어나게 될 것이다 이때 SORT AGGREGATE 이 일어나는데 이는 sort(정렬) 하고 AGGREGATE(집계) 한다는 뜻이다 즉 집계하기 전에 정렬을 먼저 하고 집계 함수를 돌리는 것이다 이때는 ORDER BY를 넣거나 넣지 않더라도 앞에서 보았던 SORT ORDER BY  오퍼레이션은 발생하지 않게 됩니다 하지만 그렇다고 소팅 연산을 하지 않은 것은 아닙니다 말 그대로 정렬하고 집계라는 의미인 SORT AGGREGATE 가 들어갔기 때문에 이 부분에 대해서는 소팅 연산이 생략되지 않았습니다

SORT GROUP BY NOSORT

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

EXPLAIN PLAN FOR 
SELECT 	MAX(A.customAge) customAge,
		A.customApplyDate 
FROM Ex4_Custom A
WHERE A.customApplyDate > '20010101'
GROUP BY A.customApplyDate
ORDER BY A.customApplyDate
 
   
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT                                                                      |
---------------------------------------------------------------------------------------+
Plan hash value: 914877714                                                             |
                                                                                       |
---------------------------------------------------------------------------------------|
| Id  | Operation            | Name           | Rows  | Bytes | Cost (%CPU)| Time     ||
---------------------------------------------------------------------------------------|
|   0 | SELECT STATEMENT     |                |  1862 | 22344 |    11   (0)| 00:00:01 ||
|   1 |  SORT GROUP BY NOSORT|                |  1862 | 22344 |    11   (0)| 00:00:01 ||
|*  2 |   INDEX RANGE SCAN   | EX4_CUSTOM_IDX |  1862 | 22344 |    11   (0)| 00:00:01 ||
---------------------------------------------------------------------------------------|
                                                                                       |
Predicate Information (identified by operation id):                                    |
---------------------------------------------------                                    |
                                                                                       |
   2 - access("A"."CUSTOMAPPLYDATE">'20010101' AND "A"."CUSTOMAPPLYDATE" IS            |
              NOT NULL)                                                                |

이와 같은 경우는 SORT GROUP BY NOSORT 가 오퍼레이션에 나타나게 되는데 이는 이미 인덱스로 정렬된 상태이기 때문에 그룹화를 하기 전에 굳이 소팅을 할 필요가 없다고 판단을 하는 것입니다 그래서 마지막 ORDER BY A.customApplyDate 입력을 했음에도 이는 인덱스로 인한 정렬이 이미 발생했기 때문에 SORT ORDER BY 오퍼레이션은 발생하지 않았고 소팅 연산도 되지 않았습니다

SORT GROUP BY

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

EXPLAIN PLAN FOR 
SELECT 	MAX(A.customAge) customAge,
		A.customApplyDate,
		A.CUSTOMSEX 
FROM Ex4_Custom A
WHERE A.customApplyDate > '20010101'
GROUP BY A.customApplyDate , A.CUSTOMSEX 
ORDER BY A.customApplyDate 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT                                                                   |
------------------------------------------------------------------------------------+
Plan hash value: 1740855204                                                         |
                                                                                    |
------------------------------------------------------------------------------------|
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     ||
------------------------------------------------------------------------------------|
|   0 | SELECT STATEMENT  |                |  1862 | 26068 |    12   (9)| 00:00:01 ||
|   1 |  SORT GROUP BY    |                |  1862 | 26068 |    12   (9)| 00:00:01 ||
|*  2 |   INDEX RANGE SCAN| EX4_CUSTOM_IDX |  1862 | 26068 |    11   (0)| 00:00:01 ||
------------------------------------------------------------------------------------|
                                                                                    |
Predicate Information (identified by operation id):                                 |
---------------------------------------------------                                 |
                                                                                    |
   2 - access("A"."CUSTOMAPPLYDATE">'20010101' AND "A"."CUSTOMAPPLYDATE" IS         |
              NOT NULL)                                                             |

이번엔 좀 다른 쿼리를 보겠습니다 그룹화할 컬럼이 하나 더 늘어나게 됨에 따라서 SORT GRUP BY 오퍼레이션이 나오게 되었습니다 이는 이름에서 알 수 있다시피 그룹화를 하기 전에 정렬을 먼저 하는 것입니다 그리고 그 정렬된 데이터로부터 그룹화를 하기 때문에 SORT ORDER BY 오퍼레이션은 발생하지 않았지만 이미 내부적으로 소트 연산이 된 상태입니다

HASH GROUP BY

PLAN_TABLE_OUTPUT | ————————————————————————————+ Plan hash value: 1338086860 | | ————————————————————————————| | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time || ————————————————————————————| | 0 | SELECT STATEMENT | | 1862 | 26068 | 12 (9)| 00:00:01 || | 1 | HASH GROUP BY | | 1862 | 26068 | 12 (9)| 00:00:01 || |* 2 | INDEX RANGE SCAN| EX4_CUSTOM_IDX | 1862 | 26068 | 11 (0)| 00:00:01 || ————————————————————————————| | Predicate Information (identified by operation id): | ————————————————— | | 2 - access(“A”.”CUSTOMAPPLYDATE”>’20010101’ AND “A”.”CUSTOMAPPLYDATE” IS | NOT NULL) |

이는 그룹화할 때 정렬을 하지 않고 GROUP BY 연산을 처리하는 방법입니다 이때 명시적으로 ORDER BY를 주지 않으면 소트 연산은 일어나지 않습니다 이때 만약 그룹화 컬럼 이용된 컬럼이 아닌 것을 정렬한다고 한다면

RE - Zero

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

EXPLAIN PLAN FOR 
SELECT 	MAX(A.customAge) customAge,
		A.customApplyDate,
		A.CUSTOMSEX 
FROM Ex4_Custom A
WHERE A.customApplyDate > '20010101'
GROUP BY A.customApplyDate , A.CUSTOMSEX 
ORDER BY customAge

 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT                                                                    |
-------------------------------------------------------------------------------------+
Plan hash value: 1334559839                                                          |
                                                                                     |
-------------------------------------------------------------------------------------|
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     ||
-------------------------------------------------------------------------------------|
|   0 | SELECT STATEMENT   |                |  1862 | 26068 |    13  (16)| 00:00:01 ||
|   1 |  SORT ORDER BY     |                |  1862 | 26068 |    13  (16)| 00:00:01 ||
|   2 |   HASH GROUP BY    |                |  1862 | 26068 |    13  (16)| 00:00:01 ||
|*  3 |    INDEX RANGE SCAN| EX4_CUSTOM_IDX |  1862 | 26068 |    11   (0)| 00:00:01 ||
-------------------------------------------------------------------------------------|
                                                                                     |
Predicate Information (identified by operation id):                                  |
---------------------------------------------------                                  |
                                                                                     |
   3 - access("A"."CUSTOMAPPLYDATE">'20010101' AND "A"."CUSTOMAPPLYDATE" IS          |
              NOT NULL)                                                              |

이 경우는 앞에서와 마찬가지로 데이터 집계를 끝낸 상태에서 customAge 기준으로 정렬을 하려고 하니 SORT ORDER BY 오퍼레이션이 명시적으로 드러나게 되었습니다

우리는 13편과 14편을 통해서 소트 연산이 왜 생기는지 왜 안 생기는지 그리고 생기면 어떤 영향이 가는지에 대해서 살펴보았습니다 물론 많은 데이터를 다루어본 것은 없지만 결론은 소트 연산이 발생하면 쿼리 성능에 영향을 미친다는 것입니다.

Back-end, SQL_Tuning
DataBase - Oracle SQL_Tuning
최근 업데이트
바로가기