Oracle索引分析与查询优化

崔黎 明志远 李婧

摘要：Oracle是目前国内在大型数据存储中用得比较多的一种关系型数据库，磁盘阵列技术（RAID）和集群技术（RAC）的运用，使Oracle在处理数据效率和数据安全上有非常大的提高，在国内交通、电力，通信和金融等重要领域都有广泛的用途。本文基于Oracle 11g版本，对Rowid和索引的原理机制做出分析，并论述利用这些原理对大型数据表查询的优化。

关键词：Oracle Rowid 索引 查询

中图分类号：TP311.13 文献标识码：A 文章编号：1007-9416（2016）07-0234-02

在逻辑上，Oracle是由多个表空间构成的，在新建一个表空间的时候，必须指定存储的文件，可以指向多个存储在不同磁盘上的数据。表空间下面分为段、区、块。新建一张表的时候，Oracle数据库就会为它创建一个段。所谓区就是指连续的块（block）构成的空间，一般区包含8个空块，block是Oracle数据库最小的数据空间，一般为8k或16k，当开始往这个表中插入数据的时候，Oralce数据库会自动为这个表分配一个区，并把数据不断往此区进行填充，当数据填满此区后，Oralce数据库会重新为该表分配一个区而不是一个段。实际上，对于我们不同的查询过程中，就是通过一些谓词过滤条件，从对应的数据块中获取正确的一行数据或多行数据。如何快速定位到该数据行，是一个数据库学习者不断探索的方向。

1 Rowid

1.1 简介

从字面上理解是行标识的意思，它是Oracle数据库中数据表的一个伪列，用于存放该表中每一行数据的地址，在8i版本之前，Oracle采用受限制的rowid，它是由数据文件编号，块编号和数据在该块内的偏移量这三个部分构成，长度为6个字节，因此在8i之前的版本中，每个数据库最多可以包含1022个文件，每个文件最多能有4m个数据块，而每个数据库最多能存储64k条记录。为了突破长度的限制和解决其他一些缺陷，Oracle数据库引入了这样一个概念：相对文件号。它的主要特点是改变之前rowid中数据文件编号是整个数据库范围组成的表空间，即文件编号为5的文件不再是数据库中编号为5的数据文件，而是表空间中对应编号的数据文件。如下图1所示为Oracle11g中一个普通表的rowid。

从该图1中可以看到rowid共有18位，分为四部分，格式为：AAAAAABBBCCCCCCDDD，其中AAAAAA六位表示dataobjectid，根据这个id可以确定该行数据在哪个段中；BBB三位表示相对文件号，通过这个字段号可以用来确定该行数据的绝对文件号；CCCCCC六位表示datablocknumber，它是相对于datafile的编号；最后三位DDD表示该行数据在哪一行。通过Oracle数据库自带的一些函数可以把字符串转成数字形式图2所示。

1.2 rowid的应用

（1）通过rowid查找重复记录的数据，sql如下：

（2）通过rowid删除重复记录的方法：

2 索引

2.1索引的概念

一般情况下，用户对数据库最多的操作是进行数据查询，当一张表很大需要进行全表扫描时，查询就需要很长的时间，服务器会造成大量的资源浪费，同时也会让用户体验效果变得很差。因此，Oracle数据库引入了索引的机制。索引是一个独立的物理存储结构，类似于C语言中的指针的概念，是表中一列或者多列和指向表中存储这些列值的地址的数据，实际上就是Rowid。索引的本质，就是根据rowid，再到数据表中定位到某条数据。类似于一本书，索引是目录，而书的内容是数据，那么rowid就是联系目录和内容的那一个“页码”。

2.2 索引的分类

Oralce数据库提供了多种类型的索引供选择使用，在给定条件下选择什么类型的索引，是单列还是组合列，对于数据库的性能来说非常重要。本文对常用的几种索引做下分析。

2.2.1 B树索引

Oracle数据库默认创建的是B树索引，可以有多列构成复合索引，但最多不能超过32列。B数索引的结构如下图3所示。

其中最顶层称为根节点，最底层称为叶子节点，每个叶子节点包含了索引值、Rowid以及指向后一个叶子节点和前一个叶节点的指针，因此Oracle可以分别从前后两个方向来遍历这个二叉树。创建B树索引的语法如下：

createindex索引名on表名（列名[，列名，......]）；

一般来说，当取出的数据行数占整个表的比例很小的时候，该索引的性能会很高；但是，当该比例超过10%的时候，使用索引的效率就会变差，此时，Oracle在基于CBO的规则下会选择使用全表扫描。表kjyq_rz2016共中有21000条数据，其中某个字段compid=“440C”的数据共有5000余条，此时在利用compid字段过滤进行查询时，Oralce就会选择使用全表扫描方式进行，执行计划如图4所示。

而如果在查询的时候强制使用索引方式，反而效果不好，强制索引的执行计划如图5所示。

对比两图来看，在强制使用索引的情况下，cost和iocost是使用全表扫描的将近5倍。所以在实际生产环境中，尽量让Oracle数据库自动选择执行计划而少用hint方式去强制使用索引。

2.2.2 位图索引

位图索引适用于在重复度比较高的场景中，比如某个表中包含性别列，它只可能有男和女两个值。如果需要按照此列作为过滤条件，此时就可以把此列设置为位图索引列。应该注意的是，该列的不同值如果越少效果就越好。普通B树索引的索引结构中存储着键值和键值的Rowid，并且是一一对应的，而位图索引主要针对大量相同值的列而创建（例如：类别，操作员，部门ID等），索引块的一个索引行中存储键值和起止Rowid，以及这些键值的位置编码，位置编码中的每一位表示键值对应的数据行的有无，一个位图索引块可能指向的是几十甚至成百上千行数据的位置。创建位图索引的语法如下：

createbitmapindex索引名on表名（列名[，列名，......]）；

还是针对上面的test1表进行处理，删除掉B数索引，同时在compid列上增加位图索引，以该列作为过滤条件进行查询，执行计划如下图6所示。

从执行计划来看，Oracle此时会自动按照位图索引的方式而不是选择全表扫描的方式了，对比上面的全表扫描方式和普通索引方式，无论是cost，iocost还是cpucost都有非常好的效果。

3 结语

本文对rowid，常用的B树索引和位图索引根据实际情况做了相应的对比分析，并利用Oracle11g做了对比测试。我们在实际的数据库生产运用环境中，需要根据业务需求和表结构选择使用最适合的索引，如果出现索引失效的情况，则需重建索引。同时也要定期检查表中的数据变化，根据不同的数据变化情况做出相应的更改，及时有效的进行数据库的建立和维护。

参考文献

[1]冯亚丽，蔡金武，冯正利，吕洪艳，刘芳吉.基于表结构及索引的Oracle查询优化研究[J].哈尔滨商业大学学报（自然科学版），2011（06）.

[2]刘华清，陈振东，涂刚.数据库索引与优化[J].现代计算机（专业版），2012（18）.

[3]赵琛.Oracle服务器性能影响因素分析与优化[J].电脑编程技巧与维护，2011（20）.

[4]刘威，周嫦娥.1553B网络数据处理系统数据库优化设计[A].2011航空试验测试技术学术交流会论文集[C].2010.

[5]马传松.基于Oracle分布式数据库远程数据的维护和安全性的研究[D].广东工业大学，2003.

[6]张新林.范式下的Oracle数据库设计及其动态交叉表的生成[J].计算机与现代化，2005（09）.