Re: Performance problems with distributed queries with table joins.

Bill Beaton (willyb_at_cadvision.com) wrote: [...]
>When we went to distributed processing (at 7.0.16), we found that the
>most effective way to do the join was to actually create a view that did
>the join on 'db_link', so that no table, etc. had to be transmitted over
>the network. i.e., the query above actually does the join on the local
>system, where the driver table doesn't have local staticstics, so the
>assumption is made that a table scan is cheapest for the table. This
>may, or may not have changed. I haven't looked to see if 7.2 has
>addressed the situation differently, as we have already set up the
>vvarious views on the remote machines to minimize the local activity.

Normally a query which references only objects at a single remote database (same dblink, actually) will be sent to the target instance verbatim and optimized according to the same rules as if it had been entered locally. There have been changes in this area over the life of Oracle7, so the behavior described in the original post might be specific to the release level of the local database.

/b

--
Bill Manry  -  Mainframe and Integration Technologies  -  Oracle Corporation
Standard disclaimer applies.