Re: index columns
From: Mladen Gogala <mgogala_at_yahoo.com>
Date: Sun, 19 Apr 2015 14:27:43 -0400
Message-ID: <5533F39F.2040806_at_yahoo.com>
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.
>
>
Date: Sun, 19 Apr 2015 14:27:43 -0400
Message-ID: <5533F39F.2040806_at_yahoo.com>
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 -- http://www.freelists.org/webpage/oracle-lReceived on Sun Apr 19 2015 - 20:27:43 CEST