Path: utzoo!utgpu!jarvis.csri.toronto.edu!mailrus!ukma!rutgers!att!laidbak!egn
From: egn@laidbak.UUCP (E. G. Nadhan)
Newsgroups: comp.databases
Subject: ORACLE SQL subqueries performance
Keywords: SQL Subqueries IN EXISTS
Message-ID: <2285@laidbak.UUCP>
Date: 28 Apr 89 15:46:42 GMT
Reply-To: egn@laidbak.UUCP (E. G. Nadhan)
Organization: Lachman Associates, Inc. Chicago
Lines: 44


	I came across an article in the March issue of DATABASE Programming
and Design which discussed the performance advantages of using a 
correlated subquery using the EXISTS clause over the IN,ANY or ALL clauses
in the ORACLE environment.

	The gist of the article is given below:

	Case 1:		SELECT X FROM TABLEA 
			WHERE X IN
				(SELECT Y FROM TABLEB
				 WHERE (condition)
				)

	Case 2:		SELECT X FROM TABLEA
			WHERE EXISTS
				(SELECT * FROM TABLEB
				 WHERE X = TABLEB.Y
				 AND (condition)
				)

	In the first case, ORACLE executes the subquery once and 
creates a temporary table with no indexes and compares each row of the 
temporary table with each of the rows generated from the main query.

	In the second case, the subquery is executed once for each of 
the rows generated from the main query.  No temporary table is created.
All indexes defined by the user are available during execution of these
queries.

	Given this, Case 2 is much faster than Case 1.

	I was wondering if anybody out there knew the manner in which SQL
has been implemented in the other RDBMS's around -- UNIFY, INFORMIX, INGRES etc.

	Certainly seems like this would be the case in all these packages
unless of course, some of them provide the capability of creating an 
index for the temporary table too.

Thanks,

E.G.Nadhan

{amdahl|att|cbosgd|spl1|sun|uwmcsd1|yclept|nucsrl} !laidbak!egn