데이터베이스 질문과 답변

제가 작성한 SQL 코드를 어떻게 단순화하고 성능을 향상시킬 수 있을까요? 0 2 1,927

by DB초보 [PL/SQL] SQL PLSQL [2020.09.17 12:23:20]

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
안녕하세요 DB초보입니다.
 
인라인 뷰를 사용해서 8개의 컬럼으로 된 하나의 행만 추출하려고 합니다.
그런데, 코드의 대부분이 중복됩니다.
PL/SQL을 사용하면 될 것 같은데 PL/SQL을 잘 몰라서 어떻게 접근할지도 모르겠습니다.
 
'CARD_NUM' 컬럼과 'CARD_TRANSACTION_TIME' 컬럼에 인덱스를 생성해서 쿼리 속도가 많이 향상됐습니다.
그럼에도, 천만건의 데이터를 다루다보니 약간의 지연이 있습니다.
 
어떻게 하면 코드를 단순화하고 쿼리속도를 향상시킬 수 있을까요?
 
 
 
WITH WHOLE AS (SELECT CARD_TRANSACTION_TIME, CARD_TRANSACTION_AMOUNT, CARD_TRANSACTION_CONTENT, INDUSTRY_CODE
                FROM CARD_TRANSACTION_TEST01
                WHERE CARD_NUM IN(SELECT CARD_NUM FROM CARD_CONTRACT_TEST01 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_TEST01    
                 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;

 

by 마농 [2020.09.17 13:11:50]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
WITH whole AS
(
-- 1. 컬럼을 가공하여 조건주지 말고 -> 조건을 가공
SELECT industry_code cd
     , card_transaction_amount amt
  FROM card_transaction_test01
 WHERE card_num IN (SELECT card_num FROM card_contract_test01 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_test01
 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)
)
-- 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
;
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 3. 2단계 집계로 CASE 함수 사용 횟수 줄이기( 8천만회(천만건*8번) -> 96회(12종*8번) ) --
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 (SELECT cd
             , SUM(amt) amt
          FROM whole
         GROUP BY cd
        )
;

 

by DB초보 [2020.09.17 15:54:34]

와 정말 감사합니다..

기존 코드에서 0.054초 걸렸는데

짜주신 코드로 실행하니  0.005 초로 10배이상 속도가 빨라졌습니다.

세가지 조건을 항상 염두에 두고 SQL 짜도록 하겠습니다.

답변 정말 감사합니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입