[오라클] 주문배송 집계정보 처리

 

해당 화면을 개발하기 위해 쿼리를 생성했다.
각 건수가 주문테이블에서 상황별로 집계하는 테이블이었다.

SELECT 
    (
        SELECT  COUNT(*)
        FROM    ORD_M A
                INNER JOIN ORD_D B ON B.ORD_ID = A.ORD_ID
        WHERE   B.ARR_DTM >= TRUNC(SYSDATE)
        AND     A.ORD_TP_CD = '100'
        AND     A.ORD_TP_DTL_CD = '110'
        AND     B.ORD_STS_CD NOT IN ('90', '91')
        AND     B.DLR_ID = '1000145054'
    ) AS GENERAL_CNT
    ,(
        SELECT  COUNT(*)
        FROM    ORD_D
        WHERE   ARR_DTM >= TRUNC(SYSDATE)
        AND     ORD_STS_CD = '60'
        AND     DLR_ID = '1000145054'
    ) AS PREPARE_CNT
    ,(
        SELECT  COUNT(*)
        FROM    ORD_M A
                INNER JOIN ORD_D B ON B.ORD_ID = A.ORD_ID
        WHERE   B.ARR_DTM >= TRUNC(SYSDATE)
        AND     A.ORD_TP_CD = '100'
        AND     A.ORD_TP_DTL_CD = '220'
        AND     B.ORD_STS_CD NOT IN ('90', '91')
        AND     B.DLR_ID = '1000145054'
    ) AS RGLR_CNT
    ,(
        SELECT  COUNT(*)
        FROM    ORD_D
        WHERE   ARR_DTM >= TRUNC(SYSDATE)
        AND     ORD_STS_CD = '70'
        AND     DLR_ID = '1000145054'
    ) AS CMPL_CNT
FROM DUAL;

문제는 속도였다. 1.5초나 걸렸다.
쿼리를 한 번에 가져온 것을 내부적으로 한 번에 처리하도록 개선했다.

WITH DLR AS (
        SELECT  B.ORD_ID, B.ORD_SEQ, A.ORD_TP_DTL_CD, B.ORD_STS_CD
        FROM    ORD_M A
                INNER JOIN ORD_D B ON B.ORD_ID = A.ORD_ID
        WHERE   B.ARR_DTM >= TRUNC(SYSDATE)
        AND     A.ORD_TP_CD = '100'
        AND     B.ORD_STS_CD NOT IN ('90', '91')
        AND     B.DLR_ID = '1000145054'
)
SELECT (SELECT COUNT(*) FROM DLR WHERE ORD_TP_DTL_CD = '110') AS GENERAL_CNT
        ,(SELECT COUNT(*) FROM DLR WHERE ORD_STS_CD = '60') AS PREPARE_CNT
        ,(SELECT COUNT(*) FROM DLR WHERE ORD_TP_DTL_CD = '110') AS RGLR_CNT
        ,(SELECT COUNT(*) FROM DLR WHERE ORD_STS_CD = '70') AS RGLR_CNT
FROM DUAL;

그리고 Count 함수 안에 case when 문을 써서 깔끔하게 개선했다.

WITH DLR AS (
        SELECT  B.ORD_ID, B.ORD_SEQ, A.ORD_TP_DTL_CD, B.ORD_STS_CD
        FROM    ORD_M A
                INNER JOIN ORD_D B ON B.ORD_ID = A.ORD_ID
        WHERE   B.ARR_DTM >= TRUNC(SYSDATE)
        AND     A.ORD_TP_CD = '100'
        AND     B.ORD_STS_CD NOT IN ('90', '91')
        AND     B.DLR_ID = '1000145054'
)
SELECT  COUNT(CASE WHEN ORD_TP_DTL_CD = '110' THEN 1 END) AS GENERAL_CNT
        ,COUNT(CASE WHEN ORD_STS_CD = '60' THEN 1 END) AS PREPARE_CNT
        ,COUNT(CASE WHEN ORD_TP_DTL_CD = '220' THEN 1 END) AS RGLR_CNT
        ,COUNT(CASE WHEN ORD_STS_CD = '70' THEN 1 END) AS RGLR_CNT
FROM DLR;

마지막으로 WITH문을 제거했다.
보기에는 편하지만, 재사용 가능성이 없으면 없애도 무방할 것 같았기 때문다.

SELECT  COUNT(CASE WHEN ORD_TP_DTL_CD = '110' THEN 1 END) AS GENERAL_CNT
        ,COUNT(CASE WHEN ORD_STS_CD = '60' THEN 1 END) AS PREPARE_CNT
        ,COUNT(CASE WHEN ORD_TP_DTL_CD = '220' THEN 1 END) AS RGLR_CNT
        ,COUNT(CASE WHEN ORD_STS_CD = '70' THEN 1 END) AS RGLR_CNT
FROM (
    SELECT  B.ORD_ID, B.ORD_SEQ, A.ORD_TP_DTL_CD, B.ORD_STS_CD
    FROM    ORD_M A
            INNER JOIN ORD_D B ON B.ORD_ID = A.ORD_ID
    WHERE   B.ARR_DTM >= TRUNC(SYSDATE)
    AND     A.ORD_TP_CD = '100'
    AND     B.ORD_STS_CD NOT IN ('90', '91')
    AND     B.DLR_ID = '1000145054'
);

결과적으로 0.4초 나온다. 비동기니깐 이정도로 갈음하다.