The principle and purpose of the Oracle Reverse key index (reduce the index hotspot block)! __oracle

We know that Oracle automatically indexes the primary key columns of the table, and this default index is the normal B-tree index. The default B-tree index is not ideal for situations in which primary key values are added sequentially (incremental or descending). This is because if the value of the indexed column is in strict order, the hierarchy of the index tree grows quickly as the data row is inserted. The number of I/O reads and writes that occur in the search index are proportional to the level of the index tree, that is, a 5-level B-tree index that can occur up to 5 I/O operations when it is eventually read to the index data. Thus, reducing the number of levels of an index is an important method of indexing performance tuning.

If the data in the indexed column is inserted in a strictly orderly manner, the B-tree index tree becomes an asymmetric "skew tree", as shown in Figure 5:

And if the indexed column's data is inserted in random values, we'll get a symmetric index tree, as shown in Figure 6:

Comparing Figure 5 and Figure 6, searching for block A in Figure 5 requires 5 I/O operations, while Figure 6 requires only 3 I/O operations.
Since indexed column data is obtained from a sequence, its ordering cannot be circumvented, but when an index is indexed, Oracle allows the value of the indexed column to be reversed, that is, to advance the bit-bit reversal of the column value, such as the 1000,10001,10011,10111,1100 backward value will be 0001, 1001,1101,0011. Obviously, the ordered data of bit reverse processing becomes more random, so the index tree is more symmetrical, thus the query performance of the table is improved.
However, the Reverse key index also has its limitations: if in the where statement, the value of the indexed column needs to be scoped, such as between, <, >, and its Reverse key index is not available, Oracle will perform a full table scan, and only the reverse key indexed columns are <> and = The reverse key index is used when the comparison is done.

1. Reverse Indexing application situation
1 when the index leaf block is found to be a hotspot block, use
In general, when using data (often in bulk inserts) are more concentrated in a contiguous range of data, it is very easy to index leaf block overheating when using a normal index, which will result in degraded system performance.
2 use in a RAC environment
When several nodes in a RAC environment Access data are concentrated and dense, the probability of an index hotspot block occurs is high. If the system does not have a high requirement for range retrieval, you can consider using reverse indexing technology to improve the performance of the system. Therefore, the technology is more common in the RAC environment, it can significantly reduce the contention of the index block.

2. Advantages of using reverse indexing
The biggest advantage is to reduce the contention of the index leaf block, reduce the hotspot block, improve the system performance.

3. Disadvantages of using reverse indexing
Because of the characteristics of the reverse indexing structure itself, if a range scan is often used in the system to read data (for example, using the "between and" statement in the WHERE clause or the comparison operator ">", etc.), the reverse index will not apply. Because there will be a large number of full table scanning phenomenon, but will reduce the performance of the system.

Sometimes you can override SQL statements to avoid using range scans, such as where ID between 12345 and 12347, which can be rewritten as where ID in (12345,12346,12347), and the CBO converts such SQL queries into where id=12345 or id=12346 or id=12347, which is also valid for reverse indexing.
4. A simple demonstration of the creation and modification of the reverse index through a small experiment

Sql> Select COUNT (*) from T1;

  COUNT (*)
----------
         0

sql> Select COUNT (*) from T2;

  COUNT (*)
----------
         0

sql> Select COUNT (*) from T3;

  COUNT (*)
----------
   2000000

sql> select Index_name,index_type,table_name from user_indexes;

Index_name                     index_type                  table_name
-------------------------------------------------------------- -------------------------
pk_t2                          normal/rev                  T2
pk_t1                          NORMAL                      T1

Table T1 is the primary key that is the normal primary key, and the primary key of the table T2 is the reverse primary key. Now I insert the table T3 data into the table T1 and table T2

Sql> set timing on;
Sql> set autotrace on;

sql> Insert/* +append/into t1 select * from T3;

2000000 lines have been created. Time used: 00:01:42.83 execution plan----------------------------------------------------------plans hash value:4161002650------- --------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |
Time |   ---------------------------------------------------------------------------------
| 0 |      INSERT STATEMENT |  |   2316k| 485m| 19014 (1) |   00:03:49 | |  1 | LOAD TABLE Conventional |       T1 |       |            |          |   |
|   2 | TABLE ACCESS Full |  T3 |   2316k| 485m| 19014 (1) |
00:03:49 | ---------------------------------------------------------------------------------Note------Dynamic sampling used For this statement (level=2) statistic----------------------------------------------------------12305 recursive call
  S 538835 db block gets   203937 consistent gets 83057 physical reads 428323528 redo size 688 Bytes sent via sql*net to Clien T 614 bytes received via sql*net from client 3 sql*net roundtrips To/from client 2 sorts (m

Emory) 0 Sorts (disk) 2000000 rows processed sql> commit;

Submit completed.

Time used: 00:00:00.04 sql> Insert/* +append/into t2 select * from T3;

2000000 lines have been created. Time used: 00:02:02.63 execution plan----------------------------------------------------------plans hash value:4161002650------- --------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |
Time |   ---------------------------------------------------------------------------------
| 0 |      INSERT STATEMENT |  |   2316k| 485m| 19014 (1) |   00:03:49 | |  1 | LOAD TABLE Conventional |       T2 |       |            |          |   |
|   2 | TABLE ACCESS Full |  T3 |   2316k| 485m| 19014 (1) |
00:03:49 | ---------------------------------------------------------------------------------Note------Dynamic sampling used For this statement (level=2) statistic----------------------------------------------------------7936 recursive call  S 6059147 db block gets 158053 consistent gets 56613 physical reads 790167468 redo size 689 Bytes sent via sql*net to client 614 bytes received via sql*net from client 3 sql*net roundtrips

Om Client 2 sorts (memory) 0 sorts (disk) 2000000 rows processed sql> commit;

Submit completed. Time used: 00:00:00.01

You can see that the DB block gets is slightly higher because the data blocks of the reverse index are more fragmented. The heat block's contention has eased, the consistent gets has fallen, from 203937 to 158053, reduced 45,884 times. Redo size has also become more. Again to make inquiries, to see the difference between them.

Sql> set Autotrace traceonly;

Sql> Select object_name from t1 where id = 100; Time used: 00:00:00.06 execution plan----------------------------------------------------------plans hash value:1141790563------- ------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |
Time |   -------------------------------------------------------------------------------------
| 0 |       SELECT STATEMENT |     |    1 |     79 | 0 (0) |   00:00:01 | |  1 | TABLE ACCESS by INDEX rowid|     T1 |    1 |     79 | 0 (0) |   00:00:01 | |* 2 | INDEX UNIQUE SCAN |     Pk_t1 |       1 |     | 0 (0) |
00:00:01 | -------------------------------------------------------------------------------------Predicate information ( identified by Operation ID):---------------------------------------------------2-access ("id" =100) statistical information---------
  -------------------------------------------------        0 Recursive calls 0 DB block gets 4 consistent gets 3 physical reads 0  Redo size 434 Bytes sent via sql*net to-client 416 bytes received via sql*net from client 2 Sql*net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed sql& Gt

Select object_name from t1 where ID > ID < 200;

99 rows have been selected. Time used: 00:00:01.10 execution plan----------------------------------------------------------plans hash value:1249713949------- ------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |
Time |   -------------------------------------------------------------------------------------
| 0 |       SELECT STATEMENT |    |  99 |     7821 | 1 (0) |   00:00:01 | |  1 | TABLE ACCESS by INDEX rowid|    T1 |  99 |     7821 | 1 (0) |   00:00:01 | |* 2 | INDEX RANGE SCAN |    Pk_t1 |       99 |     | 1 (0) |
00:00:01 | -------------------------------------------------------------------------------------Predicate information ( identified by Operation ID):---------------------------------------------------2-access ("id" >100 and "id" <20 0 Note------The dynamic sampling used for this statement (level=2) Statistics-------------------------------------------- --------------9 Recursive calls 0 DB block gets 140 consistent gets 189 Reads 2356 redo size 2656 Bytes sent via sql*net to client 482 bytes received via sql*net from CL Ient 8 sql*net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) row

S processed sql> select object_name from t2 where id = 100; Time used: 00:00:00.05 execution plan----------------------------------------------------------plans hash value:1480579010------- ------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |
Time |   -------------------------------------------------------------------------------------
| 0 |       SELECT STATEMENT |     |    1 |     79 | 0 (0) |   00:00:01 | |  1 | TABLE ACCESS by INDEX rowid|     T2 |    1 |     79 | 0 (0) |   00:00:01 | |* 2 | INDEX UNIQUE SCAN |     Pk_t2 |       1 |     | 0 (0) |
00:00:01 | -------------------------------------------------------------------------------------Predicate information ( identified by Operation ID):---------------------------------------------------2-access ("id" =100) statistical information--------- -------------------------------------------------1 Recursive calls 0 db block gets 4 con  Sistent gets 1 physical reads 0 Redo size 434 Bytes sent via sql*net to client 416 Bytes received via sql*net from CLIent 2 sql*net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 row

S processed sql> select object_name from t2 where ID > ID < 200;

99 rows have been selected. Time used: 00:00:04.39 execution plan----------------------------------------------------------plans hash value:1513984157------- -------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |
Time |   --------------------------------------------------------------------------
| 0 |      SELECT STATEMENT |   | 336 |  26544 | 8282 (1) |  00:01:40 | |* 1 | TABLE ACCESS full|   T2 | 336 |  26544 | 8282 (1) |
00:01:40 | --------------------------------------------------------------------------predicate information (identified by 
   Operation ID):---------------------------------------------------1-filter ("id" >100 and "id" <200) Note----- -Dynamic sampling used for this statement (level=2)Meter information----------------------------------------------------------recursive calls 1 db block gets 
        60187 consistent gets 30335 physical reads 5144 redo size 2656 Bytes sent via sql*net to client 482 Bytes received via sql*net from client 8 sql*net roundtrips to/from client 0 sorts (me
 Mory) 0 Sorts (disk) rows processed

As you can see, a single value query, table t1 and Table T2 no difference, but the scope of the query, the table T1 is the index range SCAN, table T2 is full of tables ACCESS. In database optimization you will often find no absolute good, no absolute bad.

Before you consider using a reverse index, it is most possible to consider hashing the index to reduce contention on the index leaf block.

Reverse index:
ALTER index Id_inx rebuild reverse online;
Alter index ID_INX rebuild online reverse;
Alter index Name_inx rebuild online noreverse;