Oracle之虚拟索引

本文涉及的产品
云原生大数据计算服务MaxCompute,500CU*H 100GB 3个月
云原生大数据计算服务 MaxCompute,5000CU*H 100GB 3个月
简介: Oracle之虚拟索引 1  BLOG文档结构图     2  前言部分 2.

Oracle之虚拟索引

 BLOG文档结构图

wps74CE.tmp 

 

 前言部分

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/

③ 若文章代码格式有错乱,推荐使用搜狗360QQ浏览器,也可以下载pdf格式的文档来查看,pdf文档下载地址:http://blog.itpub.net/26736162/viewspace-1624453/,另外itpub格式显示有问题,可以去博客园地址阅读

④ 本篇BLOG中命令的输出部分需要特别关注的地方我都用灰色背景和粉红色字体来表示,比如下边的例子中,thread 1的最大归档日志号为33thread 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皆可,您的批评指正是我写作的最大动力。

 

 

 虚拟索引(Virtual Index)

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

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

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

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

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

Oracle文档中并没有提到虚拟索引的创建语法,实际上就是普通索引语法后面加一个NOSEGMENT关键字即可,B*TREE INDEXBITMAP 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==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: 此列列表已索引

3. 不能创建和虚拟索引同名的实际索引;

4. 可以创建和虚拟索引包含相同列但不同名的实际索引;

5. 虚拟索引分析并且有效,但是数据字典里查不到结果。

 

 实验部分

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功能。

 

我们可以在instancesession两个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,因为这个索引并不存在,也不能支持真正执行。

 

5CBO or RBO

 

此时,笔者想到一个问题。Oracle CBO在工作的时候,索引路径只是执行计划的一种“可选路径”。究竟是FTSFull 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群,学习最实用的数据库技术。

img_e3029f287d989cd04bd75432ecc1c172.png
DBA笔试面试讲解
欢迎与我联系

相关实践学习
基于MaxCompute的热门话题分析
本实验围绕社交用户发布的文章做了详尽的分析,通过分析能得到用户群体年龄分布,性别分布,地理位置分布,以及热门话题的热度。
SaaS 模式云数据仓库必修课
本课程由阿里云开发者社区和阿里云大数据团队共同出品,是SaaS模式云原生数据仓库领导者MaxCompute核心课程。本课程由阿里云资深产品和技术专家们从概念到方法,从场景到实践,体系化的将阿里巴巴飞天大数据平台10多年的经过验证的方法与实践深入浅出的讲给开发者们。帮助大数据开发者快速了解并掌握SaaS模式的云原生的数据仓库,助力开发者学习了解先进的技术栈,并能在实际业务中敏捷的进行大数据分析,赋能企业业务。 通过本课程可以了解SaaS模式云原生数据仓库领导者MaxCompute核心功能及典型适用场景,可应用MaxCompute实现数仓搭建,快速进行大数据分析。适合大数据工程师、大数据分析师 大量数据需要处理、存储和管理,需要搭建数据仓库?学它! 没有足够人员和经验来运维大数据平台,不想自建IDC买机器,需要免运维的大数据平台?会SQL就等于会大数据?学它! 想知道大数据用得对不对,想用更少的钱得到持续演进的数仓能力?获得极致弹性的计算资源和更好的性能,以及持续保护数据安全的生产环境?学它! 想要获得灵活的分析能力,快速洞察数据规律特征?想要兼得数据湖的灵活性与数据仓库的成长性?学它! 出品人:阿里云大数据产品及研发团队专家 产品 MaxCompute 官网 https://www.aliyun.com/product/odps&nbsp;
目录
相关文章
|
5月前
|
SQL Oracle 关系型数据库
Oracle-index索引解读
Oracle-index索引解读
145 0
|
5月前
|
存储 Oracle 关系型数据库
Oracle索引知识看这一篇就足够
Oracle索引知识看这一篇就足够
|
5月前
|
存储 Oracle 关系型数据库
Oracle 12c的多重索引:数据的“多维导航仪”
【4月更文挑战第19天】Oracle 12c的多重索引提升数据查询效率,如同多维导航仪。在同一表上创建针对不同列的多个索引,加速检索过程。虽然过多索引会增加存储和维护成本,但合理选择和使用索引策略,结合位图、函数索引等高级特性,能优化查询,应对复杂场景。数据管理员应善用这些工具,根据需求进行索引管理,支持企业数据分析。
|
11月前
|
索引
Oracle-序列、索引和同义词
Oracle-序列、索引和同义词
45 0
|
5月前
|
SQL Oracle 关系型数据库
[Oracle]索引
[Oracle]索引
103 0
[Oracle]索引
|
11月前
|
存储 SQL Oracle
Oracle优化避免索引失效
Oracle优化避免索引失效
343 0
|
存储 Oracle 关系型数据库
9-6 Oracle 管理索引
9-6 Oracle 管理索引