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