Re: Query that fetch whether any record exist

Maoz Mussel wrote in message <>...
>What is the fastest method to check whether ANY record satesfy the
>WHERE clause? For example, the following query:
> SELECT COUNT (*) FROM tableX
> WHERE ...
>May require full table scan in order to return number of records with the
>specified criteria. Same with any MIN function usage, while all I want is
>a boolean value specifying whether any record exist, i.e. one that will
>stop execution once the first record is found.
>
You can use

SELECT COUNT (*) FROM tableX
where ...any condition
and rownum < 2

which will return 0 if no rows match or 1 if any do. In all versions after v6 this will stop as soon as one record is found. In v6 I seem to remember the query would continue throughout the entire table rejecting each row because the rownum was too high. Received on Sun Jan 09 2000 - 22:57:51 CET