Virtual Index的测试作用

今天偶然有同事建议我使用虚拟索引来测试下oracle的查询选择。看了下网上的文档http://www.oracle-base.com/articles/misc/virtual-indexes.php, 使用很简单。

在正常建索引的语句后面加上“NOSEGMENT”即可。

一下是测试过程记录：

原执行计划如下，

| Id  | Operation                     | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                       | 71467 |    11M|      |  1681K  (1)| 05:36:13 |
|   1 |  HASH GROUP BY                |                       | 71467 |    11M|   12M|  1681K  (1)| 05:36:13 |
|   2 |   NESTED LOOPS                |                       |       |       |      |            |          |
|   3 |    NESTED LOOPS               |                       | 71467 |    11M|      |  1678K  (1)| 05:35:42 |
|*  4 |     TABLE ACCESS FULL         | EDM_MS_OUTPUT_HISTORY | 71385 |  4949K|      |  1392K  (1)| 04:38:35 |
|*  5 |     INDEX RANGE SCAN          | SYS_C0012114_NEW      |     1 |       |      |     3   (0)| 00:00:01 |
|*  6 |    TABLE ACCESS BY INDEX ROWID| EDM_RESPONSES         |     1 |    94 |      |     4   (0)| 00:00:01 |

添加虚拟index：

SQL> create index wow_data.IND_EDM_MS_HIST_1 ON wow_data.EDM_MS_OUTPUT_HISTORY(comm_id,SUBSCRIBERKEY) tablespace INDEXES NOSEGMENT;

Index created.

可以像真实索引一样进行analyze：

SQL> EXEC DBMS_STATS.gather_index_stats('WOW_DATA', 'IND_EDM_MS_HIST_1');
PL/SQL procedure successfully completed.

添加后的执行计划：

| Id  | Operation                     | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                       | 71467 |    11M|      |   842K  (1)| 02:48:34 |
|   1 |  HASH GROUP BY                |                       | 71467 |    11M|   12M|   842K  (1)| 02:48:34 |
|   2 |   MERGE JOIN                  |                       | 71467 |    11M|      |   840K  (1)| 02:48:03 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| EDM_MS_OUTPUT_HISTORY | 71385 |  4949K|      |   490  (95)| 00:00:06 |
|*  4 |     INDEX FULL SCAN           | IND_EDM_MS_HIST_1     |   188K|       |      |   487  (95)| 00:00:06 |
|*  5 |    SORT JOIN                  |                       |   575K|    51M|  124M|   839K  (1)| 02:47:57 |
|*  6 |     TABLE ACCESS FULL         | EDM_RESPONSES         |   575K|    51M|      |   827K  (1)| 02:45:28 |

这里它改变了其他表的执行计划，但是也有一定的效果. 如果需要使用EDM_RESPONSES的索引可以使用hint来调用。

删除虚拟index：

SQL> drop index wow_data.IND_EDM_MS_HIST_1 ;
Index dropped.

Virtual Index仅供测试使用，但是它提供了无需浪费时间建索引就可以来查看到oracle针对特定index的执行计划。