ORACLE表连接方式的分析与优化-tony

1、数据仓库环境 ORACLE表连接方式的分析与优化摘 要: 针对在数据仓库环境下 , 由于超大数据量的处理而产生的效率问题，本文深入分析了ORACL表的几种连接方式、特点、适用范围，以及对于如何使用和优化做了详细的探讨。关键字 ：数据仓库 ORACLE 表连接引言数据仓库技术是目前已知的比较成熟和被广泛采用的解决方案，用于整和电信运营企业 内部所有分散的原始业务数据，并通过便捷有效的数据访问手段，可以支持企业内部不同部 门，不同需求，不同层次的用户随时获得自己所需的信息。数据仓库系统需要能够及时地追 踪和分析大量的历史数据，并能够及时做出分析和预测，因此实时性是一个非常重要的指标。ORACLE由

2、于可靠性、高性能等方面的特点，在电信行业大部分的数据仓库系统中担当了后台数据库的角色。由于电信行业的特点，处理的数据量十分庞大，处理的时间长。尤其是对于 大表之间的关联操作，有的大表的记录数达到数亿条，处理时间更是漫长，这成为影响数据 库运行效率的主要因素。因此，对于数据库的性能优化相当重要。性能优化是个很大的课题，需要综合考虑，从服务器、磁盘、网络、ORACLED例、ORACLE SQL?多方面着手。本文着重分析ORACLE SQ优化中对于系统性能影响极大的表连接方式、特点、适用范围，并对如何使用和优化做了详细的探讨。表的连接表的连接是指在一个 SQL语句中通过表与表之间的关联，从一个或多个

3、表检索出相关的数据。连接是通过 SQL语句中FROM从句的多个表名，以及 WHER从句里定义的表之间的连接ORACLE首先条件来实现的。如果一个 SQL语句的关联表超过两个，那么连接的顺序如何呢?连接其中的两个表，产生一个结果集；然后将产生的结果集与下一个表再进行关联；继续这 个过程，直到所有的表都连接完成；最后产生所需的数据。下面都以两个表的连接为例create table user_info(user_name char(10),user_id char(10);create table dev_info(dev_no char(10),user_id char(10),dev_type

4、char(10);说明和分析表的各种连接方式。ORACLE从6的版本开始，优化器使用 4种不同的表的连接方式:嵌套循环连接( NESTED LOOP JOIN)群集连接 (CLUSTER JOIN)排序合并连接( SORT MERGE JOI)N笛卡尔连接 (CARTESIAN JOIN)ORACLE 7.3中，新增加了哈希连接 (HASH JOIN) 。在 ORACLE 8中，新增加了索引连接 (INDEX JOIN) 。这六种连接方式都有其独特的技术特点，在一定的条件下，可以充分发挥高效的性能。但是也都有其局限性，如果使用不当，不仅不能提高效率，反而会严重影响系统的性能。因 此，深入地探讨

5、连接方式的内部运行机制对于性能优化是必要的。1 嵌套循环连接嵌套循环连接的内部处理的流程：1)Oracle优化器根据基于规则RBC或基于成本CBO的原则，选择两个表中的一个作为驱动表，并指定其为外部表。2)Oracle 优化器再将另外一个表指定为内部表。3)Oracle 从外部表中读取第一行 , 然后和内部表中的数据逐一进行对比，所有匹配的记 录放在结果集中。4)Oracle 读取外部表中的第二行， 再和内部表中的数据逐一进行对比， 所有匹配的记录添加到结果集中。5)重复上述步骤，直到外部表中的所有纪录全部处理完。6)最后产生满足要求的结果集。通过查询SQL语句的执行计划可以看出哪个表是外部表

6、，哪个为内部表。女 0 select a.user_ name,b.dev_ nofrom user_ info a, dev_i nfo bwhere a.user_id = b.user_id;的执行计划：SELECT STATEMENT Op timizer=CHOOSE上面的表是外部表，即驱 动表NESTED LOOPSTABLE ACCESS (FULL) OF 'USER_INFO'TABLE ACCESS (FULL) OF 'DEV_INFO'F面的表是内部表使用嵌套循环连接是一种从结果集中提取第一批记录最快速的方法。在驱动行源表（就是正在查找的

7、记录）较小、或者内部行源表已连接的列有惟一的索引或高度可选的非惟一索引时嵌套循环连接效果是比较理想的。嵌套循环连接比其他连接方法有优势,它可以快速地从结果集中提取第一批记录，而不用等待整个结果集完全确定下来。这样，在理想情况下，终端用户就可以通过查询屏幕查看第一批记录，而在同时读取其他记录。不管如何定义连接的条件或者模式,任何两行记录源可以使用嵌套循环连接，所以嵌套循环连接是非常灵活的。然而，如果内部行源表（读取的第二张表）已连接的列上不包含索引，或者索引不是高度可选时，嵌套循环连接效率是很低的。如果驱动表的记录非常庞大时,其他的连接方法可能更加有效。可以通过在SQL语句中添加HINTS,强制

8、ORACLED化器产生嵌套循环连接的执行计戈叽select /*+ use_ nl(a b) */ a.user_ name,b.dev_ nofrom user_i nfo a, dev_ info bwhere a.user_id = b.user_id;2 群集连接(CLUSTER JOIN )群集连接实际上是嵌套循环连接的一种特例。如果所连接的两张源表是群集中的表，即两张表属于同一个段（SEGMENT,，那么ORACL能够使用群集连接。处理的过程是：ORACL从第一张行源表中读取第一行，然后在第二张行源表中使用CLUSTE索引查找能够匹配到的纪录；继续上面的步骤处理行源表中的第二行，

9、直到所有的记录全部处理完。群集连接的效率极高，因为两个参加连接的行源表实际上处于同一个物理块上。但是，群 集连接也有其限制，没有群集的两个表不可能用群集连接。所以，群集连接实际上很少使用。3 排序合并连接( SORT MERGE JOIN )排序合并连接内部处理的流程：1)优化器判断第一个源表是否已经排序，如果已经排序，则到第3 步，否则到第 2 步。2)第一个源表排序3)优化器判断第二个源表是否已经排序，如果已经排序，则到第5 步，否则到第 4 步。4)第二个源表排序5)已经排过序的两个源表进行合并操作，并生成最终的结果集。在缺乏数据的选择性或者可用的索引时, 或者两个源表都过于庞大( 所选

10、的数据超过表记录数的 5%)时, 排序合并连接将比嵌套循环连更加高效。排列合并连接需要比较大的临时内存块 , 以用于排序， 这将导致在临时表空间占用更多的内存和磁盘 I/O 。select a.user_name,b.dev_nofrom user_info a, dev_info b where a.user_id > b.user_id;PlanSELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=336 Bytes=16128)MERGE JOIN (Cost=7 Card=336 Bytes=16128)SORT (JOIN) (Cost=

11、4 Card=82 Bytes=1968)TABLE ACCESS (FULL) OF 'USER_INFO' (Cost=2 Card=82 Bytes=1968)SORT (JOIN) (Cost=4 Card=82 Bytes=1968)TABLE ACCESS (FULL) OF 'DEV_INFO' (Cost=2 Card=82 Bytes=1968)可以通过在SQL语句中添加HINTS,强制ORACLED化器产生排序合并连接的执行计戈叽select /*+ use_merge(a b) */ a.user_name,b.dev_nofrom use

12、r_info a, dev_info bwhere a.user_id > b.user_id;排序合并连接是基于RB啲。4 笛卡尔连接(CARTESIAN JOIN)笛卡尔连接是指在 sql 语句中没有写出表连接的条件，优化器把第一个表的每一条记录和第二个表的所有纪录相连接。如果第一个表的纪录数为m,第二个表的纪录数为m,则会产生m*n 条纪录数。面的查询，未指名连接条件，就会产生笛卡尔连接。select a.user_name,b.dev_nofrom user_info a ,dev_info b;由于笛卡尔连接会导致性能很差的SQL，因此一般也很少用到。5 哈希连接当内存能够提供

13、足够的空间时，哈希（HASH）连接是 Oracle优化器通常的选择。哈希连接 中，优化器根据统计信息，首先选择两个表中的小表，在内存中建立这张表的基于连接键的 哈希表；优化器再扫描表连接中的大表，将大表中的数据与哈希表进行比较，如果有相关联 的数据，则将数据添加到结果集中。当表连接中的小表能够完全 cache 到可用内存的时候，哈希连接的效果最佳。哈希连接的成本只是两个表从硬盘读入到内存的成本。但是，如果哈希表过大而不能全部 cache 到可用内存时，优化器将会把哈希表分成多个2 1 TABLE ACCESS (FULL) OF 'USER_INFO' (Cost=2 Card

14、=82 Bytes分区，再将分区逐一 cache 到内存中。当表的分区超过了可用内存时，分区的部分数据就会EXTENT)临时地写到磁盘上的临时表空间上。因此，分区的数据写磁盘时，比较大的区间(会提高I/O性能。ORACL推荐的临时表空间的区间是1MB临时表空间的区间大小由 uniformSIZE 指定。当哈希表构建完成后，进行下面的处理：1)第二个大表进行扫描2)3)大表的第一个分区 cache 到内存如果大表不能完全 cache 到可用内存的时候， 大表同样会分成很多分区4)5)与第一个分区一样，其它的分区也类似处理。对大表第一个分区的数据进行扫描，并与哈希表进行比较，如果有匹配的纪录，添

15、加到结果集里面6)所有的分区处理完后，ORACL对产生的结果集进行归并，汇总，产生最终的结果。当哈希表过大或可用内存有限，哈希表不能完全CACH到内存。随着满足连接条件的结果集的增加，可用内存会随之下降，这时已经CACH到内存的数据可能会重新写回到硬盘去。如果出现这种情况，系统的性能就会下降。当连接的两个表是用等值连接并且表的数据量比较大时，优化器才可能采用哈希连接。哈希连接是基于CBO勺。只有在数据库初始化参数 HASH_JOIN_ENABLED为True,并且为参数PGA_AGGREGATE_TARG置了一个足够大的值的时候Oracle才会使用哈希边连接。HASH_AREA_SIZ是向下兼

16、容的参数,但在Oracle® 之前的版本中应当使用 HASH_AREA_SIZE当使用ORDERE提示时,FROM子句中的第一张表将用于建立哈希表。select a.user_name,b.dev_nofrom user_info a, dev_info bwhere a.user_id = b.user_id;Plan0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=82 Bytes=39361 0 HASH JOIN (Cost=5 Card=82 Bytes=3936)=1968)3 1 TABLE ACCESS (FULL) O

17、F 'DEV_INFO' (Cost=2 Card=82 Bytes=1968)可以通过在SQL语句中添加 HINTS,强制ORACLED化器产生哈希连接的执行计划。select /*+ use_hash(a b)*/ a.user_name,b.dev_nofrom user_info a, dev_info bwhere a.user_id = b.user_id;当缺少有用的索引时，哈希连接比嵌套循环连接更加有效。哈希连接也可能比嵌套循环连接更快，因为处理内存中的哈希表比检索BJ树索引更加迅速。6 索引连接如果一组已存在的索引包含了查询所需要的所有信息，那么优化器将在索引

18、中有选择地 生成一组哈希表。可通过范围或者快速全局扫描访问到每一个索引，而选择何种扫描方式取 决于 WHEREF句中的可有条件。在一张表有大量的列，而您只想访问有限的列时，这种方法非常有效。WHER子句约束条件越多，执行速度越快。因为优化器在评估执行查询的优化路径 时，将把约束条件作为选项看待。您必须在合适的列（那些满足整个查询的列）上建立索引， 这样可以确保优化器将索引连接作为可选项之一。这个任务通常牵涉到在没有索引，或者以 前没有建立联合索引的列上增加索引。相对于快速全局扫描，连接索引的优势在于：快速全 局扫描只有一个单一索引满足整个查询 ; 索引连接可以有多个索引满足整个查询。假设表 dev_info 上有两个索 （一个在 dev_no, 一个在 dev_type 上）。作如下的查询select dev_no,dev_type from user_infowhere user id ='U1