反向建索引

  今天是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.

我的疑惑：http://t.askmaclean.com/forum.php?mod=viewthread&tid=3846&page=1#pid21435