| reverse key index [message #256989] |
Tue, 07 August 2007 05:47  |
donind
Messages: 95
Registered: February 2007
|
Member |
|
|
Hi
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
(col3,col4,col5,col6)
(col5,col6)
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
like
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  |
cbruhn2
Messages: 41
Registered: January 2007
|
Member |
|
|
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 |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
