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> 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] *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> 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

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