Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to improve this query?

Re: How to improve this query?

From: Chris <cs123._no_spam__at_telstra.com>
Date: Mon, 24 Nov 2003 11:45:16 GMT
Message-ID: <gbmwb.25063$aT.23972@news-server.bigpond.net.au>

"Sybrand Bakker" <gooiditweg_at_sybrandb.nospam.demon.nl> wrote in message news:21k3svgttf4ujetb51q1g3ugds9q6jj45t_at_4ax.com...
> On Fri, 21 Nov 2003 15:17:22 +0100, "Tom Miskiewicz"
> <miskiewicz2_at_yahoo.com> wrote:
>
> >Hello!
> >
> >I want to count those records in a table, where the field I specified is
not
> >empty. How to make such a query efficient. Is there a different/more
> >efficient way to achieve that, then select count(1) from mytable where
> >myfield != NULL? Myfield is a varchar2 field. I'm using Oracle 9.
> >
> >
> >Thanks
> >Thomas
> >
>
> != NULL won't work. You can't apply equality or inequality to null as
> NULL doesn't compare to anything. SQL implements three-valued logic:
> true, false and NULL. You'll need to use IS NOT NULL or > chr(0)
> provided the column is indexed.
>
>
> --
> Sybrand Bakker, Senior Oracle DBA

If all you want to test for is not null and your column is indexed then you should use
select count(*)
from table
where col is not null;

AFAIK this will use the index instead of a FTS Received on Mon Nov 24 2003 - 05:45:16 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US