데이터베이스 질문과 답변
피벗 쿼리 decode 와 집계함수 과다 사용으로인한 성능 이슈는 없나요?
0
1
6,567
by 지멋대루 [SQL Query] 피벗 집계함수 decode 성능이슈 [2013.07.04 00:42:25]
안녕하세요!!~
그리드에 값을 뿌리는데 세로는 항목을 가로로는 월을 출력합니다. (최대 3년)
1월 2월 3월 4월 5월 ........
항목1
항목2
항목3
.
.
.
가로, 세로 모두 사용자의 선택에 의해 범위가 변경되므로 쿼리로 피벗을 적용하였습니다.
세로의 항목수는 10건 ~ 150건 정도 되며, 가로는 보통 12개월(최대 36개월) 을 선택합니다.
피벗 함수로 만들어서 운영에 까지 반영했는데.. 현재는 전혀 성능이슈가 없습니다. 0.몇초 걸리는듯 하네요!~
데이터가 쌓여도 크게 문제가 없을듯 보였는데... 왠지 마음한구석에 찝찝함이 있어서 검색하다가 질문 드립니다.
제목처럼 DECODE 와 집계함수의 과다사용이 성능상 이슈를 야기하진 않나요?
오라클은 11g입니다.
감사합니다.
쿼리는 대략 아래와 같습니다.
(쿼리를 직접 타이핑하여서 오타가 있을 수 있습니다...)
- 아 래 -
SELECT *
FROM (SELECT DECODE(b5.CODE, '', '', MAX(b5.CODENM_KR)) AS CODE_NAME
, MAX(CASE WHEN b5.CODETYPE = 'TYPE01' AND :locale = 'KO' THEN b4.SUBSYSTEM_NAME
WHEN b5.CODETYPE = 'TYPE01' THEN b4.SUBSYSTEM_NAME_EN
WHEN b5.CODETYPE = 'TYPE02' AND :locale = 'KO' THEN b3.CDNM
WHEN b5.CODETYPE = 'TYPE02' THEN b3.ENGNM
END ) AS REFCD_NM
, MAX(b5.UNIT) AS UNIT
, MAX(DECODE(b5.CODETYPE, 'TYPE02', b5.SEQ_SYS1, b5.SEQ_SYS2)) AS SORTSEQ
, MAX(b5.SEQ_SYS2) AS SEQ_SYS2
, MAX(b5.SEQ_SYS1) AS SEQ_SYS1
<#if 조회조건이 1개년도일때만 >
, NVL(MAX(CASE WHEN b1.WKYEAR = :accstYear AND b1.WKMONTH = '01' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y0M1CNT
, NVL(MAX(CASE WHEN b1.WKYEAR = :accstYear AND b1.WKMONTH = '02' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y0M2CNT
, NVL(MAX(CASE WHEN b1.WKYEAR = :accstYear AND b1.WKMONTH = '03' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y0M3CNT
, NVL(MAX(CASE WHEN b1.WKYEAR = :accstYear AND b1.WKMONTH = '04' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y0M4CNT
, NVL(MAX(CASE WHEN b1.WKYEAR = :accstYear AND b1.WKMONTH = '05' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y0M5CNT
, NVL(MAX(CASE WHEN b1.WKYEAR = :accstYear AND b1.WKMONTH = '06' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y0M6CNT
, NVL(MAX(CASE WHEN b1.WKYEAR = :accstYear AND b1.WKMONTH = '07' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y0M7CNT
, NVL(MAX(CASE WHEN b1.WKYEAR = :accstYear AND b1.WKMONTH = '08' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y0M8CNT
, NVL(MAX(CASE WHEN b1.WKYEAR = :accstYear AND b1.WKMONTH = '09' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y0M9CNT
, NVL(MAX(CASE WHEN b1.WKYEAR = :accstYear AND b1.WKMONTH = '10' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y0M10CNT
, NVL(MAX(CASE WHEN b1.WKYEAR = :accstYear AND b1.WKMONTH = '11' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y0M11CNT
, NVL(MAX(CASE WHEN b1.WKYEAR = :accstYear AND b1.WKMONTH = '12' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y0M12CNT
<#end 조회조건이 1개년도일때만 >
<#if 조회조건이 3개년도일때만 >
, NVL(MAX(CASE WHEN b1.WKYEAR = :accstYear OR b1.WKYEAR = :accedYear THEN NULL
WHEN b1.WKMONTH = '01' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y1M1CNT
, NVL(MAX(CASE WHEN b1.WKYEAR = :accstYear OR b1.WKYEAR = :accedYear THEN NULL
WHEN b1.WKMONTH = '02' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y1M2CNT
, NVL(MAX(CASE WHEN b1.WKYEAR = :accstYear OR b1.WKYEAR = :accedYear THEN NULL
WHEN b1.WKMONTH = '03' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y1M3CNT
, NVL(MAX(CASE WHEN b1.WKYEAR = :accstYear OR b1.WKYEAR = :accedYear THEN NULL
WHEN b1.WKMONTH = '04' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y1M4CNT
, NVL(MAX(CASE WHEN b1.WKYEAR = :accstYear OR b1.WKYEAR = :accedYear THEN NULL
WHEN b1.WKMONTH = '05' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y1M5CNT
, NVL(MAX(CASE WHEN b1.WKYEAR = :accstYear OR b1.WKYEAR = :accedYear THEN NULL
WHEN b1.WKMONTH = '06' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y1M6CNT
, NVL(MAX(CASE WHEN b1.WKYEAR = :accstYear OR b1.WKYEAR = :accedYear THEN NULL
WHEN b1.WKMONTH = '07' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y1M7CNT
, NVL(MAX(CASE WHEN b1.WKYEAR = :accstYear OR b1.WKYEAR = :accedYear THEN NULL
WHEN b1.WKMONTH = '08' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y1M8CNT
, NVL(MAX(CASE WHEN b1.WKYEAR = :accstYear OR b1.WKYEAR = :accedYear THEN NULL
WHEN b1.WKMONTH = '09' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y1M9CNT
, NVL(MAX(CASE WHEN b1.WKYEAR = :accstYear OR b1.WKYEAR = :accedYear THEN NULL
WHEN b1.WKMONTH = '10' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y1M10CNT
, NVL(MAX(CASE WHEN b1.WKYEAR = :accstYear OR b1.WKYEAR = :accedYear THEN NULL
WHEN b1.WKMONTH = '11' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y1M11CNT
, NVL(MAX(CASE WHEN b1.WKYEAR = :accstYear OR b1.WKYEAR = :accedYear THEN NULL
WHEN b1.WKMONTH = '12' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y1M12CNT
<#end 조회조건이 3개년도일때만 >
<#if 조회조건이 2~3개년도일때만 >
, NVL(MAX(CASE WHEN b1.WKYEAR = :accedYear AND b1.WKMONTH = '01' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y2M1CNT
, NVL(MAX(CASE WHEN b1.WKYEAR = :accedYear AND b1.WKMONTH = '02' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y2M2CNT
, NVL(MAX(CASE WHEN b1.WKYEAR = :accedYear AND b1.WKMONTH = '03' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y2M3CNT
, NVL(MAX(CASE WHEN b1.WKYEAR = :accedYear AND b1.WKMONTH = '04' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y2M4CNT
, NVL(MAX(CASE WHEN b1.WKYEAR = :accedYear AND b1.WKMONTH = '05' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y2M5CNT
, NVL(MAX(CASE WHEN b1.WKYEAR = :accedYear AND b1.WKMONTH = '06' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y2M6CNT
, NVL(MAX(CASE WHEN b1.WKYEAR = :accedYear AND b1.WKMONTH = '07' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y2M7CNT
, NVL(MAX(CASE WHEN b1.WKYEAR = :accedYear AND b1.WKMONTH = '08' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y2M8CNT
, NVL(MAX(CASE WHEN b1.WKYEAR = :accedYear AND b1.WKMONTH = '09' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y2M9CNT
, NVL(MAX(CASE WHEN b1.WKYEAR = :accedYear AND b1.WKMONTH = '10' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y2M10CNT
, NVL(MAX(CASE WHEN b1.WKYEAR = :accedYear AND b1.WKMONTH = '11' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y2M11CNT
, NVL(MAX(CASE WHEN b1.WKYEAR = :accedYear AND b1.WKMONTH = '12' THEN DECODE(b5.UNIT, 'RT', AVG_D_CNT || '%', SUM_CNT) ELSE NULL END),0) AS Y2M12CNT
<#end 조회조건이 2~3개년도일때만 >
, NVL(SUM.......합계쿼리)
, NVL(SUM.......합계쿼리2)
, NVL(SUM.......평균쿼리)
, NVL(SUM.......평균쿼리2)
FROM( SELECT XXXX,XXX,XXX
FROM TABLES....
WHERE ....
/* 대략 DATA 수는 항목이 150개, 각 항목별 집계는 일자별로하고 월별로 SUM하여 보여줍니다. */
)
)
by 마농
[2013.07.04 08:43:38]
일단 결과가 금방 나온다 하니...
데이터가 늘어나도, 인덱스만 잘 태워 필요한 만큼만 데이터를 읽어온다면.
큰 무리는 없을 것입니다.
개선할 수 있는 여지는...
1. 조인 후 집계하는 부분은 집계후 조인하는 방법으로 개선 할 수 있구요.
=> 조인 횟수 감소
2. 한번에 집계하는 방식은 단계별로 집계하는 방식으로 개선 할 수 있습니다.
=> 함수(Decode, Case) 사용횟수 감소
3. 기타 불필요한 부분 제거
-- DECODE(b5.CODE, '', '', MAX(b5.CODENM_KR)) 이게 과연 필요한 디코드인지?
-- ELSE NULL 이부분은 불필요합니다.
데이터가 늘어나도, 인덱스만 잘 태워 필요한 만큼만 데이터를 읽어온다면.
큰 무리는 없을 것입니다.
개선할 수 있는 여지는...
1. 조인 후 집계하는 부분은 집계후 조인하는 방법으로 개선 할 수 있구요.
=> 조인 횟수 감소
2. 한번에 집계하는 방식은 단계별로 집계하는 방식으로 개선 할 수 있습니다.
=> 함수(Decode, Case) 사용횟수 감소
3. 기타 불필요한 부분 제거
-- DECODE(b5.CODE, '', '', MAX(b5.CODENM_KR)) 이게 과연 필요한 디코드인지?
-- ELSE NULL 이부분은 불필요합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.