Skip to Main Content
  • Questions

  • Query Rewrite to improve performance.

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Ashish.

Asked: April 29, 2019 - 4:32 pm UTC

Last updated: May 10, 2019 - 8:24 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi, I have this query which takes about 45 minutes to populate the data into AFS_TABLE. The AFS_TABLE is truncated before this insert runs and it does not have any indexes or constraints. The Oracle version is 11.2.0.4. The VW_PAYMENT table is a very large IOT consisting of roughly 332 million rows. Due to the way the query is written, the same tables are visited again and again. 

INSERT INTO AFS_TABLE   
SELECT CM_CASEID CASEKEY,
       CM_CIF_ICNO ICNO,
       CM_PRODUCT PRODUCT,
       CMR_OSBAL OSBAL,
       CM_AMT_DUE DUE_AMT,
       CMR_MTHINARR MIA,
       CMR_STATUS STATUS,
       CM_PROBLEM_STATUS PROBLEM_STATUS,
       CM_STRATEGY STRATEGY,
       CM_PROFILE PROFILES,
       CMR_AKPK AKPK,
       CM_DMP_FLAG DMP,
       CMR_PDC_FLAG PDC,
       CM_DECEASED DECEASED,
       CMR_BANKRUPT_FLAG BANKRUPT,

  (SELECT MAX (PAY_DATE)
   FROM JCOLLECT.VW_PAYMENT
   WHERE PAY_SOURCE = 'SIBS'
     AND PAY_CASEKEY = CM_CASEID AND (PAY_CODE IN
            (SELECT KBT_TRAN
             FROM JCOLLECT.KBTRX
             WHERE KBT_SOURCE = 'SIBS' AND PAYMENT = 'Y'))) MAX_PAYDATE,

  (SELECT COUNT (*)
   FROM JCOLLECT.LGCASEMAST
   WHERE CM_CASEKEY = CM_CASEID
     AND CM_STATUS IN ('ACTV', 'KIV')
     AND CM_TYPE IN ('CSUT', 'FCLS', 'BOTH')) NUM_ACTV_LEGAL_CASES,

  (SELECT SUM (PAY_AMT)
   FROM JCOLLECT.VW_PAYMENT
   WHERE PAY_SOURCE = 'SIBS'
     AND PAY_CASEKEY = CM_CASEID
     AND PAY_DATE BETWEEN TRUNC (SYSDATE) - 45 AND TRUNC (SYSDATE)) SUM_PAY_BYACC,

  (SELECT SUM (PAY_AMT)
   FROM JCOLLECT.VW_PAYMENT
   WHERE PAY_SOURCE = 'SIBS'
     AND PAY_CASEKEY = CM_CASEID
     AND PAY_DATE BETWEEN
       (SELECT CSUT_NODT
        FROM JCOLLECT.JPROCESSDATA
        WHERE CASEKEY = CM_CASEID
          AND ALTKEY =
            (SELECT REC_ID
             FROM JCOLLECT.LGCASEMAST
             WHERE CM_CASEKEY = CM_CASEID
               AND CM_TYPE = 'CSUT' AND CM_STATUS = 'ACTV' AND ROWNUM <= 1)) 
 AND (SELECT CSUT_NODT_EXP_DT
        FROM JCOLLECT.JPROCESSDATA
        WHERE CASEKEY = CM_CASEID
          AND ALTKEY =
            (SELECT REC_ID
             FROM JCOLLECT.LGCASEMAST
             WHERE CM_CASEKEY = CM_CASEID
               AND CM_TYPE = 'CSUT' AND CM_STATUS = 'ACTV'
               AND ROWNUM <= 1))) SUM_PAY_NODT,

  (SELECT SUM (PAY_AMT)
   FROM JCOLLECT.VW_PAYMENT
   WHERE PAY_SOURCE = 'SIBS'
     AND PAY_CASEKEY = CM_CASEID
     AND PAY_DATE BETWEEN TRUNC (SYSDATE) - 30 AND TRUNC (SYSDATE)) SUM_PAY_BYACC30,

  (SELECT MAX (PAY_DATE)
   FROM JCOLLECT.VW_PAYMENT
   WHERE PAY_SOURCE = 'SIBS'
     AND PAY_CASEKEY = CM_CASEID
     AND PAY_CODE = '007') MAX_PAYDATE_HP_LAD
FROM JCOLLECT.JCASEMAST
WHERE CM_PRODUCT_TYPE = '03';


Here is the explain plan for this query.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                     | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                          |      1 |        |       | 98814 (100)|   1581 |00:00:00.02 |    5266 |      0 |       |       |          |
|   1 |  SORT AGGREGATE                  |                          |   1581 |      1 |    46 |            |   1580 |00:00:00.65 |   14162 |     17 |       |       |          |
|*  2 |   HASH JOIN                      |                          |   1581 |      6 |   276 |     6   (0)|  39103 |00:00:00.63 |   14162 |     17 |  1599K|  1599K| 1298K (0)|
|*  3 |    INDEX RANGE SCAN              | JPAYMENT_PAY_PKEY        |   1581 |      6 |   192 |     4   (0)|  39192 |00:00:00.25 |    7051 |     18 |       |       |          |
|*  4 |    TABLE ACCESS FULL             | KBTRX                    |   1187 |     25 |   350 |     2   (0)|  41510 |00:00:00.12 |    7116 |      0 |       |       |          |
|   5 |  SORT AGGREGATE                  |                          |   1580 |      1 |    24 |            |   1580 |00:00:00.05 |    6525 |      0 |       |       |          |
|   6 |   INLIST ITERATOR                |                          |   1580 |        |       |            |     79 |00:00:00.04 |    6525 |      0 |       |       |          |
|*  7 |    TABLE ACCESS BY INDEX ROWID   | LGCASEMAST               |   3160 |      1 |    24 |     5   (0)|     79 |00:00:00.03 |    6525 |      0 |       |       |          |
|*  8 |     INDEX RANGE SCAN             | LGCASMAST_STATUS_CASEKEY |   3160 |      1 |       |     4   (0)|     79 |00:00:00.03 |    6445 |      0 |       |       |          |
|   9 |  SORT AGGREGATE                  |                          |   1580 |      1 |    32 |            |   1580 |00:00:00.02 |    4779 |      0 |       |       |          |
|* 10 |   FILTER                         |                          |   1580 |        |       |            |    870 |00:00:00.02 |    4779 |      0 |       |       |          |
|* 11 |    INDEX RANGE SCAN              | JPAYMENT_PAY_PKEY        |   1580 |      1 |    32 |     4   (0)|    870 |00:00:00.01 |    4779 |      0 |       |       |          |
|  12 |  SORT AGGREGATE                  |                          |   1580 |      1 |    32 |            |   1580 |00:00:00.04 |    5551 |      0 |       |       |          |
|* 13 |   INDEX RANGE SCAN               | JPAYMENT_PAY_PKEY        |   1580 |      1 |    32 |     4   (0)|     14 |00:00:00.04 |    5551 |      0 |       |       |          |
|  14 |    TABLE ACCESS BY INDEX ROWID   | JPROCESSDATA             |     19 |      1 |    18 |     4   (0)|     19 |00:00:00.01 |     209 |      0 |       |       |          |
|* 15 |     INDEX RANGE SCAN             | JPROCESSDATA_CASEKEY     |     19 |      1 |       |     3   (0)|     19 |00:00:00.01 |     119 |      0 |       |       |          |
|* 16 |      COUNT STOPKEY               |                          |     19 |        |       |            |     19 |00:00:00.01 |      79 |      0 |       |       |          |
|* 17 |       TABLE ACCESS BY INDEX ROWID| LGCASEMAST               |     19 |      1 |    32 |     4   (0)|     19 |00:00:00.01 |      79 |      0 |       |       |          |
|* 18 |        INDEX RANGE SCAN          | LGCASMAST_STATUS_CASEKEY |     19 |      1 |       |     3   (0)|     19 |00:00:00.01 |      57 |      0 |       |       |          |
|  19 |    TABLE ACCESS BY INDEX ROWID   | JPROCESSDATA             |   1580 |      1 |    18 |     4   (0)|     74 |00:00:00.03 |    5284 |      0 |       |       |          |
|* 20 |     INDEX RANGE SCAN             | JPROCESSDATA_CASEKEY     |   1580 |      1 |       |     3   (0)|     74 |00:00:00.03 |    5027 |      0 |       |       |          |
|* 21 |      COUNT STOPKEY               |                          |   1580 |        |       |            |     74 |00:00:00.02 |    4878 |      0 |       |       |          |
|* 22 |       TABLE ACCESS BY INDEX ROWID| LGCASEMAST               |   1580 |      1 |    32 |     4   (0)|     74 |00:00:00.01 |    4878 |      0 |       |       |          |
|* 23 |        INDEX RANGE SCAN          | LGCASMAST_STATUS_CASEKEY |   1580 |      1 |       |     3   (0)|     74 |00:00:00.01 |    4800 |      0 |       |       |          |
|  24 |  SORT AGGREGATE                  |                          |   1580 |      1 |    32 |            |   1580 |00:00:00.02 |    4779 |      0 |       |       |          |
|* 25 |   FILTER                         |                          |   1580 |        |       |            |    568 |00:00:00.02 |    4779 |      0 |       |       |          |
|* 26 |    INDEX RANGE SCAN              | JPAYMENT_PAY_PKEY        |   1580 |      1 |    32 |     4   (0)|    568 |00:00:00.01 |    4779 |      0 |       |       |          |
|  27 |  SORT AGGREGATE                  |                          |   1580 |      1 |    32 |            |   1580 |00:00:00.03 |    5468 |      0 |       |       |          |
|* 28 |   INDEX RANGE SCAN               | JPAYMENT_PAY_PKEY        |   1580 |      1 |    32 |     4   (0)|     33 |00:00:00.03 |    5468 |      0 |       |       |          |
|* 29 |  TABLE ACCESS FULL               | JCASEMAST                |      1 |    992K|    74M| 98814   (2)|   1581 |00:00:00.02 |    5266 |      0 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("PAY_CODE"="KBT_TRAN")
   3 - access("PAY_CASEKEY"=:B1)
       filter("PAY_SOURCE"='SIBS')
   4 - filter(("KBT_SOURCE"='SIBS' AND "PAYMENT"='Y'))
   7 - filter(("CM_TYPE"='BOTH' OR "CM_TYPE"='CSUT' OR "CM_TYPE"='FCLS'))
   8 - access((("CM_STATUS"='ACTV' OR "CM_STATUS"='KIV')) AND "CM_CASEKEY"=:B1)
  10 - filter(TRUNC(SYSDATE@!)>=TRUNC(SYSDATE@!)-45)
  11 - access("PAY_CASEKEY"=:B1 AND "PAY_DATE">=TRUNC(SYSDATE@!)-45 AND "PAY_DATE"<=TRUNC(SYSDATE@!))
       filter("PAY_SOURCE"='SIBS')
  13 - access("PAY_CASEKEY"=:B1 AND "PAY_DATE">= AND "PAY_DATE"<=)
       filter("PAY_SOURCE"='SIBS')
  15 - access("CASEKEY"=:B1 AND "ALTKEY"=)
  16 - filter(ROWNUM<=1)
  17 - filter("CM_TYPE"='CSUT')
  18 - access("CM_STATUS"='ACTV' AND "CM_CASEKEY"=:B1)
  20 - access("CASEKEY"=:B1 AND "ALTKEY"=)
  21 - filter(ROWNUM<=1)
  22 - filter("CM_TYPE"='CSUT')
  23 - access("CM_STATUS"='ACTV' AND "CM_CASEKEY"=:B1)
  25 - filter(TRUNC(SYSDATE@!)>=TRUNC(SYSDATE@!)-30)
  26 - access("PAY_CASEKEY"=:B1 AND "PAY_DATE">=TRUNC(SYSDATE@!)-30 AND "PAY_DATE"<=TRUNC(SYSDATE@!))
       filter("PAY_SOURCE"='SIBS')
  28 - access("PAY_CASEKEY"=:B1 AND "PAY_CODE"='007')
       filter(("PAY_CODE"='007' AND "PAY_SOURCE"='SIBS'))
  29 - filter("CM_PRODUCT_TYPE"='03')


I tried to rewrite the query using WITH xx AS (...) SELECT... But it doesn`t seem to make a much difference. I did not run the query for long as I could see more number of similar operations and no big difference in the cost. 

WITH VPD AS ( SELECT PAY_CASEKEY, PAY_CODE, PAY_DATE, PAY_AMT FROM
JCOLLECT.VW_PAYMENT WHERE PAY_SOURCE = 'SIBS' AND PAY_CASEKEY IN(SELECT
CM_CASEID FROM JCOLLECT.JCASEMAST WHERE CM_PRODUCT_TYPE = '03')) SELECT
/*+ gather_plan_statistics */CM_CASEID CASEKEY, CM_CIF_ICNO ICNO,
CM_PRODUCT PRODUCT, CMR_OSBAL OSBAL, CM_AMT_DUE DUE_AMT, CMR_MTHINARR
MIA, CMR_STATUS STATUS, CM_PROBLEM_STATUS PROBLEM_STATUS, CM_STRATEGY
STRATEGY, CM_PROFILE PROFILES, CMR_AKPK AKPK, CM_DMP_FLAG DMP,
CMR_PDC_FLAG PDC, CM_DECEASED DECEASED, CMR_BANKRUPT_FLAG BANKRUPT,
(SELECT MAX (PAY_DATE) FROM VPD WHERE PAY_CASEKEY = CM_CASEID AND
(PAY_CODE IN (SELECT KBT_TRAN FROM JCOLLECT.KBTRX WHERE KBT_SOURCE =
'SIBS' AND PAYMENT = 'Y'))) MAX_PAYDATE, (SELECT COUNT (*) FROM
JCOLLECT.LGCASEMAST WHERE CM_CASEKEY = CM_CASEID AND CM_STATUS IN
('ACTV', 'KIV') AND CM_TYPE IN ('CSUT', 'FCLS', 'BOTH'))
NUM_ACTV_LEGAL_CASES, (SELECT SUM (PAY_AMT) FROM VPD WHERE PAY_CASEKEY
= CM_CASEID AND PAY_DATE BETWEEN TRUNC (SYSDATE) - 45 AND T


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                       | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                            |      1 |        |   289K(100)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|   1 |  SORT AGGREGATE                  |                            |      0 |      1 |            |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|*  2 |   HASH JOIN                      |                            |      0 |   2449K|  6210   (3)|      0 |00:00:00.01 |       0 |      0 |  1368K|  1368K|          |
|*  3 |    TABLE ACCESS FULL             | KBTRX                      |      0 |     25 |     2   (0)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|*  4 |    VIEW                          |                            |      0 |   5554K|  6163   (2)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|   5 |     TABLE ACCESS FULL            | SYS_TEMP_0FD9D6611_A234A94 |      0 |   5554K|  6163   (2)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|   6 |  SORT AGGREGATE                  |                            |      0 |      1 |            |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|   7 |   INLIST ITERATOR                |                            |      0 |        |            |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|*  8 |    TABLE ACCESS BY INDEX ROWID   | LGCASEMAST                 |      0 |      1 |     5   (0)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|*  9 |     INDEX RANGE SCAN             | LGCASMAST_STATUS_CASEKEY   |      0 |      1 |     4   (0)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  10 |  SORT AGGREGATE                  |                            |      0 |      1 |            |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 11 |   FILTER                         |                            |      0 |        |            |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 12 |    VIEW                          |                            |      0 |   5554K|  6163   (2)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  13 |     TABLE ACCESS FULL            | SYS_TEMP_0FD9D6611_A234A94 |      0 |   5554K|  6163   (2)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  14 |  SORT AGGREGATE                  |                            |      0 |      1 |            |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 15 |   FILTER                         |                            |      0 |        |            |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 16 |    VIEW                          |                            |      0 |   5554K|  6163   (2)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  17 |     TABLE ACCESS FULL            | SYS_TEMP_0FD9D6611_A234A94 |      0 |   5554K|  6163   (2)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  18 |    TABLE ACCESS BY INDEX ROWID   | JPROCESSDATA               |      0 |      1 |     4   (0)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 19 |     INDEX RANGE SCAN             | JPROCESSDATA_CASEKEY       |      0 |      1 |     3   (0)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 20 |      COUNT STOPKEY               |                            |      0 |        |            |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 21 |       TABLE ACCESS BY INDEX ROWID| LGCASEMAST                 |      0 |      1 |     4   (0)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 22 |        INDEX RANGE SCAN          | LGCASMAST_STATUS_CASEKEY   |      0 |      1 |     3   (0)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  23 |    TABLE ACCESS BY INDEX ROWID   | JPROCESSDATA               |      0 |      1 |     4   (0)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 24 |     INDEX RANGE SCAN             | JPROCESSDATA_CASEKEY       |      0 |      1 |     3   (0)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 25 |      COUNT STOPKEY               |                            |      0 |        |            |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 26 |       TABLE ACCESS BY INDEX ROWID| LGCASEMAST                 |      0 |      1 |     4   (0)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 27 |        INDEX RANGE SCAN          | LGCASMAST_STATUS_CASEKEY   |      0 |      1 |     3   (0)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  28 |  SORT AGGREGATE                  |                            |      0 |      1 |            |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 29 |   FILTER                         |                            |      0 |        |            |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 30 |    VIEW                          |                            |      0 |   5554K|  6163   (2)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  31 |     TABLE ACCESS FULL            | SYS_TEMP_0FD9D6611_A234A94 |      0 |   5554K|  6163   (2)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  32 |  SORT AGGREGATE                  |                            |      0 |      1 |            |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 33 |   VIEW                           |                            |      0 |   5554K|  6163   (2)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  34 |    TABLE ACCESS FULL             | SYS_TEMP_0FD9D6611_A234A94 |      0 |   5554K|  6163   (2)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  35 |  TEMP TABLE TRANSFORMATION       |                            |      1 |        |            |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  36 |   LOAD AS SELECT                 |                            |      1 |        |            |      0 |00:00:00.01 |       0 |      0 |   256K|   256K|  530K (0)|
|* 37 |    HASH JOIN                     |                            |      1 |   5554K|   190K  (2)|   5926K|00:00:18.50 |     302K|   5597 |    51M|  8094K|   53M (0)|
|* 38 |     INDEX FAST FULL SCAN         | JCASEMAST_PROD_PLACE_IDX   |      1 |    968K|  4274   (2)|    970K|00:00:00.43 |   28225 |      0 |       |       |          |
|* 39 |     INDEX FAST FULL SCAN         | JPAYMENT_PAY_PKEY          |      1 |     20M|   170K  (1)|     13M|00:00:07.48 |     274K|   5597 |       |       |          |
|* 40 |   TABLE ACCESS FULL              | JCASEMAST                  |      0 |    968K| 98793   (2)|      0 |00:00:00.01 |       0 |      0 |       |       |          |
----------------------------------------------------------------------------------

The A-rows in this plan are more than E-rows so I did gather stats and ran the query but makes no difference.

So seek your help on how can i rewrite this query to improve performance.

Thanks & Best Regards!
-a



and Chris said...

The database executes your scalar subqueries once for each row returned from the main table. You can verify this by seeing that the starts column value matches the A-rows from JCASEMAST (1,580, give or take a row).

Using the with clause isn't helping you because:

- The database still executes the subqueries once for each row from JCASEMAST
- This subquery processes a huge number of rows (5.9 million coming out of the hash join) and takes ~18 minutes to execute!

Instead join JCOLLECT.VW_PAYMENT in the main from clause:

FROM  JCOLLECT.JCASEMAST
JOIN  JCOLLECT.VW_PAYMENT
ON    PAY_CASEKEY = CM_CASEID
WHERE CM_PRODUCT_TYPE = '03'
AND   PAY_SOURCE = 'SIBS';


You'll then need to group by the columns you're selecting from JCASEMAST. And filter the values aggregating inside the sum/count.

For example, replace the following subquery:

  (SELECT MAX (PAY_DATE)
   FROM JCOLLECT.VW_PAYMENT
   WHERE PAY_SOURCE = 'SIBS'
     AND PAY_CASEKEY = CM_CASEID
     AND PAY_CODE = '007')


With on this max (i.e. remove the subquery, just have max with the filter):

   max ( 
     case 
       when PAY_CODE = '007' 
       then pay_date 
     end 
   )


Once you've done that, see how it affects performance.

Let us know how you get on.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

David, April 30, 2019 - 2:44 pm UTC


Hello TOM,

I see in both execution plans big big differences between E-ROWS (Estimated rows) and A-ROWS (Actual rows). I think that statistics must be gathered : the CBO has wrong datas.

Second, A-rows is 0 for almost all the lines in the second plan, it is very strange, no?

David D. from Paris

Chris Saxon
April 30, 2019 - 3:03 pm UTC

My guess: these figures are from incomplete queries.

But yes, if the A-rows figures in the first plan represent the complete query, then that would suggest the stats are out-of-date and need gathering. I doubt this will have much effect on the query as-is though.

Ashish, May 06, 2019 - 1:33 am UTC

Yes, it was a cancelled query. The complete execution of the query takes about 45 minutes and I cancelled the query half way. :-)


I ran the same query in another enviornment with a much smaller data and this is the execution plan.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                           | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                  |                                |      1 |        | 88570 (100)|      0 |00:01:47.08 |      17M|    566K|  10841 |         |       |          |
|   1 |  LOAD AS SELECT                   |                                |      1 |        |            |      0 |00:01:47.08 |      17M|    566K|  10841 |     530K|   530K|  530K (0)|
|   2 |   SORT AGGREGATE                  |                                |    944K|      1 |            |    944K|00:00:28.38 |    2574K|      0 |      0 |         |       |          |
|   3 |    NESTED LOOPS                   |                                |    944K|      1 |     5   (0)|   1585K|00:00:25.33 |    2574K|      0 |      0 |         |       |          |
|   4 |     NESTED LOOPS                  |                                |    944K|      1 |     5   (0)|   1938K|00:00:16.06 |    1870K|      0 |      0 |         |       |          |
|*  5 |      INDEX RANGE SCAN             | BIN$hmv26I6mN6DgUwEAAH9Dig==$0 |    944K|      1 |     3   (0)|   1586K|00:00:06.77 |    1870K|      0 |      0 |         |       |          |
|*  6 |      INDEX RANGE SCAN             | KBTRX_TRAN_IDX                 |   1586K|      1 |     1   (0)|   1938K|00:00:04.18 |       3 |      0 |      0 |         |       |          |
|*  7 |     TABLE ACCESS BY INDEX ROWID   | KBTRX                          |   1938K|      1 |     2   (0)|   1585K|00:00:03.97 |     704K|      0 |      0 |         |       |          |
|   8 |   SORT AGGREGATE                  |                                |    944K|      1 |            |    944K|00:00:16.41 |    3829K|      0 |      0 |         |       |          |
|   9 |    INLIST ITERATOR                |                                |    944K|        |            |  34696 |00:00:13.97 |    3829K|      0 |      0 |         |       |          |
|* 10 |     TABLE ACCESS BY INDEX ROWID   | LGCASEMAST                     |   1889K|      1 |     5   (0)|  34696 |00:00:11.12 |    3829K|      0 |      0 |         |       |          |
|* 11 |      INDEX RANGE SCAN             | LGCASMAST_STATUS_CASEKEY       |   1889K|      1 |     4   (0)|  34696 |00:00:07.57 |    3795K|      0 |      0 |         |       |          |
|  12 |   SORT AGGREGATE                  |                                |    944K|      1 |            |    944K|00:00:07.81 |    1853K|      0 |      0 |         |       |          |
|* 13 |    FILTER                         |                                |    944K|        |            |      0 |00:00:05.66 |    1853K|      0 |      0 |         |       |          |
|* 14 |     INDEX RANGE SCAN              | BIN$hmv26I6mN6DgUwEAAH9Dig==$0 |    944K|      1 |     3   (0)|      0 |00:00:03.92 |    1853K|      0 |      0 |         |       |          |
|  15 |   SORT AGGREGATE                  |                                |    944K|      1 |            |    944K|00:00:17.42 |    2908K|    265 |      0 |         |       |          |
|* 16 |    INDEX RANGE SCAN               | BIN$hmv26I6mN6DgUwEAAH9Dig==$0 |    944K|      1 |     3   (0)|    338 |00:00:15.10 |    2908K|    265 |      0 |         |       |          |
|  17 |     TABLE ACCESS BY INDEX ROWID   | JPROCESSDATA                   |  10071 |      1 |     4   (0)|  10071 |00:00:00.23 |   89713 |      0 |      0 |         |       |          |
|* 18 |      INDEX RANGE SCAN             | JPROCESSDATA_CASEKEY           |  10071 |      1 |     3   (0)|  10071 |00:00:00.17 |   59492 |      0 |      0 |         |       |          |
|* 19 |       COUNT STOPKEY               |                                |  10071 |        |            |  10071 |00:00:00.10 |   39323 |      0 |      0 |         |       |          |
|* 20 |        TABLE ACCESS BY INDEX ROWID| LGCASEMAST                     |  10071 |      1 |     4   (0)|  10071 |00:00:00.07 |   39323 |      0 |      0 |         |       |          |
|* 21 |         INDEX RANGE SCAN          | LGCASMAST_STATUS_CASEKEY       |  10071 |      1 |     3   (0)|  10071 |00:00:00.04 |   29252 |      0 |      0 |         |       |          |
|  22 |     TABLE ACCESS BY INDEX ROWID   | JPROCESSDATA                   |    944K|      1 |     4   (0)|  32844 |00:00:12.51 |    2805K|    265 |      0 |         |       |          |
|* 23 |      INDEX RANGE SCAN             | JPROCESSDATA_CASEKEY           |    944K|      1 |     3   (0)|  32844 |00:00:09.98 |    2700K|      0 |      0 |         |       |          |
|* 24 |       COUNT STOPKEY               |                                |    944K|        |            |  32844 |00:00:07.76 |    2634K|      0 |      0 |         |       |          |
|* 25 |        TABLE ACCESS BY INDEX ROWID| LGCASEMAST                     |    944K|      1 |     4   (0)|  32844 |00:00:05.39 |    2634K|      0 |      0 |         |       |          |
|* 26 |         INDEX RANGE SCAN          | LGCASMAST_STATUS_CASEKEY       |    944K|      1 |     3   (0)|  32844 |00:00:03.93 |    2602K|      0 |      0 |         |       |          |
|  27 |   SORT AGGREGATE                  |                                |    944K|      1 |            |    944K|00:00:07.57 |    1853K|      0 |      0 |         |       |          |
|* 28 |    FILTER                         |                                |    944K|        |            |      0 |00:00:05.44 |    1853K|      0 |      0 |         |       |          |
|* 29 |     INDEX RANGE SCAN              | BIN$hmv26I6mN6DgUwEAAH9Dig==$0 |    944K|      1 |     3   (0)|      0 |00:00:03.76 |    1853K|      0 |      0 |         |       |          |
|  30 |   SORT AGGREGATE                  |                                |    944K|      1 |            |    944K|00:00:06.84 |    1870K|      0 |      0 |         |       |          |
|* 31 |    INDEX RANGE SCAN               | BIN$hmv26I6mN6DgUwEAAH9Dig==$0 |    944K|      1 |     3   (0)|   1073 |00:00:04.49 |    1870K|      0 |      0 |         |       |          |
|* 32 |   TABLE ACCESS FULL               | JCASEMAST                      |      1 |    939K| 88570   (2)|    944K|00:00:09.87 |    2487K|    566K|      0 |         |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("PAY_CASEKEY"=:B1)
       filter("PAY_SOURCE"='SIBS')
   6 - access("PAY_CODE"="KBT_TRAN")
   7 - filter(("KBT_SOURCE"='SIBS' AND "PAYMENT"='Y'))
  10 - filter(("CM_TYPE"='BOTH' OR "CM_TYPE"='CSUT' OR "CM_TYPE"='FCLS'))
  11 - access((("CM_STATUS"='ACTV' OR "CM_STATUS"='KIV')) AND "CM_CASEKEY"=:B1)
  13 - filter(TRUNC(SYSDATE@!)>=TRUNC(SYSDATE@!)-45)
  14 - access("PAY_CASEKEY"=:B1 AND "PAY_DATE">=TRUNC(SYSDATE@!)-45 AND "PAY_DATE"<=TRUNC(SYSDATE@!))
       filter("PAY_SOURCE"='SIBS')
  16 - access("PAY_CASEKEY"=:B1 AND "PAY_DATE">= AND "PAY_DATE"<=)
       filter("PAY_SOURCE"='SIBS')
  18 - access("CASEKEY"=:B1 AND "ALTKEY"=)
  19 - filter(ROWNUM<=1)
  20 - filter("CM_TYPE"='CSUT')
  21 - access("CM_STATUS"='ACTV' AND "CM_CASEKEY"=:B1)
  23 - access("CASEKEY"=:B1 AND "ALTKEY"=)
  24 - filter(ROWNUM<=1)
  25 - filter("CM_TYPE"='CSUT')
  26 - access("CM_STATUS"='ACTV' AND "CM_CASEKEY"=:B1)
  28 - filter(TRUNC(SYSDATE@!)>=TRUNC(SYSDATE@!)-30)
  29 - access("PAY_CASEKEY"=:B1 AND "PAY_DATE">=TRUNC(SYSDATE@!)-30 AND "PAY_DATE"<=TRUNC(SYSDATE@!))
       filter("PAY_SOURCE"='SIBS')
  31 - access("PAY_CASEKEY"=:B1 AND "PAY_CODE"='007')
       filter(("PAY_CODE"='007' AND "PAY_SOURCE"='SIBS'))
  32 - filter("CM_PRODUCT_TYPE"='03')


I rewrote the parts of the query as Chris suggested and can see the improvements.

SELECT JM.CM_CASEID CASEKEY,
  JM.CM_CIF_ICNO ICNO,
  JM.CM_PRODUCT PRODUCT,
  JM.CMR_OSBAL OSBAL,
  JM.CM_AMT_DUE DUE_AMT,
  JM.CMR_MTHINARR MIA,
  JM.CMR_STATUS STATUS,
  JM.CM_PROBLEM_STATUS PROBLEM_STATUS,
  JM.CM_STRATEGY STRATEGY,
  JM.CM_PROFILE PROFILES,
  JM.CMR_AKPK AKPK,
  JM.CM_DMP_FLAG DMP,
  JM.CMR_PDC_FLAG PDC,
  JM.CM_DECEASED DECEASED,
  JM.CMR_BANKRUPT_FLAG BANKRUPT,
  MAX( CASE WHEN PAY_SOURCE = 'SIBS' AND PAY_CODE IN (SELECT KBT_TRAN FROM JCOLLECT.KBTRX WHERE KBT_SOURCE = 'SIBS' AND PAYMENT = 'Y')
        THEN PAY_DATE END)  MAX_PAYDATE,
  (SELECT COUNT (*) FROM JCOLLECT.LGCASEMAST
     WHERE CM_CASEKEY = CM_CASEID AND CM_STATUS IN ('ACTV', 'KIV') AND CM_TYPE IN ('CSUT', 'FCLS', 'BOTH')) NUM_ACTV_LEGAL_CASES,
  (SUM (CASE WHEN PAY_SOURCE = 'SIBS' AND PAY_DATE BETWEEN TRUNC (SYSDATE) - 45 AND TRUNC (SYSDATE) THEN PAY_AMT END)) SUM_PAY_BYACC,
   SUM (CASE WHEN PAY_DATE BETWEEN
    (SELECT CSUT_NODT FROM JCOLLECT.JPROCESSDATA WHERE CASEKEY = CM_CASEID
  AND ALTKEY = (CASE WHEN LM.CM_TYPE = 'CSUT' AND LM.CM_STATUS = 'ACTV' AND ROWNUM <= 1 THEN REC_ID END))
 AND
   (SELECT CSUT_NODT_EXP_DT FROM JCOLLECT.JPROCESSDATA WHERE CASEKEY = CM_CASEID
  AND ALTKEY = (CASE WHEN LM.CM_TYPE = 'CSUT' AND LM.CM_STATUS = 'ACTV' AND ROWNUM <= 1 THEN REC_ID END))
      THEN PAY_AMT END) SUM_PAY_NODT,
 ( SUM (CASE WHEN PAY_SOURCE = 'SIBS' AND PAY_DATE BETWEEN TRUNC (SYSDATE) - 30 AND TRUNC (SYSDATE)
                    THEN PAY_AMT END)) SUM_PAY_BYACC30,
 (MAX (CASE WHEN PAY_CODE = '007' AND PAY_SOURCE = 'SIBS' THEN PAY_DATE END)) MAX_PAYDATE_HP_LAD
 FROM JCOLLECT.JCASEMAST JM
LEFT JOIN JCOLLECT.VW_PAYMENT VP
ON JM.CM_CASEID = VP.PAY_CASEKEY
LEFT JOIN JCOLLECT.LGCASEMAST LM ON LM.CM_CASEKEY = JM.CM_CASEID 
WHERE JM.CM_PRODUCT_TYPE = '03'
GROUP BY JM.CM_CASEID, JM.CM_CIF_ICNO, JM.CM_PRODUCT, JM.CMR_OSBAL, JM.CM_AMT_DUE, JM.CMR_MTHINARR, JM.CMR_STATUS, JM.CM_PROBLEM_STATUS, JM.CM_STRATEGY, 
JM.CM_PROFILE, JM.CMR_AKPK, JM.CM_DMP_FLAG, JM.CMR_PDC_FLAG, JM.CM_DECEASED, JM.CMR_BANKRUPT_FLAG;

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                     | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |    1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |                          |      1 |        |   182K(100)|      0 |00:01:15.40 |    6772K|    562K|  10841 |       |         |          |
|   1 |  LOAD AS SELECT                  |                          |      1 |        |            |      0 |00:01:15.40 |    6772K|    562K|  10841 |   530K|     530K|  530K (0)|
|*  2 |   TABLE ACCESS BY INDEX ROWID    | KBTRX                    |     14 |      1 |     2   (0)|     12 |00:00:00.01 |      26 |      0 |      0 |       |         |          |
|*  3 |    INDEX RANGE SCAN              | KBTRX_TRAN_IDX           |     14 |      1 |     1   (0)|     13 |00:00:00.01 |      13 |      0 |      0 |       |         |          |
|   4 |   SORT AGGREGATE                 |                          |    944K|      1 |            |    944K|00:00:13.80 |    3829K|      0 |      0 |       |         |          |
|   5 |    INLIST ITERATOR               |                          |    944K|        |            |  34696 |00:00:11.73 |    3829K|      0 |      0 |       |         |          |
|*  6 |     TABLE ACCESS BY INDEX ROWID  | LGCASEMAST               |   1889K|      1 |     5   (0)|  34696 |00:00:09.51 |    3829K|      0 |      0 |       |         |          |
|*  7 |      INDEX RANGE SCAN            | LGCASMAST_STATUS_CASEKEY |   1889K|      1 |     4   (0)|  34696 |00:00:06.48 |    3795K|      0 |      0 |       |         |          |
|   8 |   COUNT                          |                          |   1556K|        |            |  31036 |00:00:08.11 |     184K|     10 |      0 |       |         |          |
|*  9 |    FILTER                        |                          |   1556K|        |            |  31036 |00:00:05.71 |     184K|     10 |      0 |       |         |          |
|  10 |     TABLE ACCESS BY INDEX ROWID  | JPROCESSDATA             |   1556K|      1 |     4   (0)|  31036 |00:00:03.77 |     184K|     10 |      0 |       |         |          |
|* 11 |      INDEX RANGE SCAN            | JPROCESSDATA_CASEKEY     |   1556K|      1 |     3   (0)|  31036 |00:00:01.60 |   93161 |      0 |      0 |       |         |          |
|  12 |     COUNT                        |                          |   1556K|        |            |  30590 |00:00:08.85 |     202K|      0 |      0 |       |         |          |
|* 13 |      FILTER                      |                          |   1556K|        |            |  30590 |00:00:06.34 |     202K|      0 |      0 |       |         |          |
|  14 |       TABLE ACCESS BY INDEX ROWID| JPROCESSDATA             |   1556K|      1 |     4   (0)|  30590 |00:00:03.98 |     202K|      0 |      0 |       |         |          |
|* 15 |        INDEX RANGE SCAN          | JPROCESSDATA_CASEKEY     |   1556K|      1 |     3   (0)|  30590 |00:00:01.60 |   91823 |      0 |      0 |       |         |          |
|  16 |   HASH GROUP BY                  |                          |      1 |   2825K|   182K  (2)|    944K|00:00:57.82 |    2931K|    562K|      0 |   467M|      16M|  193M (0)|
|* 17 |    HASH JOIN OUTER               |                          |      1 |   2825K|   103K  (2)|   2077K|00:00:31.72 |    2545K|    562K|      0 |   138M|    9036K|  171M (0)|
|* 18 |     HASH JOIN RIGHT OUTER        |                          |      1 |    939K| 94286   (2)|    971K|00:00:20.94 |    2513K|    562K|      0 |    49M|    6058K|   56M (0)|
|  19 |      TABLE ACCESS FULL           | LGCASEMAST               |      1 |    677K|  3946   (1)|    677K|00:00:00.29 |   25572 |      0 |      0 |       |         |          |
|* 20 |      TABLE ACCESS FULL           | JCASEMAST                |      1 |    939K| 88570   (2)|    944K|00:00:17.88 |    2487K|    562K|      0 |       |         |          |
|  21 |     INDEX FAST FULL SCAN         | JPAYMENT_PAY_PSOURCE     |      1 |   4085K|  4890   (2)|   4085K|00:00:01.79 |   32059 |      0 |      0 |       |         |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("KBT_SOURCE"='SIBS' AND "PAYMENT"='Y'))
   3 - access("KBT_TRAN"=:B1)
   6 - filter(("CM_TYPE"='BOTH' OR "CM_TYPE"='CSUT' OR "CM_TYPE"='FCLS'))
   7 - access((("CM_STATUS"='ACTV' OR "CM_STATUS"='KIV')) AND "CM_CASEKEY"=:B1)
   9 - filter(CASE  WHEN (:B1='CSUT' AND :B2='ACTV' AND ROWNUM<=1) THEN :B3 END ="ALTKEY")
  11 - access("CASEKEY"=:B1 AND CASE  WHEN (:B2='CSUT' AND :B3='ACTV' AND ROWNUM<=1) THEN :B4 END ="ALTKEY")
  13 - filter(CASE  WHEN (:B1='CSUT' AND :B2='ACTV' AND ROWNUM<=1) THEN :B3 END ="ALTKEY")
  15 - access("CASEKEY"=:B1 AND CASE  WHEN (:B2='CSUT' AND :B3='ACTV' AND ROWNUM<=1) THEN :B4 END ="ALTKEY")
  17 - access("JM"."CM_CASEID"="PAY_CASEKEY")
  18 - access("LM"."CM_CASEKEY"="JM"."CM_CASEID")
  20 - filter("JM"."CM_PRODUCT_TYPE"='03')


Then moment I change the count part of the subquery as 

(CASE WHEN LM.CM_STATUS IN ('ACTV', 'KIV') AND LM.CM_TYPE IN ('CSUT', 'FCLS', 'BOTH') THEN COUNT (*) END ) NUM_ACTV_LEGAL_CASES,

It doesn`t work. I tried this also but the data is incorrect.
COUNT(CASE WHEN LM.CM_STATUS IN ('ACTV', 'KIV') AND LM.CM_TYPE IN ('CSUT', 'FCLS', 'BOTH') THEN 1 END ) NUM_ACTV_LEGAL_CASES


So how can I get the correct count? Do I need to use some analytical functions?

When I try to change this part with Case, I get wrong results, I`m clueless as how to fix this.
(SELECT CSUT_NODT FROM JCOLLECT.JPROCESSDATA WHERE CASEKEY = CM_CASEID AND ALTKEY = (CASE WHEN LM.CM_TYPE = 'CSUT' AND LM.CM_STATUS = 'ACTV' AND ROWNUM <= 1 THEN REC_ID END))


Thanks
-a
Chris Saxon
May 10, 2019 - 8:24 am UTC

We need more detail to provide effective help here. Please post a short test case including:

- create tables
- data in insert into format
- expected output
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.