Re: Not null and index

From: Jan Krueger <jk_at_stud.uni-hannover.de>
Date: Wed, 19 Sep 2007 20:37:11 +0200
Message-ID: <46f16bc7$0$29371$4c56b896@news-read1.lambdanet.net>

Maxim Demenko wrote:
> Charles Hooper schrieb:

>> How would you index NULL values?  Function based index?
>>
>> Thanks again for your contribution.
>>
>> Charles Hooper
>> IT Manager/Oracle DBA
>> K&M Machine-Fabricating, Inc.
>>

>
> One of alternatives ?
>
> SQL> create index emp_comm on emp(comm,1);
>

Interesting idea. In case you are only looking for null values, e.g. because that's an indication these rows need processing, you may want to use a function based index

create index ... on table(nvl2(process_date, 'Y', NULL));

and use it like
select ...
where nvl2(process_date, 'Y', NULL) = 'Y'

Jan Received on Wed Sep 19 2007 - 13:37:11 CDT