Re: Indexing NULL in the Oracle Database, is this the best practice?

Remember that bitmap indexes does include null values on index.

*--*
*Att*

*Luis Santos*

2016-12-29 15:54 GMT-02:00 Sayan Malakshinov <xt.and.r_at_gmail.com>:

> Juan,
>
> I'd prefer to create function-based index with SYS_OP_MAP_NONNULL, because
> it's easier to bind NULL into the queries like that:
> select * from dates d where sys_op_map_nonnull(date_of_birth) =
> sys_op_map_nonnull(:ANY_VALUE)
> than
> where date_of_birth = :ANY_VALUE or (:ANY_VALUE is null and
> date_of_birth is null)
> or something more ugly like
> where decode(date_of_birth,:ANY_VALUE,1,0) = 1
>
> :) But that is semi-documented now, since it has appeared in the Oracle
> 12c documentation: Choosing Indexes for Materialized Views
> <http://docs.oracle.com/cd/E16655_01/server.121/e17749/basicmv.htm#DWHSG8216>
>
>
> On Tue, Dec 27, 2016 at 5:24 PM, Juan Carlos Reyes Pacheco <
> jcdrpllist_at_gmail.com> wrote:
>
>> Hello, please :)
>>
>>
>> CREATE INDEX emp_dob ON employees (date_of_birth, *'1'*)
>>
>> http://use-the-index-luke.com/sql/where-clause/null/index
>>
>>
>
>
> --
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning engineer
> Oracle ACE Associate
> http://orasql.org
>

--
http://www.freelists.org/webpage/oracle-l