In Oracle, there is an option to create index entries as reversed, which is called reverse key indexes. Oracle stores the index entries as their bytes reversed, except rowids.
Here I have explained the cases where we can use reverse key index which will improve performance.
When we have to use reverse key index?
1. Your database must be in RAC environment
2. You have a column populated by an increasing sequence
3. You delete some old rows from the table frequently
4. Do not do range scan on the reverse key indexed column.
5. You have contention issues on index blocks.
If you have column populate by increasing sequence, the new entries come to the same block when you are inserting. This will lead to contention for the same index blocks between nodes when concurrent sessions trying to insert rows from different RAC instances. But when you use reverse key index, the new index entries will go to different blocks so that contention will be reduced.
If you are deleting some old rows, the blocks from a normal index on that column will have some used and some empty space in them, but they will not be put on the freelist because they are not completely free. That empty space will not be used because the sequence values are always increasing and they will not go to those old blocks because of that. You will be able to use that space for different values with reverse key indexes.
If your application is running on high buffer busy waits for the index segment, lets say more than 100 waits for a 5 minute period. This is you can find out when you run statspack report.
To change an existing index as a reverse key index you can use the alter index statement.
alter index indexname rebuild reverse;
After this change you can notice a huge change in index size also in the program run time.
Here I have explained the cases where we can use reverse key index which will improve performance.
When we have to use reverse key index?
1. Your database must be in RAC environment
2. You have a column populated by an increasing sequence
3. You delete some old rows from the table frequently
4. Do not do range scan on the reverse key indexed column.
5. You have contention issues on index blocks.
If you have column populate by increasing sequence, the new entries come to the same block when you are inserting. This will lead to contention for the same index blocks between nodes when concurrent sessions trying to insert rows from different RAC instances. But when you use reverse key index, the new index entries will go to different blocks so that contention will be reduced.
If you are deleting some old rows, the blocks from a normal index on that column will have some used and some empty space in them, but they will not be put on the freelist because they are not completely free. That empty space will not be used because the sequence values are always increasing and they will not go to those old blocks because of that. You will be able to use that space for different values with reverse key indexes.
If your application is running on high buffer busy waits for the index segment, lets say more than 100 waits for a 5 minute period. This is you can find out when you run statspack report.
To change an existing index as a reverse key index you can use the alter index statement.
alter index indexname rebuild reverse;
After this change you can notice a huge change in index size also in the program run time.
No comments:
Post a Comment