SELECT TAD.MCD, TMMC.PMCDVAL, CASE WHEN (TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) <= 1 then 'G1' WHEN (TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) > 1 AND (TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) <= 2 then 'G2' WHEN (TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) > 2 AND (TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) <= 3 then 'G3' WHEN (TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) > 3 AND (TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) <= 4 then 'G4' end flag, SUM(CASE WHEN (TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) <= 1 then AMT WHEN (TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) > 1 AND (TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) <= 2 then AMT WHEN (TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) > 2 AND (TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) <= 3 then AMT WHEN (TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) > 3 AND (TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) <= 4 then AMT end ) amount, SUM(CASE WHEN (TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) <= 1 then 1 WHEN (TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) > 1 AND (TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) <= 2 then 1 WHEN (TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) > 2 AND (TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) <= 3 then 1 WHEN (TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) > 3 AND (TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) <= 4 then 1 end ) count_1, :B3, TMCT.PDCMLPNT, TMTD.PMENT FROM TAD TAD, TMMC TMMC, TMTD TMTD, TMCT TMCT WHERE TAD.MCD = TMMC.PTMCD AND TMTD.PMENT = TAD.PMENT AND TMTD.CID = TMCT.SWCTID AND ( ((TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) <= 1 ) OR ((TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) > 1 AND (TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) <= 2) OR ((TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) > 2 AND (TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) <= 3) OR ((TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) > 3 AND (TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) <= 4) ) AND ( (TAD.MCD = :B1 AND NVL ( :B1, 'ZZZZ') <> 'ZZZZ') OR NVL ( :B1, 'ZZZZ') = 'ZZZZ') AND TAD.DT_CR IN (SELECT MAX (DT_CR) FROM TAD) GROUP BY TMTD.PMENT, TAD.MCD, TMMC.PMCDVAL, TMCT.PDCMLPNT , CASE WHEN (TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) <= 1 then 'G1' WHEN (TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) > 1 AND (TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) <= 2 then 'G2' WHEN (TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) > 2 AND (TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) <= 3 then 'G3' WHEN (TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) > 3 AND (TRUNC ( to_date(:B2,'DD-MON-YYYY')) - ADT) <= 4 then 'G4' end order by flag Global Information ------------------------------ Status : DONE (ALL ROWS) Instance ID : 4 SQL ID : 00tvurfdd4cm7 SQL Execution ID : 67108864 Execution Started : 06/27/2021 13:46:45 First Refresh Time : 06/27/2021 13:46:45 Last Refresh Time : 06/27/2021 13:46:45 Duration : .106346s Module/Action : SQL*Plus/- Program : sqlplus.exe Fetch Calls : 2 Binds ======================================================================================================================== | Name | Position | Type | Value | ======================================================================================================================== | :B2 | 1 | VARCHAR2(4001) | 17-jun-2021 | | :B1 | 30 | VARCHAR2(4001) | ZZZZ | ======================================================================================================================== Global Stats ========================================================================================= | Elapsed | Cpu | IO | Concurrency | Cluster | Fetch | Buffer | Read | Read | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | ========================================================================================= | 0.11 | 0.06 | 0.01 | 0.00 | 0.04 | 2 | 23167 | 14 | 112KB | ========================================================================================= SQL Plan Monitoring Details (Plan Hash Value=4294317510) =================================================================================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) | =================================================================================================================================================================================================== | 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 529 | | | | | | | 1 | SORT GROUP BY | | 15 | 797 | 1 | +0 | 1 | 529 | | | 67584 | | | | 2 | NESTED LOOPS | | 15 | 796 | 1 | +0 | 1 | 5997 | | | | | | | 3 | NESTED LOOPS | | 15 | 796 | 1 | +0 | 1 | 5997 | | | | | | | 4 | HASH JOIN | | 15 | 781 | 1 | +0 | 1 | 5997 | | | 1M | | | | 5 | NESTED LOOPS | | 15 | 779 | 1 | +0 | 1 | 5997 | | | | | | | 6 | NESTED LOOPS | | 17 | 779 | 1 | +0 | 1 | 5997 | | | | | | | 7 | TABLE ACCESS BY INDEX ROWID | TAD | 17 | 728 | 1 | +0 | 1 | 5997 | | | | | | | 8 | INDEX RANGE SCAN | TAD_IX2 | 29513 | 119 | 1 | +0 | 1 | 8659 | | | | 100.00 | gc current block 3-way (1) | | 9 | SORT AGGREGATE | | 1 | | 1 | +0 | 1 | 1 | | | | | | | 10 | INDEX FULL SCAN (MIN/MAX) | TAD_IX2 | 1 | 3 | 1 | +0 | 1 | 1 | | | | | | | 11 | INDEX RANGE SCAN | TMTD_IX2 | 1 | 2 | 1 | +0 | 6183 | 5997 | 4 | 32768 | | | | | 12 | TABLE ACCESS BY INDEX ROWID | TMTD | 1 | 3 | 1 | +0 | 6114 | 5997 | 5 | 40960 | | | | | 13 | VIEW | index$_join$_002 | 110 | 2 | 1 | +0 | 1 | 112 | | | | | | | 14 | HASH JOIN | | | | 1 | +0 | 1 | 112 | | | 2M | | | | 15 | INDEX STORAGE FAST FULL SCAN | MMC_IX1 | 110 | 1 | 1 | +0 | 1 | 112 | | | | | | | 16 | INDEX STORAGE FAST FULL SCAN | MMC_IX2 | 110 | 1 | 1 | +0 | 1 | 112 | | | | | | | 17 | INDEX UNIQUE SCAN | MCT_PK | 1 | | 1 | +0 | 5997 | 5997 | | | | | | | 18 | TABLE ACCESS BY INDEX ROWID | TMCT | 1 | 1 | 1 | +0 | 5997 | 5997 | | | | | | =================================================================================================================================================================================================== Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("TAD"."MCD"="TMMC"."PTMCD") 7 - filter(("TAD"."MCD"=:B1 AND NVL(:B1,'ZZZZ')<>'ZZZZ' OR NVL(:B1,'ZZZZ')='ZZZZ') AND (TRUNC(TO_DATE(:B2,'DD-MON-YYYY'))-"ADT"<=1 OR TRUNC(TO_DATE(:B2,'DD-MON-YYYY'))-"ADT">1 AND TRUNC(TO_DATE(:B2,'DD-MON-YYYY'))-"ADT"<=2 OR TRUNC(TO_DATE(:B2,'DD-MON-YYYY'))-"ADT">2 AND TRUNC(TO_DATE(:B2,'DD-MON-YYYY'))-"ADT"<=3 OR TRUNC(TO_DATE(:B2,'DD-MON-YYYY'))-"ADT">3 AND TRUNC(TO_DATE(:B2,'DD-MON-YYYY'))-"ADT"<=4)) 8 - access("TAD"."DT_CR"= (SELECT MAX("DT_CR") FROM "SCHEMA1"."TAD" "TAD")) 11 - access("TMTD"."PMENT"="TAD"."PMENT") 14 - access(ROWID=ROWID) 17 - access("TMTD"."CID"="TMCT"."SWCTID")