Re: index columns

From: Tim Gorman <tim_at_evdbt.com>
Date: Fri, 17 Apr 2015 17:34:51 -0600
Message-ID: <5531989B.9070602_at_evdbt.com>

Consider the scenario where there are two columns, ACCOUNT_ID and YES_OR_NO, where ACCOUNT_ID is almost distinct and YES_OR_NO is populated with the "YES" in 99% of rows and "NO" in the remainder?

If both columns are used in a query, then clearly the ordering of the columns doesn't matter, as cited in Richard Foote's referenced blog posts.

But what if *only* the YES_OR_NO column is used in a query? If that column is leading the index, then it is quite possible that the optimizer might choose to perform an INDEX RANGE SCAN on that index rather than performing a FULL table scan, which would be disastrous for performance. However, if ACCOUNT_ID is the leading column, then that is far less likely to happen, although a rogue INDEX SKIP SCAN remains a remote possibility.

Column statistics, especially extended column statistics such as column groups (as described by Maria Colgan online here <https://blogs.oracle.com/optimizer/entry/how_do_i_know_what_extended_statistics_are_needed_for_a_given_workload>), can provide the optimizer enough information to prevent it from making an inappropriate decision in the scenario described above.

If column statistics or extended column statistics are not guaranteed to be in use and gathered correctly, then it is definitely worthwhile to consider cardinality when ordering columns in a concatenated index.

If your goal is that your trousers never fall down accidentally, then wear both a belt and suspenders.

On 4/17/15 16:44, Stefan Koehler wrote:
> Hi Orlando,
> oh a classic oldie and common myth :-))
>
> I think the only source i need to point to is Richard Foote's blog post about this:
> - https://richardfoote.wordpress.com/2008/02/13/its-less-efficient-to-have-low-cardinality-leading-columns-in-an-index-right/
> - https://richardfoote.wordpress.com/2008/02/15/clustering-factor-a-consideration-in-index-lead-column-decision-sweet-thing/
>
> Best Regards
> Stefan Koehler
>
> Freelance Oracle performance consultant and researcher
> Homepage: http://www.soocs.de
> Twitter: _at_OracleSK
>
>> Orlando L <oralrnr_at_gmail.com> hat am 18. April 2015 um 00:02 geschrieben:
>>
>> All
>>
>> My colleague and I got into a discussion about indexes. I feel that putting the most selective column first while creating multi column indexes is
>> the correct approach, followed by second most selective column as the second column in the index and so on. My colleague feels that the order does
>> not matter. Can someone clarify.
>>
>> OL
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Apr 18 2015 - 01:34:51 CEST