In-depth understanding of Oracle indexes (5): definitions, shortcomings, and applicable scenarios of reverse Indexes

(I) Definition

CreateSetting a reverse index will reverse the key value (each column key value) of each column in bytes. For the key combination, the column order is retained, but the bytes of each column are reversed.
For example:
The content of a column in the table.
......
1234
1235
1236
1237
......

Create a forward index
......
1234
1235
1236
1237
......
These four rows are placed in the same leaf block.
If transaction a queries the row 1234, transaction B queries the row 1235. I/O contention will occur on this leaf block.


Create reverse Index
......
4321
5321
6321
7321
......
These four rows are placed in four different leaf blocks.
If transaction a queries the row 1234, transaction B queries the row 1235. Is performed on two leaf blocks separately, and I/O contention will not occur

Many transactions access the same block and compete with I/0 for concurrent operations on the same block.
Reverse indexing can be used as a way to avoid hotspot Blocks

(Ii) Search

User_indexes.index_type

scott@ORCL> create index idx_rev on emp(sal) reverse;Index created.scott@ORCL> select index_name,index_type from user_indexes where index_name='IDX_REV';INDEX_NAME                     INDEX_TYPE------------------------------ ---------------------------IDX_REV                        NORMAL/REV

(3) What are its disadvantages?

① If you use reverse key index, index range scan will not work
② When an application needs to obtain data within a certain range, the reverse key index will not be used because the key values are not consecutively arranged. In this case, CBO will select full table Scan

Test:

hr@ORCL> drop table t purge;Table dropped.hr@ORCL> create table t (a number,b varchar2(20));Table created.hr@ORCL> ed   Wrote file afiedt.buf  1  begin  2    for i in 1..20000  3    loop  4      insert into t values(i,to_char(sysdate,'yyyymmddhhmmss'));  5      commit;  6    end loop;  7* end;hr@ORCL> /PL/SQL procedure successfully completed.hr@ORCL> create index idx_t on t (a) reverse;Index created.hr@ORCL> set autot on exphr@ORCL> select * from t where a >=19989 and a <=19990;         A B---------- --------------------     19989 20130224060219     19990 20130224060219Execution Plan----------------------------------------------------------Plan hash value: 1601196873--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     2 |    50 |    19   (6)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| T    |     2 |    50 |    19   (6)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("A">=19989 AND "A"<=19990)Note-----   - dynamic sampling used for this statementhr@ORCL> drop index idx_t;Index dropped.hr@ORCL> create index idx_t on t (a);Index created.hr@ORCL> analyze index idx_t compute statistics;Index analyzed.hr@ORCL> select * from t where a >=19989 and a <=19990;         A B---------- --------------------     19989 20130224060219     19990 20130224060219Execution Plan----------------------------------------------------------Plan hash value: 1594971208-------------------------------------------------------------------------------------| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |       |     2 |    50 |     3   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| T     |     2 |    50 |     3   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | IDX_T |     2 |       |     2   (0)| 00:00:01 |-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("A">=19989 AND "A"<=19990)Note-----   - dynamic sampling used for this statement

(Iv) When should I use it?

Reverse indexes are mainly created on columns generated by serial numbers. You can distribute the index entries that are originally connected to different leaf blocks.
When the index is obtained from the sequence, if it is a general B-tree index, after a large number of inserts, it will lead to block splitting and tree skew, use reverse key index to make index segment entries more evenly distributed


InstituteTherefore, the reverse index is mainly used to alleviate the contention of the leaf nodes on the right side of the increasing right index, which is of little significance to the query. Note that the reverse index may fail to pass the range scan
But it is good to solve the hot block problem caused by indexing!