`
nenty
  • 浏览: 108429 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
社区版块
存档分类
最新评论

Oracle索引使用规则

    博客分类:
  • sql
阅读更多
逻辑上:
Single column 单行索引
Concatenated 多行索引
Unique 唯一索引
NonUnique 非唯一索引
Function-based函数索引
Domain 域索引


物理上:
Partitioned 分区索引
NonPartitioned 非分区索引
B-tree:
Normal 正常型B树
Rever Key 反转型B树
Bitmap 位图索引


索引结构:
B-tree:
适合与大量的增、删、改(OLTP);
不能用包含OR操作符的查询;
适合高基数的列(唯一值多)
典型的树状结构;
每个结点都是数据块;
大多都是物理上一层、两层或三层不定,逻辑上三层;
叶子块数据是排序的,从左向右递增;
在分支块和根块中放的是索引的范围;
Bitmap:
适合与决策支持系统;
做UPDATE代价非常高;
非常适合OR操作符的查询;
基数比较少的时候才能建位图索引;
树型结构:
索引头
开始ROWID,结束ROWID(先列出索引的最大范围)
BITMAP
每一个BIT对应着一个ROWID,它的值是1还是0,如果是1,表示着BIT对应的ROWID有值;

B*tree索引的话通常在访问小数据量的情况下比较适用,比如你访问不超过表中数据的5%,当然这只是个相对的比率,适用于一般的情况。bitmap的话在数据仓库中使用较多,用于低基数列,比如性别之类重复值很多的字段,基数越小越好。



    B* 树索引

这些是我所说的 “ 传统 “ 索引。到目前为止,这是 Oracle 和大多数其他数据库中最常用的索引。 B* 树的构造类似于二叉树,能根据键提供一行或一个行集的快速访问,通常只需很少的读操作就能找到正确的行。不过,需要注意重要的一点, ” B* 树 “ 中的 ” B “ 不代表二叉( binary ),而代表平衡( b alanced )。B* 树索引并不是一颗二叉树,这一点在介绍如何在磁盘上物理地存储 B* 树时就会了解到。 B* 树索引有以下子类型:

索引组织表( index organized table ):索引组织表以 B* 树结构存储。堆表的数据行是以一种无组织的方式存储的(只要有可用的空间,就可以放数据),而 IOT 与之不同, IOT 中的数据要按主键的顺序存储和排序。对应用来说, IOT 表现得与 “ 常规 “ 表并无二致;需要使用 SQL 来正确地访问 IOT 。 IOT 对信息获取、空间系统和 OLAP 应用最为有用。 IOT 在上一章已经详细地讨论过。

B*树聚簇索引( B*tree cluster index )这些是传统 B* 树索引的一个变体(只是稍有变化)。 B* 树聚簇索引用于对聚簇键建立索引(见第 11. 章中 “ 索引聚簇表 “ 一节),所以这一章不再讨论。在传统 B* 树中 ,键都指向一行;而 B* 树聚簇不同,一个聚簇键会指向一个块,其中包含与这个聚簇键相关的多行。

降序索引( descending index ):降序索引允许数据在索引结构中按 “ 从大到小 “ 的顺序(降序)排序,而不是按 ” 从小到大 “ 的顺序(升序)排序。我们会解释为什么降序索引很重要,并说明降序索引如何工作。

反向键索引( reverse key index ):这也是 B* 树索引,只不过键中的字节会 “ 反转 “ 。利用反向键索引,如果索引中填充的是递增的值,索引条目在索引中可以得到更均匀的分布。例如,如果使用一个序列来生成主键,这个序列将生成诸如 987500 、 987501 、 987502 等值。这些值是顺序的,所以倘若使用一 个传统的 B* 树索引,这些值就可能放在同一个右侧块上,这就加剧了对这一块的竞争。利用反向键, Oracl e则会逻辑地对 205789 、 105789 、 005789 等建立索引。 Oracle 将数据放在索引中之前,将先 把所存储数据的字节反转,这样原来可能在索引中相邻放置的值在字节反转之后就会相距很远。通过反转字节,对索引的插入就会分布到多个块上。

    位图索引( bitmap index )

在一颗 B* 树中,通常索引条目和行之间存在一种一对一的关系:一个 索引条目就指向一行。而对于位图索引,一个索引条目则使用一个位图同时指向多行。位图索引适用于高度重复而且通常只读的数据(高度重复是指相对于表中的总行数,数据只有很少的几个不同值)。考虑在一 个有 100 万行的表中,每个列只有 3 个可取值: Y 、 N 和 NULL 。举例来说,如果你需要频繁地统计多少行有值Y ,这就很适合建立位图索引。不过并不是说如果这个表中某一列有 11.000 个不同的值就不能建立位图索引,这一列当然也可以建立 位图索引。在一个 OLTP 数据库中,由于存在并发性相关的问题,所以不能考虑使用位图索引(后面我们就会讨论这一点)。注意,位图索引要求使用 Oracle 企业版或个人版。

位图联结索引( bitmap join index ):这为索引结构(而不是表)中的数据提供了一种逆规范化的 方法。例如,请考虑简单的 EMP 和 DEPT 表。有人可能会问这样一个问题: “ 多少人在位于波士顿的部门工作 ?“ EMP 有一个指向 DEPT 的外键,要想统计 LOC 值为 Boston 的部门中的员工人数,通常必须完成表联结,将 LOC 列联结至 EMP 记录来回答这个问题。通过使用位图联结索引,则可以在 EMP 表上对 LOC 列建立索引 。

基于函数的索引( function-based index )

这些就是 B* 树索引或位图索引,它将一个函数计算得到的结果存储在行的列中,而不是存储列数据本身。可以把基于函数的索引看作一个虚拟列(或派生列)上的索引,换句话说,这个列并不物理存储在表中。基于函数的索引可以用于加快形如 SELECT * FROM T W HERE FUNCTION(DATABASE_COLUMN) = SAME_VALUE 这样的查询,因为值 FUNCTION(DATABASE_COLUMN) 已经提前计算并存储在索引中。

应用域索引( application domain index )

应用域索引是你自己构建和存储的索引,可能存储在Oracle 中,也可能在 Oracle 之外。你要告诉优化器索引的选择性如何,以及执行的开销有多大,优化器则会根据你提供的信息来决定是否使用你的索引。 Oracle 文本索引就是应用域索引的一个例子;你也可 以使用构建 Oracle 文本索引所用的工具来建立自己的索引。需要指出,这里创建的 “ 索引 “ 不需要使用传统的索引结构。例如, Oracle 文本索引就使用了一组表来实现其索引概念。




    首先,我们要确定数据库运行在何种优化模式下,相应的参数是:optimizer_mode。可在svrmgrl中运行“show parameter optimizer_mode"来查看。ORACLE V7以来缺省的设置应是"choose",即如果对已分析的表查询的话选择CBO,否则选择RBO。如果该参数设为“rule”,则不论表是否分析过,一概选用RBO,除非在语句中用hint强制。

查找原因的步骤

  首先,我们要确定数据库运行在何种优化模式下,相应的参数是:optimizer_mode。可在svrmgrl中运行“show parameter optimizer_mode"来查看。ORACLE V7以来缺省的设置应是"choose",即如果对已分析的表查询的话选择CBO,否则选择RBO。如果该参数设为“rule”,则不论表是否分析过,一概选用RBO,除非在语句中用hint强制。

  其次,检查被索引的列或组合索引的首列是否出现在PL/SQL语句的WHERE子句中,这是“执行计划”能用到相关索引的必要条件。

  第三,看采用了哪种类型的连接方式。ORACLE的共有Sort Merge Join(SMJ)、Hash Join(HJ)和Nested Loop Join(NL)。在两张表连接,且内表的目标列上建有索引时,只有Nested Loop才能有效地利用到该索引。SMJ即使相关列上建有索引,最多只能因索引的存在,避免数据排序过程。HJ由于须做HASH运算,索引的存在对数据查询速度几乎没有影响。

  第四,看连接顺序是否允许使用相关索引。假设表emp的deptno列上有索引,表dept的列deptno上无索引,WHERE语句有emp.deptno=dept.deptno条件。在做NL连接时,emp做为外表,先被访问,由于连接机制原因,外表的数据访问方式是全表扫描,emp.deptno上的索引显然是用不上,最多在其上做索引全扫描或索引快速全扫描。

  第五,是否用到系统数据字典表或视图。由于系统数据字典表都未被分析过,可能导致极差的“执行计划”。但是不要擅自对数据字典表做分析,否则可能导致死锁,或系统性能下降。


  第六,索引列是否函数的参数。如是,索引在查询时用不上。

  第七,是否存在潜在的数据类型转换。如将字符型数据与数值型数据比较,ORACLE会自动将字符型用to_number()函数进行转换,从而导致第六种现象的发生。

  第八,是否为表和相关的索引搜集足够的统计数据。对数据经常有增、删、改的表最好定期对表和索引进行分析,可用SQL语句“analyze table xxxx compute statistics for all indexes;"。ORACLE掌握了充分反映实际的统计数据,才有可能做出正确的选择。

  第九,索引列的选择性不高。

  我们假设典型情况,有表emp,共有一百万行数据,但其中的emp.deptno列,数据只有4种不同的值,如10、20、30、40。虽然emp数据行有很多,ORACLE缺省认定表中列的值是在所有数据行均匀分布的,也就是说每种deptno值各有25万数据行与之对应。假设SQL搜索条件DEPTNO=10,利用deptno列上的索引进行数据搜索效率,往往不比全表扫描的高,ORACLE理所当然对索引“视而不见”,认为该索引的选择性不高。

  但我们考虑另一种情况,如果一百万数据行实际不是在4种deptno值间平均分配,其中有99万行对应着值10,5000行对应值20,3000行对应值30,2000行对应值40。在这种数据分布图案中对除值为10外的其它deptno值搜索时,毫无疑问,如果索引能被应用,那么效率会高出很多。我们可以采用对该索引列进行单独分析,或用analyze语句对该列建立直方图,对该列搜集足够的统计数据,使ORACLE在搜索选择性较高的值能用上索引。

  第十,索引列值是否可为空(NULL)。如果索引列值可以是空值,在SQL语句中那些需要返回NULL值的操作,将不会用到索引,如COUNT(*),而是用全表扫描。这是因为索引中存储值不能为全空。

  第十一,看是否有用到并行查询(PQO)。并行查询将不会用到索引。

  第十二,看PL/SQL语句中是否有用到bind变量。由于数据库不知道bind变量具体是什么值,在做非相等连接时,如“<”,“>”,“like”等。ORACLE将引用缺省值,在某些情况下会对执行计划造成影响。


1
0
分享到:
评论

相关推荐

    Oracle索引优化相关

    Oracle数据库经典优化之索引原理篇 Oracle中建立索引并强制优化器 基于索引的SQL语句优化之降龙十八掌 30个Oracle语句优化规则详解-性能调优

    Oracle 创建索引的基本规则

    Oracle 创建索引的基本规则,好好研究一下有好处.

    Oracle优化器模式与Oracle索引优化规则.pptx

    Oracle优化器模式与Oracle索引优化规则.pptx

    Oracle 9i用索引跳跃扫描提高查询速度

    Oracle9i的索引跳跃式扫描执行规则允许使用连接索引,即使SQL查询中不指定性别。 这一特性使得无需在emp_id行中提供第二个索引。Oracle承认索引跳跃式扫描没有直接索引查询速度快,但可以这样说,相比于整个表扫描 ...

    高性能动态SQL Oracle数据安全 Oracle 数据库的聚簇技术 等等

    Oracle字段上建立并使用索引 29 用Windows脚本宿主自动化Oracle工具 31 进程结构和内存结构 32 Oracle监控数据库性能的SQL汇总 36 Oracle如何精确计算row的大小 38 PL/SQL编程 39 数据库的分组问题 41 oracle知识 42...

    数据库命名规则

    数据库命名规则 一、 数据库名 1 二、 数据库表 1 三、 表字段 1 四、 视图 1 五、 主键 2 六、 外键 2 七、 索引 2 八、 关联 2 九、 存储过程 2 十、 触发器 2

    Oracle管理艺术 中文版

    *学习使用sql语句创建数据库,是彻底了解oracle数据库的不二法门。 *充分了解表空间的真正意涵,可使数据库在管理及性能方面有出色的整体表现。 *正视数据库安全的重要性,并在数据库建立前就制定好数据库的安全规则...

    Oracle11g从入门到精通2

    3.1.3 SQL语句的编写规则 3.2 数据定义 3.2.1 CREATE 3.2.2 DROP 3.2.3 ALTER 3.3 数据查询 3.3.1 简单查询 3.3.2 WHERE子句 3.3.3 ORDER BY子句 3.3.4 GROUPBY子句 3.3.5 HAVING子句 3.3.6 ...

    Oracle高性能SQL调整

    本书由oracle公司授权,向读者...使用oracle8i优化器计划稳定性,基于成本的优化器和基于规则的优化器,调整SQL DML语句、SQL子查询和数据仓库SQL,调整带有临时表和索引的SQL语句,使用STATSPACK诊断和优化系统性能。

    Oracle数据库管理员技术指南

    1.2.3 怎样配置符合 OFA 的 Oracle 文件 系统 1.3 规划数据库文件布局 1.3.1 最大化可用性的规划 1.3.2 最小化磁盘争用的规划 1.4 建立参数文件 1.4.1 配置参数的一些注意事项 1.4.2 建立参数文件的连接 ...

    ORACLE9i_优化设计与系统调整

    §13.2 创建索引和使用索引 165 §13.2.1 使用函数索引 165 §13.2.2 使用位图索引- 166 §13.2.3 使用B树索引- 166 §13.2.4 使用反向键索引- 166 §13.2.5 使用索引组织表 166 §13.3 使用范围索引 166 §13.4 ...

    ORACLE SQL性能优化

    在数据高速缓冲区中存放着Oracle系统最近使用过的数据块(即用户的高速缓冲区),当把数据写入数据库时,它以数据块为单位进行读写,当数据高速缓冲区填满时,则系统自动去掉一些不常被用访问的数据。如果用户要查...

    oracle 数据库优化技术资料

    在缺省情况下,ORACLE采用CHOOSE优化器, 为了避免那些不必要的全表扫描(full table scan) , 你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器. 2. 访问Table的方式 ORACLE 采用两种...

    Oracle数据库Sql性能调优

    1.36 避免在索引列上使用IS NULL和IS NOT NULL 22 1.37 总是使用索引的第一个列 23 1.38 ORACLE内部操作 23 1.39 用UNION-ALL 替换UNION ( 如果有可能的话) 24 1.40 使用提示(HINTS) 25 1.41 用WHERE替代ORDER BY 25...

    oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 连接字符串

     系统权限:系统规定用户使用数据库的权限。(系统权限是对用户而言)。  DBA: 拥有全部特权,是系统最高权限,只有DBA才可以创建数据库结构。  RESOURCE:拥有Resource权限的用户只可以创建实体,不可以创建...

Global site tag (gtag.js) - Google Analytics