Performance and Distrubuted Oracele 7

We are experiencing performace problems with Oracle 7 in a distributed database environment. Here's the picture:

Local system 				Remote System
	-Oracle 7				-Oracle 6
	 +Distributed opt.			 +Distributed opt.
	 +SQL*Net 				 +SQL*Net

Table1					Table2
	-less than 10 rows			->600,000+ rows
	-foreign key to Table2			-primary key
		(name=t2ref)				(name=primkey, indexed)
	-local DB login with synonym		-user1 owns Table2
		to remote user2.Table2		 user2 has Select rights to
							Table2 (i.e. read only)
	

All queries to local and remote tables (through synonyms) work great, except for those that do a join of Table1 and Table2. When we try to do a join of these tables, the remote system experiences high disk and network I/O and the query never completes (we haven't waited for it to). A sample problem select statement is listed below:

	Select * from Table1, Table2
	 where Table1.t2ref = Table2.primkey

I don't consider myself an expert on Relational Databases, but I think the problem lies in Oracle 7's optimizer choosing to do a Sort/Merge join instead of a Nested Loop join. With a Sort/Merge join, the local system is trying to copy all of Table2 into the local system's TEMP database space. Once there, it will sort Table1 and Table2 and merge them for the join. This method is great if both tables are local, but in a distributed environment, the local system will have to move mega-bytes of data over the network: this is totally unacceptable! With a Nested Loop join, the local system would iterate over all rows in Table1 and do a lookup in Table2 for rows to join with. In the situation described above, this is obviously the preferred choice!

I haven't looked at the execution plan for the query, but my first guess is that it is doing a Sort/Merge join. I will check out the execution plan today after I find the queries/views for looking at Explain Plan data.

My questions about this problem are as follows:

• Am I correct in the diagnosis of the problem?
• Are there solutions? I have tried: -using oracle optimizer hints (USE_NL, etc...) -using in clauses with subqueries: "and Table2.primkey in (select t2ref from Table1)" -using open joins istead of closed joins
• Is the problem because the remote system is Oracle 6?
• Has anyone else experienced similar problems?
• Am I missing something obvious? There is much to Oracle that I have yet to learn

I am going to scan the Oracle 7 manuals today and may give Oracle a call this afternoon. Any help would be greatly appreciated. I will try to post follow ups and solutions if people ask for them. Thanks for listening.

        Chuck (cdm_at_idm.com) Received on Thu May 20 1993 - 17:50:30 CEST