That is interesting territory. First, it is germane to consider whether you
mean an actual NULL value or a specific data model mapping of some
particular known value to NULL in the use case of it being a highly dominant
value.
If it is an actual NULL, we now have the interesting device of a functional
index where you could in fact map a NULL to a specified value for inclusion
in the index. This does have the requirement that there is some value in the
domain that you can safely designate so it does not collide with any real
value. Still, for actual NULLs I could see a use case for wanting some list
of actual values AND any nulls and the set to return being a tiny fraction
of the total table. Being able to designate an index as "including" NULLs in
such a case, used with the predicate structure of equals, range, or list,
and "OR IS NULL" might be an efficient plan if possible in the Oracle
engine. Functionally that list is values of known interest plus values as
yet unassigned (that might become one of your values of interest if become
known.)
For the mapped dominant value NULL the presumption is the table scan will be
more efficient or you would not have undertaken the special mapping.
I'm still a cup of coffee short and working on the West Coast to East Coast
time shift today, but I think this makes sense as an enhancement request.
Good luck on that Iggy!
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Iggy Fernandez
Sent: Sunday, April 19, 2015 3:03 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: index columns
I disagree with the Oracle decision not to index nulls. Perhaps it could be
a developer choice e.g. an option called "INCLUDING NULLS".
Perhaps the option will become available some day (the day the wolf dwells
with the lamb; and the leopard lies down with the kid; and the calf and the
young lion and the fatling together; and a little child leads them) but, in
the infinite interim, could I unhesitatingly advise the use of composite
indexes as a workaround? It appears (to me) that the primary key is a good
candidate for inclusion in indexes to avoid a round-trip to the table to
join to another table.
Iggy
_____
Date: Sun, 19 Apr 2015 14:27:43 -0400
From: dmarc-noreply@xxxxxxxxxxxxx
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: index columns
On 04/19/2015 11:41 AM, Mark W. Farnham wrote:
Nice thread. A side note regarding when a highly dominant value is present,
be it 99/1 or 80/2/2/2/2/2/2/2/2/2/2 or <you get the idea>,
then it is often useful to define the physical storage of the dominant value
as NULL. (You can interpret a mapping of NULL to a value notwithstanding
that the RDBMS must not without an NVL call).
When this can usefully be done a few things happen:
1) If it is a single column index, the index becomes relatively tiny AND
the CBO automatically has to avoid the index when searching for NULL,
because NULLs are not even in the index. Thus you either get a more
appropriate index selection or an appropriate full table or partition scan
set scan.
Also, in this case hash clustering or partitioning can be very beneficial.
People frequently forget that indexing is not the only strategy capable of
improving performance.
--
Mladen Gogala
Oracle DBA
http://mgogala.freehostia.com
