介绍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 - ProductionPL/SQL Release 11.2.0.1.0 - ProductionCORE11.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 STATISTICSSTORAGE（INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 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 INDEXSCOTT IDX_T_OBJ7802078020 INDEX Executed in 0.047 seconds 在dba_objects中，我们找到idx_t_obj的信息，它依然被认为是一个索引。更重要的是，我们定位到了object_id和data_object_id，这两个分别为数据库对象的逻辑id和物理段id。 dba_indexes字典视图的基础数据表是ind$基表。其中定义了所有索引对象的信息。我们借助object_id去检查，发现了无法查询到的idx_t_obj对象记录。 SQL> select obj#, ts#, file#, block#, bo# from ind$ where obj# in （78019, 78020）; OBJ#TS#FILE#BLOCK#BO#---------- ---------- ---------- ---------- ----------78019441586780177802040078017 Executed in 0.015 seconds SQL> select owner, object_name from dba_objects where object_id=78017; OWNER OBJECT_NAME----- ---------------SCOTT T Executed in 0.016 seconds 我们可以从bo#编号，确定的确是数据表scott.t的索引对象。那么，我们思考一个问题，既然ind$中存在对应记录，为什么dba_indexes不能检索到这个信息呢？通过抽取dba_indexes的源码信息，我们可以猜到端倪。 from sys.ts$ ts, sys.seg$ s,sys.user$ iu, sys.obj$ io, sys.user$ u,sys.ind$ i,sys.obj$ o,sys.user$ itu, sys.obj$ ito, sys.deferred_stg$ dswhere u.user# = o.owner#and o.obj# = i.obj#and i.bo# = io.obj#and io.owner# = iu.user#and bitand（i.flags, 4096） = 0and bitand（o.flags, 128） = 0and i.ts# = ts.ts# （+）and i.file# = s.file# （+）and i.block# = s.block# （+）and i.ts# = s.ts# （+）and i.obj# = ds.obj# （+）and i.indmethod# = ito.obj# （+）and ito.owner# = itu.user# （+）; 虽然虚拟索引是没有段的，在seg$中必然没有对应记录。但是SQL语句中对于这个条件定义的是外连接。也就是说，即使没有段结构，索引也能显示出来。疑点就落在对一些列flag标记的bitand操作上了。我们检查一下ind$的基础flags取值，就可以知道原因了。 SQL> select obj#, bitand（flags, 4096） from ind$ where obj# in （78019, 78020）; OBJ# BITAND（FLAGS,4096）---------- ------------------780190780204096 Executed in 0.016 seconds 看来，虽然ind$中包括信息，但是不显示出来，也是Oracle的一个本意。下面我们继续来看virtual index的实际工作效果。更多详情请继续阅读第2页的精彩内容：http://www.linuxidc.com/Linux/2013-09/89856p2.htm

Oracle 11g新SQL Trace 10046方法CentOS 5.9安装Oracle 11g相关资讯 Oracle Virtual Index Oracle虚拟索引

Oracle性能优化之虚拟索引（04月18日）

本文评论查看全部评论（0）