Buffer Busy Waits – Reverse Key Index – Demo

We discussed earlier about indexing, and specifically reverse key indexing.

I mentioned that is a solution for buffer busy waits on numerical consecutive inserted keys (like the sequence based generated ones) .

This post will test that solution.

For regular index test, I’ve created the following table, sequence and associated index:

create table test_tb
( row_id number
, text varchar2(100));

create sequence test_seq start with 1 increment by 1 cache 10000;

create index test_nidx on test_tb(row_id);

and a simple procedure that will insert the rows:

create or replace procedure insert_n (p_rows number default 10000) as 
begin
 
 for i in 1..p_rows loop
 insert into test_tb
 select test_seq.nextval, 'test row '||test_seq.currval from dual;
 
 end loop;
 commit;
end;
/

 

For the reverse key index test:

create table test_tb2
( row_id number
, text varchar2(100));

create sequence test_seq2 start with 1 increment by 1 cache 10000;

create index test_ridx on test_tb2(row_id);

and a simple procedure that will insert the rows:

create or replace procedure insert_r (p_rows number default 10000) as 
begin
 
 for i in 1..p_rows loop
 insert into test_tb2
 select test_seq2.nextval, 'test row '||test_seq2.currval from dual;
 
 end loop;
 commit;
end;
/

To test the effect of concurrent inserts, I’ve executed both procedures from 3 sessions (please note in my case number of sessions is small, but will still show us the impact of the two types of indexes):

execute insert_n(100000);

execute insert_n(100000);

Now looking at the buffer busy waits we had we see a reasonable benefit already of the reverse key index:

buffer busy waits - SQLbuffer busy waits

select object_name, value, statistic_name,object_type
from v$segment_statistics
where owner='C##INSIGHT'
and object_type='INDEX'
and statistic_name='buffer busy waits'
and object_name like 'TEST%'
;

 

 

 

 

 

Oracle Reverse Index

This is one of the least used Oracle indexes I’ve encountered in a data-warehousing environment. However, a very good instrument in performance problems on OLTPs.

To understand a reverse key index, we must first look at the regular b-tree index on a numerical key, and its storage in the database memory. The index will store, in ascending sort order, all it’s keys on disk. If we consider a key generated by a sequence, then any new value for that key will therefore be inserted in the last database block.

On a high frequency insert setup system in this type of case, you will encounter the buffer busy waits event one too many times.

To see the test on this events, please see post on Buffer Busy Waits – Reverse Key Index – Demo

Now, on considering a reverse key index, the key stored is actually the reversed value. For instance, for values 12, 13, 14 the stored keys will be 21, 31 and 41. This type of index will scatter its consecutive key inserts into multiple blocks, removing the hot-block problem regular keys have.

!!! Please note there is a downside to this approach, meaning the index will no longer allow range scans.

How to create a reverse key index:

create index INDEX_NAME on TABLE_NAME(INDEX_KEY) reverse;

It is also possible to change an existing index, making it reverse:

alter index INDEX_NAME rebuild reverse;

reversed btree

And also to revert a reverse key index into a regular index:

alter index INDEX_NAME rebuild noreverse;

regular btree