Re: Indexing NULL in the Oracle Database, is this the best practice?
- From: Sayan Malakshinov <xt.and.r@xxxxxxxxx>
- To: Juan Carlos Reyes Pacheco <jcdrpllist@xxxxxxxxx>
- Date: Thu, 29 Dec 2016 20:54:47 +0300
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@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
--
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org
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