Path: utzoo!utgpu!jarvis.csri.toronto.edu!mailrus!uflorida!haven!vrdxhq!daitc!daitc.daitc.mil
From: jkrueger@daitc.daitc.mil (Jonathan Krueger)
Newsgroups: comp.databases
Subject: Re: ORACLE SQL subqueries performance
Keywords: SQL Subqueries IN EXISTS
Message-ID: <503@daitc.daitc.mil>
Date: 2 May 89 17:49:06 GMT
References: <2285@laidbak.UUCP>
Sender: jkrueger@daitc.daitc.mil
Reply-To: jkrueger@daitc.daitc.mil (Jonathan Krueger)
Organization: DTIC Special Projects Office (DTIC-SPO), Alexandria VA
Lines: 62
In-reply-to: egn@laidbak.UUCP (E. G. Nadhan)

In article <2285@laidbak.UUCP>, egn@laidbak (E. G. Nadhan) writes:
>	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.

If you convolute your code to get vendor-specific, version-specific
performance, at the cost of clean, maintainable, reliable code, why
stop at poorly expressed queries?  Why not hardwire your applications
to the tracks and cylinders on which your data currently reside?  Hey,
another article in Database Programming and Design laments SQL's
"inability" to specify this.  And why not tie your application to a
particular instruction set, a specific operating system, and within
that, a nontrivial and vendor-specific set of conventions for
navigating a shared address space with varying degrees of unsafety?
Yes, if you want CICS, you know where to find it.

>[How do other DBMS's handle this]

INGRES [5.0/05a (pyr.u42/04)] generates the following search strategy:

                        Join(XY)(CO)
                     /             \
                 Proj-rest         TABLEA
               /
          TABLEB

For which "case" does INGRES choose this strategy?  Either.  INGRES
has a query optimizer which recognizes that the two "cases" are
identities.

I would be interested in what vendor products fail to do this.  Is it
true that Oracle is thus handicapped?  Have you timed the two "cases"
on Oracle and come up with different performance results?  If so, with
what version, on what platform?

-- Jon
--