Re: Performance problems with distributed queries with table joins.

kirkcw_at_datanet.ab.ca wrote:
: I am wondering if anyone has found performance problems with distributed
: queries with table joins. The local Oracle database is running
: server 7.0.16 and SQL*Net is version 1 on a SUN. The remote database is
: version 7.0.16 and is also running SQL*NET version 1, but is on an HP.
:
: The query I am running from the local database is something like
 

: select col1, col2, .....
: from TABLEA_at_db_link, TABLEB_at_db_link
: where TABLEA.col1 = TABLEB.col1
: and TABLEA.col1 = 1;

: where tables A and B are both on the same remote database in the same
: SCHEMA. The field col1 for the joins is a primary key for both tables.
: The query only returns one row.
 

: On the remote database I ran sql_trace and it appears that ORACLE is
: doing a full table scan for the first table instead of using the
: index. What is interesting when I run the same query from another
: ORACLE database but on an HP server the query take only a couple of
: seconds to return.
 

: There is nothing special about the query except that it executed is over
: a link and I do not believe the problem is due to network traffic but to
: the way ORACLE is executing the query on the remote database.

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.

--
Names:	Bill Beaton			beatonb_at_cadvision.com
					Bill_Beaton_at_calgary.qc-data.com
Phone:	(403) 295-3254 (Home)		(403) 266-8622 (work)