关于虚拟索引的学习(r3笔记第75天)

昨天简单总结了下不可见索引,今天来说说虚拟索引。 这两个索引听起来有点类似。其实差别还是比较大。 不可见索引有对应的索引段,而虚拟索引没有对应的索引段存在。 不可见索引可以通过alter语句来直接切换可见不可见。而对于虚拟索引而言这些操作都不支持。 不可见索引可以在user_indexes中查到对应的数据字典信息。但是虚拟索引在user_indexes中都没有记录,最后只能从dba_objects里面勉强查到一条它存在的记录。 不可见索引和虚拟索引都有对应的数据库参数,可以通过alter session,system来修改生效。 不可见索引在优化器中进行了屏蔽,使得索引的可见/不可见都可以灵活的切换,而虚拟索引是在希望在优化器中做标识,使得语句的执行计划能够考虑到对应的虚拟索引的作用。 个人觉得,在日常的使用中对于索引创建存在争议的场景中,可以考虑使用虚拟索引,来通过查看执行计划来比较前后的变化。如果提升的幅度很大,再考虑创建对应的索引。 我们来举个简单的例子来说明一下虚拟索引。 我们来创建一个表,然后首先验证创建一个普通索引验证索引能够正常启用,然后删除索引,创建虚拟索引来看看语句的执行情况。 创建表t SQL> create table t as select *from dba_objects where object_id is not null and rownum<100000; Table created. 然后随机抽取4条数据。 SQL> select *from (select object_id from t order by dbms_random.value()) where rownum<5; OBJECT_ID ---------- 22969 20703 13851 8040 SQL> set autot trace exp stat 我们来看看当前的执行计划,因为没有索引,索引会走全表扫描。

SQL> select *from t where object_id=8040;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1233 |   249K|   267   (2)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |  1233 |   249K|   267   (2)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"=8040)
Statistics
----------------------------------------------------------
         18  recursive calls
          0  db block gets
       1525  consistent gets
          0  physical reads
          0  redo size
       1632  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

我们创建对应的索引。来看看语句的执行情况。

SQL> create unique index inx_t on t(object_id) ;
Index created.
SQL> select *from t where object_id=8040;
Execution Plan
----------------------------------------------------------
Plan hash value: 1855406669
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |   207 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |   207 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | INX_T |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=8040)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          1  physical reads
          0  redo size
       1499  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)
          1  rows processed

删除索引,创建一个虚拟索引。来对比一下执行计划的情况。

SQL> drop index inx_t;
Index dropped.
SQL> create unique index inx_t on t(object_id) nosegment;
Index created.
SQL> select *from t where object_id=8040;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1233 |   249K|   267   (2)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |  1233 |   249K|   267   (2)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"=8040)
Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
       1487  consistent gets
          0  physical reads
          0  redo size
       1632  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

发现创建了虚拟索引之后,这个测试的效果跟没有创建一样。我们来看看使用hint是否有作用。

SQL> select /*+index(t inx_t)*/ *from t where object_id=8040;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1233 |   249K|   267   (2)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |  1233 |   249K|   267   (2)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"=8040)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1483  consistent gets
          0  physical reads
          0  redo size
       1632  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

这个时候,我们得知虚拟索引没有启用,至于启用的方法,就是通过一个数据库参数_use_nosegment_indexes来实现。可以在session,system级别做设置。一般来说我们在session级做简单的对比测试,如果执行计划的效果提升很多,然后可以根据情况再创建存在段的索引。或者在系统级开启这个开关。 我们先在session级别开启。

SQL> alter session set "_USE_NOSEGMENT_INDEXES" = true;
Session altered.

我们来查看一下语句的执行情况。

SQL> select *from t where object_id=8040;
Execution Plan
----------------------------------------------------------
Plan hash value: 1855406669
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |   207 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |   207 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | INX_T |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=8040)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1483  consistent gets
          0  physical reads
          0  redo size
       1632  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

可以看到,索引被启用了。其实这个过程中的资源消耗很低,因为没有对应的索引段存在,完全是根据优化器的判断。 我们关闭这个开关。

SQL> alter session set "_USE_NOSEGMENT_INDEXES" = false;
Session altered.

尝试使用alter语句来rebuild这个虚拟索引。

SQL> alter index inx_t rebuild;
alter index inx_t rebuild
*
ERROR at line 1:
ORA-08114: can not alter a fake index

alter语句在虚拟索引中式不支持的。 我们来查查数据字典里的信息。

SQL> set autot off
SQL>  select segment_name,segment_type,blocks from user_segments where segment_name='INX_T';
no rows selected
SQL> select index_name ,dropped ,segment_created from user_indexes where index_name='INX_T';
no rows selected
在user_segments,user_indexes中都没有对应的记录存在,我都怀疑索引是否存在。
SQL> create unique index inx_t on t(object_id) nosegment;
create unique index inx_t on t(object_id) nosegment
                    *
ERROR at line 1:
ORA-00955: name is already used by an existing object

最后在dba_objects里面终于找到一条记录。

SQL> select object_name, object_type from dba_objects where object_name = 'INX_T';
OBJECT_NAME                                                                                                                      OBJECT_TYPE
-------------------------------------------------------------------------------------------------------------------------------- -------------------
INX_T                                                                                                                            INDEX

关于虚拟索引,可以在metalink上参考Virtual Indexes (Doc ID 1401046.1)

本文分享自微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2014-12-05

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

关于修改数据库参数的测试(r3笔记第18天)

在性能调优的时候,会发现很多类型的问题,有些问题可能通过使用隐含参数就能够解决,不过这种变更需要特别注意,因为做隐含参数的变更无形中会影响到其它的sql语句运行...

28240
来自专栏乐沙弥的世界

视图 v$sql,v$sqlarea,$sqltext,v$sqltext_with_newlines 的差异

      视图v$sql,v$sqlarea,v$sqltext,v$sqltext_with_newlines 是几个经常容易混淆的视图,主要是提供libr...

29230
来自专栏跟着阿笨一起玩NET

把数据库中表的内容转存为XML文件

11700
来自专栏乐沙弥的世界

Oracle自适应共享游标

    自适应游标共享Adaptive Cursor Sharing或扩展的游标共享(Extended Cursor Sharing)是Oracle 11g...

12720
来自专栏杨建荣的学习笔记

关于all_procedures的问题分析 (r9笔记第61天)

今天快下班的时候有一个同事问我一个存储过程的权限是否做过修改。我简单看了下发现这个滚出过程已经是很久以前创建的了,一直没有做过修改,所以就反馈给 他了。但是他过...

35760
来自专栏杨建荣的学习笔记

走索引扫描的慢查询(r3笔记45天)

今天查看awr报告的时候,发现一条sql语句异常。 Elapsed Time (s) Executions Elapsed Time per Exec (s)...

40480
来自专栏杨建荣的学习笔记

有关Oracle role的总结

oracle的role算是对sys privilege 和object privilege的打包。 今天深入的测试了下,还算有不少的东西。 role不是sche...

44760
来自专栏杨建荣的学习笔记

巧用shell生成数据库检查脚本 (74天)

在生产环境中需要部署大量的数据变更。对于新增的表,需要注意权限和同义词等。但是手动去检查这些变更是否生效就很麻烦。而且也不易维护,比如写好了一个脚本,可能在过一...

34870
来自专栏乐沙弥的世界

使用SQL tuning advisor(STA)自动优化SQL

      Oracle 10g之后的优化器支持两种模式,一个是normal模式,一个是tuning模式。在大多数情况下,优化器处于normal模式。基于CBO...

35130
来自专栏数据库新发现

Oracle诊断案例-Sql_trace之一

http://www.eygle.com/case/sql_trace_1.htm

13330

扫码关注云+社区

领取腾讯云代金券