There is an easy solution for the non-indexing of NULL values.
Build a function-based-index on SYS_OP_MAP_NONNULL(xxx) -- sorry, I
probably spelled it wrong.
This -- undocumented -- function returns non-NULL values unchanged, but
maps NULL values to an invalid-but-not-null value of the appropriate
datatype. That means it can never collide with "actual" data.
Technically, as an undocumented function, its use (by us) is unsupported.
But it is used internally by Oracle, for example in the indexes on some
materialized views.
If you *really* want an index that includes NULL values, you can certainly
have one.
[Note: You can also use this function in comparisons, to give you NULL =
NULL semantics. I am pretty sure that this too is done internally for
materialized views, and undoubtedly for many other things.]
On Mon, Apr 20, 2015 at 10:15 AM, Mark W. Farnham <mwf@xxxxxxxx> wrote:
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
