RE: hash join with remote table

I’m clueless about the new plan line.  

And Oracle’s advances since release 6 regarding plans for remote object access have been truly impressive for avoiding hand tuning of every single one.  

However, IF the expense difference is worth a DDL on the remote system, and IF the technological harness is amenable to doing this in a non-tedious way, and IF the remote system permissions are not an obstacle, then it is probably worth exploring using the alter synonym dodge and creating the remote view as a join of the remote object and a skinny remote side index oriented object created as the unique list of the join predicates shoved over from the local side.  

(Doing this by hand is the only way I know to achieve the optimal operational process in this class of query topology. Checking whether this is possibly for Oracle to do under the covers as a CBO operation was judged to be “out-of-scope” in the near term and didn’t even make the voting list at Oracle VLDB in the 1990s. Possibly the time has come with all the remote from each other clouds talking to each other. And WHETHER it is worthwhile (DDL is expensive and potentially throws relevant concurrency issues as well as permissions problems) is a tough CBO-ish calculation because it might win big on stand-alone execution cost but not scale well for many attempters of the same query harness simultaneously.)  

For queries that met the “Method R” test before Cary so intelligently described it, this was even more tedious in V6, because the alter synonym command didn’t exist yet and you had to do a drop/create, potentially making someone else’s query blow up. THAT enhancement request was replied to as “Why would you need that, you already have drop and create?” routinely until the late, great Ken Jacobs actually listened to the answer to the question previous posers had thought was a rhetorical question. Sigh. I’ll always miss him.  

Good luck!      

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Laurentiu Oprea Sent: Tuesday, October 04, 2022 1:07 PM
To: Lothar Flatz
Cc: ORACLE-L (oracle-l_at_freelists.org) Subject: 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