Re: explain plan changes if using bind vars

On Wed, 03 Aug 2005 20:34:09 +0000, NetComrade wrote:

> I wish there was a hint to make the query execute subquery remotedly,
> and don't bother trying to figure out the explain plan.. I couldn't
> figure out how to do that..
>
> We did find a solution (as Frank suggested) where we both simplified a
> query, and hid it with a view on the remote site.

That is an ancient trick. The problem with remote queries is that, if two row sources on different databases are joined, then one of them is brought over to another database and join is done locally. The site that will coordinate things is determined by parameter COMMIT_POINT_STRENGTH. Usually though, it is the site that issues the SQL. If you try joining a large remote table with a local table, the remote table will be put in the temporary tablespace of the local database and joined with the local table, much to the delight of the nervous users awaiting results. That is why you never want to resolve something that can be resolved remotely on your local database. You should ALWAYS create and optimize a view on the remote database and join local row source with the remote view. That way, you'll only get few rows over the network and join will be lightning fast. If the remote row source is large, consider snapshots.

-- 
http://www.mgogala.com