Re: hash join with remote table

My mistake, very sorry, remote object is actually a view

On Tue, Oct 4, 2022, 18:39 Lothar Flatz <l.flatz_at_bluewin.ch> wrote:

> Hi Laurentiu,
>
> I wonder if the estimates are not the more important concern.
> If 1 would be correct in both places and can not see one hour runtime.
>
> Thus, correct the estimates and the plans should fall into place.
>
> Apart from that: the overhead of fetching rows over a network was so far
> never considered in the costing. Maybe that has changed now.
>
> Thanks
>
> Lothar
> Am 04.10.2022 um 16:38 schrieb Laurentiu Oprea:
> > Hello everyone,
> >
> > DB version 19.5
> >
> > I have a situation (which I think I saw in the past) where in the
> > execution plan the result of a NL join, estimated to cardinality 1, is
> > hash joined with a remote table (estimated similarly to cardinality 1).
> >
> > The problem is, because of HASH join(right outer), the join predicate
> > is not pushed in the remote query..and as a consequence the remote
> > query is taking 1 hour. With NL, the remote query will take just a few
> > seconds (because of the added where clause).
> >
> > Looking into 10053 looks like the cost for NL is 27.154647 and cost
> > for Hash is 27.180501, so in theory NL should be used but just above
> > best join method I can see: "Cost adjustment for NL join with remote
> > table 0.005859" and then HASH is used.
> >
> > Can someone help me understand this "Cost adjustment for NL"?
> >
> > Thank you.
> >
> >
>
>

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