oracle 嵌套 哈希,Oracle-三种联接方法(哈希连接、嵌套连接、笛卡儿乘积)

在数据库系统中执行一个查询SQL语句,如果这个查询只操作一张表,那么仅仅涉及到这个表及关联对象的访问。

访问方式通常是三种:全表扫描、全索引扫描和索引扫描。

如果这个查询操作两张及以上的表,那么需要操作的表之间的连接关系就变得至关重要。

数据库系统执行该SQL时,永远都是两个结果集关联。

例如,

操作三张表,那么就是其中两张表关联出一个结果集,和第三张表关联。

操作四张表,那么就是其中两张表关联出一个结果集,和第三张表关联出一个结果集,再和第四张表关联出最后的结果集。当然也可能是两两关联出两个结果集,再相互关联得到最后的结果集。

操作五张表,各个表之间的关联方式就更复杂了。

这些描述就是数据库系统用来根据操作表的不同排列和组合而生成不同的执行计划。

Oracle数据库系统会使用一种机制,来决定哪一种组合性能最好。这种机制称为基于成本的优化器(Cost-Based Optimization,简称为CBO)。

---------------------------------------------嵌套连接

嵌套循环,顾名思义就是将一个表为出发点,将该表全部记录逐条去遍历另外一张表的记录,符合条件的就是写入结果集。

基于嵌套连接的特点,我们可以想得到,它在两个关联表的数据量相差比较大时采用,但整体上数据量都不应该太大。

该关联方式适用于得到小数据量的查询操作。

100312690.bmp

例1:

使用leading与use_n1来强制使用嵌套循环连接

leading提示要求先访问表t1,也就是它指定了哪张表作为外部循环表使用

use_nl提示指定了具体使用哪种连接方法来将内部循环返回的数据(表t2)与表t1连接起来

有必要指出的是:use_nl提示并没有引用t1

SELECT /*+ leading(t1) use_nl(t2) full(t1) full(t2) */*

FROM t1,t2 WHERE t1.id = t2.id AND t1.n = 19

执行计划如下:

100312691.jpg

100312692.jpg

100312693.jpg

100312694.bmp

100312695.bmp

100312696.bmp

100312697.bmp

100312698.bmp

100312699.bmp

100312700.bmp

例2:

select a.*, b *

from EMP a, DEPT b

where a.DEPTNO = b.DEPTNO;

如使用emp表为出发点,将emp表的记录都查询出来为m条,再将这m条记录的字段deptno值,逐条和dept表的所有记录的deptno字段值匹配,假如dept表有n条记录。

匹配出来的记录符合条件就写入到结果集中。

那么这样关联操作过程中,操作的记录条数就是:先是emp表的m条,接着是dept表n条,但查了m遍,总的记录数就是m+m*n。

如使用dept表为出发点,去遍历emp表,那么总的记录数就是n+n*m。

出发点不同的连接方法,需要的成本就是不一样的。CBO会去最小的那个。

使用伪代码来表示一下嵌套循环连接

declare

begin

for outer_table in (select * from emp) loop

for inner_table in (select *

from dept

where DEPTNO = outer_table.DEPTNO) loop

dbms_output.put_line(inner_table.*, outer_table.*);

end loop;

end loop;

end;

---------------------------------------------哈希连接

a) 这种方法是在Oracle7后来引入的,使用了比较先进的连接理论,一般来说,其效率应该好于其它2种连接,但是这种连接只能用在CBO优化器中,而且需要设置合适的hash_area_size参数,才能取得较好的性能。

b) 在2个较大的row source之间连接时会取得相对较好的效率,在一个row source较小时则能取得更好的效率。

c) 只能用于等值连接中

---------------------------------------------笛卡儿乘积

当两个row source做连接,但是它们之间没有关联条件时,就会在两个row source中做笛卡儿乘积,这通常由编写代码疏漏造成(即程序员忘了写关联条件)。笛卡尔乘积是一个表的每一行依次与另一个表中的所有行匹配。在特殊情况下我们可以使用笛卡儿乘积,如在星形连接中,除此之外,我们要尽量使用笛卡儿乘积,否则,自己想结果是什么吧!

注意在下面的语句中,在2个表之间没有连接。

SQL> explain plan for

select emp.deptno,dept,deptno

from emp,dept

Query Plan

SLECT STATEMENT [CHOOSE] Cost=5

MERGE JOIN CARTESIAN

TABLE ACCESS FULL DEPT

SORT JOIN

TABLE ACCESS FULL EMP

CARTESIAN关键字指出了在2个表之间做笛卡尔乘积。假如表emp有n行,dept表有m行,笛卡尔乘积的结果就是得到n * m行结果。

  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
嵌套哈希合并算法(Nested Loop Join)和哈希合并算法(Hash Join)是 Oracle 数据库中常用的两种连接算法。它们都用于在两个表之间执行连接操作,但在性能和适用场景上有所不同。 嵌套哈希合并算法是一种简单直观的连接算法,它适用于其中一个表的数据集相对较小(称为驱动表),而另一个表的数据集较大(称为被驱动表)。算法的原理是将驱动表的每一行与被驱动表进行比较,找出匹配的行。这种算法的时间复杂度为O(n*m),其中n为驱动表的行数,m为被驱动表的行数。由于需要对被驱动表进行多次全表扫描,所以在被驱动表数据量大时性能较差。 哈希合并算法则是一种更高效的连接算法,它适用于两个表的数据集都较大。算法的原理是将两个表中的数据都按照连接键进行哈希分区,并将相同哈希值的数据存储在内存中的哈希表中。然后,对两个哈希表进行匹配,找出匹配的行。这种算法的时间复杂度为O(n+m),其中n为第一个表的行数,m为第二个表的行数。哈希合并算法利用了哈希表的快速查找特性,在处理大规模数据时性能较好。 综合来说,嵌套哈希合并算法适用于一个表的数据集较小,而被驱动表的数据集较大的情况。而哈希合并算法则适用于两个表的数据集都较大的情况。在实际应用中,Oracle 数据库会根据统计信息和查询条件等因素自动选择合适的连接算法,以获得最佳性能。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值