[Oracle] CARD_TRANSACTION INDEX 생성

CREATE INDEX IDX_CARD_TRANSACTION
ON CARD_TRANSACTION(CARD_NUM, CARD_TRANSACTION_TIME);

 

천만건의 데이터 속도가 미친듯이 빨라졌다.

 

 

 

 

		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   
		              )
		SELECT SUBQUERY1.RESULT AS "i1", SUBQUERY2.RESULT AS "i2", SUBQUERY3.RESULT AS "i3", SUBQUERY4.RESULT AS "i4",
		        SUBQUERY5.RESULT AS "i5", SUBQUERY6.RESULT AS "i6", SUBQUERY7.RESULT AS "i7", SUBQUERY8.RESULT AS "i8",
                SUBQUERY9.RESULT AS "i9", SUBQUERY10.RESULT AS "i10", SUBQUERY11.RESULT AS "i11", SUBQUERY12.RESULT AS "i12",
                SUBQUERY13.RESULT AS "i13"
		FROM (SELECT * 
		       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS RESULT
		             FROM WHOLE
		             GROUP BY INDUSTRY_CODE
		             HAVING INDUSTRY_CODE IN('I1')
		             
		             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(CARD_TRANSACTION_AMOUNT) AS RESULT
		             FROM WHOLE
		             GROUP BY INDUSTRY_CODE
		             HAVING INDUSTRY_CODE IN('I2')
		             
		             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(CARD_TRANSACTION_AMOUNT) AS RESULT
		             FROM WHOLE
		             GROUP BY INDUSTRY_CODE
		             HAVING INDUSTRY_CODE IN('I3')
		             
		             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('I4')
		             
		             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('I5')
		             
		             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('I6')
		             
		             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('I7')
		             
		             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('I8')
		             
		             UNION ALL
		             
		             -- SELECT NULL AS NUM
		             SELECT 0 AS RESULT
		             FROM DUAL)
		       WHERE RESULT IS NOT NULL OR ROWNUM = 1) SUBQUERY8,
               
               (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) SUBQUERY9,
               
               (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) SUBQUERY10,
               
               (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) SUBQUERY11,
               
               (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) SUBQUERY12,
               
               (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) SUBQUERY13
        WHERE ROWNUM = 1;