RSS Feed

Method R Discussion Group

Reverse key indexes are generally usefull while we are doing heavy INSERTS and SELECT operations on a particular table. My question here is
"Which columns of the table needed reverse key indexes? "

Bcause in my case SELECT operations are working with the columns which one is not used by INSERT operations. 
"Will the reverse key indexes help here?" I don't think it will be.

And tell me what kind of optimization  method will be suitable for me?

The statement you made is true in cases where you have a lot of competition for updating a very popular block (for example, if you have 'buffer busy waits' waits for a small set of blocks). It can be difficult to know which blocks these will be at application design time. It's knowledge you can sometimes have only after some operational measurement experience with your actual production workload.

Which columns need reverse key indexes? The one (maybe "oneS", but probably only one) that will allow you to separate popular data across different index blocks. One downside, of course, is that you'll lose the benefit of data co-locality that gives you better likelihood of a given block's being cached in the database buffer cache.