Thank you very much Jonathan and Andrew.
I had modified the query as per Jonathan's suggestion and tested by
converting the first four UNION clauses. The results matching and also the
modified query access path does show only one time access to the
table/indexes of TAD/TAD_Ix2. Attached is the modified query with its
sqlmonitor.
Andrew, for my understanding here, I think you got the HASH JOIN access of
TAD clue from below plan, but i am still not able to understand how this
path suggests the HASH JON access of TAD, as because line 9 and 10 shows
two nested loops and HASH Join is in third position in the tree. So I am
assuming line 9- i.e first nested loop JOIN is between TNI and TMTD and
line 10 is for the join between TAD and the result set out of TNI JOIN
TMTD. So doesn't it mean that table TAD is also accessed in a nested loop
path here? Please guide me here. However, as you suggested, I simply tried
the option i.e. forcing a HASH Join path through profile , for all SELECT
queries. This way too the query is giving us results in less than minutes
as opposed to ~15-20minutes with the all NESTED LOOP access path. And to
answer your earlier question, here table TNI is a global temporary table
and XX is a literal used in the query as filter, but yes as it's a global
temporary table we are relying on dynamic sampling here and sometimes it
goes off quite a bit more than actual cardinality and that is possibly
causing different execution path.
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name
| Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT |
| | | 12967 (100)| |
| 1 | LOAD TABLE CONVENTIONAL |
| | | | |
| 2 | SORT UNIQUE |
| 18 | 1548 | 12967 (1)| 00:02:36 |
| 3 | UNION-ALL |
| | | | |
| 4 | HASH GROUP BY |
| 1 | 86 | 738 (1)| 00:00:09 |
| 5 | NESTED LOOPS |
| 1 | 86 | 736 (1)| 00:00:09 |
| 6 | NESTED LOOPS |
| 1 | 86 | 736 (1)| 00:00:09 |
| 7 | NESTED LOOPS |
| 1 | 79 | 735 (1)| 00:00:09 |
|* 8 | HASH JOIN |
| 1 | 60 | 734 (1)| 00:00:09 |
| 9 | NESTED LOOPS |
| 12 | 384 | 26 (0)| 00:00:01 |
| 10 | NESTED LOOPS |
| 12 | 384 | 26 (0)| 00:00:01 |
|* 11 | TABLE ACCESS STORAGE FULL | TNI
| 12 | 192 | 2 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | MTD_PK
| 1 | | 1 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | TMTD
| 1 | 16 | 2 (0)| 00:00:01 |
*|* 14 | TABLE ACCESS BY INDEX ROWID | TAD
| 14 | 392 | 708 (1)| 00:00:09 ||* 15 | INDEX RANGE
SCAN | TAD_IX2 | 28797 | | 116
(0)| 00:00:02 || 16 | SORT AGGREGATE |
| 1 | 8 | | || 17 |
INDEX FULL SCAN (MIN/MAX) | TAD_IX2 | 1 | 8
| 3 (0)| 00:00:01 |*
| 18 | TABLE ACCESS BY INDEX ROWID | TMMC
| 1 | 19 | 1 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | TMMC_IX1
| 1 | | 0 (0)| |
|* 20 | INDEX UNIQUE SCAN | MCT_PK
| 1 | | 0 (0)| |
| 21 | TABLE ACCESS BY INDEX ROWID | TMCT
| 1 | 7 | 1 (0)| 00:00:01 |
| 22 | HASH GROUP BY |
| 1 | 86 | 717 (1)| 00:00:09 |
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("TMTD"."PMENT"="TAD"."PMENT")
11 - storage("TNI"."NE"='XX')
filter("TNI"."NE"='XX')
12 - access("TNI"."NID"="TMTD"."DID")
14 - filter(((NVL(:B1,'ZZZZ')='ZZZZ' OR ("TAD"."MCD"=:B1 AND
NVL(:B1,'ZZZZ')<>'ZZZZ')) AND
TRUNC(:B2)-"ADT"<=1))
15 - access("TAD"."DT_CR"=)
19 - access("TAD"."MCD"="TMMC"."PTMCD")
20 - access("TMTD"."CID"="TMCT"."SWCTID")
Regards
Pap
On Thu, Jun 24, 2021 at 3:34 PM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
It's up to you to check the validaty of my guess regarding the nature of
the query simply breaking a data set for a given date by different ranges
then the basis of the mechanism is to take the predicate (list) that breaks
the data set into pieces and combine the list in a CASE expression that
generates a value that can be used as an extra grouping column, along the
lines of:
select
case
when adt - to_date(:b1,'yyyy-mm-dd') <= 1
then 'g1'
when adt - to_date(:b1,'yyyy-mm-dd') > 1 and adt -
to_date(:b1,'yyyy-mm-dd') <= 2 then 'g2'
when adt - to_date(:b1,'yyyy-mm-dd') > 2 and adt -
to_date(:b1,'yyyy-mm-dd') <= 7 then 'g3'
when adt - to_date(:b1,'yyyy-mm-dd') > 7
then 'g4'
end flag,
mcd,
sum(value_col), count(*)
from
v1
group by
case
when adt - to_date(:b1,'yyyy-mm-dd') <= 1
then 'g1'
when adt - to_date(:b1,'yyyy-mm-dd') > 1 and adt -
to_date(:b1,'yyyy-mm-dd') <= 2 then 'g2'
when adt - to_date(:b1,'yyyy-mm-dd') > 2 and adt -
to_date(:b1,'yyyy-mm-dd') <= 7 then 'g3'
when adt - to_date(:b1,'yyyy-mm-dd') > 7
then 'g4'
end,
mcd
order by
flag, mcd
/
Regards
Jonathan Lewis
On Tue, 22 Jun 2021 at 19:16, Pap <oracle.developer35@xxxxxxxxx> wrote:
Thanks much Jonathan.
Trying to visualize/understand the code modification which you suggested.
Agreed that the difference in the query is just the date criteria filter on
column ADT of table TAD. But currently we are fetching sum(amt) and
count(*) based on the grouping column in each of those queries.
