cancel
Showing results for 
Search instead for 
Did you mean: 

Index Range Scan Performance & Column Order on Oracle 11g (11.2.0.2)

PH
Participant
0 Kudos

Hello,

I have the following execution plan:

The WHERE clause actually has an equal condition for each colum of index /BIC/DWPFRM001B~01.

The index statistics look as follows:

I am wondering, if the execution of the statement would be faster, if the column order in the index is arranged in such a way, that the columns are ordered according to their selectivity, putting the most selective column (that is SID_WP00EXTI1) at first position.

Does the column order of an index really matter if all of the columns are queried with an equal condition?

If yes, what's the technical reason behind it?

Thanks for any help in advance!

Best regards,

Philipp

Accepted Solutions (1)

Accepted Solutions (1)

stefan_koehler
Active Contributor
0 Kudos

Hi Philipp,

> Does the column order of an index really matter if all of the columns are queried with an equal condition?

No and maybe yes

No in case of evaluating the predicates within the index structure (= finding the corresponding row IDs).

The branch index entries contain column entries based on all indexed columns, or at least on as much as is necessary to uniquely identify the required navigational path. Therefore, Oracle can directly navigate to the leaf block…

Richard Foote (a well known Oracle index specialist) has written a blog post with demo about this: It’s Less Efficient To Have Low Cardinality Leading Columns In An Index (Right)?

Maybe yes in case of clustering factor, but this is not related to the index access itself. It is much more about how the table data is ordered regarding the corresponding index (or the other way round). Richard Foote also posted a blog about this: Clustering Factor: A Consideration in Concatenated Index Leading Column Decision (Sweet Thing)

Depending on the table structure (and data) and SQLs - maybe an IOT would also be an option to reduce the corresponding I/Os to table " /BIC/DWPFRM001B". Looks like a BI table and so need to be verified if applicable or not in any case.

Regards

Stefan

PH
Participant
0 Kudos

Hi Stefan,

thanks for your answers. They help a lot.

And yes, you're right: it's a BI dimension table of an InfoCube. I don't think I have any chance to configure that as an IOT.

I will read through the blogs you have mentioned... let's see what additional infos they reveal

Regards,

Philipp

Answers (1)

Answers (1)

0 Kudos

regarding the field order, you also have the SAP note 1293807 Case Studies for Application Developement on Oracle DBs. And the order

The order of the fields in the index can determine its compression level