RE: index columns
Date: Mon, 20 Apr 2015 12:12:27 -0400
Message-ID: <03da01d07b84$cc90b700$65b22500$_at_rsiz.com>
True. There is an interesting discussion at
Unless I missed a memo though, this is indeed still unsupported. Perhaps simply documenting it and supporting it would accomplish Iggy’s request. Even without supporting it they could use it under the covers to support Iggy’s suggestion of making it a declarable option. (And then of course they’d have to tweak the CBO to understand it.)
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of MARK BRINSMEAD
Sent: Monday, April 20, 2015 11:21 AM
To: mwf_at_rsiz.com
Cc: Iggy Fernandez; ORACLE-L
Subject: Re: index columns
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_at_rsiz.com> 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Iggy Fernandez
Sent: Sunday, April 19, 2015 3:03 PM
To: oracle-l_at_freelists.org
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_at_freelists.org
To: oracle-l_at_freelists.org
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:
- 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 -- http://www.freelists.org/webpage/oracle-lReceived on Mon Apr 20 2015 - 18:12:27 CEST