Re: Question: select that just detects a matching row

Chris <netac_at_worldnet.att.net> asks:

> I have many places in my application where the user can enter some
> search criteria, a date lets say, and then makes a request against a
> table just to see whether any rows matching the criteria are present. In
> this part of the application, I'm not interested in actually getting the
> data from the table, or even how many rows match the criteria, I just
> want to see if some row in the table matches the criteria. What I'm
> doing now of course is a "select count(*) from table_name where column
> name = search_criteria". I don't need the total count, and I've got a
> performance problem with running this query against the tables. Is
> there any function that's faster that this that will stop and return a
> "true" value when it encounters the first row that matches the where
> clause, instead of scanning through all rows and counting them up, in my
> case unneccesarily? Any ideas would be appreciated...

This should be the fastest method:

select 'x' from dual
where exists( select 'x' from table_name where column_name = criteria);

If the query succeeds, there is at least on row which matches the criteria.
If the query fails, there are no matching rows.

-- 
Bob Poortinga (mailto:bobp_at_tsc.com)
Senior Database Consultant
Technology Service Corp.
Bloomington, Indiana USA