RE: Need help to rewrite sql statement to improve performance

>We have SQL statement use "is NOT null" and this
>sql
>statement use 2.5 million memory I/O. Does their
>has
>way to improve it? Thanks.
>
>select count(*) FROM EMP_TABLE
> where hire_date is not null
> and work_date is not null
>~

Mike,

  Since NULL values are stored (and therefore have a rowid) only when squeezed between not NULL values, Oracle doesn't expect to find them and always go for a full scan, in CBO as in RBO. An exception might be bitmap indexes, but I must confess to a dreadful lack of experience with these ones. Classic workarounds are using values which are not in your table ( ... hire_date > to_date('14-OCT-1066', 'DD-MON-YYYY') ...) which may (possibly with a little push) incite Oracle to scan an index, or making columns NOT NULL and using a default date by which time we'll all be six feet under. Side effects, it may badly inflate the size of your indexes.

Regards,

Stephane Faroult
Oriole

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriolecorp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).