Home > oracle 11g > Invisible index

Invisible index

September 3rd, 2007
Goto comments Leave a comment

11g的Invisible index和前面版本的virtual index是完全不同的。

Invisible index是有segment的，并且在做DML操作的时候是维护这个索引的,而virtual index并没有真实的被创建出来,没有分配segment空间，只是optimizer可见而已。

让我来说的话，virtual index是在QA环境上做测试的一个工具，用处不大，主要被oracle自己使用(Oracle Enterprise Manger Tuning Pack)，
而invisible index是在production实施的一个过渡阶段，对于某些大型的复杂的application很有价值。

另外invisible index对于drop index也很有帮助，可以做为drop index的一个过渡阶段。
在drop前（建议先monitoring usage）先设置index invisible，观测一段时间，比如一个星期，如果有问题可以很快的rollback，如果没有问题再drop index。

在我06年初的那篇文章里提到virtual index的几个特性：

1. 需要设定隐藏参数 “_use_nosegment_indexes”=true, 因为涉及隐藏参数,所以不建议在prod环境使用,除非oracle建议
2. hint 对virtual index有效
3. 没有hint的情况下,RBO时虚拟索引对于optimizer不可见, CBO时oracle会考虑使用虚拟索引

这里对应的也列出Invisible的几个特点：
1. 可以在创建的时候指定，也可以用alter index xx invisible 修改，是oracle support的new feature.

  1. SQL> create index test_idx on test(object_id) invisible;
  2.  
  3. Index created.
  4.  
  5. SQL> select index_name,visibility from user_indexes where index_name='TEST_IDX';
  6.  
  7. INDEX_NAME                                                                                 VISIBILITY
  8. ------------------------------------------------------------------------------------------ ---------------------------
  9. TEST_IDX                                                                                   INVISIBLE
  10.  
  11. SQL> alter index test_idx visible;
  12.  
  13. Index altered.
  14.  
  15. SQL> select index_name,visibility from user_indexes where index_name='TEST_IDX';
  16.  
  17. INDEX_NAME                                                                                 VISIBILITY
  18. ------------------------------------------------------------------------------------------ ---------------------------
  19. TEST_IDX                                                                                   VISIBLE
  20.  
  21. SQL> alter index test_idx invisible;
  22.  
  23. Index altered.
  24.  
  25. SQL> select index_name,visibility from user_indexes where index_name='TEST_IDX';
  26.  
  27. INDEX_NAME                                                                                 VISIBILITY
  28. ------------------------------------------------------------------------------------------ ---------------------------
  29. TEST_IDX                                                                                   INVISIBLE

2. Invisible index对于optimizer是不可见的，除非session开始设置OPTIMIZER_USE_INVISIBLE_INDEXES=true。文档上说加hint也可以使用invisible index，不过我没有试出来，


可能是一个bug，也可能oracle在发布正式版的时候去掉了这一功能

  1. SQL> alter index test_idx invisible;
  2.  
  3. Index altered.
  4.  
  5. SQL> select index_name,visibility from user_indexes where index_name='TEST_IDX';
  6.  
  7. INDEX_NAME                                                                                 VISIBILITY
  8. ------------------------------------------------------------------------------------------ ---------------------------
  9. TEST_IDX                                                                                   INVISIBLE
  10.  
  11. SQL> set autot on
  12. SQL> select object_id from test where object_id=100;
  13.  
  14.  OBJECT_ID
  15. ----------
  16.        100
  17.        100
  18.  
  19.  
  20. Execution Plan
  21. ----------------------------------------------------------
  22. Plan hash value: 1357081020
  23.  
  24. --------------------------------------------------------------------------
  25. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  26. --------------------------------------------------------------------------
  27. |   0 | SELECT STATEMENT  |      |     2 |     8 |    86   (2)| 00:00:02 |
  28. |*  1 |  TABLE ACCESS FULL| TEST |     2 |     8 |    86   (2)| 00:00:02 |
  29. --------------------------------------------------------------------------
  30.  
  31. Predicate Information (identified by operation id):
  32. ---------------------------------------------------
  33.  
  34.    1 - filter("OBJECT_ID"=100)
  35.  
  36.  
  37. Statistics
  38. ----------------------------------------------------------
  39.           1  recursive calls
  40.           0  db block gets
  41.         332  consistent gets
  42.         324  physical reads
  43.           0  redo size
  44.         455  bytes sent via SQL*Net to client
  45.         419  bytes received via SQL*Net from client
  46.           2  SQL*Net roundtrips to/from client
  47.           0  sorts (memory)
  48.           0  sorts (disk)
  49.           2  rows processed
  50.  
  51. SQL> alter session set OPTIMIZER_USE_INVISIBLE_INDEXES=true;
  52.  
  53. Session altered.
  54.  
  55. SQL> select object_id from test where object_id=100;
  56.  
  57.  OBJECT_ID
  58. ----------
  59.        100
  60.        100
  61.  
  62.  
  63. Execution Plan
  64. ----------------------------------------------------------
  65. Plan hash value: 2882402178
  66.  
  67. -----------------------------------------------------------------------------
  68. | Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
  69. -----------------------------------------------------------------------------
  70. |   0 | SELECT STATEMENT |          |     2 |     8 |     1   (0)| 00:00:01 |
  71. |*  1 |  INDEX RANGE SCAN| TEST_IDX |     2 |     8 |     1   (0)| 00:00:01 |
  72. -----------------------------------------------------------------------------
  73.  
  74. Predicate Information (identified by operation id):
  75. ---------------------------------------------------
  76.  
  77.    1 - access("OBJECT_ID"=100)
  78.  
  79.  
  80. Statistics
  81. ----------------------------------------------------------
  82.           1  recursive calls
  83.           0  db block gets
  84.           3  consistent gets
  85.           1  physical reads
  86.           0  redo size
  87.         455  bytes sent via SQL*Net to client
  88.         419  bytes received via SQL*Net from client
  89.           2  SQL*Net roundtrips to/from client
  90.           0  sorts (memory)
  91.           0  sorts (disk)
  92.           2  rows processed
  93.  
  94. SQL> alter session set OPTIMIZER_USE_INVISIBLE_INDEXES=false;
  95.  
  96. Session altered.
  97.  
  98. SQL> select /*+ index(TEST TEST_IDX) */ object_id from test where object_id=100;
  99.  
  100.  OBJECT_ID
  101. ----------
  102.        100
  103.        100
  104.  
  105.  
  106. Execution Plan
  107. ----------------------------------------------------------
  108. Plan hash value: 1357081020
  109.  
  110. --------------------------------------------------------------------------
  111. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  112. --------------------------------------------------------------------------
  113. |   0 | SELECT STATEMENT  |      |     2 |     8 |    86   (2)| 00:00:02 |
  114. |*  1 |  TABLE ACCESS FULL| TEST |     2 |     8 |    86   (2)| 00:00:02 |
  115. --------------------------------------------------------------------------
  116.  
  117. Predicate Information (identified by operation id):
  118. ---------------------------------------------------
  119.  
  120.    1 - filter("OBJECT_ID"=100)
  121.  
  122.  
  123. Statistics
  124. ----------------------------------------------------------
  125.           1  recursive calls
  126.           0  db block gets
  127.         332  consistent gets
  128.         324  physical reads
  129.           0  redo size
  130.         455  bytes sent via SQL*Net to client
  131.         419  bytes received via SQL*Net from client
  132.           2  SQL*Net roundtrips to/from client
  133.           0  sorts (memory)
  134.           0  sorts (disk)
  135.           2  rows processed

3. invisible index在RBO下同样不可见

  1. SQL> alter session set optimizer_mode=rule;
  2.  
  3. Session altered.
  4.  
  5. SQL> select object_id from test where object_id=100;
  6.  
  7.  OBJECT_ID
  8. ----------
  9.        100
  10.        100
  11.  
  12.  
  13. Execution Plan
  14. ----------------------------------------------------------
  15. Plan hash value: 1357081020
  16.  
  17. ----------------------------------
  18. | Id  | Operation         | Name |
  19. ----------------------------------
  20. |   0 | SELECT STATEMENT  |      |
  21. |*  1 |  TABLE ACCESS FULL| TEST |
  22. ----------------------------------
  23.  
  24. Predicate Information (identified by operation id):
  25. ---------------------------------------------------
  26.  
  27.    1 - filter("OBJECT_ID"=100)
  28.  
  29. Note
  30. -----
  31.    - rule based optimizer used (consider using cbo)
  32.  
  33.  
  34. Statistics
  35. ----------------------------------------------------------
  36.           0  recursive calls
  37.           0  db block gets
  38.         333  consistent gets
  39.           4  physical reads
  40.           0  redo size
  41.         455  bytes sent via SQL*Net to client
  42.         419  bytes received via SQL*Net from client
  43.           2  SQL*Net roundtrips to/from client
  44.           0  sorts (memory)
  45.           0  sorts (disk)
  46.           2  rows processed

4. 如果索引上是invisible，dbms_stat收集统计信息时要指定OPTIMIZER_USE_INVISIBLE_INDEXES=true，analyze没有问题，这看上去也像一个bug

  1. SQL> alter session set OPTIMIZER_USE_INVISIBLE_INDEXES=false;
  2.  
  3. Session altered.
  4.  
  5. SQL> exec dbms_stats.gather_table_stats(user,'TEST');
  6. BEGIN dbms_stats.gather_table_stats(user,'TEST'); END;
  7.  
  8. *
  9. ERROR at line 1:
  10. ORA-00904: : invalid identifier
  11. ORA-06512: at "SYS.DBMS_STATS", line 17806
  12. ORA-06512: at "SYS.DBMS_STATS", line 17827
  13. ORA-06512: at line 1
  14.  
  15.  
  16. SQL> alter session set OPTIMIZER_USE_INVISIBLE_INDEXES=true;
  17.  
  18. Session altered.
  19.  
  20. SQL> exec dbms_stats.gather_table_stats(user,'TEST');
  21.  
  22. PL/SQL procedure successfully completed.

总体来说invisible index是一个很实用的新特性，不过上面小小的几个测试已经暴露了两个疑似bug的问题。

所以对于invisible index我个人意见还是谨慎为好，最好等11g出R2的时候再在production上尝试。

Eagle Fan oracle 11g

  1. No comments yet.
  1. No trackbacks yet.