ORACLE表连接方式的分析与优化tony模板.doc

ORACLE表连接方式的分析与优化-tony数据仓库环境ORACLE表连接方式的分析与优化摘要:针对在数据仓库环境下,由于超大数据量的处理而产生的效率问题,本文深入分析了ORACLE表的几种连接方式、特点、适用范围,以及对于如何使用和优化做了详细的探讨。关键字:数据仓库ORACLE表连接一引言数据仓库技术是目前已知的比较成熟和被广泛采用的解决方案,用于整和电信运营企业内部所有分散的原始业务数据,并通过便捷有效的数据访问手段,可以支持企业内部不同部门,不同需求,不同层次的用户随时获得自己所需的信息。数据仓库系统需要能够及时地追踪和分析大量的历史数据,并能够及时做出分析和预测,因此实时性是一个非常重要的指标。ORACLE由于可靠性、高性能等方面的特点,在电信行业大部分的数据仓库系统中担当了后台数据库的角色。由于电信行业的特点,处理的数据量十分庞大,处理的时间长。尤其是对于大表之间的关联操作,有的大表的记录数达到数亿条,处理时间更是漫长,这成为影响数据库运行效率的主要因素。因此,对于数据库的性能优化相当重要。性能优化是个很大的课题,需要综合考虑,从服务器、磁盘、网络、ORACLE实例、ORACLESQL等多方面着手。本文着重分析ORACLESQL优化中对于系统性能影响极大的表连接方式、特点、适用范围,并对如何使用和优化做了详细的探讨。·二表的连接表的连接是指在一个SQL语句中通过表与表之间的关联,从一个或多个表检索出相关的数据。连接是通过SQL语句中FROM从句的多个表名,以及WHERE从句里定义的表之间的连接条件来实现的。如果一个SQL语句的关联表超过两个,那么连接的顺序如何呢?ORACLE首先连接其中的两个表,产生一个结果集;然后将产生的结果集与下一个表再进行关联;继续这个过程,直到所有的表都连接完成;最后产生所需的数据。下面都以两个表的连接为例createtableuser_info(user_namechar(10),user_idchar(10));createtabledev_info(dev_nochar(10),user_idchar(10),dev_typechar(10));说明和分析表的各种连接方式。ORACLE从6的版本开始,优化器使用4种不同的表的连接方式:嵌套循环连接(NESTEDLOOPJOIN)群集连接(CLUSTERJOIN)排序合并连接(SORTMERGEJOIN)笛卡尔连接(CARTESIANJOIN)ORACLE,新增加了哈希连接(HASHJOIN)。在ORACLE8中,新增加了索引连接(INDEXJOIN)。这六种连接方式都有其独特的技术特点,在一定的条件下,可以充分发挥高效的性能。但是也都有其局限性,如果使用不当,不仅不能提高效率,反而会严重影响系统的性能。因此,深入地探讨连接方式的内部运行机制对于性能优化是必要的。1嵌套循环连接嵌套循环连接的内部处理的流程:Oracle优化器根据基于规则RBO或基于成本CBO的原则,选择两个表中的一个作为驱动表,并指定其为外部表。Oracle优化器再将另外一个表指定为内部表。Oracle从外部表中读取第一行,然后和内部表中的数据逐一进行对比,所有匹配的记录放在结果集中。Oracle读取外部表中的第二行,再和内部表中的数据逐一进行对比,所有匹配的记录添加到结果集中。重复上述步骤,直到外部表中的所有纪录全部处理完。最后产生满足要求的结果集。通过查询SQL语句的执行计划可以看出哪个表是外部表,哪个为内部表。如select,fromuser_infoa,dev_infobwhere=;上面的表是外部表,即驱动表下面的表是内部表的执行计划:SELECTSTATEMENTOptimizer=ESS(FULL)OF'USER_INFO'ESS(FULL)OF'DEV_INFO'使用嵌套循环连接是一种从结果集中提取第一批记录最快速的方法。在驱动行源表(就是正在查找的记录)较小、或者内部行源表已连接的列有惟一的索引或高度可选的非惟一索引时,嵌套循环连接效果是比较理想的。嵌套循环连接比其他连接方法有优势,它可以快速地从结果集中提取第一批记录,而不用等待整个结果集完全确定下来。这样,在理想情况下,终端用户就可以通过查询屏幕查看第一批记录,而在同时读取其他记录。不管如何定义连接的条件或者模式,任何两行记录源可以使用嵌套循环连接,所以嵌套循环连接是非常灵活的。然而,如果内部行源表(读取的第二张表)已连接的列上不包含索引,或者索引不是高度可选时,嵌套循环连接效率是很低的。如果驱动表的记录非常庞大时,其他的连接方法可能更加有效。可以通过在SQL语句中添加HINTS,强制ORACLE优化器产生嵌套循环连接的执行计划。select/*+use_nl(ab)*/,fromuser_info