RE: Indexing NULL in the Oracle Database, is this the best practice?
- From: "Reen, Elizabeth " <dmarc-noreply@xxxxxxxxxxxxx> (Redacted sender "elizabeth.reen" for DMARC)
- To: "'Sayan Malakshinov'" <xt.and.r@xxxxxxxxx>
- Date: Fri, 30 Dec 2016 14:16:14 +0000
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@xxxxxxxxx]
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@xxxxxxxx<
mailto:elizabeth.reen@xxxxxxxx>> 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@xxxxxxxxxxxxx<
mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[
mailto:oracle-l-bounce@xxxxxxxxxxxxx<
mailto:oracle-l-bounce@xxxxxxxxxxxxx>] 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@xxxxxxxxx<
mailto:jcdrpllist@xxxxxxxxx>> 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=>
Other related posts:
- » Indexing NULL in the Oracle Database, is this the best practice?- Juan Carlos Reyes Pacheco
- » RE: Indexing NULL in the Oracle Database, is this the best practice?- Mark W. Farnham
- » Re: Indexing NULL in the Oracle Database, is this the best practice?- Mladen Gogala
- » RE: Indexing NULL in the Oracle Database, is this the best practice?- Reen, Elizabeth
- » Re: Indexing NULL in the Oracle Database, is this the best practice?- Sayan Malakshinov
- » Re: Indexing NULL in the Oracle Database, is this the best practice?- Luis Santos
- » RE: Indexing NULL in the Oracle Database, is this the best practice?- Reen, Elizabeth
- » Re: Indexing NULL in the Oracle Database, is this the best practice?- Mladen Gogala
- » Re: Indexing NULL in the Oracle Database, is this the best practice?- Sayan Malakshinov
- » Re: Indexing NULL in the Oracle Database, is this the best practice?- Sayan Malakshinov
- » RE: Indexing NULL in the Oracle Database, is this the best practice? - Reen, Elizabeth
- » Re: Indexing NULL in the Oracle Database, is this the best practice?- Michael D O'Shea/Woodward Informatics Ltd
- » Re: Indexing NULL in the Oracle Database, is this the best practice?- Mladen Gogala
- » RE: Indexing NULL in the Oracle Database, is this the best practice?- Reen, Elizabeth
- » Re: Indexing NULL in the Oracle Database, is this the best practice?- Michael D O'Shea/Woodward Informatics Ltd
- » Re: Indexing NULL in the Oracle Database, is this the best practice?- Lothar Flatz
- » Re: Indexing NULL in the Oracle Database, is this the best practice?- Mladen Gogala
- » Re: Indexing NULL in the Oracle Database, is this the best practice?- Mladen Gogala
- » Re: Indexing NULL in the Oracle Database, is this the best practice?- Michael D O'Shea/Woodward Informatics Ltd
- » RE: Indexing NULL in the Oracle Database, is this the best practice?- Reen, Elizabeth