간단스키마
1
2
3
4
5
6
7
8
9
10
11
12
13
drop table Ex14_saleList
create table Ex14_SaleList(
item varchar(20),
saleMan varchar(10),
saleDate varchar(6)
)
CREATE INDEX Ex14_SaleList_idx1 ON Ex14_SaleList(saleDate , item);
Between 과 Like 의 스캔범위 비교
월별로 집계된 테이블에서 2022년 1월부터 12월 데이터를 조회하고자 할 때 흔히 아래와 같이 LIKE 연산자를 사용한다
1
2
3
4
5
SELECT *
FROM Ex14_SaleList
WHERE SALEDATE LIKE '2022%'
그럼 이 쿼리와 비교를 해보자
1
2
3
4
5
SELECT *
FROM Ex14_SaleList
WHERE SALEDATE BETWEEN '20220101' AND '20221231'
쿼리의 결과는 같은 결과가 나올 것이지만 다음 쿼리를 보자 이때 소스 데이터를 변경해서 RTX5090 비율을 10% 나머지 5080 비율을 90% 조정해서 비율을 남겼습니다 이때 다음의 각각의 실행 쿼리를 분석하겠습니다
비교
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
SELECT /*+ INDEX(Ex14_SaleList Ex14_SaleList_idx1 )*/
*
FROM Ex14_SaleList
WHERE SALEDATE LIKE '2024%'
AND ITEM = 'RTX5090'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 1 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 17 0.00 0.00 0 214 0 238
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 19 0.00 0.00 0 214 1 238
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 110
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
238 238 238 TABLE ACCESS BY INDEX ROWID BATCHED EX14_SALELIST (cr=214 pr=0 pw=0 time=517 us starts=1 cost=311 size=23864 card=1256)
238 238 238 INDEX RANGE SCAN EX14_SALELIST_IDX1 (cr=28 pr=0 pw=0 time=747 us starts=1 cost=11 size=0 card=1256)(object id 77484)
********************************************************************************
SELECT /*+ INDEX(Ex14_SaleList Ex14_SaleList_idx1 )*/
*
FROM Ex14_SaleList
WHERE SALEDATE BETWEEN '202401' AND '202412'
AND ITEM = 'RTX5090'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 17 0.00 0.00 0 213 0 238
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 19 0.00 0.00 0 213 0 238
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 110
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
238 238 238 TABLE ACCESS BY INDEX ROWID BATCHED EX14_SALELIST (cr=213 pr=0 pw=0 time=506 us starts=1 cost=311 size=23864 card=1256)
238 238 238 INDEX RANGE SCAN EX14_SALELIST_IDX1 (cr=27 pr=0 pw=0 time=1563 us starts=1 cost=11 size=0 card=1256)(object id 77484)
이 둘의 쿼리는 같은 인덱스에 같은 결과를 보이지만 트레이스 로그를 따보면 미세하게 차이가 난다 특히 날짜에 관련한 like를 쓸 때는 위와 같은 쿼리는 문제가 발생할 수 있다 왜 그런지 알아보자
LIKE , BETWEEN 의 날짜 스캔 비효율성
사실 우리는 날짜라는것이 무조건 01 이라는것을 시작하는 것과 끝이 12 라는것을 알 수 있다 그렇기에 날짜에 관련한 조건에서는 01 ~ 12 가 끝이므로 명시적으로 이를 입력해주느것이 좋지만 옵티마이저는 이런것을 직접적으로 알 수 없다 2024가 그냥 문자열인줄 알고 202401 보다 빠른 202400 이 있는지 살펴보고 202412 보다 큰 202413이 있는지 살펴보게 된다 그렇그에 결과가 동일해도 스캔하는 양 자체가 다르다 그렇기에 우리가 물리적인 시작과 끝을 알고 있으면 그것을 알 수 있게끔 명시적으로 옵티마이저에 입력해주는것이 좋다
그럼 BETWEEN 이 답인가
그렇지 않다 우리 앞에서 인덱스 액세스 조건, 인덱스 필터 조건에 대해서 배웠는데 다음을 알아보자
1
2
3
4
5
6
7
SELECT *
FROM Ex14_SaleList
WHERE SALEDATE BETWEEN '202401' AND '202412'
AND SALEMAN = 'KIM'
AND ITEM = 'RTX5080'
만약 인덱스 선두 컬럼이 SALEDATE + SALEMAN + ITEM라면 제일 앞에 있는 인덱스 선두 컬럼이 범위 조건을 취하기 때문에 인덱스 군집성에 따라서 SALEMAN , ITEM 은 모두 인덱스 필터 조건으로 들어가게 된다 즉 필수조건은 SALEDATE이고 나머지 두 개의 컬럼은 필터 조건이 되는 것이고 이는 인덱스 스캔 비용이 늘어나게 된다고 앞에서 배웠다 그렇기에 선두 컬럼에 범위 조건은 금물이다
이를 방지할려면
사실 개발을 하다 보면 LIKE 와 BETWEEN 은 버릴 수 없다 이는 옵션 조건 처리를 위해 많이 사용하는 방식 중 하나이기 때문이다 그러면 어떻게 해야 하는가? 바로 이 옵션 컬럼을 인덱스 선두 컬럼에 포함이 되어서는 안되고 필수조건이 인덱스 컬럼이 선두 컬럼에 두고 LIKE/BETWEEN 필터 조건이어도 충분히 좋은 성능을 낼 수 있다