Home » RDBMS Server » Performance Tuning » reverse key index
reverse key index [message #256989] Tue, 07 August 2007 05:47 Go to next message
Messages: 95
Registered: February 2007

For the below scenario. which index will be better.

Tab1 col1,col2,col3,col4,col5,col6. (pk key (col1,col2))

Instead of creating a Btree index on



I am creating a reverse key index on Tab1 with below cols

(col3,col4,col5,col6) reverse key index.

But in query where clause it appears in different way

1.where col4='a' and col5='9'and col6='v';

2.where col6='v' and col5='9';

we have to create a reverse key index or btree index in the order which they appear in where clause??

Thanks in advance

Re: reverse key index [message #257029 is a reply to message #256989] Tue, 07 August 2007 07:34 Go to previous messageGo to next message
Messages: 41
Registered: January 2007
Hi donind,

I don't think you give very much information about the problem.
Why do you consider building a reverse index ?
Wouldn't it be an easy thing to try the different scenarios, by creating the different indexes ?
Then you could run the queries against the data and see which is performing best. (Remember this could be different on another version of the database if you use the cost based optimizer)
After all you can easily create and drop indexes as opposed to entering data.

best regards
Carl Bruhn
Re: reverse key index [message #257261 is a reply to message #257029] Tue, 07 August 2007 22:14 Go to previous message
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
What do you think a Reverse-Key index is?

It has nothing to do with the column sequence. It reverses the byte-order of the physical index data to reduce block-contention between processes that insert rows with similar values.

Both of your queries use col5 and col6 in equals clauses, so an index on (col5, col6) or (col6, col5) would work for both of them.

If a query on col5 and col6 returns a lot of rows, then the first query would benefit from adding col4 to the end of the index. ie. (col5, col6, col4). This would have almost no effect on the other query.

Ross Leishman
Previous Topic: Performance Slow - Jobs
Next Topic: Creation of index
Goto Forum:

Current Time: Wed Oct 26 12:52:22 CDT 2016

Total time taken to generate the page: 0.11320 seconds