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

From: Mladen Gogala <gogala.mladen_at_gmail.com>
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-l
Received on Thu Dec 29 2016 - 23:52:16 CET

Original text of this message