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: @OracleSK
Orlando L <oralrnr@xxxxxxxxx> 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
//www.freelists.org/webpage/oracle-l