Re: Remote query optimization

The first query is bringing rows from the remote site and then returning back to the remote site in a looping method (nested loops is best for getting the first row of the query back fast but not got for throughput or getting all rows back fast). Creating the remote view IS the best way to do this since it does the query remotely and then passes the result back to the local database once
(this is a very good trick to know ... not everyone knows it so I'm glad you
showed it here).

If you can't do this, then you can try placing hints to achieve the same result
(although this could get overriden internally on remotes). Try the ALL_ROWS
hint which is for throughput (usually eliminates the use of nested loops). I haven't tried this (I usually use remote views), but I figure it's worth a suggestion.

If you were joining two tables (I know that you're not ... future use) in Oracle8, there is a DRIVING_SITE(table_name) hint that could be used to force the smallest number of rows to be moved to one site or the other. Also, a snapshot that is refreshed on the local site (or table that is replicated on the local site) would obviously bring a lot better performance as well when possible. Also, one last way to eliminate nested loops in a local join (also not your issue but fyi), the USE_MERGE(table_name) and USE_HASH(needs some init.ora's set) will force best throughput. If you do want to get the first row fast and force nested loops, then the USE_NL(table_name) is the hint.

Hope that helps someone,
Rich Niemiec
Oracle Performance Tips and Techniques
Oracle Press - ISBN: 0078824346 Received on Tue Jun 08 1999 - 11:48:08 CDT