Re: Indexing NULL in the Oracle Database, is this the best practice?
Date: Thu, 29 Dec 2016 20:54:47 +0300
Message-ID: <CAOVevU55Cv8VMon=b6BGoS=Lmtea0CkmXm_bKTFTb5Vmr58ESw_at_mail.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)
where date_of_birth = :ANY_VALUE or (:ANY_VALUE is null and
date_of_birth is null)
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 :)
than
or something more ugly like
>
>
> 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-lReceived on Thu Dec 29 2016 - 18:54:47 CET