Oracle索引分析和查询优化.docVIP

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条数据，其中某个字段comp