Re: faster select

[Ed Crotty <73747.1247@COMPUSERVE.COM>]

Message text written by Louisa L Tripp
>On Wed, 29 Jan 1997, Angel Guzman wrote:

> Which of these selects would be more efficient/faster in comparing
> data from two different databases?
>
>   from tt_shop
>  where department = $dept
>    and shop in (select master-shop from ldsloc.dbo.tt_shop)
> !  and exists  (select master-shop from ldsloc.dbo.tt_shop
> !                where master-shop = shop)
>  order by department, division, branch, shop
>
> Angel
>
<

If the  ldsloc.dbo.tt_shop table is indexed on master-shop  the the exists
wil be faster.    A join between the two tables would likely be fastest.

An exists clause is a "filter" which is the executed for each row of the
"main"
query.  If this is an indexed query then it can be very efficient,
especially if the
number of rows in the "main" query is not enormous.

If the number of rows in the main query is very large, then a join would be
faster

Ex:

select a.*
from table1 a , table2 b
where a.key = b.key

if the tables are not equivalent  , say a.key is unique but b.key is not,
in Oracle7
you can use an "inline view" in place of a table

select a.*
from table1 a, (select distinct key from table2) b
where a.key = b.key

Ed Crotty
BALR Corporation
Oak Brook IL
(630)575-8200