介绍Oracle Virtual Index虚拟索引

添加索引是我们经常使用的性能优化手段。在遇到问题的时候，试一试添加索引，看看能不能改变执行计划，是我们分析和解决问题的过

传统的性能优化和调整工作，大都是在系统上线之后，由运维团队进行的。当系统数据量积累到一定程度之后，原有一些隐藏的问题就不断出现。所以，在大数据量、应急场景下进行SQL调优，往往是运维团队经常遇到的问题。

添加索引是我们经常使用的性能优化手段。在遇到问题的时候，试一试添加索引，看看能不能改变执行计划，是我们分析和解决问题的过程手段。但是对于大数据表情况下，快速的创建索引是比较困难的事情。这个时候，我们可以利用Oracle的virtual index技术。

1、环境介绍和数据准备

Virtual Index出现的很早。笔者从9i时候的文档资料中，就可以看到virtual index的技术材料。我们还是选择Oracle 11gR2进行试验。

SQL> select * from v$version;

 

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE11.2.0.1.0Production

 

 

我们创建数据表T作为实验对象，同时创建正常Index和虚拟Index。

 

 

SQL> show user;

User is "scott"

 

SQL> create table t as select * from dba_objects;

Table created

 

SQL> set timing on;

--创建一个普通索引

SQL> create index idx_t_owner on t(owner);

Index created

 

Executed in0.687seconds

 

SQL> select count(*) from t;

 

COUNT(*)

----------

72792

 

Executed in 0.015 seconds

 

 

我们创建virtual index，需要使用nosegment关键字。

 

 

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

Index created

 

Executed in0.047seconds

 

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

 

Executed in 1.716 seconds

 

 

此处我们需要注意一个细节，同样是在7万多基础数据上面创建索引。nosegment虚拟索引使用的时间很短。

 

2、数据字典层面看virtual index

 

我们创建了虚拟索引idx_t_obj，又创建了作为参照的idx_t_owner。下面可以从数据字典的层面，，去看看虚拟索引的内容信息。

 

Oracle所有索引信息都记录在dba_indexes视图中。

 

 

SQL> select index_name, index_type from dba_indexes where wner='SCOTT' and table_name='T';

INDEX_NAMEINDEX_TYPE

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

IDX_T_OWNERNORMAL

 

Executed in 0.031 seconds

 

 

SQL> select segment_name from dba_segments where wner='SCOTT' and segment_name in ('IDX_T_OWNER','IDX_T_OBJ');

 

SEGMENT_NAME

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

IDX_T_OWNER

 

Executed in 0.062 seconds

 

 

我们从dba_indexes和dba_segments中，都只能看到普通索引idx_t_owner的信息。而创建的虚拟索引idx_t_obj没有踪迹。nosegment选项可以让我们猜测是没有索引段对象的创建过程。但是，作为字典的dba_indexes信息没有，就让人疑惑。

 

验证我们的想法，使用dbms_metadata.get_ddl方法，抽取到数据表t的字典定义。其中，我们看到了idx_t_obj的信息。

 

 

CREATE INDEX "SCOTT"."IDX_T_OBJ" ON "SCOTT"."T" ("OBJECT_ID")

PCTFREE 10 INITRANS 2 MAXTRANS 255NOSEGMENT;

 

CREATE INDEX "SCOTT"."IDX_T_OWNER" ON "SCOTT"."T" ("OWNER")

PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS" ;

 

 

相对于idx_t_owner，虚拟索引的定义全文显得很简单，只有nosegment很显眼。

 

那么，作为万物汇总的dba_objects中呢？

 

 

SQL> select owner,object_name, object_id, data_object_id, object_type from dba_objects where object_name in ('IDX_T_OWNER','IDX_T_OBJ');

 

OWNER OBJECT_NAMEOBJECT_ID DATA_OBJECT_ID OBJECT_TYPE

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

SCOTT IDX_T_OWNER7801978019 INDEX

SCOTT IDX_T_OBJ7802078020 INDEX

 

Executed in 0.047 seconds