Performance Impact of Low Cardinality Leading Columns in an Index!!

Author: Richard Foote

Page: 1 2

Mostly we face the scenario when we have to order columns in a concatenated, multi-column index and most of us prefer avoiding low cardinality columns in front. Low cardinality columns have a small number of distinct values compared to the number of rows whereas high cardinality columns have large numbers of distinct values compared to the number of rows.

Suppose you have two columns COL1 and COL2. COL1 has a large number of distinct values whereas COL2 has a few number of distinct values. Following the common perception you will create an index as (COL1, COL2). The reason behind is that it is assumed that index (COL1, COL2) is far efficient than index (COL2, COL1). If you create an index as (COL2, COL1) then before Oracle finds the desired index entries it will scan through multiple index leaf blocks that contain low cardinality columns. Hence the performance is degraded.

Oracle Navigation to Desired Leaf Block:

You will be astonished to hear that in fact there is not much difference in above mentioned cases when both indexed columns are known and Oracle has to navigate to specific leaf block.

If all index column values are known before hand then Oracle is able to navigate directly to the leaf block. This is because column entries based on all indexed columns are present in the branch index or at least on that much indexed column as it is necessary to uniquely identify the required navigational path.

The high cardinality column should not necessarily be the column given leading column status as same pattern and cost is involved in navigation of Oracle to desired leaf block and this navigation does not depend on the ordering of low and high cardinality columns in the index.

Larger Branch Entries:

As discussed earlier there is no performance impact even if you use low cardinality indexes in front. However there is a slight overhead that you will face while dealing with index based on (COL2, COL1). The branch index entries will be larger as it will require both columns for the branch index entries but likely only the one column the other way around. As we know that only a small portion of overall index structure is taken by branch blocks and hence this slight overhead is unlikely to have effect on index height. Continued...

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Performance Gains by using Oracle Regular Expressions
   The power of Oracle Diagnostic Tools!
   Beware of major Oracle Failures: Are you Safe??
   Performance Gains by Managing Space: Segments, Tablespace & Datafiles!!