Java开发网 - [转]用Oracle9i高级索引技术助力SQL

Java开发网

您没有登录

» Java开发网 » Database/JDBC/SQL/JDO/Hibernate » Oracle

打印话题 寄给朋友 订阅主题

作者

[转]用Oracle9i高级索引技术助力SQL [精华]

发贴: 0
积分: 0

于 2002-09-02 02:04

作者： ZDNET CHINA

新推出的Oracle9i内容翻新，采用了多种新颖的索引算法以显著地提高Oracle查询数据库的速度。本文对Oracle9i的索引技术内幕进行了一番讨论，对标准的b-tree索引、位图索引、基于函数的索引以及唯索引表（IOT）等进行了说明。同时，还会列出相应的代码程序，演示这些索引提高Oracle SQL的查询速度是如何显著。

在数据库操作中，我们该如何避免大型数据表扫描、全数据表扫描和磁盘排序等既费时间又费力气的操作呢？Oracle9i对这一问题的回答是：采用索引技术，显然，在SQL优化器无法找到有效的方式来完成SQL查询的情况下就要用到索引运算了。我们首先看一下Oracle9i索引中标准的Oracle b-tree索引方法。

Oracle b-tree 索引

在Oracle的索引类型中，最古老、同时也是最常用的就是标准b-tree索引，标准b-tree索引在效率上通常会胜过简单查询。b-tree索引在最早的Oracle数据库产品中就被引入了而且一直到Oracle9i都是最常用的查询技术之一（参看图A）。B-tree索引的主要功能目标就是用来避免大规模排序操作。比方说，假设有个SQL查询需要对1万行数据进行排序操作，在这种情况下就会经常用到b-tree索引，有了它，在把数据交付给最终用户的时候就可以不用对数据进行大型排序操作了。

图A

Oracle b-tree索引

Oracle在用默认b-tree结构创建索引的时候给用户提供了好几种选择。首先，用户可以对多列索引（连接索引）来提高访问速度。此外，用户还可以让表内各列按照不同的顺序排序。比如，我们可以对last_name列按升序创建一个b-tree索引，然后在这个索引之内再找出第2列（salary）按降序显示salary列。清单A就显示了这一SQL。

 
 create index name_salary_idx 
 on person 
 ( last_name asc, salary desc);

显然，b-tree 索引通常是非常适合进行简单的单一查询的，但是在以下条件下就不适合选择这种方法了：

* 列的基数较低—可区分值少于200个的列无须采用标准的b-tree索引结构。
* 不支持SQL函数—B-tree索引不能采用Oracle的内置函数来支持SQL查询。而Oracle9i提供了各种内置函数，通过它们可以让SQL表达式查询部分索引列。

在Oracle9i问世之前，由于以上的缺陷，在很多情况下，Oracle SQL优化器不得不执行费时的长表、全表扫描。Oracle当然认识到了这一点，自然在新版数据库中引入了更为稳固的索引结构类型。

位图索引

Oracle位图索引同标准的b-tree索引可就大不相同了。位图结构是一种二维数组，由被索引的数据表内某一列的各行产生。在位图索引中，每一列就代表一个可起区分作用的值。这种二维数组代表了数据表内列乘以行数的索引中的每个值。在取得各行的时候，Oracle就把位图解压缩并存放在内存缓冲内，这样数据可以很快地被扫描来匹配实际值。这些匹配的值再以Row-ID 列表的形式交付给Oracle，而这些Row-ID值可以直接访问所需要的信息。

位图索引特别适用于表内包含多个位图索引这种情况：每个单列都可能会有较低的基数。创建多个位图索引这种方法就可以更快的速度决绝SQL查询所面临的困难。

再如，假设现在有一个汽车数据库，其中包含了大量的低基数列，比如car_color、car_make、car_model和car_year等。以上这些列各自包含的值数目不超过100，b-tree索引在一个包含2000万辆汽车数据的数据库面前毫无用处。但是，把这些索引组合到一个查询里就会相比传统查询读取表内2000万行记录的速度要快得多。比如，假设我们想找出1981年生产的老式Toyota Corollas汽车，其查询情况请见清单B。

 
 select license_plat_nbr 
 from vehicle 
 where color = ‘blue’ 
 and make = ‘toyota’ 
 and year = 1981;

Oracle采用了专门的优化器把位图索引合并起来服务于查询操作。在位图索引合并的情况下，每一个Row-ID或者说RID列表都采用位图独立创建，一种专门的合并程序则用来比较RID列表同时找出交叉值。采用这种方法，Oracle就可以在处理多个低基数列的同时实现更快的响应时间（请参看图B）。

图B

Oracle位图合并连接

函数索引

Oracle索引有一个最重要的高明之处，这就是引入了基于函数的索引。基于函数的索引可以根据表达式、内部函数以及用户用PL/SQL和Java编写的函数来创建索引。基于函数的索引保证了Oracle设计人员能采用索引作为其查询手段。在Oracle8之前，内置函数的采用不能满足索引的性能要求。最终，Oracle只能执行严重损害查询性能的全规模扫描。基于函数的SQL示例可能包含以下内容：

 
 Select * from customer where substr(cust_name,1,4) = ‘BURL’; 
 Select * from customer where to_char(order_date,’MM’) = ’01; 
 Select * from customer where upper(cust_name) = ‘JONES’; 
 Select * from customer where initcap(first_name) = ‘Mike’;

在Oracle9i系统下，Oracle总会检查SQL 表达式的where子句查看是否存在匹配的索引。在基于函数的索引这一情况下，Oracle设计人员可以创建用SQL where语句准确判断的匹配索引。这样就保证了查询可以最低程度的磁盘读写和最快的速度得到结果。

唯索引表

从Oracle8开始，Oracle认识到对每一列采取索引的表并不需要数据表的行。换句话说， Oracle承认，采用专门的数据表访问方法（称做索引快速全扫描）的话，索引就可以在并没有实际接触数据本身的情况下完成数据查询。

Oracle用唯索引表结构（IOT）实现了以上的想法。在使用IOT的情况下，Oracle并不创建实际的表而是把所有需要的信息都保存在Oracle索引之内。在查询的时候，Oracle SQL优化器在索引树内找出服务于现有查询的所有必要的值，此时，Oracle根据查询代价的优化器选择读取索引树节点然后按序取出数据或者调用索引快速全扫描，后者将用全表扫描同样的方式采用连续预取（由db_file_multiblock_read_count参数定义）方法读取数据表。Multiblock读取工具可以让Oracle很快地线性扫描索引块，快速地读取索引表空间内的每一块。清单C就包括了创建IOT的语法示例。

 
 CREATE TABLE emp_iot ( emp_id number, ename varchar2(20), sal number(9,2), 
  deptno number, 
 CONSTRAINT k_emp_iot_index PRIMARY KEY (emp_id) ) 
 ORGANIZATION index 
 TABLESPACE spc_demo_ts_01 
 PCTHRESHOLD 20 INCLUDING ename;

小结

Oracle在关系型数据库市场上占据这龙头老大的地位，所以Oracle的设计师必须关注特殊的索引结构而且完全理解索引用来提升Oracle SQL查询的方法。这类技术很多都在图书《Oracle High-Performance SQL Tuning》（Oracle公司2001年出版）中进行了讨论。该书对创建所有Oracle索引树结构的过程进行了详细的讨论，同时还提出了一些特殊的技巧和相关技术，借以保证SQL查询能用到最快和最有效率的索引结构。

九佰 edited on 2003-07-25 17:54

话题树型展开

人气	标题	作者	字数	发贴时间
15603	[精华] [转]用Oracle9i高级索引技术助力SQL	C	3852	2002-09-02 02:04

已读帖子

新的帖子

被删除的帖子