on 10-24-2013 8:04 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
76 | |
10 | |
10 | |
7 | |
7 | |
6 | |
6 | |
6 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.