Re: How to improve this query?

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Tue, 25 Nov 2003 13:51:52 -0800
Message-ID: <1069797141.73258@yasure>

Frank wrote:

> lve wrote:
>

>> If you are using Oracle 9, you can use:
>> select count(*) from mytable where nullif(myfield,'') is not null
>>
>> Nullif will convert the empty strings to null and thus the statement
>> will cover both Null and empty string.
>>
>> lv
>>
>> "Chris" <cs123._no_spam__at_telstra.com> wrote in message 
>> news:<gbmwb.25063$aT.23972_at_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

>
>
> '' still is NULL:
> Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
> With the Partitioning, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.4.0 - Production
>
> SQL> select nvl('','Is it empty or is it NULL?') from dual;
>
> NVL('','ISITEMPTYORISITNULL?')
> ------------------------------------------------------------------------------
>
> Is it empty or is it NULL?
>

And that seems to hold true in another test too:

SQL> select *

   2 from dual
   3 where '' IS NULL;

D
-
X

SQL> select * from dual

   2 where '' IS NOT NULL;

no rows selected

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Tue Nov 25 2003 - 15:51:52 CST