Oracle之虚拟索引


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;

本页内容版权归属为原作者,如有侵犯您的权益,请通知我们删除。

MySQL 事务提交过程 - 2016-08-18 17:08:13

开发老大要求通过binlog查询一条被修改的数据,数据被查出后问我,有没有可能binlog中不会记录,回答不会,因为数据被修改,若失败直接回滚,不会在binlog中记录,此刻一个朋友用了洪荒之力告诉我,失败的话也会记录,坐地无语,因为他sqlserver dba,用sqlserver的思维考虑mysql,哈哈哈哈哈,用实验让他闭嘴! 简单测试步骤如下: root(yoon) flush logs; Query OK, 0 rows affected (0.01 sec) root((none)) show

Data Guard跳归档恢复的案例 - 2016-08-18 14:08:08

自前些天写了一个脚本之后,今天特意测试了一下,没想到一下子发现了一个大问题。有一套一主两备的10gR2环境,一个异机备库一直在READ ONLY状态,也就意味着数据库在打开之后一直忘了恢复应用归档,然后在某一天发现时,已经延迟了好几个月。无论怎样,还得庆幸发现了这个问题。 目前来看一种行之有效的方法就是重搭备库,但是这种修复方式需要大量的磁盘空间,而且需要恢复的时间较长,怎么改进呢,可以考虑通过基于SCN的增量备份来跳归档恢复。目前的环境是一主两备,再怎么改进呢,我们可以基于备库1来完成基于SCN的增量备份
背景 本文对 5.6 主备场景下,在备库做物理备份遇到死锁的case进行分析,希望对大家有所帮助。 这里用的的物理备份工具是 Percona-XtraBackup(PXB),有的同学可能不清楚其备份流程,所以这里先简单说下,PXB的备份步骤是这样的: 拷贝 InnoDB redo log,这是一个单独的线程在拷,直到备份结束; 拷贝所有InnoDB ibd文件; 加全局读锁,执行 FLUSH TABLES WITH READ LOCK(FTWRL); 拷贝 frm、MYD、MYI 等文件; 获取位点信息,
链接: http://blog.itpub.net/28602568/viewspace-2123386/ 标题: Oracle not exist子查询全扫的优化   作者: lōττéry ©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.] 前言:   之前写过  Oracle 针对子查询里有group by 表全扫的优化 ( 子查询和外层关系是left join);   本次介绍 子查询与外层是not exists的关系是如何优化子查询全扫的;    优化前: SQL SET
【故障处理】 ORA-12545: Connect failed because target host or object does not exist 1    BLOG 文档结构图       2    前言部分 2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~ : ①  错误 ORA-12545: Connect failed because target host or object does not exis
今天写了个脚本,虽然实现的功能不多,但是个人感觉是一个好的开始,架子出来了,后面要补充的细节加进来就逐步完善了。 这个脚本的运行效果如下: OS     Version  is :[ RHEL_6.3 ] Oracle Version  is :[ 11.2.0.3.0] Oracle Instance is :[ dgtest ] dgtest ORACLE_HOME     is :[ /U01/app/oracle/product/11.2.0.2/db_1  ] Oracle  status  is

DUAL系列 - 2016-08-12 17:08:18

DUAL 系列   1    BLOG 文档结构图     2    前言部分   2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~ : ① DUAL遭到破坏后的重建(重点) ②  关于参数replication_dependency_tracking简介 ③ DUAL简介     Tips: ① 本文在 ITpub ( http://blog.itpub.net/26736162 )、博客园 ( http://www.
今天在处理一个工单的时候发现了一个奇怪的现象,开发同学需要创建一个存储过程,目前的架构类似这样的形式 数据库中存在一个属主用户,表,存储过程等对象都创建在这个用户上,而另外有一些连接用户,根据业务和功能可能访问的对象权限也有所不同。所以就会出现一个owner,多个connect user的情况。这种方式可以减少很多误操作,权限控制更为细粒度。 现在的问题是在owner用户上创建存储过程,存储过程会引用若干张表,都在owner用户下,而connect user下则没有这些表相关的任何同义词。看起来好像是不大合

Oracle如何删除表中重复记录 - 2016-08-11 14:08:09

Oracle如何删除表中重复记录 1    引言 在对数据库进行操作过程中我们可能会遇到这种情况,表中的数据可能重复出现,使我们对数据库的操作过程中带来 读诸 多不便,那么怎么删除这些重复没有用的数据呢 ? 平时工作中可能会遇到当试图对库表中的某一列或几列创建唯一索引时,系统提示 ORA-01452  :不能创建唯一索引,发现重复记录。 2    处理过程 重复的数据可能有这样两种情况 : 第一种 是 表中只有某些字段一样,第二种是两行记录完全一样 。删除重复记录后的结果也分为 2 种, 第一种 是重复的

外连接转换为内连接的情况 - 2016-08-10 04:08:06

一般的情况下外连接如下a right join b on a.id=b.id 那么b一定要作为驱动表,原因在于只有b作为驱动表才能得到完整的结果集,如果a作为驱动,那么返回的结果集 可能不完整,但是在特殊的情况的,可能将外连接转换为内连接 考虑如下的情况 b    id  name   1   g1   1   g2   2   g3   2   g4 a   id name   2  gname2 使用如下语句: select b.id,a.id from  a right join b on a.id=