HASH JOIN ,MERGE JOIN ,NESTED LOOP
对于Oracle的一些基本知识要加强学习，我从网上抄了三个人对这个题目的见解下来，供已参考。NESTED LOOP:
对于被连接的数据子集较小的情况，嵌套循环连接是个较好的选择。在嵌套循环中，内
表被外表驱动，外表返回的每一行都要在内表中检索找到与它匹配的行，因此整个查询返回
的结果集不能太大（大于1 万不适合），要把返回子集较小表的作为外表（CBO 默认外表是
驱动表），而且在内表的连接字段上一定要有索引。当然也可以用ORDERED 提示来改变CBO
默认的驱动表，使用USE_NL(table_name1 table_name2)可是强制CBO 执行嵌套循环连接。
HASH JOIN :
散列连接是CBO 做大数据集连接时的常用方式，优化器使用两个表中较小的表（或数据
源）利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配
的行。
这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成
本之和。但是在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的
分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有较大的临时段从而尽量
提高I/O 的性能。
也可以用USE_HASH(table_name1 table_name2)提示来强制使用散列连接。如果使用散
列连接HASH_AREA_SIZE 初始化参数必须足够的大，如果是9i，Oracle建议使用SQL工作区
自动管理，设置WORKAREA_SIZE_POLICY 为AUTO，然后调整PGA_AGGREGATE_TARGET 即可。
排序合并连接
通常情况下散列连接的效果都比排序合并连接要好，然而如果行源已经被排过序，在执
行排序合并连接时不需要再排序了，这时排序合并连接的性能会优于散列连接。可以使用
USE_MERGE(table_name1 table_name2)来强制使用排序合并连接
Nested loop join:
步骤：确定一个驱动表(outer table)，另一个表为inner table，驱动表中的每一行与inner表中的相应记录JOIN。类似一个嵌套的循环。适用于驱动表的记录集比较小（,=,)
3.HASH_JOIN_ENABLED=false
4.数据源已排序
cost = (outer access cost * # of hash partitions) + inner access cost
转载biti的一段话:
举例，表连接返回一条记录
存在两个表，一个 10条记录 ，一个1000万条记录
若2表都存在连接字段索引，若以小表为驱动表，则
代价：
10* (通过索引在大表查询一条记录的代价)
若以大表为驱动表：
1000万 * (通过索引在小表中查询一条记录的代价)
通过索引获取一条记录，10rows的表，代价通常在 3 blocks
索引2块，表一块
而如果是1000万的表，索引可能达到4块表一块
这样一来参考上面的计算，你说哪个更好？很显然！
小表查询参考
SQL> create table test as select * from all_objects where rownum create index test_index on test(object_id);
Index created.
SQL> select object_id from test;
OBJECT_ID
----------
18159
7781
4841
19891
22549
17099
17712
4287
10107
19135
10 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST'
Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
6 consistent gets
0 physical reads
0 redo size
736 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> select * from test where object_id = 4287;
OWNER OBJECT_NAME
------------------------------ ------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- ------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- --------- ------------------- ------- - - -
SYS /1033c8a_SqlTypeWithMethods
4287 JAVA CLASS
14-NOV-00 03-JUL-03 2003-07-03:11:18:19 INVALID N N N
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
2 1 INDEX (RANGE SCAN) OF 'TEST_INDEX' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1157 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u1/46451/showart_415529.html
页:
[1]