Re: Not null and index

From: Jan Krueger <jk_at_stud.uni-hannover.de>
Date: Thu, 20 Sep 2007 14:43:30 +0200
Message-ID: <46f26a60$0$29372$4c56b896@news-read1.lambdanet.net>

Jan Krueger wrote:
> 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'
I gave the wrong order of parameter, should read nvl2(process_date, NULL, 'Y')
if process_date IS NULL => 'Y'
if process_date NOT NULL => NULL

Jan Received on Thu Sep 20 2007 - 07:43:30 CDT