ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle索引

Oracle索引

原创 Linux操作系统 作者:chinagjj2008 时间:2009-04-13 23:32:31 0 删除 编辑
新推出的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查询能用到最快和最有效率的索引结构。

索引跳跃式扫描(index skip scan)是ORACLE9i用来提高性能的新特性,对于使用复合索引的数据库应用程序意义尤为重大。

复合索引(又称为连接索引)是一个包含多个字段的索引。在ORACLE9i以前的ORACLE版本里,只有那些在WHERE子句里引用整个索引或者是引用索引的一个或多个前导字段的查询,才能使复合索引有效而提高检索效率。而在ORACLE9i里,一个复合索引即使在一次查询中没有使用前导字段,也可能通过“索引跳跃式扫描”被有效引用。在一次跳跃式扫描中,每个复合索引前导字段的独特值(DISTINCT VALUE)只会被搜索一次,ORACLE9i会在复合索引前导字段每个独特值区间结合WHERE子句中的其它复合索引字段搜索符合条件的目标记录,这种做法的结果导致了在索引中的跳跃式扫描。

索引跳跃式扫描主要有两个优点:
1、以前版本中的表扫描(TABLE SCAN)可能会转变为索引扫描,提高了某些查询的执行效率;
2、应用程序使用较少的索引就能达到提高效能的目的,并且既节省存储空间,又能提高DML和维护操作的效率。

假设有一个汽车注册信息的表,包含了250万条纪录,结构如下:

 REGISTERED_OWNERS  
(STATE VARCHAR2(2) NOT NULL,
REGISTRATION# VARCHAR2(10) NOT NULL,
FIRST_NAME VARCHAR2(30),
LAST_NAME VARCHAR2(30),
MAKE VARCHAR2(30),
MODEL VARCHAR2(15),
YEAR_MFG NUMBER,
CITY VARCHAR2(30),
ZIP NUMBER)



进一步假设在字段(STATE,REGISTRATION#)上存在一个复合索引,那么下面语句的执行速度会有很大提高:
SELECT first_name, last_name, zip FROM REGISTERED_OWNERS
WHERE registration# = '4FBB428'
使用索引查询速度大约可以提高90倍,在ORACLE8i(索引跳跃式扫描)中,这个查询需要21.42秒,而同样的硬件配置使用ORACLE9i只需要0.24秒。

索引跳跃式扫描非常有效,对用户和应用程序而言是透明的,并且不需要使用额外的新索引就能提高数据库的性能,有些时候甚至可以允许移除某些索引而不会对性能有太大影响。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/464838/viewspace-588901/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-01-14

  • 博文量
    32
  • 访问量
    60702