Consider Reverse Key Index

Well this post was inspired by seeing some reverse index in our environment to avoid contention (May be tested with small set of data and might not have observed issues), which was pretty good but as we know this has very ugly side effects. Most of us know about it but this is just an reminder to myself and use reverse key indexes with extreme caution otherwise can lead to wild wild west.

So, as mention earlier there are few very nasty side effects of reverse key index and let’s start with some test. 1st performance test case with select queries based on reverse index, oh by the way i am not going to mention that reverse key index can’t be used with range predicates as we know.

Let’s start by creating a test table from dba_objects


create table REV_TEST parallel 4 tablespace soe as
select rownum id,x.owner,x.object_name,x.object_id,x.object_type,x.created
from dba_objects x ,(select rownum from dual connect by level <=100) y;

create unique index IX_REV_TEST on REV_TEST(ID) ;
exec dbms_Stats.gather_table_stats(null,'REV_TEST',cascade=>true,degree=>4);

declare
name rev_test.object_name%type;
begin
for i in 50000..60000 loop
select object_name into name from REV_TEST where id=i;
end loop;
end;
/

Now With first test case without reverse index, here are the details for that for loop with first showing size, leaf blocks and clustering factor


Non Reverse

SEGMENT_NAME                 MB     BLOCKS
-------------------- ---------- ----------
IX_REV_TEST                1472     188416
REV_TEST              5634.4375     721208

CLUSTERING_FACTOR
-----------------
           900381

   LF_BLKS   PCT_USED
---------- ----------
    182577         90

Before Any Query

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10001      0.51       0.56          0          0          0           0
Fetch    10001      0.14       0.12        113      40004          0       10001
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    20003      0.66       0.69        113      40004          0       10001

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  TABLE ACCESS BY INDEX ROWID REV_TEST (cr=4 pr=4 pw=0 time=1808 us cost=3 size=31 card=1)
         1          1          1   INDEX UNIQUE SCAN IX_REV_TEST (cr=3 pr=3 pw=0 time=1323 us cost=2 size=0 card=1)(object id 109599)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        2        0.00          0.00
  db file sequential read                       113        0.00          0.

As, you can see that we did approximately same amount of IO to get that equality key. Now, let’s drop this table and recreate same data but using reverse key index.


SEGMENT_NAME                 MB     BLOCKS
-------------------- ---------- ----------
IX_REV_TEST                1472     188416
REV_TEST              5634.4375     721208

CLUSTERING_FACTOR
-----------------
         83834113

   LF_BLKS   PCT_USED
---------- ----------
    182716         90

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10001      0.04       0.58          0          0          0           0
Fetch    10001      2.49       4.72      10342      40004          0       10001
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    20003      2.54       5.31      10342      40004          0       10001

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  TABLE ACCESS BY INDEX ROWID REV_TEST (cr=4 pr=4 pw=0 time=3668 us cost=3 size=31 card=1)
         1          1          1   INDEX UNIQUE SCAN IX_REV_TEST (cr=3 pr=3 pw=0 time=3171 us cost=2 size=0 card=1)(object id 109598)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                     10342        0.01          4.13

As, you can see from above output you’r clustering factor went to the roof, and now all your key are scattered all over the place and you have to do more IO’s to get that same work done and if this grows beyond your cache imagine what would happen, you’r cache will constantly flush in flush out blocks and even good blocks.

This is one case what about insert performance, because all we did this to avoid right hand tree contention reduction. While doing this let’s see what we have introduced here. First without reverse key


insert into rev_test select 82221812+rownum id,x.owner,x.object_name,x.object_id,x.object_type,x.created
    from dba_objects x

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.02          0          0          0           0
Execute      1      0.47       0.52       1622       5355      13497       91156
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.49       0.54       1622       5355      13497       91156

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                       100        0.00          0.04
  db file scattered read                          1        0.00          0.00
  direct path read                                6        0.00          0.01
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        7.43          7.43

SEGMENT I/O WAIT SUMMARY
~~~~~~~~~~~~~~~~~~~~~~~~
                                                                 Wait   Times    Average        Max          Average
 #      Obj  Type  Segment Name(1)         Event Name         Time(2)  Waited  Wait Time  Wait Time  Blocks   Blocks
--- ------- ------ ---------------- ------------------------ -------- ------- ---------- ---------- ------- --------
 1:  109593 TABLE  TARAL.REV_TEST   db file sequential read     0.032      72   0.000441   0.002966      72        1
 2:      18 TABLE  SYS.OBJ$         direct path read            0.011       6   0.001901   0.006582      87     14.5

As, you can see we have inserted about 91k rows and our top io wait just not that bad, let’s see if we have reverse key index


insert into rev_test select 82312968+rownum id,x.owner,x.object_name,x.object_id,x.object_type,x.created
	from dba_objects x 

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.02          0          0          0           0
Execute      1      4.76      12.94      27508       4778     282675       91156
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      4.78      12.97      27508       4778     282675       91156

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                     25986        0.05          9.99
  db file scattered read                          1        0.00          0.00
  direct path read                                6        0.00          0.00
  log file switch completion                      1        0.01          0.01
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        3.02          3.02

SEGMENT I/O WAIT SUMMARY
~~~~~~~~~~~~~~~~~~~~~~~~
                                                                   Wait   Times    Average        Max          Average
 #      Obj  Type   Segment Name(1)          Event Name         Time(2)  Waited  Wait Time  Wait Time  Blocks   Blocks
--- ------- ------ ------------------ ------------------------ -------- ------- ---------- ---------- ------- --------
 1:  109603 INDEX  TARAL.IX_REV_TEST  db file sequential read     9.966   25917   0.000385   0.053896   25917        1
 2:  109593 TABLE  TARAL.REV_TEST     db file sequential read     0.023      59   0.000385   0.001039      59        1

Now we have introduced much bigger problem with this. So, next time if you think of reverse key index consider all this issues. If anyone have anything more to say or can provide more information then i will be happy to include it

About these ads

About Taral
I am Rookie to Oracle Technology so let's see where it goes

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: