Re: query enhancement

On Jun 15, 11:07 am, "fitzjarr...@xxxxxxx" <fitzjarr...@xxxxxxx>
wrote:
On Jun 15, 12:44 pm, "fitzjarr...@xxxxxxx" <fitzjarr...@xxxxxxx>
wrote:



On Jun 15, 12:22 pm, keeling <jkeelin...@xxxxxxxxx> 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.

.

Relevant Pages

  • Re: query enhancement
    ... The query, as it now stands, tests for the presence of 'null' in one ... select countfrom tableX where columnX IS NULL; ... Presuming you have an index this may produce an index fast full scan ...
    (comp.databases.oracle.server)
  • Re: query enhancement
    ... The query, as it now stands, tests for the presence of 'null' in one ... select countfrom tableX where columnX IS NULL; ... Presuming you have an index this may produce an index fast full scan ...
    (comp.databases.oracle.server)
  • perl 5.8 CGI DBM Oracle and ?
    ... I need to run a basic sql, query select "5 colums" from db.table where ... columnx = 123. ...
    (perl.beginners)
  • Re: Help with expression builder and IF Statements
    ... Put your query into Design Mode thne put the following in ... the nest available field column ... Gerald Stanley MCSD ... >I have a field which contains a numerical value (ColumnX) ...
    (microsoft.public.access.queries)
  • query enhancement
    ... 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 ... select countfrom tableX where columnX = null. ...
    (comp.databases.oracle.server)