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

Agreed the world is not perfect. I support systems with these issues and to me it is a design flaw. I even have code checking for null on columns where there is a constraint against it! ☹ I also have code which jumps through hoops trying to deal with it. While I have to live with it, I don’t have to accept it. Of course if all systems were perfect, none of us would have a job.

Liz

Long suffering DBA

From: Sayan Malakshinov [mailto:xt.and.r_at_gmail.com] Sent: Thursday, December 29, 2016 6:15 PM To: Reen, Elizabeth [ICG-IT]
Cc: Juan Carlos Reyes Pacheco; ORACLE-L Subject: Re: Indexing NULL in the Oracle Database, is this the best practice?

Liz,

> If it is not required then why are we doing this?
Unfortunately, the world is not perfect, and sometimes we need to search by NULLs :) Especially on popular "ORM-based universal systems" :D

PS. Hard-coded default values like "NOT SPECIFIED"/NA/etc vs NULLS is a too difficult topic, for example, I don't like "special" dates because of problems with such HIGH VALUES for CBO. Another example is "empty" fields vs "not specified" - in such cases we need another field for that, and so on...

On Fri, Dec 30, 2016 at 1:03 AM, Reen, Elizabeth <elizabeth.reen_at_citi.com<mailto:elizabeth.reen_at_citi.com>> wrote: I don’t see the need to use a function. We don’t need to fix data entry issues in the database. If it is required it there should be a constraint or default value. If it is not required then why are we doing this? Let us say this is an online store and we want the birthday so we can send a coupon. If they want the coupon, they give us their birth date. When we run the birthday greetings, we will get the customers who want them and not those who don’t. I don’t see a problem with it. If it is a HR system, they must have the birthday. Why allow a record to be created without it?

A function based index is a pain to maintain. You can’t shrink the table with that. You have to drop it, shrink, and recreate it. This means I can only do work on those tables during green zones, which are few and far between.

Liz Reen
Long suffering DBA

From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] On Behalf Of Sayan Malakshinov Sent: Thursday, December 29, 2016 12:55 PM To: Juan Carlos Reyes Pacheco
Cc: ORACLE-L
Subject: 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<https://urldefense.proofpoint.com/v2/url?u=http-3A__docs.oracle.com_cd_E16655-5F01_server.121_e17749_basicmv.htm-23DWHSG8216&d=DQMFaQ&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=OM2TvNmF5TtnQc6e70Ubnsuu6ACc0exEFwDB2HFhFwc&s=KIpWR7ydOVdyigmV_a8OxH0a_8xhUdU5YgmgrMzUuZI&e=>

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<https://urldefense.proofpoint.com/v2/url?u=http-3A__use-2Dthe-2Dindex-2Dluke.com_sql_where-2Dclause_null_index&d=DQMFaQ&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=OM2TvNmF5TtnQc6e70Ubnsuu6ACc0exEFwDB2HFhFwc&s=poQ__8AupDFLyAC0O5kFJndy9wQB1fecxXyXV8pg3Hc&e=>

--
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org<https://urldefense.proofpoint.com/v2/url?u=http-3A__orasql.org&d=DQMFaQ&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=OM2TvNmF5TtnQc6e70Ubnsuu6ACc0exEFwDB2HFhFwc&s=REbjLlhM4M_6ewNCHnYTnQ4xNXSP4MkSflzRdcgqKS8&e=>

--
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org<https://urldefense.proofpoint.com/v2/url?u=http-3A__orasql.org&d=DQMFaQ&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=gcw2Ey2jHLTt4OUp3_0aDqAJ9R6wBHLL3v0PwmFtPAk&s=QGynGOeIV2B-n26-uikPBL52YngbauhxnIn7_RTT0MU&e=>

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