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
