NESTED LOOPS & HASH JOINS & MERGE JOIN
来源:互联网 发布:全息投影软件下载 编辑:程序博客网 时间:2024/05/08 00:45
参考Oracle官方文档:Performance Tuning Guide
1、NESTED LOOPS(嵌套循环链接)
Nested loop joins are useful when the following conditions are true:
■The database joins small subsets of data.
■The join condition is an efficient method of accessing the second table.
A nested loop join involves the following steps:
1.The optimizer determines the driving table and designates it as the outer table.
2.The other table is designated as the inner table.
3.For every row in the outer table, Oracle Database accesses all the rows in the inner table. The outer loop is for every row in the outer table and the inner loop is for every
row in the inner table. The outer loop appears before the inner loop in the execution plan, as follows:
NESTED LOOPS
outer_loop
inner_loop
Nested Loop Join Hints
If the optimizer chooses to use some other join method, then you can use the USE_NL(table1 table2) hint, where table1 and table2 are the aliases of the tables being joined.
hint:USE_NL链接
2、HASH JOINS(散列连接)
The database uses hash joins to join large data sets. The optimizer uses the smaller of two tables or data sources to build a hash table on the join key in memory. It then
scans the larger table, probing the hash table to find the joined rows.
This method is best when the smaller table fits in available memory. The cost is then limited to a single read pass over the data for the two tables.
When the Optimizer Uses Hash Joins
The optimizer uses a hash join to join two tables if they are joined using an equijoin and if either of the following conditions are true:
■A large amount of data must be joined.
■A large fraction of a small table must be joined.
hint:USE_HASH
3、MERGE JOIN(排序合并连接)
Sort merge joins can join rows from two independent sources. Hash joins generally perform better than sort merge joins. However, sort merge joins can perform better
than hash joins if both of the following conditions exist:
■The row sources are sorted already.
■A sort operation does not have to be done.
Sort merge joins are useful when the join condition between two tables is an inequality condition such as <, <=, >, or >=. Sort merge joins perform better than nested loop
joins for large data sets. You cannot use hash joins unless there is an equality condition.
In a merge join, there is no concept of a driving table. The join consists of two steps:
1.Sort join operation: Both the inputs are sorted on the join key.
2.Merge join operation: The sorted lists are merged together.
When the Optimizer Uses Sort Merge Joins
The optimizer can choose a sort merge join over a hash join for joining large amounts of data if any of the following conditions are true:
■The join condition between two tables is not an equijoin.
■Because of sorts required by other operations, the optimizer finds it is cheaper to use a sort merge than a hash join.
hint:USE_MERGE
- NESTED LOOPS & HASH JOINS & MERGE JOIN
- Nested loops, Hash join and Sort Merge joins – difference?
- nested loops/hash join
- NESTED LOOPS HASH JOIN
- Nested Loops Join、Hash join、Merge Sort Join三大经典表连接浅谈(笔记)
- Oracle执行计划中的连接方式nested loops join、sort merge joinn、hash join
- Nested loops、Hash join、Sort merge join(三种连接类型原理、使用要点)
- 认识优化查询中的Merge Join、Nested Loops和Hash Match
- 【学习】认识优化查询中的Merge Join、Nested Loops和Hash Match
- 认识优化查询中的Merge Join、Nested Loops和Hash Match
- 普通表的Join 三种算法(join 一) 嵌套循环Join(Nested Loops Join)、排序合并Join(Sort-Merge Join)和哈希Join(Hash Join)
- HASH JOIN ,SORT MERGE JOIN ,NESTED LOOP
- HASH JOIN/MERGE JOIN/NESTED LOOP
- 描述HASH JOIN ,MERGE JOIN ,NESTED LOOP
- Hash Join, Nested Loop, Sort-Merge Join
- HASH JOIN ,MERGE JOIN ,NESTED LOOP 比较
- HASH JOIN ,MERGE JOIN ,NESTED LOOP 比较
- hash join、nested loop,sort merge join
- 黑马程序员 - 银行业务调度系统
- 【C++再学习】【04】宏与内联函数共同点与不同点
- 在HelloWorld上--建立新场景
- 音乐中的琴与箫
- Linux的.a、.so和.o文件
- NESTED LOOPS & HASH JOINS & MERGE JOIN
- oracle ipad函数(从左边填充)
- JNative 调用动态链接库 调用动态链接库函数
- Javascript 中的false、0、null、undefined和空字符串对象
- gsoap使用
- 8款唯美设计的HTML5/CSS3应用
- 小议Java开发的含“金”量
- 超级台阶问题
- socket应用