+什么是反向键索引+反向键索引原理+创建反向键索引

发布网友发布时间：2022-05-04 00:45

共2个回答

懂视网时间：2022-05-04 05:06

今天是2014-01-20，本来打算将方向键索引和其他索引混为其他索引日志中的，但是还是感觉这部分内容太重要了。在此记录一下方向键索引笔记。什么是方向键索引？方向键索引也是B树索引的特例索引，它是按照数据字节反转排列的，其中也存在rowid和普通B树索引

今天是2014-01-20，本来打算将方向键索引和其他索引混为其他索引日志中的，但是还是感觉这部分内容太重要了。在此记录一下方向键索引笔记。

什么是方向键索引？

方向键索引也是B树索引的特例索引，它是按照数据字节反转排列的，其中也存在rowid和普通B树索引一样。如在一个表中一个字段rowid+123456 rowid+234567 这是普遍B树索引，那么方向键索引为：rowid+654321 rowid+765432。

方向键索引使用情况：

特别在oracle RAC中方向键索引更为常见。当一个表的字段为递增序列字段填充的，那么当进行insert数据的时候会将相邻的数据添加到相同的索引叶子节点块中，那么这个时候在并发高的情况下就会产生索引的热块征用，如果使用方向键索引，那么可以避免此类事情的发生。

对于热块的征用往往伴随着buffer busy wait event等待事件（read by other session）。对于是否因为序列产生的可以通过查看gv$enqueue_stat如果字段EQ_TYPE如果类型为SQ ENQUEUE那么往往说明存在征用序列情况，此时可以通过设置序列的cache 数目以及noorder属性来避免此类等待征用的发生。

另外，对于解决索引热块征用的方案为：将索引创建为散列分区全局索引，这也是首先考虑的方式，因为如果设置为方向键索引会对数据库的cpu使用性能存在略微的消耗。

再次，就是在sql语句中等值谓词条件中方向键索引可以提高很高的性能，但是不能进行索引范围扫描，因为这是方向键打散的结果，其次注意,在非等值谓词条件中，可能不会使用方向键索引。

序列创建语法：

create sequence sequence_name

[start with start]

[increment by increment]

[minvalue minvalue|nominvalue]

[mavalue maxvalue| nomaxvalue}

[cache cache| nocache]

[cycle |no cycle}

{order | noorder}

创建方向键索引;

创建方向键索引很简单就是使用reverse关键字：

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 object


SQL> 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     1
SYS    V_$MAP_FILE   VIEW     2
PUBLIC    V$MAP_FILE   SYNONYM     3
SYS    V_$MAP_FILE_EXTENT  VIEW     4
PUBLIC    V$MAP_FILE_EXTENT  SYNONYM     5
SYS    V_$MAP_ELEMENT   VIEW     6
PUBLIC    V$MAP_ELEMENT   SYNONYM     7
SYS    V_$MAP_EXT_ELEMENT  VIEW     8
PUBLIC    V$MAP_EXT_ELEMENT  SYNONYM     9

9 rows selected.

SQL>

创建方向键索引：

SQL> 
SQL> create index emp_text_reidx1 on emp_text(object_id) reverse;

Index created.

SQL> set autotrace trace exp
SQL> 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>

从上面可以看出，对于方向键索引对between xxx and xxx以及不等值的谓词条件oracle是不走方向键索引的，另外之所以走index rance scan，那么说因为序列为递增序列且是order，因此有一部分列如（1-20）都是存在于一个索引叶子节点块中，那么就是走range scan。通常这是不合理的，为了避免出现热块需要序列使用noorder；

另注：

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.

热心网友时间：2022-05-04 02:14

我们知道oracle会自动为表的主键列建立索引，这个默认的索引是普通的b-tree索引。对于主键值是按顺序(递增或递减)加入的情况，默认的b-
tree索引并不理想。这是因为如果索引列的值具有严格顺序时，随着数据行的插入，索引树的层级增长很快。搜索索引发生的i/o读写次数和索引树的层级数成正比，也就是说，一棵具有5个层级的b-tree索引，在最终读取到索引数据时最多可能发生多达5次i/o操作。因而，减少索引的层级数是索引性能调整的一个重要方法。
如果索引列的数据以严格的有序的方式插入，那么b-tree索引树将变成一棵不对称的"歪树"，如图
5所示：
图
5不对称的b-tree索引
而如果索引列的数据以随机值的方式插入，我们将得到一棵趋向对称的索引树，如图
6所示：
图
6对称的b-tree索引
比较图
5和图
6，在图
5中搜索到a块需要进行5次i/o操作，而图
6仅需要3次i/o操作。
既然索引列数据从序列中获取，其有序性无法规避，但在建立索引时，oracle允许对索引列的值进行反向，即预先对列值进行比特位的反向，如
1000,10001,10011,10111,1100经过反向后的值将是0001,1001,1101,0011。显然经过位反向处理的有序数据变得比较随机了，这样所得到的索引树就比较对称，从而提高表的查询性能。
但反向键索引也有它局限性：如果在where语句中，需要对索引列的值进行范围性的搜索，如between、<、>等，其反向键索引无法使用，此时，oracle将执行全表扫描；只有对反向键索引列进行
<>
和
=
的比较操作时，其反向键索引才会得到使用。