Understanding Reverse Key Index In Oracle Database

fncbased_indx

Reverse B-TREE key indexes reverses the bytes of the key value in the index to reduce Index block contention.

Index block contention can cause :-

• buffer busy wait
• read by other session wait
• index splitting wait

Reverse key index works best during INSERTION (sequence/primary key).But reverse key index during UPDATION & DELETION is a costly affair.

Oracle actually stores index values in hexadecimal format, so for 20 instead of storing the value as C1,15,it will now store as 15,C1.

It’s hard to perform INDEX RANGE SCAN on reverse key index.

SQL> CREATE TABLE rev_idx_demo1
  2  (
  3   order_id INT,
  4   product VARCHAR2(40)
  5  );

Table created.

SQL> INSERT INTO rev_idx_demo1
  2  SELECT rownum,'Milk' FROM dual CONNECT BY LEVEL <= 1000;

1000 rows created.

SQL> COMMIT;

Commit complete.

SQL> CREATE UNIQUE INDEX idx_rev_idx_demo1 ON rev_idx_demo1(order_id);

Index created.

SQL> ANALYZE INDEX idx_rev_idx_demo1 VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT * FROM rev_idx_demo1 WHERE order_id = 20;

  ORDER_ID PRODUCT
---------- ----------------------------------------
        20 Milk

Execution Plan
----------------------------------------------------------
Plan hash value: 4136733169
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    35 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| rev_idx_demo1     |     1 |    35 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | idx_rev_idx_demo1 |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ORDER_ID"=20)
   
SQL> SELECT * FROM rev_idx_demo1 WHERE order_id IN(10,20);

  ORDER_ID PRODUCT
---------- ----------------------------------------
        10 Milk
        20 Milk

Execution Plan
----------------------------------------------------------
Plan hash value: 1956714805

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |     2 |    70 |     1   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |                  |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| rev_idx_demo1     |     2 |    70 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | idx_rev_idx_demo1 |     4 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ORDER_ID"=10 OR "ORDER_ID"=20)

Note
-----
   - dynamic sampling used for this statement (level=2)   

SQL> SET AUTOTRACE TRACEONLY

SQL> SELECT * FROM rev_idx_demo1 WHERE order_id BETWEEN 10 AND 20;

11 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 475772762

---------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                   |    11 |   385 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| REV_IDX_DEMO1     |    11 |   385 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_REV_IDX_DEMO1 |    11 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ORDER_ID">=10 AND "ORDER_ID"<=20)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
        816  bytes sent via SQL*Net to client
        422  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         11  rows processed

SQL> DROP INDEX idx_rev_idx_demo1;

Index dropped.

SQL> CREATE UNIQUE INDEX idx_rev_idx_demo1 ON rev_idx_demo1(order_id) REVERSE;

Index created.

SQL> ANALYZE INDEX idx_rev_idx_demo1 VALIDATE STRUCTURE;

Index analyzed.

SQL> SET AUTOTRACE TRACEONLY

SQL> SELECT * FROM rev_idx_demo1 WHERE order_id BETWEEN 10 AND 20;

11 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2310639035

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |    11 |   385 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| REV_IDX_DEMO1 |    11 |   385 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ORDER_ID">=10 AND "ORDER_ID"<=20)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
         13  recursive calls
          0  db block gets
         13  consistent gets
          0  physical reads
          0  redo size
        763  bytes sent via SQL*Net to client
        426  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         11  rows processed

SQL> SELECT * FROM rev_idx_demo1 WHERE order_id > 20;

980 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2310639035

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |   980 | 34300 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| REV_IDX_DEMO1 |   980 | 34300 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ORDER_ID">20)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         82  consistent gets
          0  physical reads
          0  redo size
      21821  bytes sent via SQL*Net to client
       1124  bytes received via SQL*Net from client
         67  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        980  rows processed

SQL> SELECT * FROM rev_idx_demo1 WHERE order_id = 20;

  ORDER_ID PRODUCT
---------- ----------------------------------------
        20 Milk

Execution Plan
----------------------------------------------------------
Plan hash value: 449032173

-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |     1 |    35 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| REV_IDX_DEMO1     |     1 |    35 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IDX_REV_IDX_DEMO1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ORDER_ID"=20)

SQL> SELECT * FROM rev_idx_demo1 WHERE order_id IN (10,20);

  ORDER_ID PRODUCT
---------- ----------------------------------------
        10 Milk
        20 Milk

Execution Plan
----------------------------------------------------------
Plan hash value: 3830223044

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |     2 |    70 |     1   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |                   |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| REV_IDX_DEMO1     |     2 |    70 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | IDX_REV_IDX_DEMO1 |     4 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ORDER_ID"=10 OR "ORDER_ID"=20)

Note
-----
   - dynamic sampling used for this statement (level=2)   

SQL> INSERT /*+ INDEX(idx_rev_idx_demo1) */ INTO rev_idx_demo1 VALUES(1002,'Milk');

1 row created.

Execution Plan
----------------------------------------------------------

------------------------------------------------------------------------------------------
| Id  | Operation                | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |               |     1 |   100 |     1   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | REV_IDX_DEMO1 |       |       |            |          |
------------------------------------------------------------------------------------------

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (N - Unresolved (1))
---------------------------------------------------------------------------

   1 -  INS$1
         N -  INDEX(idx_rev_idx_demo1)

Statistics
----------------------------------------------------------
          1  recursive calls
          4  db block gets
          0  consistent gets
          0  physical reads
        532  redo size
        494  bytes sent via SQL*Net to client
        971  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
1	rows processed


SQL> INSERT INTO rev_idx_demo1 VALUES(1001,'Milk');

1 row created.

Execution Plan
----------------------------------------------------------

------------------------------------------------------------------------------------------
| Id  | Operation                | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |               |     1 |   100 |     1   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | REV_IDX_DEMO1 |       |       |            |          |
------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          3  recursive calls
          4  db block gets
          4  consistent gets
          0  physical reads
        552  redo size
        494  bytes sent via SQL*Net to client
        939  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> UPDATE rev_idx_demo1 SET product = 'Rice' WHERE order_id = 1001;

1 row updated.

Execution Plan
----------------------------------------------------------
Plan hash value: 2445366742

----------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |                   |     1 |    35 |     2   (0)| 00:00:01 |
|   1 |  UPDATE            | REV_IDX_DEMO1     |       |       |            |          |
|*  2 |   INDEX UNIQUE SCAN| IDX_REV_IDX_DEMO1 |     1 |    35 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ORDER_ID"=1001)

Statistics
----------------------------------------------------------
          1  recursive calls
          1  db block gets
          2  consistent gets
          0  physical reads
        264  redo size
        494  bytes sent via SQL*Net to client
        957  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> DELETE FROM rev_idx_demo1 WHERE order_id = 1001;

1 row deleted.

Execution Plan
----------------------------------------------------------
Plan hash value: 2788191988

----------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |                   |     1 |    13 |     1   (0)| 00:00:01 |
|   1 |  DELETE            | REV_IDX_DEMO1     |       |       |            |          |
|*  2 |   INDEX UNIQUE SCAN| IDX_REV_IDX_DEMO1 |     1 |    13 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ORDER_ID"=1001)

Statistics
----------------------------------------------------------
          1  recursive calls
          3  db block gets
          2  consistent gets
          0  physical reads
        524  redo size
        494  bytes sent via SQL*Net to client
        941  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Shoumadip Das

Hi Folks, I am Shoumadip Das hailing from Kolkata, India welcoming you to explore my blog www.oraclemasterpiece.com. I am a self motivated and successful professional working in a leading IT Giant for more than 10 years.My area of expertise includes –

  • Oracle Database Programming (SQL & PL/SQL)
  • Perl Programming
  • Unix Programming

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *