Oh well, I've got it working with the IN now (code generated SQL) so - if it
ain't broke I aint gonna fix it. If I start getting complaints about searching
taking to long then maybe I'll have a look at INTERSECTS.
Anyway, thanks to Kerry Sainsbury for the initial solution.
Phil.
Aaron Scott-Boddendijk <[EMAIL PROTECTED]> on 03/11/99 20:10:37
Please respond to [EMAIL PROTECTED]
To: Multiple recipients of list database <[EMAIL PROTECTED]>
cc: (bcc: Phillip Middlemiss/NZ Forest Research Institute/NZ)
Subject: Re: [DUG-DB]: SQL from hell.... (was something else...)
> > Most Generic and maybe fastest:
> >
> > select Name
> > from Table
> > where GroupID in (1,7)
> > group by Name
> > having count(GroupID) = 2;
There are considerable references in the ORACLE texts about IN
being inefficient.
> > select Name
> > from Table
> > where GroupID = 1
> > intersect
> > select Name
> > from Table
> > where GroupID = 7
Dat's da one I would've used.
> > select Name
> > from Table
> > where
> > GroupID = 1
> > and Name in
> > (select Name
> > from Table
> > where GroupID = 7);
Given what is being done in this I suspect it's performance after
optimisation would be much the same as the intersect and this should be
relatively generic.
> > select Name
> > from Table t
> > where
> > GroupID = 1
> > and exists
> > (select Name
> > from Table
> > where
> > Name = t.Name
> > GroupID = 7);
My only comment here is to change the selected item in the Exists
selection to a single character constant since exists matches the record
on the rows located not the selected data. EG
SELECT
Name
FROM
Table t
WHERE
GroupID=1 AND EXISTS (
SELECT
'X'
FROM
Table
WHERE
Name = t.Name AND GroupID=7
)
;
Should functionally be the same but the database won't waste time
handling String data from the Name selected... Of course I'd hope that
the optimiser would remove it anyway but I can't see a reference to that
in 'ORACLE: The Complete reference' from ORACLE Press - Version 7.3
though (O8 is definitely faster so it could the optimiser is even better now).
--
Aaron Scott-Boddendijk
Jump Productions
(07) 838-3371 Voice
(07) 838-3372 Fax
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
