On Jun 15, 11:07 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net>
wrote:
> 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
no apologies necessary --I REALLY appreciate the help. There is no
index on this column. As a side note, I'm testing for the presence of
null, because if found, I update all 'null' values with '-1'. I'm
doing this because I intend to use this column as part of a primary
key constraint.
Received on Fri Jun 15 2007 - 13:20:02 CDT
