oracle虚拟索引简介

一、目的与应用场景 虚拟索引的目的是模拟索引的存在而不用真实创建一个完整索引。这允许开发者创建虚拟索引来查看相关执行计划， 而不用等到真实创建完索引后才能查看索引对执行计划的影响。虚拟索引主要应用于SQL优化调优当中， 尤其是给数据库管理员（DBA）在生产环境进行SQL优化时使用。DBA可以根据虚拟索引的测试效果来判断是否需要创建实际索引。 二、创建与验证 创建虚拟索引： 语法：create index index_name on table_name(col_name) nosegment; 示例：create index idx_test_virtual on tt(object_id) nosegment; 注意：在创建虚拟索引时，需要在CREATE INDEX语句中指定nosegment子句。 验证虚拟索引： 创建虚拟索引后，需要设置隐含参数"_use_nosegment_indexes"为true，Oracle才会选择虚拟索引。 使用EXPLAIN PLAN或AUTOTRACE来查看执行计划，验证虚拟索引是否生效。 实例：   SQL> create table tt as select * from dba_objects; Table created. SQL> create index idx_test_virtual on tt(object_id) nosegment; Index created. SQL>     alter session set "_use_nosegment_indexes"=true;   Session altered. SQL> set autotrace traceonly explain; SQL>  select * from tt where object_id='100'; Execution Plan ---------------------------------------------------------- Plan hash value: 1614994782 -------------------------------------------------------------------------------------------------------- | Id  | Operation     | Name        | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT     |        |    15 |  7215 |     5 (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TT        |    15 |  7215 |     5 (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN     | IDX_TEST_VIRTUAL |   277 |       |     1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("OBJECT_ID"=100) Note -----    - dynamic statistics used: dynamic sampling (level=2) 三、特点与限制 特点： 虚拟索引不会增加存储空间的使用。 虚拟索引支持B-TREE索引和BIT位图索引。 在CBO（Cost-Based Optimizer，基于代价的优化器）模式下，Oracle优化器会考虑虚拟索引； 但在RBO（Rule-Based Optimizer，基于规则的优化器）模式下，需要添加hint才行。 限制： 虚拟索引无法执行ALTER INDEX选项，如重建索引等。 使用回收站特性时，虚拟索引必须显式DROP，才能创建同名的索引。 不能创建和虚拟索引同名的实际索引。 虚拟索引分析有效，但数据字典里可能查不到结果，因为Oracle可能内部临时保存了分析结果。 四、使用场景示例 假设有一个性能较差的SQL语句，涉及几个数据量非常大的表。如果想尝试新增一个索引来优化性能， 但不确定优化器是否会使用该索引，或者使用该索引后执行计划是否能朝着预想的方向发展。 此时，可以在大表上创建一个虚拟索引来测试其效果，而无需承担创建和删除实际索引的高昂代价和潜在风险。 五、注意事项 虚拟索引并不是物理存在的，所以不要用自动跟踪去测试虚拟索引，因为那是实际执行的效果，用不到虚拟索引。 在生产环境中使用虚拟索引时，应谨慎操作，并确保不会对数据库中运行的其他查询的执行计划产生负面影响。 综上所述，Oracle虚拟索引是一种强大的工具，可以帮助DBA在SQL优化过程中更有效地评估索引的潜在影响。 然而，在使用时也需要了解其特点和限制，以确保正确和有效地利用这一功能。