Re: index columns

From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>
Date: Fri, 17 Apr 2015 16:31:39 -0700
Message-ID: <CAA2DszwDTLc_CKP6yxpAzi4nvwgXycovk115K=7wgnVZkgdVGQ_at_mail.gmail.com>

Hi,
 I fear that the context of your question is equally important to analyze it accurately.

  Do your queries specify predicates on all leading columns of the index ? Allow me to refine my question with a few examples: Consider that you are indexing on columns c1,c2, c3 in that order, c1 being more selective:

1. Let's say, application queries do not specify any predicate on c1, then the chosen execution plan will be either skip scan of the index or full table scan. I would say that your indexing strategy may not be optimal depending upon the data distribution. Skip scan on an index with highly selective leading column(s), usually, doesn't perform well.
2. Let's assume, frequently executed queries by the application specify a broad range on c1 column (such as c1 between 'a%' and 'm%'), but, specifies equality predicates on c2 and c3 column(s) ( thereby improving the selectivity of the combined predicates), then, I would inclined to think that the indexing strategy based upon selectivity alone is not optimal.
3. What if there is a data type difference between the bind variables defined in the application and the leading column c1? Applying a data type conversion function *can* make the c1 column unusable for predicate filtering.

  My point is that, these is no one-size-fits-all answer to your question ( i.e. without understanding the context of your question ) and so, you should consider the predicate usage also. If the application queries specify equality predicates on all three columns, then choose most selective columns as the leading columns, as TIm pointed out. If the predicates on leading columns are not specified or if the predicates on leading columns are inequality/range predicates, then you should consider the predicates carefully, while choosing the index column order.

Cheers

Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com - Specialists in Performance, RAC and EBS
Blog: http://orainternals.wordpress.com/ Oracle ACE Director and OakTable member <http://www.oaktable.com/>

Co-author of the books: Expert Oracle Practices
<http://tinyurl.com/book-expert-oracle-practices/>, Pro Oracle SQL,
<http://tinyurl.com/ahpvms8> <http://tinyurl.com/ahpvms8>Expert RAC
Practices 12c. <http://tinyurl.com/expert-rac-12c> Expert PL/SQL practices
<http://tinyurl.com/book-expert-plsql-practices>

<http://tinyurl.com/book-expert-plsql-practices>

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

Received on Sat Apr 18 2015 - 01:31:39 CEST