How can I simplify the SQL below and improve performance?

Question

I'm trying to extract only one row of eight columns using inline views.

However, there is considerable overlap. I'm not good at using PL/SQL.

How can I simplify SQL code and improve the performance of queries?

I created INDEX in the 'CARD_NUM' column and the 'CARD_TRANSACTION_TIME' column.
Performance has improved a lot, but with 10 million data, there is still a delay.

I'd appreciate your help.

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;

Answer 1

I have simplified the query as you have same group by for all sub queries , we can just use conditional aggregation instead of multiple sub queries.

Could you check as I cannot check the validity of the query syntactically because of schema objects.

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
);