1. 개요
통계 산출 쿼리 튜닝을 진행하면서,
- 서브쿼리 및 WITH 절 연산 최소화
- 기준테이블 최소화
최종적으로, 이를 위한 대용량 데이터 산출 시 성능 개선을 위해 소계(SUM OVER PARTITION)을 사용해야 했다.
이때 GROUP BY로 1차 기준 데이터를 산출하고, 이를 서브쿼리로 활용하여 기준 데이터의 소계값을 산출해야 했는데 기준데이터의 부호에 따라 합산항목에 넣거나 제외해야 하는 조건절을 구성해야 했다.
SUM OVER PARTITION BY에 의한 소계 시 조건이 필요할 경우 조치하였던 이번 경험을 기록하여 남긴다.
2. SUM 내부 조건 구성 혹은 조건 데이터를 서브쿼리화하여 활용
두가지 방안이 있다.
- 조건 자체가 하나의 항목, 하나의 데이터에 대해서만 해당할 경우, 예를 들어 A항목과 B항목을 합산하여 소계를 하는데, A항목 혹은 B항목 하나라도 부호가 -일 경우 합산에서 제외하는 조건이 있다고 하자.
이 경우 CASE WHEN / DECODE를 이용해서 A항목과 B항목에 대해서 조건을 구성해주면 되므로 간단하게 처리할 수 있다.
SUM(A+B) OVER PARTION... 이런 식으로 SUM 내부에 합산항목과 조건을 구성해줄 수 있다.
- 문제의 경우와 같이 각각의 항목을 보면 안되고, 항목의 합산 등 최종적인 산출값에 대해 소계 조건이 필요할 경우, 내부적으로 구성할 수는 없고 반드시 서브쿼리화하여 확실한 기준 데이터를 먼저 만들어 놓아야 한다.
이 경우, 예를 들어 SELECT SUM_OF_A_B FROM (SELECT SUM(A+B) AS SUM_OF_A_B FROM TABLE GROUP BY..)에서 1차적으로 SUM_OF_A_B 기준 데이터를 산출하였으며
이후에 SELECT SUM_OF_A_B, SUM(DECODE(SIGN(SUM_OF_A_B),-1,0,SUM_OF_A_B)) OVER PARTITION BY(...)).... 이런 식으로, 위의 경우 SUM_OF_A_B가 -일 경우 합산 제외(0), +일 경우 합산하는 방식으로 진행할 수 있다.
서브쿼리를 하면서 성능개선을 기대할 수 있는 상황은,
1) 기준 데이터 테이블(WITH)을 최소화할 수 있는 경우
2) 연산을 하나의 테이블 안에서 모두 진행할 수 있는 경우
이 두가지 경우로, 나의 경우 서브쿼리화 작업을 통해 10초 이상 걸렸던 수행을 0.5~6초로, 99% 이상 개선되었음을 확인하였다.
