反向索引的定义

反向索引作为B-tree索引的一个分支，主要是在创建索引时，针对索引列的索引键值进行字节反转，进而实现分散存放到不同叶子节点块的目的。

反向索引针对的问题

  • 使用传统的B-tree索引，当索引的列是按顺序产生时，相应的索引键值会基本分布在同一个叶块中。当用户对该列进行操作时，难免会发生索引块的争用。
  • 使用反向索引，将索引列的键值进行反转，实现顺序的键值分散到不同的叶块中，从而减少索引块的争用。
  • 例如：键值1001、1002、1003，反转后1001、2001、3001，进而分散到不用的叶子节点块中。

反向索引应用场景

  1. 索引块成为热点块
  2. rac环境 ：
    • rac环境下中多节点访问访问数据呈现密集且集中的特点，索引热块的产生较高。
    • 在范围检索不高的rac环境中使用反向索引可有效提高性能。

反向索引的优点与缺点

  • 优点：降低索引叶子块的争用问题，提升系统性能。
  • 缺点：对于范围检索，例如：between，>，<时，反向索引无法引用，进而导致全表扫面的产生，降低系统性能。

反向索引示例说明

  1. -- 创建两张相同结构的表，内部结构及数据均引用scott用户下的emp表
  3. SQL> select count(*) from test01;
  5.   COUNT(*)
  6. ----------
  8. SQL> select count(*) from test02;
  10.   COUNT(*)
  11. ----------
  1. --针对表TEST01的empno列，添加B-tree索引 
  2. SQL> create index PK_TEST01 on TEST01(EMPNO);
  3. Index created.
  5. --针对表TEST02的empno列，添加反向索引
  6. SQL> create index PK_REV_TEST02 on TEST02(EMPNO) REVERSE;
  7. Index created.
  10. --验证上面的索引，NORMAL/REV表明为反向索引
  11. SQL> select TABLE_NAME,INDEX_NAME,INDEX_TYPE from user_indexes where INDEX_NAME like '%TEST%';
  13. TABLE_NAME           INDEX_NAME           INDEX_TYPE
  14. -------------------- -------------------- --------------------
  15. TEST01               PK_TEST01            NORMAL
  16. TEST02               PK_REV_TEST02        NORMAL/REV
  2. --打开会话追踪
  3. SQL> set autotrace traceonly
  6. --相同条件查询，观察两表的执行计划
  7. SQL> select * from TEST01 where empno=7369;
  8. Execution Plan
  9. ----------------------------------------------------------
  10. Plan hash value: 515586510
  12. -----------------------------------------------------------------------------------------
  13. | Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
  14. -----------------------------------------------------------------------------------------
  15. |   0 | SELECT STATEMENT            |           |     1 |    87 |     2   (0)| 00:00:01 |
  16. |   1 |  TABLE ACCESS BY INDEX ROWID| TEST01    |     1 |    87 |     2   (0)| 00:00:01 |
  17. |*  2 |   INDEX RANGE SCAN          | PK_TEST01 |     1 |       |     1   (0)| 00:00:01 |
  18. -----------------------------------------------------------------------------------------
  20. Predicate Information (identified by operation id):
  21. ---------------------------------------------------
  22. - access("EMPNO"=7369)
  24. Note
  25. -----
  26.    - dynamic sampling used for this statement (level=2)
  29. Statistics
  30. ----------------------------------------------------------
  31.  recursive calls
  32.  db block gets
  33.  consistent gets
  34.  physical reads
  35.  redo size
  36.  bytes sent via SQL*Net to client
  37.  bytes received via SQL*Net from client
  38.  SQL*Net roundtrips to/from client
  39.  sorts (memory)
  40.  sorts (disk)
  41.  rows processed
  46. SQL> select * from TEST02 where empno=7369;
  48. Execution Plan
  49. ----------------------------------------------------------
  50. Plan hash value: 1053012716
  52. ---------------------------------------------------------------------------------------------
  53. | Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
  54. ---------------------------------------------------------------------------------------------
  55. |   0 | SELECT STATEMENT            |               |     1 |    87 |     2   (0)| 00:00:01 |
  56. |   1 |  TABLE ACCESS BY INDEX ROWID| TEST02        |     1 |    87 |     2   (0)| 00:00:01 |
  57. |*  2 |   INDEX RANGE SCAN          | PK_REV_TEST02 |     1 |       |     1   (0)| 00:00:01 |
  58. ---------------------------------------------------------------------------------------------
  60. Predicate Information (identified by operation id):
  61. ---------------------------------------------------
  62. - access("EMPNO"=7369)
  64. Note
  65. -----
  66.    - dynamic sampling used for this statement (level=2)
  69. Statistics
  70. ----------------------------------------------------------
  71.  recursive calls
  72.  db block gets
  73.  consistent gets
  74.  physical reads
  75.  redo size
  76.  bytes sent via SQL*Net to client
  77.  bytes received via SQL*Net from client
  78.  SQL*Net roundtrips to/from client
  79.  sorts (memory)
  80.  sorts (disk)
  81.  rows processed
  1. -- 相同范围条件查询，观察两表的执行计划
  3. SQL> select * from TEST01 where empno between 7350 and 7500;
  5. Execution Plan
  6. ----------------------------------------------------------
  7. Plan hash value: 515586510
  9. -----------------------------------------------------------------------------------------
  10. | Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
  11. -----------------------------------------------------------------------------------------
  12. |   0 | SELECT STATEMENT            |           |     2 |   174 |     2   (0)| 00:00:01 |
  13. |   1 |  TABLE ACCESS BY INDEX ROWID| TEST01    |     2 |   174 |     2   (0)| 00:00:01 |
  14. |*  2 |   INDEX RANGE SCAN          | PK_TEST01 |     2 |       |     1   (0)| 00:00:01 |
  15. -----------------------------------------------------------------------------------------
  17. Predicate Information (identified by operation id):
  18. ---------------------------------------------------
  19. - access("EMPNO">=7350 AND "EMPNO"<=7500)
  21. Note
  22. -----
  23.    - dynamic sampling used for this statement (level=2)
  26. Statistics
  27. ----------------------------------------------------------
  28.  recursive calls
  29.  db block gets
  30.  consistent gets
  31.  physical reads
  32.  redo size
  33.  bytes sent via SQL*Net to client
  34.  bytes received via SQL*Net from client
  35.  SQL*Net roundtrips to/from client
  36.  sorts (memory)
  37.  sorts (disk)
  38.  rows processed
  44. SQL> select * from TEST02 where empno between 7350 and 7500;
  46. Execution Plan
  47. ----------------------------------------------------------
  48. Plan hash value: 3294238222
  50. ----------------------------------------------------------------------------
  51. | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
  52. ----------------------------------------------------------------------------
  53. |   0 | SELECT STATEMENT  |        |     2 |   174 |     3   (0)| 00:00:01 |
  54. |*  1 |  TABLE ACCESS FULL| TEST02 |     2 |   174 |     3   (0)| 00:00:01 |
  55. ----------------------------------------------------------------------------
  57. Predicate Information (identified by operation id):
  58. ---------------------------------------------------
  59. - filter("EMPNO">=7350 AND "EMPNO"<=7500)
  61. Note
  62. -----
  63.    - dynamic sampling used for this statement (level=2)
  66. Statistics
  67. ----------------------------------------------------------
  68.  recursive calls
  69.  db block gets
  70.  consistent gets0  redo size
  71.  bytes sent via SQL*Net to client
  72.  bytes received via SQL*Net from client
  73.  SQL*Net roundtrips to/from client
  74.  sorts (memory)
  75.  sorts (disk)
  76.  rows processed

通过上面的示例可以看到，当使用between条件进行范围查询时，采用反向索引的表，并没有使用索引，而是采用了全表扫面的方式进行检索。