登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

li.angshan 的博客

关注数据计算领域

 
 
 

日志

 
 
 
 

Oracle SQL 几种连接类型  

2011-06-28 16:10:13|  分类: oracle 优化 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
1. Oracle是通过CBO(基于成本的优化器)提供sql的执行计划的, 通常情况, 在统计数据完整的情况下, CBO都能提供最优化的执行计划(优化器会根据事先创建几个执行计划,然后比较选择一个成本最低的执行计划。),所以Oracle DBA 在做sql优化时基本就是对统计数据的管理与日常维护。
这就要求DBA们在日常的运维管理中对统计数据的监控和管理更加用心,这样才能为突如其来的sql语句提供最优的执行计划提供保障。
2.SQL语句的执行,有时要受到语句编写不当造成的性能降低,这涉及到业务提取的干扰,如访问了不需要的表,或者编写的格式不正确(表的访问顺序、索引的使用等)

SQL语句编写结构对性能的影响:
嵌套循环连接 Nested Loop:

一. NESTED LOOP:

当一个小的数据集合被连接并且连接条件已等号的方式去连接另一个表时,嵌套循环连接很有用。在嵌套循环中,内表被外表驱动,外表返回的每一行都要在内表中检索找到与它匹配的行,因此整个查询返回的结果集不能太大(大于1 万不适合),要把返回子集较小表的作为外表(CBO 默认外表是驱动表),而且在内表的连接字段上一定要有索引。当然也可以用ORDERED 提示来改变CBO默认的驱动表,使用USE_NL(table_name1 table_name2)可是强制CBO 执行嵌套循环连接

 一点非常重要:内部表是被外部表驱动访问的, 也就说,循环从外部表开始,然后根据外部表的每个值在去全读一次内部表。如果不是这样, 嵌套循环连接会有相当大的性能损耗,这种情况,哈希连接会更好       

Nested loop一般用在连接的表中有索引,并且索引选择性较好的时候.

 

步骤:确定一个驱动表(outer table),另一个表为inner table,驱动表中的每一行与inner表中的相应记录JOIN。类似一个嵌套的循环。适用于驱动表的记录集比较小(<10000)而且inner表需要有有效的访问方法(Index。需要注意的是:JOIN的顺序很重要,驱动表的记录集一定要小,返回结果集的响应时间是最快的。

cost = outer access cost + (inner access cost * outer cardinality)

 

|   2 |   NESTED LOOPS                |              |     3 |   141 |     7  (15)| 
|* 3 | TABLE ACCESS FULL | EMPLOYEES | 3 | 60 | 4 (25)|
| 4 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 513 | 2 (50)|
|* 5 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | |

EMPLOYEESouter table, JOBSinner table.


如下所示:


select count(1) from ta ; ----1000000

select count(1) from tb ; ---18 

create index idx_ta_id on ta(id);

create index idx_tb_id on tb(id);



在有索引的情况,优化器将使用嵌套循环连接,结果集较小的表(ta) 做为驱动表,并且根据表的连接顺序无关。



注意: 在Oracle RBO 时代, 由于解析器是从sql语句的后面向前执行的, 所以写在from子句最后的表会先被执行,如果加上过滤条件就会影响到性能上的很大的差距。CBO则不会出现这个情况,它始终将结果集最小的表当做驱动表。


二. HASH JOIN :

哈希连接用于连接大的数据集合。优化器在两个表中或多个数据源中选择一个数据量最小的集合在内存中构造一个含有连接健的结构,然后扫描大表,寻找哈希表对应的行。

这种方式适用于较小的表完全可以放于内存中的情况,这样总成本就是访问两个表的成本之和。但是在表很大的情况下并不能完全放入内存,这时优化器会将它分割成若干不同的分区,不能放入内存的部分就把该分区写入磁盘的临时段,此时要有较大的临时段从而尽量提高I/O 的性能。

也可以用USE_HASH(table_name1 table_name2)提示来强制使用散列连接。如果使用散列连接HASH_AREA_SIZE 初始化参数必须足够的大,如果是9iOracle建议使用SQL工作区自动管理,设置WORKAREA_SIZE_POLICY AUTO,然后调整PGA_AGGREGATE_TARGET 即可。

        

Hash join在两个表的数据量差别很大的时候将被使用.

 

步骤:将两个表中较小的表根据连接键值构造一个内存HASH,扫描另一个表,同样对JOIN KEY进行HASH后探测是否可以JOIN适用于记录集比较大的情况。需要注意的是:如果HASH表太大,无法一次构造在内存中,则分成若干个partition,写入磁盘的temporary segment,则会多一个写的代价,会降低效率。

cost = (outer access cost * # of hash partitions) + inner access cost

 

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 665 | 13300 | 8 (25)|
| 1 | HASH JOIN | | 665 | 13300 | 8 (25)|
| 2 | TABLE ACCESS FULL | ORDERS | 105 | 840 | 4 (25)|
| 3 | TABLE ACCESS FULL | ORDER_ITEMS | 665 | 7980 | 4 (25)|
--------------------------------------------------------------------------

ORDERSHASH TABLEORDER_ITEMS扫描


select count(1) from ta ; ----1000000

select count(1) from tb ; ---18 




oracle sql 优化思路 - 梁山 - li.angshan 的博客
 


在没有 索引的情况,优化器将使用哈希连接,结果集较小的表(ta) 做为内存表,并且根据表的连接顺序无关。






  评论这张
 
阅读(814)| 评论(0)

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018