Блог

Я пытаюсь извлечь только одну строку из восьми столбцов, используя встроенные представления.

Однако существует значительное совпадение. Я не очень хорошо использую PL / SQL.

Как я могу упростить код SQL и повысить производительность запросов?

Я создал индекс в столбце ‘CARD_NUM’ и столбце ‘CARD_TRANSACTION_TIME’.
Производительность значительно улучшилась, но с 10 миллионами данных все еще существует задержка.

Я был бы признателен за вашу помощь.

 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;

  

Я упростил запрос, поскольку у вас одинаковая группа по для всех подзапросов, мы можем просто использовать условную агрегацию вместо нескольких подзапросов.

Не могли бы вы проверить, поскольку я не могу проверить синтаксическую корректность запроса из-за объектов схемы.

 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 extract(year from card_transaction_time) = 2020
     and extract(month from card_transaction_time) = 1
  union all
  select card_transaction_time, card_transaction_amount, card_transaction_content, industry_code
     from open_card_transaction_test01     
   where resi_num = '9203101937988'
     and extract(year from card_transaction_time) = 2020
     and extract(month from card_transaction_time) = 1
     and means in('cardOne', 'cardTwo')
 )
select result1 as TYPE1
     , result2 as TYPE2
     , result3 as TYPE3
     , result4 as TYPE4
     , result5 as TYPE5
     , result6 as TYPE6
     , result7 as TYPE7
     , result8 as TYPE8
from 
(
  select sum(case when industry_code in ('I1', 'I2') then card_transaction_amount else 0 end) as result1
       , sum(case when industry_code in ('I4', 'I5', 'I6') then card_transaction_amount else 0 end) as result2
       , sum(case when industry_code in ('I7', 'I8') then card_transaction_amount else 0 end) as result3
       , sum(case when industry_code in ('I9') then card_transaction_amount else 0 end) as result4
       , sum(case when industry_code in ('I10') then card_transaction_amount else 0 end) as result5
       , sum(case when industry_code in ('I11') then card_transaction_amount else 0 end) as result6
       , sum(case when industry_code in ('I12') then card_transaction_amount else 0 end) as result7
       , sum(case when industry_code in ('I13') then card_transaction_amount else 0 end) as result8
    from whole
  group by industry_code
);