[ORACLE] 인덱스 생성 전후, 쿼리 튜닝 전후 속도

인덱스 제거하고 쿼리 실행 : 3.622

인덱스 생성 후 쿼리 실행 : 0.853 (1/4 줆)

쿼리 튜닝 후 : 0.011 (1/80 줆)

 

 

 

WITH WHOLE AS
(
-- 1. 컬럼을 가공하여 조건주지 말고 -> 조건을 가공
SELECT INDUSTRY_CODE CD, CARD_TRANSACTION_AMOUNT AMT
  FROM CARD_TRANSACTION
 WHERE CARD_NUM IN (SELECT CARD_NUM FROM CARD_CONTRACT WHERE RESI_NUM = '9203101937988')
   AND CARD_TRANSACTION_TIME >= TO_DATE('2020'||'01', 'YYYYMM')
   AND CARD_TRANSACTION_TIME <  ADD_MONTHS(TO_DATE('2020'||'01', 'YYYYMM'), 1)
 UNION ALL
SELECT INDUSTRY_CODE CD, CARD_TRANSACTION_AMOUNT AMT
  FROM OPEN_CARD_TRANSACTION
 WHERE RESI_NUM = '9203101937988'
   AND MEANS IN ('신한카드', '국민카드')
   AND CARD_TRANSACTION_TIME >= TO_DATE('2020'||'01', 'YYYYMM')
   AND CARD_TRANSACTION_TIME <  ADD_MONTHS(TO_DATE('2020'||'01', 'YYYYMM'), 1)
)
-- 2. SUM(CASE 를 이용한 피벗 구현으로 테이블 읽기 횟수 줄이기(8회 -> 1회) --
SELECT NVL(SUM(CASE WHEN CD IN ('I1', 'I2'      ) THEN AMT END), 0) TYPE1
     , NVL(SUM(CASE WHEN CD IN ('I4', 'I5', 'I6') THEN AMT END), 0) TYPE2
     , NVL(SUM(CASE WHEN CD IN ('I7', 'I8'      ) THEN AMT END), 0) TYPE3
     , NVL(SUM(CASE WHEN CD IN ('I9'            ) THEN AMT END), 0) TYPE4
     , NVL(SUM(CASE WHEN CD IN ('I10'           ) THEN AMT END), 0) TYPE5
     , NVL(SUM(CASE WHEN CD IN ('I11'           ) THEN AMT END), 0) TYPE6
     , NVL(SUM(CASE WHEN CD IN ('I12'           ) THEN AMT END), 0) TYPE7
     , NVL(SUM(CASE WHEN CD IN ('I13'           ) THEN AMT END), 0) TYPE8
  FROM WHOLE
;

 

WITH WHOLE AS
(
-- 1. 컬럼을 가공하여 조건주지 말고 -> 조건을 가공
    SELECT INDUSTRY_CODE CD, CARD_TRANSACTION_AMOUNT AMT
    FROM CARD_TRANSACTION
    WHERE CARD_NUM IN (SELECT CARD_NUM FROM CARD_CONTRACT WHERE RESI_NUM = '9203101937988')
          AND CARD_TRANSACTION_TIME >= TO_DATE('2020'||'01', 'YYYYMM')
          AND CARD_TRANSACTION_TIME <  ADD_MONTHS(TO_DATE('2020'||'01', 'YYYYMM'), 1)
          
    UNION ALL
    
    SELECT INDUSTRY_CODE CD, CARD_TRANSACTION_AMOUNT AMT
    FROM OPEN_CARD_TRANSACTION
    WHERE RESI_NUM = '9203101937988'
          AND MEANS IN ('신한카드', '국민카드')
          AND CARD_TRANSACTION_TIME >= TO_DATE('2020'||'01', 'YYYYMM')
          AND CARD_TRANSACTION_TIME <  ADD_MONTHS(TO_DATE('2020'||'01', 'YYYYMM'), 1)
)
-- 2. SUM(CASE 를 이용한 피벗 구현으로 테이블 읽기 횟수 줄이기(8회 -> 1회) --
SELECT NVL(SUM(CASE WHEN CD IN ('I1', 'I2'      ) THEN AMT END), 0) TYPE1
     , NVL(SUM(CASE WHEN CD IN ('I4', 'I5', 'I6') THEN AMT END), 0) TYPE2
     , NVL(SUM(CASE WHEN CD IN ('I7', 'I8'      ) THEN AMT END), 0) TYPE3
     , NVL(SUM(CASE WHEN CD IN ('I9'            ) THEN AMT END), 0) TYPE4
     , NVL(SUM(CASE WHEN CD IN ('I10'           ) THEN AMT END), 0) TYPE5
     , NVL(SUM(CASE WHEN CD IN ('I11'           ) THEN AMT END), 0) TYPE6
     , NVL(SUM(CASE WHEN CD IN ('I12'           ) THEN AMT END), 0) TYPE7
     , NVL(SUM(CASE WHEN CD IN ('I13'           ) THEN AMT END), 0) TYPE8
  FROM WHOLE
;

 

WITH WHOLE AS
(
    SELECT INDUSTRY_CODE CD, CARD_TRANSACTION_AMOUNT AMT
    FROM CARD_TRANSACTION
    WHERE CARD_NUM IN (SELECT CARD_NUM FROM CARD_CONTRACT WHERE RESI_NUM = '9203101937988')
          AND CARD_TRANSACTION_TIME >= TO_DATE('2020'||'01', 'YYYYMM')
          AND CARD_TRANSACTION_TIME <  ADD_MONTHS(TO_DATE('2020'||'01', 'YYYYMM'), 1)
          
    UNION ALL
    
    SELECT INDUSTRY_CODE CD, CARD_TRANSACTION_AMOUNT AMT
    FROM OPEN_CARD_TRANSACTION
    WHERE RESI_NUM = '9203101937988'
          AND MEANS IN ('cardOne', 'cardTwo')
          AND CARD_TRANSACTION_TIME >= TO_DATE('2020'||'01', 'YYYYMM')
          AND CARD_TRANSACTION_TIME <  ADD_MONTHS(TO_DATE('2020'||'01', 'YYYYMM'), 1)
)

SELECT NVL(SUM(CASE WHEN CD IN ('I1', 'I2'      ) THEN AMT END), 0) TYPE1
     , NVL(SUM(CASE WHEN CD IN ('I4', 'I5', 'I6') THEN AMT END), 0) TYPE2
     , NVL(SUM(CASE WHEN CD IN ('I7', 'I8'      ) THEN AMT END), 0) TYPE3
     , NVL(SUM(CASE WHEN CD IN ('I9'            ) THEN AMT END), 0) TYPE4
     , NVL(SUM(CASE WHEN CD IN ('I10'           ) THEN AMT END), 0) TYPE5
     , NVL(SUM(CASE WHEN CD IN ('I11'           ) THEN AMT END), 0) TYPE6
     , NVL(SUM(CASE WHEN CD IN ('I12'           ) THEN AMT END), 0) TYPE7
     , NVL(SUM(CASE WHEN CD IN ('I13'           ) THEN AMT END), 0) TYPE8
  FROM WHOLE;

 

 

WITH WHOLE AS 
(
    SELECT CARD_TRANSACTION_TIME, CARD_TRANSACTION_AMOUNT, CARD_TRANSACTION_CONTENT, INDUSTRY_CODE
    FROM CARD_TRANSACTION
    WHERE CARD_NUM IN(SELECT CARD_NUM FROM CARD_CONTRACT WHERE RESI_NUM = '9203101937988')
          AND TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY') = '2020'
          AND TO_CHAR(CARD_TRANSACTION_TIME, 'MM') = '01'
           
    UNION ALL
     
    SELECT CARD_TRANSACTION_TIME, CARD_TRANSACTION_AMOUNT, CARD_TRANSACTION_CONTENT, INDUSTRY_CODE
    FROM OPEN_CARD_TRANSACTION     
    WHERE RESI_NUM = '9203101937988'
          AND TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY') = '2020'
          AND TO_CHAR(CARD_TRANSACTION_TIME, 'MM') = '01'   
          AND MEANS IN('cardOne', 'cardTwo')
)
SELECT SUBQUERY1.RESULT AS TYPE1, SUBQUERY2.RESULT AS "TYPE2", SUBQUERY3.RESULT AS "TYPE3", SUBQUERY4.RESULT AS "TYPE4",
        SUBQUERY5.RESULT AS "TYPE5", SUBQUERY6.RESULT AS "TYPE6", SUBQUERY7.RESULT AS "TYPE7", SUBQUERY8.RESULT AS "TYPE8"
FROM (SELECT * 
       FROM (SELECT SUM(RESULT) AS RESULT
             FROM(
                    SELECT SUM(CARD_TRANSACTION_AMOUNT) AS RESULT
                    FROM WHOLE
                    GROUP BY INDUSTRY_CODE
                    HAVING INDUSTRY_CODE IN('I1', 'I2')
              
             UNION ALL
              
             -- SELECT NULL AS NUM
             SELECT 0 AS RESULT
             FROM DUAL))
       WHERE RESULT IS NOT NULL OR ROWNUM = 1) SUBQUERY1,
       
(SELECT * 
       FROM (SELECT SUM(RESULT) AS RESULT
             FROM(
                    SELECT SUM(CARD_TRANSACTION_AMOUNT) AS RESULT
                    FROM WHOLE
                    GROUP BY INDUSTRY_CODE
                    HAVING INDUSTRY_CODE IN('I4', 'I5', 'I6')
              
             UNION ALL
              
             -- SELECT NULL AS NUM
             SELECT 0 AS RESULT
             FROM DUAL))
       WHERE RESULT IS NOT NULL OR ROWNUM = 1) SUBQUERY2,
       
     (SELECT * 
       FROM (SELECT SUM(RESULT) AS RESULT
             FROM(
                    SELECT SUM(CARD_TRANSACTION_AMOUNT) AS RESULT
                    FROM WHOLE
                    GROUP BY INDUSTRY_CODE
                    HAVING INDUSTRY_CODE IN('I7', 'I8')
              
             UNION ALL
              
             -- SELECT NULL AS NUM
             SELECT 0 AS RESULT
             FROM DUAL))
       WHERE RESULT IS NOT NULL OR ROWNUM = 1) SUBQUERY3,
       
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS RESULT
             FROM WHOLE
             GROUP BY INDUSTRY_CODE
             HAVING INDUSTRY_CODE IN('I9')
              
             UNION ALL
              
             -- SELECT NULL AS NUM
             SELECT 0 AS RESULT
             FROM DUAL)
       WHERE RESULT IS NOT NULL OR ROWNUM = 1) SUBQUERY4,
       
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS RESULT
             FROM WHOLE
             GROUP BY INDUSTRY_CODE
             HAVING INDUSTRY_CODE IN('I10')
              
             UNION ALL
              
             -- SELECT NULL AS NUM
             SELECT 0 AS RESULT
             FROM DUAL)
       WHERE RESULT IS NOT NULL OR ROWNUM = 1) SUBQUERY5,
       
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS RESULT
             FROM WHOLE
             GROUP BY INDUSTRY_CODE
             HAVING INDUSTRY_CODE IN('I11')
              
             UNION ALL
              
             -- SELECT NULL AS NUM
             SELECT 0 AS RESULT
             FROM DUAL)
       WHERE RESULT IS NOT NULL OR ROWNUM = 1) SUBQUERY6,
       
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS RESULT
             FROM WHOLE
             GROUP BY INDUSTRY_CODE
             HAVING INDUSTRY_CODE IN('I12')
              
             UNION ALL
              
             -- SELECT NULL AS NUM
             SELECT 0 AS RESULT
             FROM DUAL)
       WHERE RESULT IS NOT NULL OR ROWNUM = 1) SUBQUERY7,
       
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS RESULT
             FROM WHOLE
             GROUP BY INDUSTRY_CODE
             HAVING INDUSTRY_CODE IN('I13')
              
             UNION ALL
              
             -- SELECT NULL AS NUM
             SELECT 0 AS RESULT
             FROM DUAL)
       WHERE RESULT IS NOT NULL OR ROWNUM = 1) SUBQUERY8
WHERE ROWNUM <=1;