데이터베이스 질문과 답변
by 도뎡이 [SQL Query] count case when 쿼리 성능 개선 [2023.03.13 10:50:16]
안녕하세요! DB는 큐브리드이고, 쿼리는 다음과 같습니다.
select
COUNT(CASE WHEN type = 'SU' THEN 1 END) AS SU
, COUNT(CASE WHEN type = 'CH' AND ch_type = '1' THEN 1 END) AS CH1
, COUNT(CASE WHEN type = 'CH' AND ch_type = '2' THEN 1 END) AS CH2
, COUNT(CASE WHEN type = 'EV' THEN 1 END) AS EV
, COUNT(CASE WHEN type = 'ER' THEN 1 END) AS ER
, COUNT(CASE WHEN type = 'PL' THEN 1 END) AS PL
from
tb_sample
;
type과 ch_type 모두 인덱스가 걸려있는 컬럼입니다.
조건이 포함되지 않는 상황에 어떻게 쿼리의 성능을 개선할 수 있을까요?
선배님들의 피드백 미리 감사드립니다!
1. 무리 없어 보이는 쿼리입니다.
- 실행계획 확인하셔서 인덱스 풀스캔이 정상적으로 이루어 지는지 확인하세요.
2. 혹시 type 이 위에 언급된 5개 외에 더 있지는 않은지?
- 만 약 그렇다면? WHERE 에 IN 조건을 주세요.
3. 2단계 그룹바이 집계도 고려해 보세요.
- 1단계 : GROUP BY COUNT
- 2담계 : 전체 SUM( CASE
답변 감사드립니다 선생님!
1. 인덱스 풀스캔이 아닌 단순 풀스캔을 타고 있습니다. WHERE 조건에 type IN으로 실행하면 인덱스 풀스캔을 타긴 하는데, 속도는 크게 차이가 없습니다..
2. 타입은 위에 언급된 다섯 개가 전부입니다.
3번의 GROUP BY COUNT는 dic_type으로 group by를 한 후 SUM을 하라는 말씀이신 걸까요?!
추가적으로 전체 데이터는 183만건이 있는 상황입니다. (조건이 무조건 들어가야 하지 않을까 싶긴 합니다...)
-- 오라클 기준으로 힌트 작성해 봤습니다. index fast full scan
-- 그룹바이를 2단계로 수행하여 Case 문의 수행 회수를 줄입니다.
SELECT NVL(SUM(CASE WHEN type = 'SU' THEN cnt END), 0) su
, NVL(SUM(CASE WHEN type = 'CH' AND ch_type = '1' THEN cnt END), 0) ch1
, NVL(SUM(CASE WHEN type = 'CH' AND ch_type = '2' THEN cnt END), 0) ch2
, NVL(SUM(CASE WHEN type = 'EV' THEN cnt END), 0) ev
, NVL(SUM(CASE WHEN type = 'ER' THEN cnt END), 0) er
, NVL(SUM(CASE WHEN type = 'PL' THEN cnt END), 0) pl
FROM (SELECT /*+ INDEX_FFS(tb_sample 인덱스명) */
type, ch_type
, COUNT(*) cnt
FROM tb_sample
GROUP BY type, ch_type
) a
;
선생님이 작성해주신 쿼리로 테스트해 보니, 3~4초가량 시간이 단축됐습니다.
오늘도 큰 거 하나 배워갑니다..!
항상 존경하고 감사드립니다 선생님.
좋은 한 주 보내세요 :)
혹시 결합인덱스 맞나요? 각각의 단일인덱스는 아닌지?
) 버튼을 클릭하여 작성 하시면 됩니다.