Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How to improve this query?
"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