Tuesday, July 13, 2004
A question was asked on Oracle-l regarding indexes and column order. Specifically
1 Comments
Saw a table where the "order of query" on the table and the "order of key"
are different . For faster results shouldn't they be in the same order.
for eg:
Select ... where A.ID = ... and A.Name = ...
The Key order on this table A NOW is Name & ID Order .
Shouldn't they be in the order ID , Name ?..
I have a script that shows the same execution plan regardless of order. The better bet is to put the column used most often first.
1 Comments:
Hey Niall,
I agree with the results of your script. However, I tend to use index compression and order the columns in the index according in order to gain the best compression. Usually, I do a
select count(distinct col2),count(distinct col2) from t
,place the columns that have the lowest count nearest the beginning of the index and compress depending on the results of the counts in the query.
I have seen performance gains of this approach when compared to the uncompressed index apporach. I don't have anything I can post to this effect though.
Mat.
Post a Comment
I agree with the results of your script. However, I tend to use index compression and order the columns in the index according in order to gain the best compression. Usually, I do a
select count(distinct col2),count(distinct col2) from t
,place the columns that have the lowest count nearest the beginning of the index and compress depending on the results of the counts in the query.
I have seen performance gains of this approach when compared to the uncompressed index apporach. I don't have anything I can post to this effect though.
Mat.
