Re: Indexing NULL in the Oracle Database, is this the best practice?
Date: Thu, 29 Dec 2016 17:52:16 -0500
Message-ID: <2863f93e-d201-00e1-9de8-ac36294e17ae_at_gmail.com>
I am not a big fan of using semi documented functions like that, because
developers usually don't know anything about them. Queries are written
by developers, and not by DBA personnel. DBA personnel writes perfect
queries by definition, because they are the ones who judge them.
Unfortunately DBA folks usually don't write applications. That is just
like the tears of Chuck Norris. They are told to cure cancer, but he
never cries.
And no, I am not a DBA. I don't have a database that I am responsible
for. And I am not Chuck Norris, either.
On 12/29/2016 12:54 PM, Sayan Malakshinov wrote:
> 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 <mailto: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
> <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
-- Mladen Gogala Oracle DBA http://mgogala.freehostia.com -- http://www.freelists.org/webpage/oracle-lReceived on Thu Dec 29 2016 - 23:52:16 CET