关于不可见索引的学习(r3笔记74天)

不可见索引在日常工作中可能实用比较少,自己体验了一把,还是比较实用的功能,在平时的工作中不妨尝试一下。 我们来先体验一下,然后再细细的总结一下。 测试环境基于11gR2 > sqlplus -v SQL*Plus: Release 11.2.0.2.0 Production 我们先来创建一个表来看看,然后创建一个不可见索引,来看看索引的访问情况。 create table t as select *from dba_objects where object_id is not null and rownum<100000; 我们随机抽取5条数据,后续的查询都会基于里面的1条数据来做说明。 select *from (select object_id from t order by dbms_random.value()) where rownum<5; OBJECT_ID ---------- 9445 4672 5048 4096 打开debug,来看看sql语句的执行情况。 set autot trace exp stat 我们选取了object_id为9445的记录。在没有索引的情况下,看看执行情况,好做比对。 select *from t where object_id=9445 ; Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 116 | 24012 | 26 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T | 116 | 24012 | 26 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=9445) Statistics ---------------------------------------------------------- 18 recursive calls 0 db block gets 177 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 上面的语句毫无疑问走了全表扫描,因为我们还没有创建索引。 我们来创建一个唯一性索引,来在查询中启用一下索引,这个时候索引还是可见的。 create unique index inx_t on t(object_id) ; SQL> select *from t where object_id=9445; 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"=9445) 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 这个时候一切都没有什么特别之处。 我们来尝试下面的步骤。把索引改为不可见索引,或者我们删除已经存在的索引,然后重建一个不可见索引。 --删除,重建索引 drop index inx_t; create unique index inx_t on t(object_id) invisible; --修改索引属性,改为不可见 alter index inx_t invisible; 然后再次尝试运行同样的sql语句。看看是否索引能够正常启用。 select *from t where object_id=9445; Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 116 | 24012 | 26 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T | 116 | 24012 | 26 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=9445) Statistics ---------------------------------------------------------- 9 recursive calls 0 db block gets 139 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 这个时候索引没有启用,感觉就跟删除了一样。其实在dml操作中还是会有消耗和正常的索引是一样的,只是在优化器中对这个索引不可见。 我们来使用hint看看是否能够正常启用。 select /*+index(t inx_t)*/ *from t where object_id=9445; SQL> select /*+index(t inx_t)*/ *from t where object_id=9445; Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 116 | 24012 | 26 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T | 116 | 24012 | 26 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=9445) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 135 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,但是还是不买账,依旧没有反应。 我们来看看怎么使用不可见索引。 默认在数据库参数中,有一个参数optimizer_use_invisible_indexes,默认是不会启用不可见索引的。我们可以从session级别,system级别进行设定。一般都会在session级启用,来查看索引是否能够达到预期目标,然后再决定是否设定为可见。 SQL> show parameter vis NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ optimizer_use_invisible_indexes boolean FALSE 我们在session级启用。 alter session set "optimizer_use_invisible_indexes"=true; 然后执行同样的语句。可以看到索引能够正常启用了。 select *from t where object_id=9445; 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"=9445) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 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 我们恢复原值。来看看,又开始走了全表扫描。 alter session set "optimizer_use_invisible_indexes"=false; select *from t where object_id=9445; Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 116 | 24012 | 26 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T | 116 | 24012 | 26 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=9445) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 135 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 如果在session级别确认了索引能够极大的提高效率,可以修改索引的属性,把索引置为可见状态。 我们设置索引为可见,看看语句的执行情况,索引又能够正常启用了。 alter index inx_t visible; select *from t where object_id=9445; 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"=9445) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 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 table t; create table t as select *from dba_objects where rownum<5000000; create index inx_t on t(object_id) invisible; 然后我们查看锁的情况。可以看到在创建不可见索引的过程中还是存在着锁。在线业务中还是会有影响的。

SID_SERIAL   ORACLE_USE OBJECT_NAME     LOGON_TIM SEC_WAIT OSUSER     MACHINE    PROGRAM                 STATE              STATUS     LOCK_ MODE_HELD
------------ ---------- --------------- --------- -------- ---------- ---------- -------------------- ---------- ---------- ----- ----------
763,33293    N1         OBJ$            04-DEC-14        0 testuser   testdb    sqlplus@xx (TNS V1-V3)  WAITED SHORT TIME   ACTIVE     DML   Row-X (SX)
763,33293    N1         T               04-DEC-14        0 testuser   testdb    sqlplus@xx (TNS V1-V3)  WAITED SHORT TIME   ACTIVE     DML   Share
763,33293    N1         T               04-DEC-14        0 testuser    testdb   sqlplus@xx (TNS V1-V3)  WAITED SHORT TIME   ACTIVE     DL    Row-X (SX)
763,33293    N1         T               04-DEC-14        0 testuser    testdb   sqlplus@xx (TNS V1-V3)  WAITED SHORT TIME   ACTIVE     DL    Row-X (SX)

不可见索引在数据字典视图中和普通索引没有太大的区别,都有对应的索引段,这是在索引对应的数据字典中有一个字段visibility标明索引是否可见。

SQL> select segment_name,segment_type,blocks from user_segments where segment_name='INX_T'
SEGMENT_NAME         SEGMENT_TYPE           BLOCKS
-------------------- ------------------ ----------
INX_T                INDEX                   10240

SQL> SELECT INDEX_NAME,VISIBILITY FROM USER_INDEXES WHERE INDEX_NAME='INX_T';
INDEX_NAME                     VISIBILIT
------------------------------ ---------
INX_T                          INVISIBLE

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

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

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

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Ken的杂谈

【系统设置】CentOS 修改机器名

17430
来自专栏前端桃园

知识体系解决迷茫的你

最近在星球里群里都有小伙伴说道自己对未来的路比较迷茫,一旦闲下来就不知道自己改干啥,今天我这篇文章就是让你觉得一天给你 25 个小时你都不够用,觉得睡觉都是浪费...

19540
来自专栏腾讯社交用户体验设计

ISUX Xcube智能一键生成H5

50920
来自专栏钱塘大数据

中国互联网协会发布:《2018中国互联网发展报告》

在2018中国互联网大会闭幕论坛上,中国互联网协会正式发布《中国互联网发展报告2018》(以下简称《报告》)。《中国互联网发展报告》是由中国互联网协会与中国互联...

13250
来自专栏钱塘大数据

理工男图解零维到十维空间,烧脑已过度,受不了啦!

让我们从一个点开始,和我们几何意义上的点一样,它没有大小、没有维度。它只是被想象出来的、作为标志一个位置的点。它什么也没有,空间、时间通通不存在,这就是零维度。

27330
来自专栏怀英的自我修炼

考研英语-1-导学

英二图表作文要重视。总体而言,英语一会比英语二难点。不过就写作而言,英语二会比英语一有难度,毕竟图表作文并不好写。

11410
来自专栏微信公众号:小白课代表

不只是软件,在线也可以免费下载百度文库了。

不管是学生,还是职场员工,下载各种文档几乎是不可避免的,各种XXX.docx,XXX.pptx更是家常便饭,人们最常用的就是百度文库,豆丁文库,道客巴巴这些下载...

43330
来自专栏haifeiWu与他朋友们的专栏

复杂业务下向Mysql导入30万条数据代码优化的踩坑记录

从毕业到现在第一次接触到超过30万条数据导入MySQL的场景(有点low),就是在顺丰公司接入我司EMM产品时需要将AD中的员工数据导入MySQL中,因此楼主负...

26840
来自专栏FSociety

SQL中GROUP BY用法示例

GROUP BY我们可以先从字面上来理解,GROUP表示分组,BY后面写字段名,就表示根据哪个字段进行分组,如果有用Excel比较多的话,GROUP BY比较类...

5.1K20
来自专栏腾讯高校合作

【倒计时7天】2018教育部-腾讯公司产学合作协同育人项目申请即将截止!

15320

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励