Re: query enhancement

On Jun 15, 12:22 pm, keeling <jkeelin..._at_yahoo.com> wrote:
> I am attempting to enhance a query so as to avoid a full table scan.
> The query, as it now stands, tests for the presence of 'null' in one
> column; the return of 1 or greater rows will satisfy my test,
> therefore I'd like the query to stop after finding the first
> occurrence of null. present query is as follows:
>
> select count(*) from tableX where columnX = null.
>
> Any suggestions would be greatly appreciated.

Sorry, that query won't work as nothing equals NULL; I expect your count(*) will always be 0. You'd be better writing this:

select count(*) from tableX where columnX IS NULL;

which will produce an accurate count(*). You might try this:

select count(*)
from tableX
where exists (select 'x' from tableX where columnX is null);

Presuming you have an index this may produce an index fast full scan access path rather than your full table scan.

It's a thought.

David Fitzjarrell Received on Fri Jun 15 2007 - 12:44:42 CDT