At 08:13 AM 12-04-02 +0000, you wrote:

>Say I have a table of a million unindexed records. The first record
>and the last record are the only fields that match my select criteria
>in the code below:
>
>if (exists (select 1 from bigtable where criteria))
>then <reselect and process> ...
>
>Will the EXISTS test be satisfied as soon as the first row matches,

Yes

>or will it only be satisfied after the server has walked through the
>whole table to get the selected subset?

EXISTS() isn't fetching a subset. In the select, it just tests the row it
is looking at and either exits (because it found the match) or moves on and
tests the next row.

>I'm just wondering if there is any advantage in always using FIRST
>when testing for existence. With this:
>
>if (exists (select first 1 1 from bigtable where criteria))
>then <reselect and process> ...
>
>would it still walk the entire table or quit after fetching the first
>row?

It would have to create a complete subset in order to sort it. It is NOT
an economical alternative to EXISTS(). It's illogical, too. A row either
exists or it doesn't - exists() doesn't have to sort data, doesn't have to
create any I/O structures. All it has to do is *find* one single candidate
row that matches all of the WHERE criteria and then go home.

cheers,
Helen


All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________