Re: index columns
Date: Sat, 18 Apr 2015 01:45:24 -0400
Message-ID: <CAAaXtLC29PeNR_KXtzvtcgA-420344j58StHZLP3Cg32Pi-sTA_at_mail.gmail.com>
Another consideration similar to Riyaj's...
... what if you have "families" of common queries with similar but not identical predicates. Let's say you have frequently run queries with predicates on the following sets of columns, and assume (for now) they are all equality predicates.
- [ A ]
- [ A C ]
- [ A B C ]
- [ A D C B ]
If you create a composite index on the columns [ B C D A ], the index might be usable (for all queries) via index skip-scans, but it definitely will not be "optimal" for *any* of the queries.
You can easily get "optimal" results by creating 4 indexes, ordering the columns exactly as I have above ...
... BUT, if you order the columns correctly, you can also get "optimal" (or at least very close to optimal) results from a single index on the columns [ A C B D ]. That is, when the columns in the index are ordered appropriately, all four "families" of queries will find ALL of their predicates on the leading edge of the index. The SAME index.
When you do this, not only do you improve performance of the SELECT statements accessing the table through the index, but you also improve the performance of DML statements inserting and modifying data, because they have fewer indexes to maintain.
Mileage on this will vary -- sometimes a lot.
If the result sets are all fairly small, you'll usually be okay. But if the result sets a larger, then the index CLUSTER_FACTOR comes into play. Reordering the columns in an index can change the clustering factor, perhaps drastically. You can make indexes redundant by ordering the columns well but if you get unlucky that change to the clustering factor could be big enough to make the optimizer stop using the index completely, for some queries.
As with the other considerations, there are almost no absolutes. No choice of column orders is always right. (Some, though, are always wrong!)
In general, range scans will be smaller (and skip-scans avoided) when columns used in equality filters are kept at the leading edge, followed by conditions used in range filters. Ordering the columns appropriately can allow a single index to efficiently resolve queries on data
On Fri, Apr 17, 2015 at 7:31 PM, Riyaj Shamsudeen < riyaj.shamsudeen_at_gmail.com> wrote:
> 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-lReceived on Sat Apr 18 2015 - 07:45:24 CEST