Scalar sub query with group by need to optimize

Question

Below Query takes long time to run and thing query need to be optimize. I tries to optimize the query but i thing i needs more improvement.

Am work with scalar sub query with group by to get count of records in each area_code. So i used below query with sub query i tries with join but cant get required result.

SELECT int.AREA_CODE, mc.DESCRIPTION, (SELECT COUNT(*)
  FROM INTIMA_TB a
  JOIN REIMB_TB b
  ON a.INTIMA_KEY = b.INTIMA_KEY
  WHERE MA_TYPE  IN ('MAPAA')
  AND a.AREA_CODE = int.AREA_CODE
  ) "F_Allocate", (SELECT COUNT(*)
  FROM INTIMA_TB a
  JOIN REIMB_TB b
  ON a.INTIMA_KEY = b.INTIMA_KEY
  WHERE MA_TYPE  IN ('MAPS')
  AND a.AREA_CODE = int.AREA_CODE
  ) "F_Manual", (SELECT COUNT(*)
  FROM INTIMA_TB a
  JOIN REIMB_TB b
  ON a.INTIMA_KEY = b.INTIMA_KEY
  WHERE MA_TYPE  IN ('MAPS', 'MAPAA')
  AND a.AREA_CODE = int.AREA_CODE
  ) "F_Total", ROUND(
  (SELECT COUNT(*)
  FROM INTIMA_TB a
  JOIN REIMB_TB b
  ON a.INTIMA_KEY = b.INTIMA_KEY
  WHERE MA_TYPE  IN ('MAPAA')
  AND a.AREA_CODE = int.AREA_CODE
  )             /
  (SELECT COUNT(*)
  FROM INTIMA_TB a
  JOIN REIMB_TB b
  ON a.INTIMA_KEY = b.INTIMA_KEY
  WHERE MA_TYPE  IN ('MAPS', 'MAPAA')
  AND a.AREA_CODE = int.AREA_CODE
  ))                                          *100
  ||' %' AS "F_PER_Allocation", (SELECT COUNT(*)
  FROM INTIMA_TB a
  JOIN REIMB_TB b
  ON a.INTIMA_KEY     = b.INTIMA_KEY
  WHERE BILLING_TYPE IN ('CBFAA')
  AND a.AREA_CODE     = int.AREA_CODE
  ) "D_Allocation", (SELECT COUNT(*)
  FROM INTIMA_TB a
  JOIN REIMB_TB b
  ON a.INTIMA_KEY     = b.INTIMA_KEY
  WHERE BILLING_TYPE IN ('CBFA')
  AND a.AREA_CODE     = int.AREA_CODE
  ) "D_Manual", (SELECT COUNT(*)
  FROM INTIMA_TB a
  JOIN REIMB_TB b
  ON a.INTIMA_KEY     = b.INTIMA_KEY
  WHERE BILLING_TYPE IN ('CBFA', 'CBFAA')
  AND a.AREA_CODE     = int.AREA_CODE
  ) "D_Total", ROUND(
  (SELECT COUNT(*)
  FROM INTIMA_TB a
  JOIN REIMB_TB b
  ON a.INTIMA_KEY     = b.INTIMA_KEY
  WHERE BILLING_TYPE IN ('CBFAA')
  AND a.AREA_CODE     = int.AREA_CODE
  )             /
  (SELECT COUNT(*)
  FROM INTIMA_TB a
  JOIN REIMB_TB b
  ON a.INTIMA_KEY     = b.INTIMA_KEY
  WHERE BILLING_TYPE IN ('CBFA', 'CBFAA')
  AND a.AREA_CODE     = int.AREA_CODE
  ))*100
  ||' %' AS "D_per_Allocation"
FROM INTIMA_TB INT
JOIN REIMB_TB rem
ON int.INTIMA_KEY = rem.INTIMA_KEY
JOIN MAS_CPU_CODE mc
ON mc.CPU_KEY                 = int.AREA_CODE
WHERE TRUNC(rem.CREATED_DATE) = TRUNC(SYSDATE-3)
GROUP BY int.AREA_CODE, mc.DESCRIPTION;

Answer 1

It is my suggestion join tables directly and avoid sub query because compiler first read inner query and then outer query and you have so many so it made it so heavy.

Answer 2

All your count queries were per area code and killing going through every join for every scalar, and then each time yet again upon at final join.

What I did is a single pre-query from your consistent JOIN between the INTIMA_TB and REIMB_TB tables, but also added the final join based on the MAS_CPU_CODE within same area code. You MAY need to tweak that, but dont think so.

The inner query only looks at the records that qualify by your "MA_TYPE" or "BILLING_TYPE" since that was all that was counted against. Each individual summation (grouped by the area code) is a case/when of its respective condition. So the complete inner query will be a single row, per area code, with each of the individual aggregates.

Now, you can use each Pre-Query (PQ alias) aggregate column as the final columns in the outer query. Sample below should SIGNIFICANTLY help resolve your processing time.

select
        PQ.Area_Code,
        PQ.F_Allocate,
        PQ.F_Manual,
        PQ.F_Total,
        round( PQ.F_Allocate / PQ.F_Total ) * 100 || ' %' AS F_PER_Allocation, 
        PQ.D_Allocation,
        PQ.D_Manual,
        PQ.D_Total,
        round( PQ.D_Allocation / PQ.D_Total ) * 100 || ' %' AS D_PER_Allocation
    from
        (select
                i.area_code,
                sum( case when i.ma_type = 'MAPAA' then 1 else 0 end ) F_Allocate,
                sum( case when i.ma_type = 'MAPS'  then 1 else 0 end ) F_Manual,
                sum( case when i.ma_type in ( 'MAPAA', 'MAPS' ) then 1 else 0 end ) F_Total,
                sum( case when i.billing_type = 'CBFAA' then 1 else 0 end ) D_Allocation,
                sum( case when i.billing_type = 'CBFA' then 1 else 0 end ) D_Manual,
                sum( case when i.billing_type in ( 'CBFAA', 'CBFA' ) then 1 else 0 end ) D_Total,
            from
                INTIMA_TB i
                    JOIN REIMB_TB r
                        ON a.INTIMA_KEY = b.INTIMA_KEY
                    JOIN MAS_CPU_CODE mc
                        ON i.AREA_CODE = mc.CPU_KEY
            where
                    i.ma_type in ( 'MAPAA', 'MAPS' )
                OR  i.billing_type in ( 'CBFAA', 'CBFA' )
            group by
                i.area_code ) PQ

Only thing that may need changing is the alias reference of the "ma_type" and "billing_type" from the respective "i" or "r" alias. Your original query did not qualify the table those columns referenced, so that may need to be adjusted within each of the SUM() and WHERE clauses respectively.

Answer 3

Replace all the inner SELECTs with appropriate JOINs on your tables. In the posted query, the inner SELECTs will be executed for every row which is very heavy. Moving it out to be joined on the tables will execute the query once and then perform the correct mapping between the tables. Showing an example for one of the tables:

...
FROM INTIMA_TB INT
JOIN REIMB_TB rem
ON int.INTIMA_KEY = rem.INTIMA_KEY
JOIN MAS_CPU_CODE mc
ON mc.CPU_KEY = int.AREA_CODE
LEFT JOIN 
(SELECT COUNT(*), a.AREA_CODE 
  FROM INTIMA_TB a
  JOIN REIMB_TB b
  ON a.INTIMA_KEY = b.INTIMA_KEY
  WHERE MA_TYPE  IN ('MAPAA')
  GROUP BY a.AREA_CODE
) "F_Allocate" on F_Allocate.AREA_CODE = int.AREA_CODE
...