Path: utzoo!utgpu!jarvis.csri.toronto.edu!mailrus!ames!pacbell!att!laidbak!egn
From: egn@laidbak.UUCP (E. G. Nadhan)
Newsgroups: comp.databases
Subject: Re: ORACLE SQL subqueries performance
Keywords: SQL Subqueries IN EXISTS
Message-ID: <2297@laidbak.UUCP>
Date: 3 May 89 16:49:19 GMT
References: <2285@laidbak.UUCP> <503@daitc.daitc.mil>
Reply-To: egn@laidbak.UUCP (E. G. Nadhan)
Organization: Lachman Associates, Inc. Naperville, Il.
Lines: 58

In article <503@daitc.daitc.mil> jkrueger@daitc.daitc.mil (Jonathan Krueger) writes:

>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
>-- 

	Like I mentioned earlier, I did not perform this tests but was quoting
from an article in Database Programming and Design.

	To answer your question, I again quote from the article:

	The two cases that the article has taken into consideration are:

		Case 1:	SELECT staff_num,job_num,job_phase
			FROM   Time
			WHERE (Job_Num,Job_Phase)
				NOT IN 
				(SELECT Job_Num,Job_Phase
				 FROM Phase
				)
			;

		Case 2:	SELECT Staff_Num,Job_Num,Job_Phase
			FROM Time
			WHERE NOT EXISTS
				(SELECT 'X'
					FROM Phase
					WHERE Time.Job_Num = Phase.Job_Num
					AND   Time.Job_Phase = Phase.Job_Phase
				)
			;

Case 1 Performance figures:
===========================

	On an IBM 4331(small mainframe) in a VM/CMS environment, with no other
	users on the system, the Case 1 query processed for 30 minutes,
	25 seconds, selecting 19 rows.  With other users active, the 
	average time to complete was 37 minutes.  On the PS/2, the query took
	six minutes 34 seconds.  

Case 1 Performance figures:
===========================

	On the IBM 4331, the Case 2 query selected the identical 19 rows in one
	minute, five seconds -- 34 times faster than the Case 1 query.  When
	executed on the PS/2, this query finished in 13 seconds, a 30-fold
	improvement.



E.G.Nadhan 

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