Oracle之虚拟索引

时间:2022-09-05 13:50:38

一、引言

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之虚拟索引的更多相关文章

  1. 【索引】Oracle之不可见索引和虚拟索引的比对

    [索引]Oracle之不可见索引和虚拟索引的比对    Oracle之不可见索引 :http://blog.itpub.net/26736162/viewspace-2124044/ Oracle之虚 ...

  2. ORACLE虚拟索引(Virtual Index)

    ORACLE虚拟索引(Virtual Index)   虚拟索引概念 虚拟索引(Virtual Indexes)是一个定义在数据字典中的假索引(fake index),它没有相关的索引段.虚拟索引的目 ...

  3. Oracle性能调优之虚拟索引用法简介

    本博客记录一下Oracle虚拟索引的用法,虚拟索引是定义在数据字典中的伪索引,可以说是伪列,没有修改的索引字段的.虚拟索引的目的模拟索引,不会增加存储空间的使用,有了虚拟索引,开发者使用执行计划的时候 ...

  4. Oracle虚拟索引,大表或生产环境下预估索引效果的好东西

    在数据库优化过程中,索引的重要性是不言而喻的,但是在我们进行性能调整过程中, 一个索引是否能够被使用到,在索引创建之前是存在不确定性的. 而创建索引又是一个代价很高的操作,尤其是数据量很大的情况下,在 ...

  5. Oracle 11g 虚拟列 Virtual Column介绍

    Oracle 11G 虚拟列 Virtual Column Oracle 11G 在表中引入了虚拟列,虚拟列是一个表达式,在运行时计算,不存储在数据库中,不能更新虚拟列的值. 定义一个虚拟列的语法: ...

  6. ORACLE不可见索引(Invisible Indexes)

    不可见索引概念 不可见索引(Invisible Index)是ORACLE 11g引入的新特性.不可见索引是会被优化器忽略的不可见索引,除非在会话或系统级别上将OPTIMIZER_USE_INVISI ...

  7. ORACLE表、索引和分区详解

    ORACLE表.索引和分区 一.数据库表 每种类型的表都有不同的特性,分别应用与不同的领域 堆组织表 聚簇表(共三种) 索引组织表 嵌套表 临时表 外部表和对象表 1.行迁移 建表过程中可以指定以下两 ...

  8. Oracle序列和索引

    序列和索引 一.序列 1.序列的概念: 序列(Sequence)是用来生成连续的整数数据的对象.它常常用来作为主键的增长列,可以升序,也可以降序. 2.创建序列: 语法:创建序列           ...

  9. Oracle中的索引详解

    Oracle中的索引概述 索引与表一样,也属于段(segment)的一种.里面存放了用户的数据,跟表一样需要占用磁盘空间.索引是一种允许直接访问数据表中某一数据行的树型结构,为了提高查询效率而引入,是 ...

随机推荐

  1. 关于JAVA中的static方法、并发问题以及JAVA运行时内存模型

    一.前言 最近在工作上用到了一个静态方法,跟同事交流的时候,被一个问题给问倒了,只怪基础不扎实... 问题大致是这样的,“在多线程环境下,静态方法中的局部变量会不会被其它线程给污染掉?”: 我当时的想 ...

  2. Java运算符优先级(转)

    转自:http://www.cnblogs.com/gw811/archive/2012/10/13/2722752.html Java运算符优先级 序列号 符号 名称 结合性(与操作数) 目数 说明 ...

  3. ofbiz进击 第二节。 control 理解与创建

    首先要说的是,学习ofbiz,要去http://ofbiz.apache.org/官网里面,去看右边菜单里   Management Apps  的例子,然后找到类似的页面,去看调用的源码方法. co ...

  4. ServiceStack

    https://github.com/ServiceStack/ServiceStack/wiki/Routing Service Gateway https://github.com/Service ...

  5. JavaScript 继承的几种模式

    /** * Created by 2016 on 2016/6/5. */ //1.原型链继承 //把子类的原型,定义为超类的实例 通过原型来访问超类的方法和属性 function Person(){ ...

  6. 坑中速记整理! 使用 kotlin 写第一个 ReactNative Android 模块

    Kotlin 和 Swift, 两大新宠! 借 ReactNative 熟悉下 kotlin 的用法,不料掉坑里面了.昨晚花了大半夜,趁这会儿思路清晰,把涉及到的一些关键信息,迅速整理下. 最佳的使用 ...

  7. [SDOI2011]消耗战

    题目描述 在一场战争中,战场由n个岛屿和n-1个桥梁组成,保证每两个岛屿间有且仅有一条路径可达.现在,我军已经侦查到敌军的总部在编号为1的岛屿,而且他们已经没有足够多的能源维系战斗,我军胜利在望.已知 ...

  8. ffdshow 源代码分析 8: 视频解码器类(TvideoCodecDec)

    ===================================================== ffdshow源代码分析系列文章列表: ffdshow 源代码分析 1: 整体结构 ffds ...

  9. JS——函数

    一.函数的种类 1.无参函数 function showName() { alert("我是无参函数"); } 2.有参函数 在函数中的参数为"形式参数" 形式 ...

  10. python学习记录(一)

    1.打印操作 >>> print('hello') hello >>> print(1+2) 3 2.字符串操作 ① ') Traceback (most rece ...