一、引言
DBA在日常维护管理数据库进行低性能SQL分析时,有时候需要通过创建索引对SQL进行优化,但有些时候我们创建的索引是否能用到?这个只能创建以后才能看出效果,但是在实际工作中,特别是对大表创建索引对系统性能有很大影响,因此我们不得不避开业务高峰时段,但是有没有一种办法创建索引而不影响性能呢?有,那就是虚拟索引。
虚拟索引不是物理存在的,它并不会创建实际的索引段,只是在数据字典中加了一个索引的记录,使得优化器能够意识到一个索引的存在,从而判断是否使用该索引作为访问路径。作用仅仅是为了DBA作SQL优化时使用,DBA根据虚拟索引的优化效果决定是否创建物理索引。
二、虚拟索引类型
虚拟索引支持B-TREE索引和BIT位图索引,在CBO模式下ORACLE优化器会考虑虚拟索引,但是在RBO模式下需要添加hint才行。
三、虚拟索引创建实例
SQL> drop table t purge;
表已删除。
SQL> create table t as select * from dba_objects;
表已创建。
--创建虚拟索引,首先要将_use_nosegment_indexes的隐含参数设置为true
SQL> alter session set "_use_nosegment_indexes"=true;
会话已更改。
--虚拟索引的创建语法比较简单,实际上就是普通索引语法后面加一个nosegment关键字
SQL> create index ix_t_id on t(object_id) nosegment;
索引已创建。
SQL> explain plan for select * from t where object_id=;
已解释。
SQL> set linesize
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value:
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| | SELECT STATEMENT | | | | ()| :: |
| | TABLE ACCESS BY INDEX ROWID| T | | | ()| :: |
|* | INDEX RANGE SCAN | IX_T_ID | | | ()| :: |
--------------------------------------------------------------------------------------- Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ - access("OBJECT_ID"=) Note
-----
- dynamic sampling used for this statement (level=) 已选择18行。 SQL> set autotrace traceonly
SQL> select * from t where object_id=; 未选定行 执行计划
----------------------------------------------------------
Plan hash value: ---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| | SELECT STATEMENT | | | | ()| :: |
| | TABLE ACCESS BY INDEX ROWID| T | | | ()| :: |
|* | INDEX RANGE SCAN | IX_T_ID | | | ()| :: |
--------------------------------------------------------------------------------------- Predicate Information (identified by operation id):
--------------------------------------------------- - access("OBJECT_ID"=) Note
-----
- dynamic sampling used for this statement (level=) 统计信息
----------------------------------------------------------
recursive calls
db block gets
consistent gets
physical reads
redo size
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
rows processed SQL> set autotrace off
--以下看的是真实执行计划,显然是用不到索引。
SQL> alter session set statistics_level=all; 会话已更改。 SQL> select * from t where object_id=; 未选定行 SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2qhwh0nzrzx2r, child number
-------------------------------------
select * from t where object_id= Plan hash value: ------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| | SELECT STATEMENT | | | | |::00.01 | |
|* | TABLE ACCESS FULL| T | | | |::00.01 | | PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id):
--------------------------------------------------- - filter("OBJECT_ID"=) Note
-----
- dynamic sampling used for this statement (level=) 已选择22行。
--从数据字段中是无法找到这个索引的。
SQL> select index_name,status from user_indexes where table_name='T'; 未选定行
四、虚拟索引的特点
4.1、虚拟索引无法执行alter index选项
SQL> alter index IX_T_ID rebuild;
alter index IX_T_ID rebuild
*
第 1 行出现错误:
ORA-08114: 无法变更假索引
4.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 BIN$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: 此列列表已索引
4.3、不能创建和虚拟索引同名的实际索引;
4.4、可以创建和虚拟索引包含相同列但不同名的实际索引;
4.5、虚拟索引分析并且有效,但是数据字典里查不到结果。
Oracle之虚拟索引的更多相关文章
-
【索引】Oracle之不可见索引和虚拟索引的比对
[索引]Oracle之不可见索引和虚拟索引的比对 Oracle之不可见索引 :http://blog.itpub.net/26736162/viewspace-2124044/ Oracle之虚 ...
-
ORACLE虚拟索引(Virtual Index)
ORACLE虚拟索引(Virtual Index) 虚拟索引概念 虚拟索引(Virtual Indexes)是一个定义在数据字典中的假索引(fake index),它没有相关的索引段.虚拟索引的目 ...
-
Oracle性能调优之虚拟索引用法简介
本博客记录一下Oracle虚拟索引的用法,虚拟索引是定义在数据字典中的伪索引,可以说是伪列,没有修改的索引字段的.虚拟索引的目的模拟索引,不会增加存储空间的使用,有了虚拟索引,开发者使用执行计划的时候 ...
-
Oracle虚拟索引,大表或生产环境下预估索引效果的好东西
在数据库优化过程中,索引的重要性是不言而喻的,但是在我们进行性能调整过程中, 一个索引是否能够被使用到,在索引创建之前是存在不确定性的. 而创建索引又是一个代价很高的操作,尤其是数据量很大的情况下,在 ...
-
Oracle 11g 虚拟列 Virtual Column介绍
Oracle 11G 虚拟列 Virtual Column Oracle 11G 在表中引入了虚拟列,虚拟列是一个表达式,在运行时计算,不存储在数据库中,不能更新虚拟列的值. 定义一个虚拟列的语法: ...
-
ORACLE不可见索引(Invisible Indexes)
不可见索引概念 不可见索引(Invisible Index)是ORACLE 11g引入的新特性.不可见索引是会被优化器忽略的不可见索引,除非在会话或系统级别上将OPTIMIZER_USE_INVISI ...
-
ORACLE表、索引和分区详解
ORACLE表.索引和分区 一.数据库表 每种类型的表都有不同的特性,分别应用与不同的领域 堆组织表 聚簇表(共三种) 索引组织表 嵌套表 临时表 外部表和对象表 1.行迁移 建表过程中可以指定以下两 ...
-
Oracle序列和索引
序列和索引 一.序列 1.序列的概念: 序列(Sequence)是用来生成连续的整数数据的对象.它常常用来作为主键的增长列,可以升序,也可以降序. 2.创建序列: 语法:创建序列 ...
-
Oracle中的索引详解
Oracle中的索引概述 索引与表一样,也属于段(segment)的一种.里面存放了用户的数据,跟表一样需要占用磁盘空间.索引是一种允许直接访问数据表中某一数据行的树型结构,为了提高查询效率而引入,是 ...
随机推荐
-
关于JAVA中的static方法、并发问题以及JAVA运行时内存模型
一.前言 最近在工作上用到了一个静态方法,跟同事交流的时候,被一个问题给问倒了,只怪基础不扎实... 问题大致是这样的,“在多线程环境下,静态方法中的局部变量会不会被其它线程给污染掉?”: 我当时的想 ...
-
Java运算符优先级(转)
转自:http://www.cnblogs.com/gw811/archive/2012/10/13/2722752.html Java运算符优先级 序列号 符号 名称 结合性(与操作数) 目数 说明 ...
-
ofbiz进击 第二节。 control 理解与创建
首先要说的是,学习ofbiz,要去http://ofbiz.apache.org/官网里面,去看右边菜单里 Management Apps 的例子,然后找到类似的页面,去看调用的源码方法. co ...
-
ServiceStack
https://github.com/ServiceStack/ServiceStack/wiki/Routing Service Gateway https://github.com/Service ...
-
JavaScript 继承的几种模式
/** * Created by 2016 on 2016/6/5. */ //1.原型链继承 //把子类的原型,定义为超类的实例 通过原型来访问超类的方法和属性 function Person(){ ...
-
坑中速记整理! 使用 kotlin 写第一个 ReactNative Android 模块
Kotlin 和 Swift, 两大新宠! 借 ReactNative 熟悉下 kotlin 的用法,不料掉坑里面了.昨晚花了大半夜,趁这会儿思路清晰,把涉及到的一些关键信息,迅速整理下. 最佳的使用 ...
-
[SDOI2011]消耗战
题目描述 在一场战争中,战场由n个岛屿和n-1个桥梁组成,保证每两个岛屿间有且仅有一条路径可达.现在,我军已经侦查到敌军的总部在编号为1的岛屿,而且他们已经没有足够多的能源维系战斗,我军胜利在望.已知 ...
-
ffdshow 源代码分析 8: 视频解码器类(TvideoCodecDec)
===================================================== ffdshow源代码分析系列文章列表: ffdshow 源代码分析 1: 整体结构 ffds ...
-
JS——函数
一.函数的种类 1.无参函数 function showName() { alert("我是无参函数"); } 2.有参函数 在函数中的参数为"形式参数" 形式 ...
-
python学习记录(一)
1.打印操作 >>> print('hello') hello >>> print(1+2) 3 2.字符串操作 ① ') Traceback (most rece ...