Re: query enhancement

On Jun 15, 12:44 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> 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

And that might not give the correct answer, after testing:

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

  COUNT(*)

       147

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

  COUNT(*)

       540

It indeed has an index fast full scan, but that isn't doing you any good since the answer is so far from correct.

My apologies for supplying an untested and summarily unsuitable initial response.

The question now becomes: is there an index on columnX?

David Fitzjarrell Received on Fri Jun 15 2007 - 13:07:29 CDT