Re: joins across physical servers
Date: Tue, 21 Mar 1995 19:01:06 GMT
Message-ID: <D5t0tv.FqE_at_lanier.com>
In article <D5qz8C.4J_at_gremlin.nrtc.northrop.com>,
Mark W. Aurit <maurit_at_world.nad.northrop.com> wrote:
>We are joining tables across physical servers on our Oracle 6 databases,
>using SQL*NET, i.e.
>select <stuff> from table1 a, table2_at_server2 b,
>where a.column = b.column
>Reponse is pretty hideous, and under explain it appears that
>joins across servers dont make use of indexes.
>Is that true? What are some good ways to optimize performance
>when going across servers?
You can get a lot of improvement by using a view on server2 instead of a table:
select [stuff] from table1 a, view_of_table2_at_server2 b, ...
Due to a quirk in the implementation of the Oracle optimizer, server2 will use indexes when it has to go through a view that is defined on server2, but will NOT use indexes when the remote access is directly to a table.
In more extreme cases, we have created a local copy of table2 on server1 (create table local2 as select * from view_of_table2_at_server2). You will have to run some experiments for your application. Sometimes joins to local2 will run significantly faster than the join to the remote view (but may not be feasible in your app of course).
Again, this behavior doesn't make sense; it's just the way the optimizer is coded. C'est la vie.
Going to Oracle 7, as someone else suggested may also solve the problem, but I think vestiges of it still remain, at least in 7.0
Don
Donald E. Vick (dvick_at_lanier.com, dvick_at_crl.com) Voice: (404) 493-2194 Fax: (404) 493-2399 Received on Tue Mar 21 1995 - 20:01:06 CET