| reversed indexes [message #128040] |
Thu, 14 July 2005 15:35  |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
I have a issue with my developers.
A while ago we started to use reversed indexes for some of our PK indexes. And we have much better results. The hot spots are not too many, I have never seen an index scan due to them.
Now, the developers want to remove them as harmful, because they have read in an article that:"As a result of the key now being reversed, a range scan is no longer an option, because Oracle may need to scan the entire set of index leaf blocks hundreds of times, or full scan the whole index, before the complete range is satisfied. Therefore, reverse key indexes are useful only where single-row fetches are used to retrieve the inserted rows. If this is not possible because of application design, alternative methods for reducing index leaf block contention must be considered in a Parallel Server environment."
We are running ora10g - 10.1.0.4 on aix 5.2, high OLTP system.
My question is:
The reversed indexes helped us to reduce the contention, and the latches. I haven't seen a change in the execution plans after the change - to start making index scans, and it does the scan ranges... may be because we do not use range scans...
What is the down side and why I did not see it?
Are the reversing really "dangerous" for the performance?
Thanks a lot,mj
|
|
|
|
|
|
| Re: reversed indexes [message #128045 is a reply to message #128040] |
Thu, 14 July 2005 16:08  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
I wouldn't use the word "dangerous" to describe anything related to this discussion. You aren't talking about backup/recovery or disaster data guard situations or truncating tables.
You can in fact still range scan an index that has been reverse keyed. The issue is that you are having to scan more index leaf blocks to get a given range of values. Possibly all the leaf blocks, meaning a full scan. So the cost of doing so increases because your values are more spread out, and this increased cost may tell the CBO to choose an alternate path and not use the index. But maybe not, maybe the cost of that is still very acceptible to the CBO and the index is used.
But it all depends on the business value to the data that is in the reverse key index. Lets say you have a meaningless sequence driven pk, increasing from 1 to 100. Well if the index is reverse keyed, then those values will be spread around all over the place in different blocks -- which is the point, that is what reduces the contention on a single block. So key 1 will be on a different block than key 2 which is different than 3, etc.
But if it isn't reverse keyed, then key 1 and key 2 and key 3 will likely be on the same block. But of course that block can be a source of contention. But, if you often do a query where key value between 1 and 3, then that query can make better use of the index because it can do a tiny nice range scan.
But you have to ask yourself is it really meaningful for someone to want to know all the rows with a meaningless key id range between 1 and 3? If it isn't, and I would think it wouldn't be, then you have given up the reduced contention for nothing. If instead people say I want row where key = 2, then the reverse key index will perform just fine, and have less contention on the inserts.
Now, if the pk (or index key) is on a different column, like a date, where a range scan would be meaningful and common, then it is a whole different ballgame. I hope some of this rambling has helped. I would require that there be evidence of a definite performance issue before changing anything.
Work with the developers to either prove or disprove that performance is negatively impacted on selects to the point that it outweighs the gains in insert performance. Post some explain plans and tkprofs here and we can discuss further.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
