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
