Oracle之虚拟索引
1 BLOG文档结构图
2 前言部分
2.1 导读和注意事项
各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~:
① Oracle虚拟索引的使用
Tips:
① 本文在ITpub
(http://blog.itpub.net/26736162)、博客园( http://www.cnblogs.com/lhrbest)和微信公众号(xiaomaimiaolhr )有同步更新② 文章中用到的所有代码,相关软件,相关资料请前往小麦苗的云盘下载(http://blog.itpub.net/26736162/viewspace-1624453/)
③ 若文章代码格式有错乱,推荐使用搜狗、360或QQ浏览器,也可以下载pdf格式的文档来查看,pdf文档下载地址:http://blog.itpub.net/26736162/viewspace-1624453/,另外itpub格式显示有问题,可以去博客园地址阅读
④ 本篇BLOG中命令的输出部分需要特别关注的地方我都用灰色背景和粉红色字体来表示,比如下边的例子中,thread 1的最大归档日志号为33,thread 2的最大归档日志号为43是需要特别关注的地方;而命令一般使用黄色背景和红色字体标注;对代码或代码输出部分的注释一般采用蓝色字体表示。
List of Archived Logs in backup set 11
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48
1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58
2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49
2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53
[ZHLHRDB1:root]:/>lsvg -o
T_XDESK_APP1_vg
rootvg
[ZHLHRDB1:root]:/>
00:27:22 SQL> alter tablespace idxtbs read write;
====》2097152*512/1024/1024/1024=1G
本文如有错误或不完善的地方请大家多多指正,ITPUB留言或QQ皆可,您的批评指正是我写作的最大动力。
3 虚拟索引(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资源。
查找系统中已经存在的虚拟索引:
SELECT INDEX_OWNER, INDEX_NAME
FROM DBA_IND_COLUMNS
WHERE INDEX_NAME NOT LIKE 'BIN$%'
MINUS
SELECT OWNER, INDEX_NAME
FROM DBA_INDEXES;
3.1 虚拟索引类型及特点
虚拟索引支持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: 无法变更假索引
2. 使用回收站特性的时候,虚拟索引必须显示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 BIN7jAFlUG6b1zgQAB/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: 此列列表已索引
3. 不能创建和虚拟索引同名的实际索引;
4. 可以创建和虚拟索引包含相同列但不同名的实际索引;
5. 虚拟索引分析并且有效,但是数据字典里查不到结果。
4 实验部分
4.1 个人示例
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
SYS@lhrdb> SELECT * FROM T_VI_20160818_01_LHR WHERE OBJECT_ID=1;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3209519479
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 2898 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_VI_20160818_01_LHR | 14 | 2898 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_VI01_ID | 312 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1249 consistent gets
0 physical reads
0 redo size
1343 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SYS@lhrdb>
以下看的是真实执行计划,显然是用不到索引。
SYS@lhrdb> SET AUTOTRACE OFF
SYS@lhrdb> ALTER SESSION SET STATISTICS_LEVEL=ALL;
Session altered.
SYS@lhrdb> SELECT * FROM T_VI_20160818_01_LHR WHERE OBJECT_ID=1;
no rows selected
SYS@lhrdb> SELECT SQL_ID,CHILD_NUMBER,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%SELECT * FROM T_VI_20160818_01_LHR WHERE OBJECT_ID=1%';
SQL_ID CHILD_NUMBER SQL_TEXT
------------- ------------ ---------------------------------------------------
d5v59m8vyyz7d 0 SELECT * FROM T_VI_20160818_01_LHR WHERE OBJECT_ID=1
SYS@lhrdb> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('d5v59m8vyyz7d',0,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID d5v59m8vyyz7d, child number 0
-------------------------------------
SELECT * FROM T_VI_20160818_01_LHR WHERE OBJECT_ID=1
Plan hash value: 847945500
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 1249 |
|* 1 | TABLE ACCESS FULL| T_VI_20160818_01_LHR | 1 | 14 | 0 |00:00:00.01 | 1249 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
22 rows selected.
查找系统中已经存在的虚拟索引:
SYS@lhrdb> SELECT INDEX_OWNER, INDEX_NAME
2 FROM DBA_IND_COLUMNS
3 WHERE INDEX_NAME NOT LIKE 'BIN$%'
4 MINUS
5 SELECT OWNER, INDEX_NAME
6 FROM DBA_INDEXES;
INDEX_OWNER INDEX_NAME
------------------------------ ------------------------------
SYS IX_VI01_ID
虚拟索引是定义在数据字典中的伪索引,但没有相关的索引段。虚拟索引的目的是模拟索引的存--而不用真实的创建一个完整索引。这允许开发者创建虚拟索引来查看相关执行计划而不用等到真实创建完索引才能查看索引对执行计划的影响,并且不会增加存储空间的使用。如果我们观察到优化器生成了一个昂贵的执行计划并且SQL调整指导建议我们对某些的某列创建索引,但在生产数据库环境中创建索引与测试并不总是可以操作。我们需要确保创建的索引将不会对数据库中的其它查询产生负面影响,因此可以使用虚拟索引。
下面举例进行说明
1.创建一个测试表test
SQL> create table test as select * from dba_objects; Table created.
2.从表test查询object_name等于standard的记录
SQL> select * from test where object_name='STANDARD'; OWNER ------------------------------ OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE ------------------------------ ---------- -------------- ------------------- CREATED LAST_DDL_TIM TIMESTAMP STATUS T G S ------------ ------------ ------------------- ------- - - - SYS STANDARD 888 PACKAGE 19-APR-10 19-APR-10 2003-04-18:00:00:00 VALID N N N OWNER ------------------------------ OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE ------------------------------ ---------- -------------- ------------------- CREATED LAST_DDL_TIM TIMESTAMP STATUS T G S ------------ ------------ ------------------- ------- - - - SYS STANDARD 889 PACKAGE BODY 19-APR-10 19-APR-10 2010-04-19:10:22:58 VALID N N N
3.查询上面查询的执行计划
SQL> set autotrace traceonly explain SQL> select * from test where object_name='STANDARD'; Execution Plan ---------------------------------------------------------- Plan hash value: 1357081020 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 1416 | 155 (1)| 00:00:02 | |* 1 | TABLE ACCESS FULL| TEST | 8 | 1416 | 155 (1)| 00:00:02 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_NAME"='STANDARD') Note ----- - dynamic sampling used for this statement
4.在表test的object_name列上创建一个虚拟索引
SQL> create index test_index on test(object_name) nosegment; Index created.
为了创建虚拟索引必须在create index语句中指定nosegment子句,并且不会创建索引段。
5.来验证虚拟索引不会创建索引段
SQL> set autotrace off SQL> select index_name from dba_indexes where table_name = 'TEST' and index_name = 'TEST_INDEX'; no rows selected SQL> col OBJECT_NAME format a20; SQL> select object_name, object_type from dba_objects where object_name = 'TEST_INDEX'; OBJECT_NAME OBJECT_TYPE -------------------- ------------------- TEST_INDEX INDEX
从上面的结果可以看到索引对象已经创建,但没有创建索引段。
6.重新执行sql查看创建的虚拟索引是否被使用
SQL> set autotrace traceonly explain SQL> select * from test where object_name='STANDARD'; Execution Plan ---------------------------------------------------------- Plan hash value: 1357081020 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 1416 | 155 (1)| 00:00:02 | |* 1 | TABLE ACCESS FULL| TEST | 8 | 1416 | 155 (1)| 00:00:02 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_NAME"='STANDARD') Note ----- - dynamic sampling used for this statement
从上面的执行计划可以清楚地看到创建的虚拟索引并没有被使用
7.为了能使用所创建的虚拟索引,需要将_USE_NOSEGMENT_INDEXES设置为true
SQL> alter session set "_USE_NOSEGMENT_INDEXES" = true; Session altered.
8.重新执行sql查看创建的虚拟索引是否被使用
SQL> set long 900 SQL> set linesize 900 SQL> select * from test where object_name='STANDARD'; Execution Plan ---------------------------------------------------------- Plan hash value: 2627321457 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 8 | 1416 | 5 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 8 | 1416 | 5 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TEST_INDEX | 238 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_NAME"='STANDARD') Note ----- - dynamic sampling used for this statement
从上面的执行计划可以看到当设置隐含参数_USE_NOSEGMENT_INDEXES后,优化器将会使用创建的虚拟索引。在使用虚拟索引需要注意,我们可以分析虚拟索引,但不能重建虚拟索引,如果重建虚拟索引会收到ORA-8114: "User attempted to alter a fake 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
CORE 11.2.0.1.0 Production
我们创建数据表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 in 0.687 seconds
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 in 0.047 seconds
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_NAME INDEX_TYPE
------------------------------ ---------------------------
IDX_T_OWNER NORMAL
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 255 NOSEGMENT ;
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_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
----- --------------- ---------- -------------- -------------------
SCOTT IDX_T_OWNER 78019 78019 INDEX
SCOTT IDX_T_OBJ 78020 78020 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#
---------- ---------- ---------- ---------- ----------
78019 4 4 1586 78017
78020 4 0 0 78017
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$ ds
where u.user# = o.owner#
and o.obj# = i.obj#
and i.bo# = io.obj#
and io.owner# = iu.user#
and bitand(i.flags, 4096) = 0
and bitand(o.flags, 128) = 0
and 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)
---------- ------------------
78019 0
78020 4096
Executed in 0.016 seconds
看来,虽然ind$中包括信息,但是不显示出来,也是Oracle的一个本意。
下面我们继续来看virtual index的实际工作效果。
3、“不成功”的实验
Virtual index的特点就是没有段segment结构的支持,在数据字典的基表中存在痕迹。那么,它对于我们的执行计划有什么样的影响呢?
这里我们需要区分两个概念,就是执行计划SEP的生成和执行。Oracle优化器是一个独立的组件,是可以单独进行工作的。同时,Oracle执行计划真正的情况,是从Shared Pool中抽取出来的。
Virtual index没有segment结构支持,所以根本不可能实际去执行,即使优化器命令走virtual index路径。那么,我们从执行计划和实际执行两个角度看问题。
首先,我们不做任何额外的配置,看看在virtual index存在的情况下,默认情况下会给我们带来什么。
--反映Oracle Optimizer的判定;
SQL> explain plan for select * from t where object_id = 10000;
Explained
Executed in 0.016 seconds
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97 | 273 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 1 | 97 | 273 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=10000)
13 rows selected
Executed in 0.109 seconds
Explain plan for是优化器单独工作,SQL是不真正执行的!看来virtual index不会在这个时候影响优化器。
那么,运行时如何?我们先执行SQL,从shared pool中抽取sql_id信息。
SQL> select /*+demo*/count(*) from t where object_id=10000;
COUNT(*)
----------
1
Executed in 0.078 seconds
SQL> select sql_id, executions, version_count from v$sqlarea where sql_text like 'select /*+demo*/count(*)%';
SQL_ID EXECUTIONS VERSION_COUNT
------------- ---------- -------------
d2s9wnt37f4g7 1 1
利用dbms_xplan包进行抽取。
SQL> select * from table(dbms_xplan.display_cursor(sql_id => 'd2s9wnt37f4g7'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID d2s9wnt37f4g7, child number 0
-------------------------------------
select /*+demo*/count(*) from t where object_id=10000
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 273 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| T | 1 | 5 | 273 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"=10000)
19 rows selected
实际执行的SQL中,也没有执行virtual index路径。所以:在默认的情况下,virtual index既不会参与单独的Optimizer决定,也不会生成与virtual index有关的真实执行计划来执行。
4、“受到影响”的优化器
要让virtual index起作用,需要调整一个Oracle隐含参数_use_nosegment_indexes。默认这个参数取值为false,表示不开启nosegment indexes功能。
我们可以在instance和session两个level去设置这个参数。
SQL> alter session set "_use_nosegment_indexes" = true;
Session altered
Executed in 0 seconds
我们再来看刚刚的实验。
--explain plan for命令
SQL> explain plan for select * from t where object_id = 10000;
Explained
Executed in 0.016 seconds
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2999300365
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97 | 2 (0)| 0
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 97 | 2 (0)| 0
|* 2 | INDEX RANGE SCAN | IDX_T_OBJ | 1 | | 1 (0)| 0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=10000)
14 rows selected
Executed in 0.078 seconds
我们发现,单独调用optimizer工作的时候,virtual index路径被走到了。那么,真实执行呢?
--真正去执行一下
SQL> select /*+demo_2*/count(*) from t where object_id=10000;
COUNT(*)
----------
1
Executed in 0.015 seconds
SQL> select sql_id, executions, version_count from v$sqlarea where sql_text like 'select /*+demo_2*/count(*)%';
SQL_ID EXECUTIONS VERSION_COUNT
------------- ---------- -------------
8gbx9grs6cga2 1 1
Executed in 0.016 seconds
SQL> select * from table(dbms_xplan.display_cursor(sql_id => '8gbx9grs6cga2'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 8gbx9grs6cga2, child number 0
-------------------------------------
select /*+demo_2*/count(*) from t where object_id=10000
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 273 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| T | 1 | 5 | 273 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"=10000)
19 rows selected
Executed in 0.078 seconds
从实际情况看,设置了隐含参数后,单独CBO进行执行计划判定的时候,是会考虑nosegment索引的。但是,在真正执行的时候,还是不会考虑virtual index,因为这个索引并不存在,也不能支持真正执行。
5、CBO or RBO
此时,笔者想到一个问题。Oracle CBO在工作的时候,索引路径只是执行计划的一种“可选路径”。究竟是FTS(Full Table Scan)还是Index Path,取决于统计量计算出的成本值。
那么,virtual index在工作的时候,没有段结构与之对应,统计量也必然有一些不完全。那么,Oracle在生成执行计划的时候,是否进行CBO判定呢?
一个最简单的方法,就是偏移列索引路径判定。
--构造偏移列
SQL> update t set wner='SYS' where owner <> 'SCOTT';
72773 rows updated
Executed in 7.628 seconds
SQL> commit;
Commit complete
Executed in 0 seconds
删除原有的owner列一般索引,创建nosegment索引。
SQL> drop index idx_t_owner;
Index dropped
Executed in 0.234 seconds
SQL> create index idx_t_owner on t(owner) nosegment;
Index created
Executed in 0.078 seconds
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
Executed in 1.123 seconds
--不存在index正式结论;
SQL> select count(*) from dba_indexes where wner='SCOTT' and index_name='IDX_T_OWNRE';
COUNT(*)
----------
0
Executed in 0.015 seconds
测试对owner列的选择执行计划。
SQL> alter session set "_use_nosegment_indexes" = true;
Session altered
Executed in 0 seconds
--小数值执行计划
SQL> explain plan for select * from t where wner='SCOTT';
Explained
Executed in 0.015 seconds
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1516787156
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 1235 | 2 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 13 | 1235 | 2 (0)|
|* 2 | INDEX RANGE SCAN | IDX_T_OWNER | 13 | | 1 (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SCOTT')
14 rows selected
Executed in 0.031 seconds
--大数值偏移执行计划
SQL> explain plan for select * from t where wner='SYS';
Explained
Executed in 0 seconds
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 72772 | 6751K| 273 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 72772 | 6751K| 273 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
13 rows selected
Executed in 0.063 seconds
看来,在工作中的确是CBO成本运算。对于一些不存在的统计值,Oracle可能是选择一个默认值来定义计算。
6、结论
Oracle Virtual Index是一个研究工具,是我们在投产环境上继续SQL优化方案研究时候的不错工具。它既满足了让我们创建索引,看执行计划效果的需求。同时也不会消耗很多的索引build资源。
About Me
...............................................................................................................................
● 本文作者:小麦苗,只专注于数据库的技术,更注重技术的运用
● 本文在itpub(http://blog.itpub.net/26736162)、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号(xiaomaimiaolhr)上有同步更新
● 本文itpub地址:http://blog.itpub.net/26736162/abstract/1/
● 本文博客园地址:http://www.cnblogs.com/lhrbest
● 本文pdf版及小麦苗云盘地址:http://blog.itpub.net/26736162/viewspace-1624453/
● 数据库笔试面试题库及解答:http://blog.itpub.net/26736162/viewspace-2134706/
● QQ群:230161599 微信群:私聊
● 联系我请加QQ好友(646634621),注明添加缘由
● 于 2017-05-09 09:00 ~ 2017-05-30 22:00 在魔都完成
● 文章内容来源于小麦苗的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解
● 版权所有,欢迎分享本文,转载请保留出处
...............................................................................................................................
拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号:xiaomaimiaolhr,扫描右边的二维码加入小麦苗的QQ群,学习最实用的数据库技术。