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

jeanron100

发布于 2018-03-15 11:33:55

6320

发布于 2018-03-15 11:33:55

昨天简单总结了下不可见索引，今天来说说虚拟索引。这两个索引听起来有点类似。其实差别还是比较大。不可见索引有对应的索引段，而虚拟索引没有对应的索引段存在。不可见索引可以通过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)

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2014-12-05，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库