- 浏览: 543434 次
- 性别:
- 来自: 杭州
文章分类
- 全部博客 (340)
- Spring (4)
- Hibernate (2)
- Linux (34)
- Oracle (145)
- Eclipse (1)
- UML (1)
- HTML&&JAVASCRIPT (11)
- JAVA (33)
- 设计模式 (1)
- 版本控制 (1)
- wrap框架 (3)
- IBATIS (5)
- Ruby (1)
- DWR (1)
- MINA (11)
- JBPM (2)
- 缓存技术 (4)
- 网络 (3)
- 应用服务器 (1)
- GWT (5)
- 杂谈 (2)
- ICE (4)
- XML (2)
- ArcGis (2)
- Flex (8)
- junit单元测试 (1)
- SNMP (1)
- 存储 (1)
- office (1)
- MongoDB (0)
- Greenplum (3)
- 管理点滴 (1)
- C++ (6)
- 网络入门 (3)
- Tomcat (7)
- JMX (0)
- webservice (1)
- Oracle的10046事件 (1)
- Library cache内部机制详解 (1)
- expdp通过dblink来导入 (1)
最新评论
-
yuanliangding:
有没有关于mock的更多知识。
基于mock对象和JUnit框架简化Spring Web组件单元测试 -
saup007:
ssh端口不是22,怎么搞呢?
Greenplum 学习笔记 -
springmvc-freemarker:
java开源项目源码实例下载
Apache上全部JAVA开源项目简介 -
bobbell:
哇塞,你真厉害,整理的非常全面。我是一个java barcod ...
Greenplum 学习笔记 -
wsj55133245513324:
这不是bug,你将日志级别从debug提升到INFO 就好了 ...
Spring,smppapi,apache mina, ssl快速实现安全的smpp(5)
摘要: 针对在数据仓库环境下,由于超大数据量的处理而产生的效率问题,本文深入分析了ORACLE表的几种连接方式、特点、适用范围,以及对于如何使用和优化做了详细的探讨。
关键字: 数据仓库 ORACLE 表连接
一 引言
数据仓库技术是目前已知的比较成熟和被广泛采用的解决方案,用于整和电信运营企业内部所有分散的原始业务数据,并通过便捷有效的数据访问手段,可以支持企业内部不同部门,不同需求,不同层次的用户随时获得自己所需的信息。数据仓库系统需要能够及时地追踪和分析大量的历史数据,并能够及时做出分析和预测,因此实时性是一个非常重要的指标。ORACLE由于可靠性、高性能等方面的特点,在电信行业大部分的数据仓库系统中担当了后台数据库的角色。由于电信行业的特点,处理的数据量十分庞大,处理的时间长。尤其是对于大表之间的关联操作,有的大表的记录数达到数亿条,处理时间更是漫长,这成为影响数据库运行效率的主要因素。因此,对于数据库的性能优化相当重要。性能优化是个很大的课题,需要综合考虑,从服务器、磁盘、网络、ORACLE实例、ORACLE SQL等多方面着手。本文着重分析ORACLE SQL优化中对于系统性能影响极大的表连接方式、特点、适用范围,并对如何使用和优化做了详细的探讨。
二 表的连接
表的连接是指在一个SQL语句中通过表与表之间的关联,从一个或多个表检索出相关的数据。连接是通过SQL语句中FROM从句的多个表名,以及WHERE从句里定义的表之间的连接条件来实现的。如果一个SQL语句的关联表超过两个,那么连接的顺序如何呢?ORACLE首先连接其中的两个表,产生一个结果集;然后将产生的结果集与下一个表再进行关联;继续这个过程,直到所有的表都连接完成;最后产生所需的数据。下面都以两个表的连接为例
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 char(10));
说明和分析表的各种连接方式。
ORACLE 从6的版本开始,优化器使用4种不同的表的连接方式:
Ø 嵌套循环连接(NESTED LOOP JOIN)
Ø 群集连接 (CLUSTER JOIN)
Ø 排序合并连接(SORT MERGE JOIN)
Ø 笛卡尔连接 (CARTESIAN JOIN)
ORACLE 7.3中,新增加了
Ø 哈希连接(HASH JOIN)。
在ORACLE 8中,新增加了
Ø 索引连接(INDEX JOIN)。
这六种连接方式都有其独特的技术特点,在一定的条件下,可以充分发挥高效的性能。
但是也都有其局限性,如果使用不当,不仅不能提高效率,反而会严重影响系统的性能。因此,深入地探讨连接方式的内部运行机制对于性能优化是必要的。
1 嵌套循环连接
嵌套循环连接的内部处理的流程:
1) Oracle 优化器根据基于规则RBO或基于成本CBO的原则,选择两个表中的一个作为驱动表,并指定其为外部表。
2) Oracle 优化器再将另外一个表指定为内部表。
3) Oracle从外部表中读取第一行,然后和内部表中的数据逐一进行对比,所有匹配的记录放在结果集中。
4) Oracle读取外部表中的第二行,再和内部表中的数据逐一进行对比,所有匹配的记录添加到结果集中。
5) 重复上述步骤,直到外部表中的所有纪录全部处理完。
6) 最后产生满足要求的结果集。
通过查询SQL语句的执行计划可以看出哪个表是外部表,哪个为内部表。
如 select a.user_name,b.dev_no
from user_info a, dev_info b
where a.user_id = b.user_id;
的执行计划:
SELECT STATEMENT Optimizer=CHOOSE
NESTED LOOPS
TABLE ACCESS (FULL) OF 'USER_INFO'
TABLE ACCESS (FULL) OF 'DEV_INFO'
上面的表是外部表,即驱动表
下面的表是内部表
使用嵌套循环连接是一种从结果集中提取第一批记录最快速的方法。在驱动行源表(就是正在查找的记录)较小、或者内部行源表已连接的列有惟一的索引或高度可选的非惟一索引时, 嵌套循环连接效果是比较理想的。嵌套循环连接比其他连接方法有优势,它可以快速地从结果集中提取第一批记录,而不用等待整个结果集完全确定下来。这样,在理想情况下,终端用户就可以通过查询屏幕查看第一批记录,而在同时读取其他记录。不管如何定义连接的条件或者模式,任何两行记录源可以使用嵌套循环连接,所以嵌套循环连接是非常灵活的。
然而,如果内部行源表(读取的第二张表)已连接的列上不包含索引,或者索引不是高度可选时, 嵌套循环连接效率是很低的。如果驱动表的记录非常庞大时,其他的连接方法可能更加有效。
可以通过在SQL语句中添加HINTS,强制ORACLE优化器产生嵌套循环连接的执行计划。
select /*+ use_nl(a b) */ a.user_name,b.dev_no
from user_info a, dev_info b
where a.user_id = b.user_id;
2 群集连接(CLUSTER JOIN)
群集连接实际上是嵌套循环连接的一种特例。如果所连接的两张源表是群集中的表,即两张表属于同一个段(SEGMENT),,那么ORACLE能够使用群集连接。处理的过程是:ORACLE从第一张行源表中读取第一行,然后在第二张行源表中使用CLUSTER索引查找能够匹配到的纪录;继续上面的步骤处理行源表中的第二行,直到所有的记录全部处理完。
群集连接的效率极高,因为两个参加连接的行源表实际上处于同一个物理块上。但是,群集连接也有其限制,没有群集的两个表不可能用群集连接。所以,群集连接实际上很少使用。
3 排序合并连接(SORT MERGE JOIN)
排序合并连接内部处理的流程:
1) 优化器判断第一个源表是否已经排序,如果已经排序,则到第3步,否则
到第2步。
2) 第一个源表排序
3) 优化器判断第二个源表是否已经排序,如果已经排序,则到第5步,否则
到第4步。
4) 第二个源表排序
5) 已经排过序的两个源表进行合并操作,并生成最终的结果集。
在缺乏数据的选择性或者可用的索引时,或者两个源表都过于庞大(所选的数据超过表记录数的5%)时,排序合并连接将比嵌套循环连更加高效。
排列合并连接需要比较大的临时内存块,以用于排序,这将导致在临时表空间占用更多的内存和磁盘I/O。
select a.user_name,b.dev_no
from user_info a, dev_info b
where a.user_id > b.user_id;
Plan
--------------------------------------------------
SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=336 Bytes=16128)
MERGE JOIN (Cost=7 Card=336 Bytes=16128)
SORT (JOIN) (Cost=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,强制ORACLE优化器产生排序合并连接的执行计划。
select /*+ use_merge(a b) */ a.user_name,b.dev_no
from user_info a, dev_info b
where a.user_id > b.user_id;
排序合并连接是基于RBO的。
4 笛卡尔连接 (CARTESIAN JOIN)
笛卡尔连接是指在sql语句中没有写出表连接的条件,优化器把第一个表的每一条记录和第二个表的所有纪录相连接。如果第一个表的纪录数为m, 第二个表的纪录数为m,则会产生m*n条纪录数。
下面的查询,未指名连接条件,就会产生笛卡尔连接。
select a.user_name,b.dev_no
from user_info a ,dev_info b;
由于笛卡尔连接会导致性能很差的SQL,因此一般也很少用到。
5哈希连接
当内存能够提供足够的空间时,哈希(HASH)连接是Oracle优化器通常的选择。哈希连接中,优化器根据统计信息,首先选择两个表中的小表,在内存中建立这张表的基于连接键的哈希表;优化器再扫描表连接中的大表,将大表中的数据与哈希表进行比较,如果有相关联的数据,则将数据添加到结果集中。
当表连接中的小表能够完全cache到可用内存的时候,哈希连接的效果最佳。哈希连接的成本只是两个表从硬盘读入到内存的成本。
但是,如果哈希表过大而不能全部cache到可用内存时,优化器将会把哈希表分成多个分区,再将分区逐一cache到内存中。当表的分区超过了可用内存时,分区的部分数据就会临时地写到磁盘上的临时表空间上。因此,分区的数据写磁盘时,比较大的区间(EXTENT)会提高I/O性能。ORACLE推荐的临时表空间的区间是1MB。临时表空间的区间大小由UNIFORM SIZE指定。
当哈希表构建完成后,进行下面的处理:
1) 第二个大表进行扫描
2) 如果大表不能完全cache到可用内存的时候,大表同样会分成很多分区
3) 大表的第一个分区cache到内存
4) 对大表第一个分区的数据进行扫描,并与哈希表进行比较,如果有匹配的纪录,添加到结果集里面
5) 与第一个分区一样,其它的分区也类似处理。
6) 所有的分区处理完后,ORACLE对产生的结果集进行归并,汇总,产生最终的结果。
当哈希表过大或可用内存有限,哈希表不能完全CACHE到内存。随着满足连接条件的结果集的增加,可用内存会随之下降,这时已经CACHE到内存的数据可能会重新写回到硬盘去。如果出现这种情况,系统的性能就会下降。
当连接的两个表是用等值连接并且表的数据量比较大时,优化器才可能采用哈希连接。哈希连接是基于CBO的。只有在数据库初始化参数HASH_JOIN_ENABLED设为True,并且为参数PGA_AGGREGATE_TARGET设置了一个足够大的值的时候,Oracle才会使用哈希边连接。HASH_AREA_SIZE是向下兼容的参数,但在Oracle9i之前的版本中应当使用HASH_AREA_SIZE。当使用ORDERED提示时,FROM子句中的第一张表将用于建立哈希表。
select a.user_name,b.dev_no
from user_info a, dev_info b
where a.user_id = b.user_id;
Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=82 Bytes=3936
)
1 0 HASH JOIN (Cost=5 Card=82 Bytes=3936)
2 1 TABLE ACCESS (FULL) OF 'USER_INFO' (Cost=2 Card=82 Bytes
=1968)
3 1 TABLE ACCESS (FULL) OF 'DEV_INFO' (Cost=2 Card=82 Bytes=
1968)
可以通过在SQL语句中添加HINTS,强制ORACLE优化器产生哈希连接的执行计划。
select /*+ use_hash(a b)*/ a.user_name,b.dev_no
from user_info a, dev_info b
where a.user_id = b.user_id;
当缺少有用的索引时,哈希连接比嵌套循环连接更加有效。哈希连接也可能比嵌套循环连接更快,因为处理内存中的哈希表比检索B_树索引更加迅速。
6 索引连接
如果一组已存在的索引包含了查询所需要的所有信息,那么优化器将在索引中有选择地生成一组哈希表。可通过范围或者快速全局扫描访问到每一个索引,而选择何种扫描方式取决于WHERE子句中的可有条件。在一张表有大量的列,而您只想访问有限的列时,这种方法非常有效。WHERE子句约束条件越多,执行速度越快。因为优化器在评估执行查询的优化路径时,将把约束条件作为选项看待。您必须在合适的列(那些满足整个查询的列)上建立索引,这样可以确保优化器将索引连接作为可选项之一。这个任务通常牵涉到在没有索引,或者以前没有建立联合索引的列上增加索引。相对于快速全局扫描,连接索引的优势在于:快速全局扫描只有一个单一索引满足整个查询;索引连接可以有多个索引满足整个查询。
假设表dev_info上有两个索(一个在dev_no,一个在dev_type 上)。
作如下的查询
select dev_no,dev_type
from user_info
where user_id = ‘U101010’
and dev_type = ‘1010’;
三 几种主要表连接的比较
类别 |
嵌套循环连接 |
排序合并连接 |
哈希连接 |
优化器提示 |
USE_NL |
USE_MERGE |
USE_HASH |
使用的条件 |
任何连接 |
主要用于不等价连接,如<、 <=、 >、 >=; 但是不包括 <> |
仅用于等价连接 |
相关资源 |
CPU、磁盘I/O |
内存、临时空间 |
内存、临时空间 |
特点 |
当有高选择性索引或进行限制性搜索时效率比较高,能够快速返回第一次的搜索结果。 |
当缺乏索引或者索引条件模糊时,排序合并连接比嵌套循环有效。 |
当缺乏索引或者索引条件模糊时,哈希连接连接比嵌套循环有效。通常比排序合并连接快。 在数据仓库环境下,如果表的纪录数多,效率高。
|
缺点 |
当索引丢失或者查询条件限制不够时,效率很低; 当表的纪录数多时,效率低。 |
所有的表都需要排序。它为最优化的吞吐量而设计,并且在结果没有全部找到前不返回数据。 |
为建立哈希表,需要大量内存。第一次的结果返回较慢。 |
四 结束语
深入地理解和掌握oracle的表连接对于优化数据库的性能至关重要。由于优化器选择方式的不同,以及统计信息的缺失或统计信息的不准确,ORACLE自动选择的表连接方式不一定是最优的。当SQL语句的执行效率很低时,可通过auto trace对执行计划进行跟踪和分析。当出现多表连接时,需要仔细分析是否有更佳的连接条件。根据系统的特点,必要时可以在SQL中添加HINTS,从而改变SQL的执行计划,从而达到性能优化的目的。
发表评论
-
expdp通过dblink来导入
2011-12-14 15:01 1741create.sql: spo create.log rem ... -
Library cache内部机制详解
2011-12-14 14:55 820Library cache内部机制详解 http://www ... -
Oracle的10046事件
2011-12-14 14:53 2051Oracle的10046事件,可以跟踪应用程序所执行的SQL语 ... -
REDO LOG MEMBER STATUS 和 REDO LOG GROUP STATUS
2011-12-14 14:51 892V$LOG 中列出的是REDO LOG GROUP STA ... -
oracle的exp/imp使用方法学习(转)
2011-08-30 08:54 918exp/imp两个命令可以说是oracle中最常用的命令了 ... -
oracle几个awr报告
2011-08-09 14:25 843工作中,碰到的数据库慢的几个awr报告 -
Oracle Profile 使用详解
2011-06-21 14:41 933一、目的: Oracle系统中的profile可以用来对 ... -
ORA-27013
2011-04-13 14:25 1476很新的一个bug,看下你的 memory_target是不是& ... -
ORACLE 外部表应用
2011-03-09 14:58 1025SQL> create or replace direc ... -
How to convert a 32-bit database to 64-bit database on Linux
2011-02-18 14:09 1183How to convert a 32-bit databas ... -
expdp impdp 数据库导入导出命令详解
2011-02-16 09:10 1905一、创建逻辑目录,该命令不会在操作系统创建真正的目录,最 ... -
Oracle手工解/锁表
2011-01-25 14:15 2393手工锁表:lock table tbl_t1 in row ... -
oracle动态性能视图
2011-01-22 10:47 820Oracle动态性能视图个人整理 -
RBO规则介绍
2011-01-17 16:45 963•无条件使用索引•使用内置的优先级别决定访问路径•比较难以 ... -
关于排序、sort_area_size、临时表空间
2011-01-13 15:30 1071简单陈述一下:针对每 ... -
创建索引ORACLE 需要做的工作
2011-01-13 14:53 1279一. 先来看一下创建索引要做哪些操作:1. 把inde ... -
表之间的连接
2011-01-13 10:42 762Join是一种试图将两个表结合在一起的谓词,一次只能连接2 ... -
重建索引的条件
2011-01-12 16:43 906如果索引存在碎片,那每个索引数据块上的索引数据就更少,会导致我 ... -
TKPROF使用学习
2011-01-10 17:38 673Tkprof工具可用来格式化sql trace产生的文件, ... -
利用USE_INDIRECT_DATA_BUFFERS突破32位的2G内存限制
2011-01-09 09:35 1573对于绝大部分32位系统上的32位数据库,内存最大的设置都不能超 ...
相关推荐
NEON-intrinsics 指令集
acle中国生产力信息网PPNET电子商务建设.docx
本文分析了实例解析acle ORA-00903错误的具体原因:表名或簇名不存在或无效,当运行ALTER CLUSTER或 DROP CLUSTER语句时,会出现此错误信息。解决方法:检查拼写是否正确。一个有效的表名或簇名必须以字母开头,只...
acle: DBA入门、进阶与诊断案例. (盖国强).rar
这是专门连接oracle数据库的客户端,非常适用于数据库安装在远程服务器的情况使用,解压到本地即可直接运行。 输入ip,用户名,密码即可连接你在远端的oracle数据库。只用一百多mb,windows下使用。
ARM C Language Extensions for SVE
mir.acle自述文件 异步命令行环境。 在运行任何其他make命令之前,请运行: $ make pipenv 要构建可安装的车轮,请运行: $ make wheel 要构建源发行版,请运行: $ make sdist 要运行测试,请运行: $ make ...
jspshop网络购物系统(orjspshop网络购物系统(oracle版) 共享版acle版) 共享版
首先介绍了基于OCCI 技术的Oracle9i 应用程序的建立方法,然后详细探讨了OCCI 开发环境的建立和使用OCCI 链接和操纵数据库的方法。
分析Java Web中分页的技术,并对各种分页技术进行对比。具体说明了超大数据量的分页在MySQL、Or-acle中的实现方法,编写了针对Oracle数据库分页接口和实现类。
随着空间信息技术不断发展,以及Or acle数据库规模的不断扩大、容量的不断增加,对Or acle数据库的性能的要求也不断提高。如何提高Or acle数据库的效率,如何解决海量空间数据的优化问题,成为数据库管理员面临的一个...
Or acle 11g 基于 Centos7 静默安装教程 ( 无图形界面 , 远程安装 )
ARM微控制器C语言扩展官方文档,Arm C Language Extensions Documentation(ACLE_Q3_2020),官方材料,最新,最权威。 电子版仅供预览及学习交流使用,下载后请24小时内删除,支持正版,喜欢的请购买正版书籍。
数据挖掘技术目前主要应用于智能商务领域, 在石化领域中如何展开数据挖掘开发应用还有待研究。...于Or acle Data Mining ( ODM ) 数据挖掘工具, 结合实例介绍了在石化企业中应用数据挖掘的系统方法和基本 过程。
实习报告 实习目的: 通过毕业前的实习巩固自己在大学期间所学的相关知识,锻炼自己实际... 5、熟悉oracle数据库,了解应用项目所需的oracle数据库,可以在项目中正确部署or acle数据库。 6、深入学习系统知识,了解工
Miracle Power Tool 是一款免费终极解锁工具,支持MTK、Qualcomm、UNiSOC(SPD)、powered、Samsung Android手机,帮助用户FRP Bypass、...如果您想要免费体验没有盒子和加密狗的高级手机维acle Power Tool下载软件。
Or(www.jb51.net)acle 9i中提供了一项新的技术手段–闪回查询,用户使用闪回查询可以及时取得误操作前的数据,并可以针对错误进行相应的恢复措施,而这一切都无需DBA干预 因为一时手贱,生产上的数据被我给delete掉...
映射到“ \ SERVER \ acle \ ONEDrive \ Nwucadev \ Nwudev_python_contaud” 服务器=内部审计拥有并由IT维护的Windows服务器(v-acl-win1.nwu.ac.za)。程式设计环境用python( )开发的脚本,所有脚本都驻留在S:...