Reverse index creation

Today is. I was planning to mix the direction key index and other indexes into other index logs, but I still feel that this part is too important. Record the arrow key index notes here. What is a direction key index? The direction key index is also a special index of the B-tree index, which is arranged in reverse order of data bytes. The rowid and common B-tree index also exist.

Today is. I was planning to mix the direction key index and other indexes into other index logs, but I still feel that this part is too important. Record the arrow key index notes here. What is a direction key index? The direction key index is also a special index of the B-tree index, which is arranged in reverse order of data bytes. The rowid and common B-tree index also exist.

Today is. I was planning to mix the direction key index and other indexes into other index logs, but I still feel that this part is too important. Record the arrow key index notes here.

What is a direction key index?

The direction key index is also a special index of the B-tree index. It is arranged in reverse order of data bytes, And the rowid is the same as the normal B-tree index. For example, if rowid + 123456 rowid + 234567 is a common B-tree index in a table, the direction key index is: rowid + 654321 rowid + 765432.

Direction key index usage:

Especially in oracle RAC, the direction key index is more common. When the field of a table is filled with an incremental sequence field, the adjacent data will be added to the leaf node block of the same index when the data is inserted, at this time, the hot block requisition of the index will be generated when the concurrency is high. If the direction key index is used, this kind of thing can be avoided.

The acquisition of hot blocks is often accompanied by the buffer busy wait event (read by other session ). For sequence generation, you can check gv $ enqueue_stat. If the EQ_TYPE field is of the sq enqueue type, it usually indicates the requisition sequence, in this case, you can set the cache number and noorder attribute of the sequence to avoid such waiting for requisition.

In addition, the solution to hot index block requisition is to create an index as a global index for hash partitions, which is also the first consideration, if it is set as the direction key index, the cpu usage performance of the database will be slightly consumed.

Again, the direction key index in the condition of a Medium-value predicate in the SQL statement can improve the performance, but it cannot be used for index range scanning, because this is the result of direction keys being dispersed. Pay attention to the following, in non-equivalent predicates, the direction key index may not be used.

Sequence creation Syntax:

Create sequence sequence_name

[Start with start]

[Increment by increment]

[Minvalue | nominvalue]

[Mavalue maxvalue | nomaxvalue}

[Cache | nocache]

[Cycle | no cycle}

{Order | noorder}

Create a direction key index;

To create a direction key index, use the reverse Keyword:

Eg:

SQL> create sequence emp_seq  2  start with 1  3  increment by 1  4  minvalue 1  5  nomaxvalue  6  cache 100  7  noorder;SQL> SQL> create table emp_text(owner,object_name,object_type) as select owner,object_name,object_type from dba_objects;SQL> commit;SQL> insert into emp_text (owner,object_name,object_type)  select owner,object_name,object_type from dba_objects where rownum<10000;9999 rows created.SQL> commit;Commit complete.SQL> create sequence emp_seq  2  start with 1  3  increment by 1  4  minvalue 1  5  nomaxvalue  6  cache 1000  7  order;create sequence emp_seq                *ERROR at line 1:ORA-00955: name is already used by an existing objectSQL> drop sequence emp_seq;Sequence dropped.SQL> create sequence emp_seq  2  start with 1  3  increment by 1  4  minvalue 1  5  nomaxvalue  6  cache 1000  7  order;Sequence created.SQL> SQL> declare  2    cursor emp_cursor is  3      select * from emp_text for update;  4    v_object_name emp_text%rowtype;  5  begin  6    open emp_cursor;  loop  7    8      fetch emp_cursor  9        into v_object_name; 10      if emp_cursor%found then 11        update emp_text 12           set object_id = 13               (emp_seq.nextval) 14         where object_name = v_object_name.object_name; 15      end if; 16      exit when emp_cursor%notfound; 17    end loop; 18    close emp_cursor; 19  end; 20  /PL/SQL procedure successfully completed.SQL> commit;Commit complete.SQL> select * from emp_text where rownum<10;OWNER                          OBJECT_NAME                    OBJECT_TYPE                       OBJECT_ID------------------------------ ------------------------------ ------------------------------ ------------PUBLIC                         V$MAP_LIBRARY                  SYNONYM                                   1SYS                            V_$MAP_FILE                    VIEW                                      2PUBLIC                         V$MAP_FILE                     SYNONYM                                   3SYS                            V_$MAP_FILE_EXTENT             VIEW                                      4PUBLIC                         V$MAP_FILE_EXTENT              SYNONYM                                   5SYS                            V_$MAP_ELEMENT                 VIEW                                      6PUBLIC                         V$MAP_ELEMENT                  SYNONYM                                   7SYS                            V_$MAP_EXT_ELEMENT             VIEW                                      8PUBLIC                         V$MAP_EXT_ELEMENT              SYNONYM                                   99 rows selected.SQL> 

Create a direction key index:

SQL> SQL> create index emp_text_reidx1 on emp_text(object_id) reverse;Index created.SQL> set autotrace trace expSQL> select * from emp_text where object_id=20;Execution Plan----------------------------------------------------------Plan hash value: 2362949500-----------------------------------------------------------------------------------------------| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |                 |     6 |   642 |    29   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| EMP_TEXT        |     6 |   642 |    29   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | EMP_TEXT_REIDX1 |    42 |       |     1   (0)| 00:00:01 |-----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("OBJECT_ID"=20)Note-----   - dynamic sampling used for this statement (level=2)SQL> select * from emp_text where object_id=300;Execution Plan----------------------------------------------------------Plan hash value: 2362949500-----------------------------------------------------------------------------------------------| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |                 |     6 |   642 |    29   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| EMP_TEXT        |     6 |   642 |    29   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | EMP_TEXT_REIDX1 |    42 |       |     1   (0)| 00:00:01 |-----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("OBJECT_ID"=300)Note-----   - dynamic sampling used for this statement (level=2)

SQL> select * from emp_text where object_id<200;Execution Plan----------------------------------------------------------Plan hash value: 4288487957------------------------------------------------------------------------------| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------|   0 | SELECT STATEMENT  |          |     6 |   642 |   153   (0)| 00:00:02 ||*  1 |  TABLE ACCESS FULL| EMP_TEXT |     6 |   642 |   153   (0)| 00:00:02 |------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("OBJECT_ID"<200)Note-----   - dynamic sampling used for this statement (level=2)SQL> select * from emp_text where object_id between 20 and 200;Execution Plan----------------------------------------------------------Plan hash value: 4288487957------------------------------------------------------------------------------| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------|   0 | SELECT STATEMENT  |          |    26 |  2782 |   153   (0)| 00:00:02 ||*  1 |  TABLE ACCESS FULL| EMP_TEXT |    26 |  2782 |   153   (0)| 00:00:02 |------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("OBJECT_ID">=20 AND "OBJECT_ID"<=200)Note-----   - dynamic sampling used for this statement (level=2)SQL> select * from emp_text where object_id in (10,20,500);Execution Plan----------------------------------------------------------Plan hash value: 1428765950------------------------------------------------------------------------------------------------| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT             |                 |     6 |   642 |    31   (0)| 00:00:01 ||   1 |  INLIST ITERATOR             |                 |       |       |            |          ||   2 |   TABLE ACCESS BY INDEX ROWID| EMP_TEXT        |     6 |   642 |    31   (0)| 00:00:01 ||*  3 |    INDEX RANGE SCAN          | EMP_TEXT_REIDX1 |    42 |       |     3   (0)| 00:00:01 |------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - access("OBJECT_ID"=10 OR "OBJECT_ID"=20 OR "OBJECT_ID"=500)Note-----   - dynamic sampling used for this statement (level=2)SQL> 

We can see from the above that for the direction key index to between xxx and the non-equivalent predicate condition oracle does not go to the direction key index. In addition, the reason for using index rance scan is as follows, because the sequence is an incremental sequence and is an order, some columns such as (1-20) exist in the leaf node block of an index, so range scan is used. This is usually unreasonable. In order to avoid the occurrence of hot blocks, noorder should be used in sequence;

Note:

If you use sequence numbers, then always use CACHE with the NOORDER option for optimal performance in sequence number generation. with the CACHEoption, however, you may have gaps in the sequence numbers. if your environment cannot tolerate sequence number gaps, then use the NOCACHE option or consider pre-generating the sequence numbers. if your application requires sequence number ordering but can tolerate gaps, then use CACHE andORDER to cache and order sequence numbers in Oracle RAC. if your application requires ordered sequence numbers without gaps, then use NOCACHE andORDER. the NOCACHE and ORDER combination has the most negative effect on performance compared to other caching and ordering combinations.