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

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