合 Oracle之虚拟索引

虚拟索引(Virtual Index)

传统的性能优化和调整工作，大都是在系统上线之后，由运维团队进行的。当系统数据量积累到一定程度之后，原有一些隐藏的问题就不断出现。所以，在大数据量、应急场景下进行SQL调优，往往是运维团队经常遇到的问题。添加索引是我们经常使用的性能优化手段。在遇到问题的时候，试一试添加索引，看看能不能改变执行计划，是我们分析和解决问题的过程手段。但是对于大数据表情况下，快速的创建索引是比较困难的事情。这个时候，我们可以利用Oracle的virtual index技术。

在数据库优化中，索引的重要性不言而喻。但是，在性能调整过程中，一个索引是否能被查询用到，在索引创建之前是无法确定的，而创建索引是一个代价比较高的操作，尤其是数据量较大的时候。

虚拟索引(Virtual Index)是定义在数据字典中的伪索引，但没有相关的索引段。虚拟索引的目的是模拟索引的存在而不用真实的创建一个完整索引。这允许开发者创建虚拟索引来查看相关执行计划而不用等到真实创建完索引才能查看索引对执行计划的影响，并且不会增加存储空间的使用。如果我们观察到优化器生成了一个昂贵的执行计划并且SQL调整指导建议我们对某些的某列创建索引，但在生产数据库环境中创建索引与测试并不总是可以操作。我们需要确保创建的索引将不会对数据库中的其它查询产生负面影响，因此可以使用虚拟索引。

虚拟索引不是物理存在的，它并不会创建实际的索引段，只是在数据字典中加了一个索引的记录，使得优化器能够意识到一个索引的存在，从而判断是否使用该索引作为访问路径。当然，实际上最终查询的访问路径是不会使用该虚拟索引的。所以，虚拟索引的用处就是用来判断一个索引对于SQL的执行计划的影响，尤其是对整个数据库的影响，从而判断是否需要创建物理索引。可以将这个索引删掉并重建常规索引。

虚拟索引与不可见索引的不同之处在于不可见索引是有与之相关的存储的，只是优化器不能选择它们。而虚拟索引没有与之相关的存储空间。由于这个原因，虚拟索引也被称为无段索引。

Oracle文档中并没有提到虚拟索引的创建语法，实际上就是普通索引语法后面加一个NOSEGMENT关键字即可，B*TREE INDEX和BITMAP INDEX都可以。

必须设置隐含参数"_USE_NOSEGMENT_INDEXES"=TRUE（默认为FALSE)后CBO优化器模式才能使用虚拟索引，RBO优化器模式无法使用虚拟索引。

--创建虚拟索引，首先要将_use_nosegment_indexes的隐含参数设置为true

SQL> alter session set "_use_nosegment_indexes"=true;

--虚拟索引的创建语法比较简单，实际上就是普通索引语法后面加一个nosegment关键字

SQL> create index ix_t_id on t(object_id) nosegment;

索引已创建。

Oracle Virtual Index是一个研究工具，是我们在投产环境上继续SQL优化方案研究时候的不错工具。它既满足了让我们创建索引，看执行计划效果的需求。同时也不会消耗很多的索引build资源。

查找系统中已经存在的虚拟索引：

虚拟索引类型及特点

虚拟索引支持B-TREE索引和BIT位图索引，在CBO模式下ORACLE优化器会考虑虚拟索引，但是在RBO模式下需要添加hint才行。

同样的数据量，若使用nosegment虚拟索引使用的时间很短。

1. 虚拟索引无法执行alter index选项

SQL> alter index IX_T_ID rebuild;

alter index IX_T_ID rebuild*

第 1 行出现错误:

ORA-08114: 无法变更假索引

1. 使用回收站特性的时候，虚拟索引必须显示drop，才能创建同名的索引。

SQL> create index ind_status on t(status);

索引已创建。

SQL> drop table t;

表已删除。

SQL> flashback table t to before drop;

闪回完成。

SQL> select table_name,index_name,status from user_indexes where table_name='T';

TABLE_NAME INDEX_NAME STATUS

------------------------------ ------------------------------ --------

T BIN$7jAFlUG6b1zgQAB/AQAPyw==$0 VALID

SQL> create index ind_object_id on t(object_id);

索引已创建。

SQL> create index inds_status on t(status);create index inds_status on t(status)

*

第 1 行出现错误:

ORA-01408: 此列列表已索引

1. 不能创建和虚拟索引同名的实际索引；
2. 可以创建和虚拟索引包含相同列但不同名的实际索引；
3. 虚拟索引分析并且有效，但是数据字典里查不到结果。

实验部分

个人示例

SYS@lhrdb> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

SYS@lhrdb> CREATE TABLE T_VI_20160818_01_LHR AS SELECT * FROM DBA_OBJECTS;

Table created.

虚拟索引的创建语法比较简单，实际上就是普通索引语法后面加一个nosegment关键字

SYS@lhrdb> CREATE INDEX IX_VI01_ID ON T_VI_20160818_01_LHR(OBJECT_ID) NOSEGMENT;

Index created.

从数据字段中是无法找到这个索引的。

SYS@lhrdb> SELECT INDEX_NAME,STATUS FROM DBA_INDEXES WHERE TABLE_NAME='T_VI_20160818_01_LHR';

no rows selected

SYS@lhrdb> COL OBJECT_NAME FORMAT A10

SYS@lhrdb> SELECT D.OWNER,D.OBJECT_NAME,D.OBJECT_TYPE FROM DBA_OBJECTS D WHERE D.OBJECT_NAME='IX_VI01_ID';

OWNER OBJECT_NAM OBJECT_TYPE

------------------------------ ---------- -------------------

SYS IX_VI01_ID INDEX

SYS@lhrdb> SELECT TO_CHAR(DBMS_METADATA.GET_DDL('INDEX','IX_VI01_ID')) FROM DUAL;

TO_CHAR(DBMS_METADATA.GET_DDL('INDEX','IX_VI01_ID'))

-------------------------------------------------------------------

CREATE INDEX "SYS"."IX_VI01_ID" ON "SYS"."T_VI_20160818_01_LHR" ("OBJECT_ID")

PCTFREE 10 INITRANS 2 MAXTRANS 255 NOSEGMENT

使用虚拟索引，首先要将_use_nosegment_indexes的隐含参数设置为true

SYS@lhrdb> ALTER SESSION SET "_USE_NOSEGMENT_INDEXES"=TRUE;

Session altered.

SYS@lhrdb> SHOW PARAMETER optimizer_mode

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

optimizer_mode string ALL_ROWS

SYS@lhrdb> SET AUTOTRACE TRACEONLY

SYS@lhrdb> SET LINE 9999

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。