Re: HELP needed w/SQL*NET V1

maif_at_charm.net (MD Automobile Insurance Fund) writes:
|> We are currently running Oracle version 6 with SQL*NET v1. I have set up
|> a database link to a remote database. I am trying to run a query doing a
|> join of 2 tables, both on the remote machine. Both tables are indexed by
|> the columns being joined. I am not getting any response. I can see that
|> the link is made and the query is running by logging into the remote
|> machine and bringing up the SQL*DBA monitor. If I query against 1 table
|> at a time the response is instantaneous. It seems as though the indexes
|> are not being used on a join condition. I have also tried using a
|> subquery and get no response.

The problem technically is not with SQL*Net V1. It is due to the way Oracle V6 executes distributed queries. Even though both tables are on the same remote database and being referenced through the same db link, the Oracle V6 optimizer does not figure this out nor does it have information about the indexes on those remote tables. So it might try to pull both tables (or a subset of both) into temporary segments and perform the join locally. This sort of scenario is supposed to be handled much better in Oracle7 (with either SQL*Net V1 or V2). One workaround in V6 would be to create a view on the remote database that will perform the join, and then query against the view via the db link. Of course, make sure the join itself is optimized by running the query on the remote database through EXPLAIN PLAN.

Hope this helps.
Roderick Manalac
Oracle Corporation

DISCLAIMER: Speaking for myself not my company. Received on Sun Feb 19 1995 - 00:18:12 CET