今天同事在优化的时候说到了虚拟索引这个东西，下面我们就来测试一下ORACLE数据的invisible与virtual索引，这两个索引对我们优化来说都有很大的好处。invisible索引在11G中才出现的，以前的版本中不可用。

     invisible索引：当我们在生产环境中优化的时候，创建一个索引后，可能会影响到其它的SQL的执行效果，如果使用invisible索引，就不会影响到其它SQL语句的执行效果，因为它对其它是不可见，除非指定OPTIMIZER_USE_INVISIBLE_INDEXES这个参数为ture。invisible索引要占用空间，同时可以使用alter语句来对索引进行操作。

     virtual索引：跟invisible一样，正常情况下不可用，除非指定_USE_NOSEGMENT_INDEXES参数为true。因为是虚拟的索引，所以创建他们不会分配空间，不能使用alter语句对它进行操作，可以对它进行分析。如果在一张几十G的表上创建一个virtual是很方面的。

     下面就是关于invisible与virtual的测试。

     测试环境是OS :RHEL 5.6 X*6_64 DB:11.2.0.3

  1  invisible索引：

  1. 1.1 创建invisible索引，创建方法跟其它索引差不多，只是在最后增加invisible就可以了。 
  2. SQL> create index scott.pk_test_owner on scott.test(owner) invisible; 
  3.  
  4. Index created. 
  5. 1.2 执行测试语句 
  6. SQL> select count(*) from scott.test where owner='SCOTT'; 
  7.  
  8.   COUNT(*) 
  9. ---------- 
  10.          9 
  11. 1.3 查看执行计划 
  12. 这里就是select * from table(dbms_xplan.display_cursor(null,null,all))这个语句 
  13. SQL> @/home/oracle/rs/sql/plan.sql 
  14.  
  15. PLAN_TABLE_OUTPUT 
  16. ---------------------------------------------------------------------------------------------------- 
  17. SQL_ID  248pfx54z79v4, child number 0 
  18. ------------------------------------- 
  19. select count(*) from scott.test where owner='SCOTT' 
  20.  
  21. Plan hash value: 1950795681 
  22.  
  23. --------------------------------------------------------------------------- 
  24. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
  25. --------------------------------------------------------------------------- 
  26. |   0 | SELECT STATEMENT   |      |       |       |   297 (100)|          | 
  27. |   1 |  SORT AGGREGATE    |      |     1 |    17 |            |          | 
  28. |*  2 |   TABLE ACCESS FULL| TEST |    12 |   204 |   297   (1)| 00:00:04 | 
  29. --------------------------------------------------------------------------- 
  30. #这里我们看到了走的是全面扫描 
  31. Query Block Name / Object Alias (identified by operation id): 
  32. ------------------------------------------------------------- 
  33.  
  34.    1 - SEL$1 
  35.    2 - SEL$1 / TEST@SEL$1 
  36.  
  37. Predicate Information (identified by operation id): 
  38. --------------------------------------------------- 
  39.  
  40.    2 - filter("OWNER"='SCOTT') 
  41.  
  42. Column Projection Information (identified by operation id): 
  43. ----------------------------------------------------------- 
  44.  
  45.    1 - (#keys=0) COUNT(*)[22] 
  46.  
  47. Note 
  48. ----- 
  49.    - dynamic sampling used for this statement (level=2) 
  50.  
  51.  
  52. 34 rows selected. 
  53. 1.4 设置参数为true 
  54. SQL> alter session set OPTIMIZER_USE_INVISIBLE_INDEXES=true; 
  55.  
  56. Session altered. 
  57.  
  58.  
  59. 1.4 执行SQL 
  60. SQL> select count(*) from scott.test where owner='SCOTT'; 
  61.  
  62.   COUNT(*) 
  63. ---------- 
  64.          9 
  65. 1.6 查看执行计划 
  66. SQL> @/home/oracle/rs/sql/plan 
  67.  
  68. PLAN_TABLE_OUTPUT 
  69. ---------------------------------------------------------------------------------------------------- 
  70. SQL_ID  248pfx54z79v4, child number 1 
  71. ------------------------------------- 
  72. select count(*) from scott.test where owner='SCOTT' 
  73.  
  74. Plan hash value: 4000037813 
  75.  
  76. ----------------------------------------------------------------------------------- 
  77. | Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     | 
  78. ----------------------------------------------------------------------------------- 
  79. |   0 | SELECT STATEMENT  |               |       |       |     1 (100)|          | 
  80. |   1 |  SORT AGGREGATE   |               |     1 |    17 |            |          | 
  81. |*  2 |   INDEX RANGE SCAN| PK_TEST_OWNER |     9 |   153 |     1   (0)| 00:00:01 | 
  82. ----------------------------------------------------------------------------------- 
  83. #注意这里已经走的是index range scan扫描了，已经达到效果了 
  84. Query Block Name / Object Alias (identified by operation id): 
  85. ------------------------------------------------------------- 
  86.  
  87.    1 - SEL$1 
  88.    2 - SEL$1 / TEST@SEL$1 
  89.  
  90. Predicate Information (identified by operation id): 
  91. --------------------------------------------------- 
  92.  
  93.    2 - access("OWNER"='SCOTT') 
  94.  
  95. Column Projection Information (identified by operation id): 
  96. ----------------------------------------------------------- 
  97.  
  98.    1 - (#keys=0) COUNT(*)[22] 
  99.  
  100. Note 
  101. ----- 
  102.    - dynamic sampling used for this statement (level=2) 
  103.  
  104.  
  105. 34 rows selected. 
  106.  
  107. 1.7 查看索引的类型 
  108. SQL> select index_name,visibility from dba_indexes where owner='SCOTT' and table_name='TEST'; 
  109.  
  110. INDEX_NAME                     VISIBILIT 
  111. ------------------------------ --------- 
  112. PK_TEST_OWNER                  INVISIBLE 
  113. 1.8 查看索引占用的空间大小 
  114. SQL> col segment_name for a20 
  115. SQL> select segment_name,sum(bytes/1024/1024)||'M' from dba_extents where segment_name='PK_TEST_OWNER'  group by segment_name; 
  116.  
  117. SEGMENT_NAME         SUM(BYTES/1024/1024)||'M' 
  118. -------------------- ----------------------------------------- 
  119. PK_TEST_OWNER        2M 
  120. 1.9 重建索引 
  121. SQL> alter index scott.pk_test_owner rebuild; 
  122.  
  123. Index altered. 
  124. SQL> select index_name,visibility from dba_indexes where owner='SCOTT' and table_name='TEST'; 
  125.  
  126. INDEX_NAME                     VISIBILIT 
  127. ------------------------------ --------- 
  128. PK_TEST_OWNER                  INVISIBLE 
  129. 1.10 invisible/visible转化 
  130. 我们可以通过alter语句把对invisible/visible进行相互的转换 
  131. SQL> alter index scott.pk_test_owner visible; 
  132.  
  133. Index altered. 
  134.  
  135. SQL> select index_name,visibility from dba_indexes where owner='SCOTT' and table_name='TEST'; 
  136.  
  137. INDEX_NAME                     VISIBILIT 
  138. ------------------------------ --------- 
  139. PK_TEST_OWNER                  VISIBLE 
2 virtual索引
  1. 2.1 创建一个virutal索引，就是在普通创建索引的方法后面增加一个nosegment就可以。 
  2. SQL> create index scott.pk_test_objectname on scott.test(object_name) nosegment; 
  3.  
  4. Index created. 
  5. 2.2 执行测试语句，并查看执行计划 
  6. SQL> select count(*) from scott.test where object_name='TEST'; 
  7.  
  8.  
  9. Execution Plan 
  10. ---------------------------------------------------------- 
  11. Plan hash value: 1950795681 
  12.  
  13. --------------------------------------------------------------------------- 
  14. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
  15. --------------------------------------------------------------------------- 
  16. |   0 | SELECT STATEMENT   |      |     1 |    66 |   297   (1)| 00:00:04 | 
  17. |   1 |  SORT AGGREGATE    |      |     1 |    66 |            |          | 
  18. |*  2 |   TABLE ACCESS FULL| TEST |    12 |   792 |   297   (1)| 00:00:04 | 
  19. --------------------------------------------------------------------------- 
  20. #注意这里走的全表扫描 
  21. Predicate Information (identified by operation id): 
  22. --------------------------------------------------- 
  23.  
  24.    2 - filter("OBJECT_NAME"='TEST') 
  25.  
  26. Note 
  27. ----- 
  28.    - dynamic sampling used for this statement (level=2) 
  29.  
  30.  
  31. Statistics 
  32. ---------------------------------------------------------- 
  33.           5  recursive calls 
  34.           0  db block gets 
  35.        1134  consistent gets 
  36.        1061  physical reads 
  37.           0  redo size 
  38.         526  bytes sent via SQL*Net to client 
  39.         523  bytes received via SQL*Net from client 
  40.           2  SQL*Net roundtrips to/from client 
  41.           0  sorts (memory) 
  42.           0  sorts (disk) 
  43.           1  rows processed 
  44.  2.3 设置参数为true          
  45. SQL>  alter session set "_USE_NOSEGMENT_INDEXES" = true; 
  46.  
  47. Session altered. 
  48. 2.4 执行测试语句，并查看执行计划 
  49. SQL> select count(*) from scott.test where object_name='TEST'; 
  50.  
  51.  
  52. Execution Plan 
  53. ---------------------------------------------------------- 
  54. Plan hash value: 2753868186 
  55.  
  56. ---------------------------------------------------------------------------------------- 
  57. | Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Time     | 
  58. ---------------------------------------------------------------------------------------- 
  59. |   0 | SELECT STATEMENT  |                    |     1 |    66 |     1   (0)| 00:00:01 | 
  60. |   1 |  SORT AGGREGATE   |                    |     1 |    66 |            |          | 
  61. |*  2 |   INDEX RANGE SCAN| PK_TEST_OBJECTNAME |    12 |   792 |     1   (0)| 00:00:01 | 
  62. ---------------------------------------------------------------------------------------- 
  63. #已经走了索引了，达到了效果 
  64. Predicate Information (identified by operation id): 
  65. --------------------------------------------------- 
  66.  
  67.    2 - access("OBJECT_NAME"='TEST') 
  68.  
  69. Note 
  70. ----- 
  71.    - dynamic sampling used for this statement (level=2) 
  72.  
  73.  
  74. Statistics 
  75. ---------------------------------------------------------- 
  76.           0  recursive calls 
  77.           0  db block gets 
  78.        1064  consistent gets 
  79.        1061  physical reads 
  80.           0  redo size 
  81.         526  bytes sent via SQL*Net to client 
  82.         523  bytes received via SQL*Net from client 
  83.           2  SQL*Net roundtrips to/from client 
  84.           0  sorts (memory) 
  85.           0  sorts (disk) 
  86.           1  rows processed 
  87. SQL> select table_name,index_name from dba_indexes where owner='SCOTT' and table_name='TEST'; 
  88.  
  89. TABLE_NAME                     INDEX_NAME 
  90. ------------------------------ ------------------------------ 
  91. TEST                           PK_TEST_OWNER 
  92. 2.5 查看索引是否分配了segment，这里为0，所以没有分配segment，不占用空间的。 
  93. SQL> select count(*) from dba_segments where segment_name='PK_TEST_OBJECTNAME'; 
  94.  
  95.   COUNT(*) 
  96. ---------- 
  97.          0 
  98. 2.6 测试是否可以用alter对index进行操作 
  99. SQL> alter index PK_TEST_OBJECTNAME rebuild; 
  100. alter index PK_TEST_OBJECTNAME rebuild 
  101. * 
  102. ERROR at line 1: 
  103. ORA-01418: specified index does not exist 

invisible/virtual索引就测试到这里。