Re: How to fix cache buffer chain issue

There are two details that show what the hash join is doing:

In the predicate information section the predicate for operation 8 (the hash join) is access("TMTD"."PMENT"="TAD"."PMENT"); in other words, this is where TAD and TMTD are joined.

The other detail is in the shape of the plan - it's an example of NLJ_BATCHING (see https://jonathanlewis.wordpress.com/2014/04/23/nl-history/ - search for thge section "Basic Plan for 11g (11.2.0.4)") The first of the two nested loops you specified was between table TNI and (only) the index MTD_PK
The second of the two nested loops was between that result and table TMTD

Regards
Jonathan Lewis

On Sun, 27 Jun 2021 at 19:37, Pap <oracle.developer35_at_gmail.com> wrote:

> Thank you very much Jonathan and Andrew.
>
> 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?
>

--
http://www.freelists.org/webpage/oracle-l