예제소스
https://gitlab.com/kimdongy1000/sqltuningproject
EX25 참고
인덱스를 이용한 소트 연산 생략
우리는 앞서서 인덱스는 항상 키 컬럼 순으로 정렬된 상태를 유지한다 이를 활용하면 SQL에 Order By 또는 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
EXPLAIN PLAN FOR
SELECT *
FROM Ex4_Custom
WHERE customApplyDate > '20010101'
ORDER BY customApplyDate
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
-----------------------------------------------------------------------------------+
Plan hash value: 4234916565 |
|
-----------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
-----------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 1862 | 44688 | 13 (0)| 00:00:01 ||
|* 1 | INDEX RANGE SCAN| EX4_CUSTOM_IDX | 1862 | 44688 | 13 (0)| 00:00:01 ||
-----------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - access("CUSTOMAPPLYDATE">'20010101' AND "CUSTOMAPPLYDATE" IS NOT |
NULL) |
예전 예제이다 이때 인덱스는 customApplyDate 일 때 Order 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
EXPLAIN PLAN FOR
SELECT *
FROM Ex4_Custom
WHERE customApplyDate > '20010101'
ORDER BY customApplyDate
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
---------------------------------------------------------------------------------+
Plan hash value: 3638973679 |
|
---------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
---------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 1862 | 44688 | 14 (8)| 00:00:01 ||
| 1 | SORT ORDER BY | | 1862 | 44688 | 14 (8)| 00:00:01 ||
|* 2 | TABLE ACCESS FULL| EX4_CUSTOM | 1862 | 44688 | 13 (0)| 00:00:01 ||
---------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
2 - filter("CUSTOMAPPLYDATE">'20010101') |
하지만 인덱스가 없거나 선두칼럼이 아닌 경우 소트 연산을 생략할 수 없는 것을 우리가 앞선 예제에서 본 적이 있다
부분범위처리
부분 범위 처리는 쿼리 수행 결과 중 앞쪽 일부를 우선 정송하고 멈추었다가 클라이언트가 추가 전송을 요청 (웹 화면에서는 스크롤 내리기, 페이징 처리) 할 때마다 조금씩 나눠 전송하는 방식을 말한다 하지만 클라이언트와 DB 사이 WAS AP 서버가 존재하는 3-Tier 아키텍처는 서버 리소스를 수많은 클라이언트가 공유하는 구조이므로 클라이언트가 특정 DB 커넥션을 독점할 수 없다 단위 작업을 마치면 DB 커넥션을 바로 커넥션 풀에 반환해야 하므로 그전에 쿼리 결과를 조금씩 나눠서 전송하는 방식을 사용할 수 없지만 TOP N 처리를 통해서 그 기능을 만들 수 있다
TOP N 처리
TOP N 쿼리는 전체 결과 집합 중 상위 N 개 레코드만 선택하는 쿼리이다 대표적으로 오라클 쿼리만 한번 보자 예제는 Ex25를 참고하면 되겠다
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT stock_code
,trade_datetime
,trade_count
,trade_volume
,trade_amount
,trad_row_number
FROM (
SELECT stock_code
,trade_datetime
,trade_count
,trade_volume
,trade_amount
,ROW_NUMBER() OVER(ORDER BY trade_datetime DESC) trad_row_number
FROM EX25_STOCK_TRADE
WHERE STOCK_CODE = 'KR333444'
AND trade_datetime > '20250402'
)
WHERE trad_row_number BETWEEN 1 AND 10
실제로 내가 제일 많이 사용하고 있는 TOP N 처리이다 이때 스크롤이나 다음 버튼을 누르면 trad_row_number 가 10씩 올라가서 부분 범위 처리를 하는 것이다
1
2
3
4
5
6
7
8
SELECT stock_code, trade_datetime, trade_count, trade_volume, trade_amount
FROM EX25_STOCK_TRADE
WHERE stock_code = 'KR333444'
AND trade_datetime > TO_DATE('20250402', 'YYYYMMDD')
ORDER BY trade_datetime DESC
FETCH FIRST 10 ROWS ONLY;
이것도 오라클에서 처리하는 TOP N 처리이다 이때는 처음 10줄을 보여달라는 것이고
1
2
3
4
5
6
7
8
SELECT stock_code, trade_datetime, trade_count, trade_volume, trade_amount
FROM EX25_STOCK_TRADE
WHERE stock_code = 'KR333444'
AND trade_datetime > TO_DATE('20250402', 'YYYYMMDD')
ORDER BY trade_datetime DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
이건은 21번부터 다음 10건까지 보여주는 TOP N 처리입니다 저는 주로 가독성 때문에 ROW_NUMBER 형태로 많이 사용합니다
STOPKEY
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT stock_code
,trade_datetime
,trade_count
,trade_volume
,trade_amount
,trad_row_number
FROM (
SELECT stock_code
,trade_datetime
,trade_count
,trade_volume
,trade_amount
,ROW_NUMBER() OVER(ORDER BY trade_datetime DESC) trad_row_number
FROM EX25_STOCK_TRADE
WHERE STOCK_CODE = 'KR333444'
AND trade_datetime > '20250402'
)
WHERE trad_row_number BETWEEN 1 AND 10
언뜻 보면 이 쿼리는 인라인 뷰에서 해당되는 모든 데이터를 읽은 뒤에 위 절에서 상위 10건만 보여준다고 생각한다 하지만 이를 실행계획에 넣고 돌리면 다른 결과가 나온다
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
PLAN_TABLE_OUTPUT |
-------------------------------------------------------------------------------------------------------+
Plan hash value: 3824892180 |
|
-------------------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
-------------------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 10 | 770 | 1 (0)| 00:00:01 ||
|* 1 | VIEW | | 10 | 770 | 1 (0)| 00:00:01 ||
|* 2 | WINDOW NOSORT STOPKEY | | 195 | 12480 | 1 (0)| 00:00:01 ||
| 3 | TABLE ACCESS BY INDEX ROWID | EX25_STOCK_TRADE | 195 | 12480 | 1 (0)| 00:00:01 ||
|* 4 | INDEX RANGE SCAN DESCENDING| EX25_STOCK_TRADE_IDX | 10 | | 1 (0)| 00:00:01 ||
-------------------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - filter("TRAD_ROW_NUMBER">=1 AND "TRAD_ROW_NUMBER"<=10) |
2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("TRADE_DATETIME") DESC )<=10) |
4 - access("STOCK_CODE"='KR333444' AND "TRADE_DATETIME">TO_TIMESTAMP('20250402')) |
|
Note |
----- |
- dynamic statistics used: dynamic sampling (level=2) |
PLAN_TABLE_OUTPUT |
------------------------------------------------------------------------------------------------------+
Plan hash value: 3219221853 |
|
------------------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
------------------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 195 | 12480 | 3 (0)| 00:00:01 ||
| 1 | WINDOW NOSORT | | 195 | 12480 | 3 (0)| 00:00:01 ||
| 2 | TABLE ACCESS BY INDEX ROWID | EX25_STOCK_TRADE | 195 | 12480 | 3 (0)| 00:00:01 ||
|* 3 | INDEX RANGE SCAN DESCENDING| EX25_STOCK_TRADE_IDX | 195 | | 3 (0)| 00:00:01 ||
------------------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
3 - access("STOCK_CODE"='KR333444' AND "TRADE_DATETIME">TO_TIMESTAMP('20250402')) |
|
Note |
----- |
- dynamic statistics used: dynamic sampling (level=2) |
비교를 위해서 아래 실행계획은 인라인 뷰만 실행계획을 떠본 것이다 지금 보면 성능 차이가 무려 2가 난다 STOPKEY 은 오라클 실행계획에서 TOP-N 처리 구문이 사용되었을 때 더 이상 읽지 않아도 됨 중단을 뜻합니다 그렇기에 쿼리의 성능이 올라가게 됩니다
WINDOW NOSORT STOPKEY
내가 사용한 기법은 ROW_NUMBER 을 이용한 TOP-N 이 사용되었다 이때 STOPKEY 가 사용되었지만 이때는 인덱스와 정렬하려는 순서가 일치할 때만 발생하게 된다 즉 인덱스 = 정렬하려는 칼럼이 동일한 때만 가능 그에 반해 FETCH 같은 경우는 거의 대부분 STOPKEY 가 발생하게 됩니다
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
PLAN_TABLE_OUTPUT |
-------------------------------------------------------------------------------------------------------+
Plan hash value: 3824892180 |
|
-------------------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
-------------------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 30 | 2700 | 1 (0)| 00:00:01 ||
|* 1 | VIEW | | 30 | 2700 | 1 (0)| 00:00:01 ||
|* 2 | WINDOW NOSORT STOPKEY | | 195 | 12480 | 1 (0)| 00:00:01 ||
| 3 | TABLE ACCESS BY INDEX ROWID | EX25_STOCK_TRADE | 195 | 12480 | 1 (0)| 00:00:01 ||
|* 4 | INDEX RANGE SCAN DESCENDING| EX25_STOCK_TRADE_IDX | 30 | | 1 (0)| 00:00:01 ||
-------------------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=30 AND |
"from$_subquery$_002"."rowlimit_$$_rownumber">20) |
2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("TRADE_DATETIME") DESC )<=30) |
4 - access("STOCK_CODE"='KR333444' AND "TRADE_DATETIME">TIMESTAMP' 2025-04-02 00:00:00') |
|
Note |
----- |
- dynamic statistics used: dynamic sampling (level=2) |
페이징처리
그럼 이것을 가지고 JAVA 소스코드를 오늘은 하나 만들어볼 것이다 그림 화면을 상상해 보자 종목코드로 검색을 하고 화면엔 10개의 데이터가 출력이 되고 밑에는 다음이라는 페이징 처리가 되어 있는 것을 볼 수 있다 그럼 사용자가 이 버튼을 눌렀을 때 개발자가 어떻게 코딩을 하고 SQL 을 만들어야 하는지 한번 보자
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
@GetMapping("/read")
public List<EX_25_StockTrade> read(
@RequestParam(value = "paging") int paging
)
{
Map<String, Object> params = new HashMap<>();
params.put("paging", paging);
List<EX_25_StockTrade> result = sqlSessionTemplate.selectList("read", params);
return result;
}
<select id = "read" parameterType="Map" resultType="com.example.sqltuningproject.dao.EX_25_StockTrade">
SELECT stock_code
,trade_datetime
,trade_count
,trade_volume
,trade_amount
,trad_row_number
FROM (
SELECT stock_code
,trade_datetime
,trade_count
,trade_volume
,trade_amount
,ROW_NUMBER() OVER(ORDER BY trade_count DESC) trad_row_number
FROM EX25_STOCK_TRADE
WHERE STOCK_CODE = 'KR333444'
AND trade_datetime > '20250402'
)
WHERE trad_row_number BETWEEN 10 * (#{paging} -1) + 1 AND 10 * #{paging}
</select>
컨트롤러와 쿼리는 이렇게 만들면 됩니다 그렇 post - man에서는 다음과 같이 요청하면
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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
http://localhost:8080/ex25/read?paging=1
[
{
"stock_code": "KR333444",
"trade_datetime": "2025-04-02T05:32:40.000+00:00",
"trade_count": 10,
"trade_volume": 28,
"trade_amount": 13412,
"trad_row_number": 1
},
{
"stock_code": "KR333444",
"trade_datetime": "2025-04-13T22:46:51.000+00:00",
"trade_count": 10,
"trade_volume": 84,
"trade_amount": 675696,
"trad_row_number": 2
},
{
"stock_code": "KR333444",
"trade_datetime": "2025-04-13T14:06:22.000+00:00",
"trade_count": 10,
"trade_volume": 286,
"trade_amount": 954096,
"trad_row_number": 3
},
{
"stock_code": "KR333444",
"trade_datetime": "2025-04-13T03:15:07.000+00:00",
"trade_count": 10,
"trade_volume": 477,
"trade_amount": 1922787,
"trad_row_number": 4
},
{
"stock_code": "KR333444",
"trade_datetime": "2025-04-09T18:26:54.000+00:00",
"trade_count": 10,
"trade_volume": 264,
"trade_amount": 1557600,
"trad_row_number": 5
},
{
"stock_code": "KR333444",
"trade_datetime": "2025-04-09T08:11:51.000+00:00",
"trade_count": 10,
"trade_volume": 89,
"trade_amount": 122286,
"trad_row_number": 6
},
{
"stock_code": "KR333444",
"trade_datetime": "2025-04-06T18:45:42.000+00:00",
"trade_count": 10,
"trade_volume": 718,
"trade_amount": 6285372,
"trad_row_number": 7
},
{
"stock_code": "KR333444",
"trade_datetime": "2025-04-05T15:21:26.000+00:00",
"trade_count": 10,
"trade_volume": 26,
"trade_amount": 106704,
"trad_row_number": 8
},
{
"stock_code": "KR333444",
"trade_datetime": "2025-04-03T01:22:50.000+00:00",
"trade_count": 10,
"trade_volume": 970,
"trade_amount": 3404700,
"trad_row_number": 9
},
{
"stock_code": "KR333444",
"trade_datetime": "2025-04-02T17:41:59.000+00:00",
"trade_count": 10,
"trade_volume": 924,
"trade_amount": 6110412,
"trad_row_number": 10
}
]
다음 offset 을 하게 되면
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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
http://localhost:8080/ex25/read?paging=2
[
{
"stock_code": "KR333444",
"trade_datetime": "2025-04-14T11:20:35.000+00:00",
"trade_count": 10,
"trade_volume": 478,
"trade_amount": 123324,
"trad_row_number": 11
},
{
"stock_code": "KR333444",
"trade_datetime": "2025-04-13T22:46:51.000+00:00",
"trade_count": 10,
"trade_volume": 84,
"trade_amount": 675696,
"trad_row_number": 12
},
{
"stock_code": "KR333444",
"trade_datetime": "2025-04-13T14:06:22.000+00:00",
"trade_count": 10,
"trade_volume": 286,
"trade_amount": 954096,
"trad_row_number": 13
},
{
"stock_code": "KR333444",
"trade_datetime": "2025-04-13T03:15:07.000+00:00",
"trade_count": 10,
"trade_volume": 477,
"trade_amount": 1922787,
"trad_row_number": 14
},
{
"stock_code": "KR333444",
"trade_datetime": "2025-04-09T18:26:54.000+00:00",
"trade_count": 10,
"trade_volume": 264,
"trade_amount": 1557600,
"trad_row_number": 15
},
{
"stock_code": "KR333444",
"trade_datetime": "2025-04-09T08:11:51.000+00:00",
"trade_count": 10,
"trade_volume": 89,
"trade_amount": 122286,
"trad_row_number": 16
},
{
"stock_code": "KR333444",
"trade_datetime": "2025-04-06T18:45:42.000+00:00",
"trade_count": 10,
"trade_volume": 718,
"trade_amount": 6285372,
"trad_row_number": 17
},
{
"stock_code": "KR333444",
"trade_datetime": "2025-04-05T15:21:26.000+00:00",
"trade_count": 10,
"trade_volume": 26,
"trade_amount": 106704,
"trad_row_number": 18
},
{
"stock_code": "KR333444",
"trade_datetime": "2025-04-03T01:22:50.000+00:00",
"trade_count": 10,
"trade_volume": 970,
"trade_amount": 3404700,
"trad_row_number": 19
},
{
"stock_code": "KR333444",
"trade_datetime": "2025-04-02T17:41:59.000+00:00",
"trade_count": 10,
"trade_volume": 924,
"trade_amount": 6110412,
"trad_row_number": 20
}
]
이렇게 부분범위처리를 할 수 있게 됩니다
주의할점
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT * FROM (
SELECT stock_code
,trade_datetime
,trade_count
,trade_volume
,trade_amount
,trad_row_number
FROM (
SELECT stock_code
,trade_datetime
,trade_count
,trade_volume
,trade_amount
,ROW_NUMBER() OVER(ORDER BY trade_count DESC) trad_row_number
FROM EX25_STOCK_TRADE
WHERE STOCK_CODE = 'KR333444'
AND trade_datetime > '20250402'
)
)
WHERE trad_row_number BETWEEN 1 AND 10
이 쿼리는 지금 TOP-N 쿼리일까? 결과는 동일하게 나오지만 안타깝게도 이것은 TOP-N 쿼리가 아니다 실행계획을 보자
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
PLAN_TABLE_OUTPUT |
--------------------------------------------------------------------------------------------------------------+
Plan hash value: 108204164 |
|
--------------------------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
--------------------------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 10 | 770 | 4 (25)| 00:00:01 ||
|* 1 | VIEW | | 10 | 770 | 4 (25)| 00:00:01 ||
|* 2 | WINDOW SORT PUSHED RANK | | 195 | 12480 | 4 (25)| 00:00:01 ||
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| EX25_STOCK_TRADE | 195 | 12480 | 3 (0)| 00:00:01 ||
|* 4 | INDEX RANGE SCAN | EX25_STOCK_TRADE_IDX | 195 | | 3 (0)| 00:00:01 ||
--------------------------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - filter("TRAD_ROW_NUMBER">=1 AND "TRAD_ROW_NUMBER"<=10) |
2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("TRADE_COUNT") DESC )<=10) |
4 - access("STOCK_CODE"='KR333444' AND "TRADE_DATETIME">TO_TIMESTAMP('20250402')) |
|
Note |
----- |
- dynamic statistics used: dynamic sampling (level=2) |
STOPKEY 가 없어진 것을 볼 수 있다 즉 TOP-N 을 처리할 때는 반드시 그 바로 아래에 WHERE 절을 써주어야 한다 그렇지 않고 지금처럼 쿼리를 작성하면 데이터를 모두 가져온 뒤 WHERE 절로 거르는 작업을 하게 되는 것이다 그래서 이는 보이기에는 TOP-N처럼 보여도 실행계획을 살펴보면 영락없이 거짓말을 하고 있는 것을 볼 수 있다