`

oracle 反向键索引的原理和用途（减少索引热点块）！

oraclesql 
阅读更多
我们知道Oracle会自动为表的主键列建立索引，这个默认的索引是普通的B-Tree索引。对于主键值是按顺序(递增或递减)加入的情况，默认的B-Tree索引并不理想。这是因为如果索引列的值具有严格顺序时，随着数据行的插入，索引树的层级增长很快。搜索索引发生的I/O读写次数和索引树的层级数成正比，也就是说，一棵具有5个层级的B -Tree索引，在最终读取到索引数据时最多可能发生多达5次I/O操作。因而，减少索引的层级数是索引性能调整的一个重要方法。
如果索引列的数据以严格的有序的方式插入，那么B-Tree索引树将变成一棵不对称的"歪树"，如图 5所示：

而如果索引列的数据以随机值的方式插入，我们将得到一棵趋向对称的索引树，如图 6所示：

比较图 5和图 6，在图 5中搜索到A块需要进行5次I/O操作，而图 6仅需要3次I/O操作。
既然索引列数据从序列中获取，其有序性无法规避，但在建立索引时，Oracle允许对索引列的值进行反向，即预先对列值进行比特位的反向，如 1000,10001,10011,10111,1100经过反向后的值将是0001,1001,1101,0011。显然经过位反向处理的有序数据变得比较随机了，这样所得到的索引树就比较对称，从而提高表的查询性能。
但反向键索引也有它局限性：如果在WHERE语句中，需要对索引列的值进行范围性的搜索，如BETWEEN、<、>等，其反向键索引无法使用，此时，Oracle将执行全表扫描；只有对反向键索引列进行 <>和 = 的比较操作时，其反向键索引才会得到使用。

1.反向索引应用场合
1）发现索引叶块成为热点块时使用
通常，使用数据时（常见于批量插入操作）都比较集中在一个连续的数据范围内，那么在使用正常的索引时就很容易发生索引叶子块过热的现象，严重时将会导致系统性能下降。
2）在RAC环境中使用
当RAC环境中几个节点访问数据的特点是集中和密集，索引热点块发生的几率就会很高。如果系统对范围检索要求不是很高的情况下可以考虑使用反向索引技术来提高系统的性能。因此该技术多见于RAC环境，它可以显著的降低索引块的争用。

2.使用反向索引的优点
最大的优点莫过于降低索引叶子块的争用，减少热点块，提高系统性能。

3.使用反向索引的缺点
由于反向索引结构自身的特点，如果系统中经常使用范围扫描进行读取数据的话（例如在where子句中使用“between and”语句或比较运算符“>”“<”等），那么反向索引将不适用，因为此时会出现大量的全表扫描的现象，反而会降低系统的性能。
有时候可以通过改写sql语句来避免使用范围扫描，例如where id between 12345 and 12347，可以改写为where id in(12345,12346,12347)，CBO会把这样的sql查询转换为where id=12345 or id=12346 or id=12347,这对反向索引也是有效的。
4.通过一个小实验简单演示一下反向索引的创建及修改 
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

表t1是主键是正常的主键，表t2的主键是反向主键。现在我把表t3的数据分别插入到表t1和表t2 
SQL> set timing on;  
SQL> set autotrace on;  
SQL> insert /* +append */ into t1 select * from t3;  
  
已创建2000000行。  
  
已用时间:  00: 01: 42.83  
  
执行计划  
----------------------------------------------------------  
Plan 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)  
  
  
统计信息  
----------------------------------------------------------  
      12305  recursive calls  
     538835  db block gets  
     203937  consistent gets  
      83057  physical reads  
  428323528  redo size  
        688  bytes sent via SQL*Net to client  
        614  bytes received via SQL*Net from client  
          3  SQL*Net roundtrips to/from client  
          2  sorts (memory)  
          0  sorts (disk)  
    2000000  rows processed  
  
SQL> commit;  
  
提交完成。  
  
已用时间:  00: 00: 00.04  
  
SQL> insert /* +append */ into t2 select * from t3;  
  
已创建2000000行。  
  
已用时间:  00: 02: 02.63  
  
执行计划  
----------------------------------------------------------  
Plan 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)  
  
  
统计信息  
----------------------------------------------------------  
       7936  recursive calls  
    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 to/from client  
          2  sorts (memory)  
          0  sorts (disk)  
    2000000  rows processed  
  
SQL> commit;  
  
提交完成。  
  
已用时间:  00: 00: 00.01

可以看见：由于反向索引的数据块比较分散了后，db block gets要稍微高一些。热块的争用有所缓解，consistent gets有所下降，从203937下降到158053，减少了45884次。redo size 也变多了！再来做查询，来看看他们的区别。 
SQL> set autotrace traceonly;  
SQL> select OBJECT_NAME from t1 where id = 100;  
  
已用时间:  00: 00: 00.06  
  
执行计划  
----------------------------------------------------------  
Plan 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)  
  
  
统计信息  
----------------------------------------------------------  
          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> select OBJECT_NAME from t1 where id > 100 and id < 200;  
  
已选择99行。  
  
已用时间:  00: 00: 01.10  
  
执行计划  
----------------------------------------------------------  
Plan 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"<200)  
  
Note  
-----  
   - dynamic sampling used for this statement (level=2)  
  
  
统计信息  
----------------------------------------------------------  
          9  recursive calls  
          0  db block gets  
        140  consistent gets  
        189  physical reads  
       2356  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 (memory)  
          0  sorts (disk)  
         99  rows processed  
  
SQL> select OBJECT_NAME from t2 where id = 100;  
  
已用时间:  00: 00: 00.05  
  
执行计划  
----------------------------------------------------------  
Plan 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)  
  
  
统计信息  
----------------------------------------------------------  
          1  recursive calls  
          0  db block gets  
          4  consistent 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  rows processed  
  
SQL> select OBJECT_NAME from t2 where id > 100 and id < 200;  
  
已选择99行。  
  
已用时间:  00: 00: 04.39  
  
执行计划  
----------------------------------------------------------  
Plan 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)  
  
  
统计信息  
----------------------------------------------------------  
         29  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 (memory)  
          0  sorts (disk)  
         99  rows processed

可以看见，单个值查询的时候，表t1和表t2并无差别，但是范围查询的时候，表t1是INDEX RANGE SCAN，表t2是TABLE ACCESS FULL了。在数据库的优化中你经常会发现没有绝对的好，也没有绝对的差。
在考虑使用反向索引之前，大多数情况可以考虑对索引进行散列分区（hash）来减少索引叶块的争用。
反向索引：
alter index id_inx rebuild reverse online;
alter index id_inx rebuild online reverse;
alter index name_inx rebuild online noreverse;

转载：http://blog.csdn.net/zq9017197/article/details/7321604
分享到：
| oracle反向索引
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    oracle rac环境下索引热块的处理思路

    Oracle RAC 环境下索引热块的处理思路...在 Oracle RAC 环境下，索引热块争用的处理思路需要考虑到多实例并发 insert 表和多实例并发查询导致的索引热块争用，并 采用反向索引和按实例分区表等方法来缓解索引热块争用。

    oracle的索引学习

    在Oracle中，常见的索引类型有B树索引、位图索引和反向键索引。B树索引适用于经常进行单行或范围查询的列，它们提供高效的查找性能。位图索引则适用于低基数（即不同值较少）的列，或者在进行多列组合查询时，可以将...

    oracle索引，常见索引问题

    - **反向键索引**：用于解决连续值导致的索引块竞争问题，通过字节反转使索引条目更均匀分布，减少热点块的出现。 2. **位图索引**：在位图索引中，一个索引条目使用位图标记多个行。这种索引适用于具有高度重复值...

    Oracle索引分析与比较

    本篇文章将深入探讨Oracle中的B*Tree索引、反向索引、降序索引、位图索引和函数索引。 1. **B*Tree索引**： - B*Tree索引是Oracle中最常见的索引类型，其结构类似于二叉树，可以高效地处理高基数数据列，即具有...

    09 oracle的索引 PPT

    总结，Oracle的索引是提升数据库性能的关键工具，理解索引的工作原理，合理设计和管理索引，能有效优化数据库的查询性能，但同时也要注意其带来的额外存储和维护成本。在实际应用中，需要根据业务需求和查询模式，...

    oracle索引类型及扫描方式大整理new

    而反向索引通过反转索引键中的字节顺序，使得原本连续的键值分散到不同的叶块中，从而均衡负载，减少索引块的争用情况。 然而，反向索引并非万能解药，它在某些场景下存在限制。例如，在进行范围查询(`WHERE column...

    Oracle索引机制分析

    **快速全局扫描**和**索引跳跃式扫描**是Oracle优化查询性能的策略，它们可以避免全表扫描，只读取必要的数据块。 **INDEX_STATS**视图提供了索引的统计信息，帮助管理员评估索引的效率和选择性。 索引的创建和...

    oracle索引的建立、修改、删除.doc

    Oracle 索引的建立、修改、删除 Oracle 索引是关系数据库中用于存放每一条记录的一种对象，主要目的是加快数据的读取速度和完整性检查。...对反向键索引的使用需要根据实际情况进行选择，避免不必要的索引建立和维护。

    Oracle数据库中的索引管理技术.pdf

    Oracle 8i 支持几种不同类型的索引以满足许多类型的应用程序的需求，其中包括 B-树（标准）索引、反向键索引、位图索引、索引编排表和基于函数的索引。这些索引都是基于表列创建的各种类型的索引。 （1）标准（B-树...

    Oracle_Index 索引2

    反向索引是Oracle为解决并行服务器环境下索引叶块竞争问题而设计的一种特殊索引。其核心思想是通过反转索引码中每列的字节顺序，使索引条目分布更加均匀，减少热点现象，即多个事务同时访问同一叶块的情况。 - **...

    Oracle索引详解与优化技巧：B*Tree索引、位图索引及其他类型索引的应用

    内容概要：本文详细介绍了 Oracle中的各种索引类型及其使用场景，包括 B*Tree索引、位图索引、索引组织表、降序索引、反向键索引和基于函数的索引。每种索引的优缺点、适用场合和创建方法均有详细介绍。文章还讨论了...

    Oracle索引优化

    在Oracle数据库中，索引的种类繁多，包括B*Tree索引、反向索引、降序索引、位图索引和函数索引等。理解并正确使用这些索引类型对于提升查询性能至关重要。 首先，B*Tree索引是最常见的索引类型，适用于大部分常规...

    oracle索引类型及扫描方式大整理

    本文主要介绍Oracle数据库中不同类型的索引及其内部结构，以及各种索引的工作原理和适用场景。 首先，Oracle使用平衡树（B-Tree）作为其索引的基础结构。B-Tree是一种自平衡的树形数据结构，它保证了在树的任意层级...

    Oracle Index 索引介绍

    反向索引主要应用于长文本字段，如CLOB和NCLOB，将索引键的最后一个字节作为第一个字节存储，提高查询性能。 4. **函数索引（Function-Based Index）** 允许基于列的函数结果创建索引，使得对函数的结果进行查询...

    Oracle优化全攻略一【Oracle 索引概念】.docx

    在Oracle数据库中，常见的索引类型包括B*Tree索引、反向索引、降序索引、位图索引、函数索引以及interMedia全文索引。B*Tree索引是最常见的一种，适用于大部分常规查询场景；反向索引则主要用于长文本字段，降低存储...

    高效ORACLE之索引(完整).pdf

    反向键索引（Reverse Key Index）：反向键索引是B*树索引的一种特殊形式，其键值顺序被反转。例如，对于数值键值，最高位被存储在叶节点的最低位置，最低位则存储在最高位置。这种方式有助于减少键值冲突，特别是...

    oracle索引分析与比较

    2. **反向索引**：反向索引是为了减少并行服务器环境中的索引叶块竞争而设计的。它通过反转索引键的字节顺序，使得连续的值在索引中分散分布，从而减少索引块的争用。这对于递增序列号生成的列特别有用，例如时间戳...

Magicbox
Global site tag (gtag.js) - Google Analytics