> -----Original Message-----
> From: Ronald J Kimball [mailto:[EMAIL PROTECTED]]
> Sent: Monday, July 09, 2001 6:33 AM
> To: Tim Harsch
> Cc: M.W. Koskamp; Thomas A. Lowery; [EMAIL PROTECTED]
> Subject: Re: Checking for the existence of a certain row.
>
>
> On Sun, Jul 08, 2001 at 12:52:45PM -0700, Tim Harsch wrote:
> > but, does this (Sybase specific) query:
> > select 1 where exists
> > ( select * from some_table where some_column = 'some_value')
> >
> > and/or this Oracle specific query:
> > select 1 from dual where exists
> > ( select * from some_table where some_column = 'some_value')
> >
> > access all pages on the disk that contain those rows? Or, does it stop
> > after visiting the first row that matches?
>
> Regarding Oracle, it stops after the first row that matches. I
> tested this
> on a table with 50 million rows, with a where clause that would select 20
> million rows. The query returned in seconds. Here are the statistics:
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 4 db block gets
> 5 consistent gets
> 0 physical reads
> 0 redo size
> 393 bytes sent via SQL*Net to client
> 515 bytes received via SQL*Net from client
> 4 SQL*Net roundtrips to/from client
> 1 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
>
> On the other hand, with a where clause that selects 0 rows from the 50
> million row table, the query took 4 minutes. Here are the statistics for
> that:
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 359103 consistent gets
> 355080 physical reads
> 0 redo size
> 331 bytes sent via SQL*Net to client
> 449 bytes received via SQL*Net from client
> 3 SQL*Net roundtrips to/from client
> 3 sorts (memory)
> 0 sorts (disk)
> 0 rows processed
>
> I don't know if there's any way around that, though.
Don't think so, the database would clearly have to visit every row in order
to know there are none that match. But, I think the work you've done here
(which is way cool,thank you) shows that the exists method is clearly the
fastest way to do a lookup. The count method will have to visit every row
in order to return a result. So, now for the next question how about the
portability of this? Is exist available in all the major RDMS'? We know
for certain Oracle and Sybase, which presumes SQL Server 2000 (since it is
really just Sybase internals that MS bought).
PS was your column indexed?
>
>
> Ronald
>
