这三类表连接方式是oracle最基本的连接方式：哈希连接(hash join) 原理 排序合并连接(sort merge join)的原理 嵌套循环连接(nes
这三类表连接方式是oracle最基本的连接方式：
哈希连接(hash join) 原理
排序合并连接(sort merge join)的原理
嵌套循环连接(nested loops join)
访问次数：驱动表返回几条，，被驱动表访问多少次。
驱动表是否有顺序：有。
是否要排序：否。
应用场景： 1.关联中有一个表比较小；
2.被关联表的关联字段上有索引；
3.索引的键值不应该重复率很高。
如果你做过开发，就把它看成两层嵌套的for循环。
下面我们来做个实验：
sql> create table test1 as select * from dba_objects where rownum
sql> create table test2 as select * from dba_objects where rownum sql> exec dbms_stats.gather_table_stats(user,'test1');
sql> exec dbms_stats.gather_table_stats(user,'test2');
sql> alter session set statistics_level=all;
sql> select /*+leading(t1) use_nl(t2)*/count(*)
2 from test1 t1, test2 t2
3 where t1.object_id = t2.object_id;
count(*)
----------
100
sql> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
plan_table_output
---------------------------------------------------------------------------------------------------------
sql_id 3v5gu7ppdsz67, child number 0
-------------------------------------
select /*+leading(t1) use_nl(t2)*/count(*) from test1 t1, test2 t2 where
t1.object_id = t2.object_id
plan hash value: 1459699139
----解释一下：
starts为该sql执行的次数。
e-rows为执行计划预计的行数。
a-rows为实际返回的行数。a-rows跟e-rows做比较，就可以确定哪一步执行计划出了问题。
a-time为每一步实际执行的时间（hh：mm：ss.ff），根据这一行可以知道该sql耗时在了哪个地方。
buffers为每一步实际执行的逻辑读或一致性读。
reads为物理读。
omem、1mem为执行所需的内存评估值，0mem为最优执行模式所需内存的评估值，1mem为one-pass模式所需内存的评估值。
0/1/m 为最优/one-pass/multipass执行的次数。
used-mem耗的内存
---------------------------------------------------------------------------------------
| id | operation | name | starts | e-rows | a-rows | a-time | buffers |
---------------------------------------------------------------------------------------
| 1 | sort aggregate | | 1 | 1 | 1 |00:00:00.01 | 1504 |
| 2 | nested loops | | 1 | 100 | 100 |00:00:00.01 | 1504 |
| 3 | table access full| test1 | 1 | 100 | 100 |00:00:00.01 | 4 |
|* 4 | table access full| test2 | 100 | 1 | 100 |00:00:00.01 | 1500 |
---------------------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
4 - filter(t1.object_id=t2.object_id)
sql> select /*+leading(t1) use_nl(t2)*/count(*)
2 from test1 t1, test2 t2
3 where t1.object_id = t2.object_id
4 and t1.object_id in (10, 11, 12);
count(*)
----------
3
sql> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
plan_table_output
---------------------------------------------------------------------------------------------------------
sql_id 0skx6hyjtsncu, child number 0
-------------------------------------
select /*+leading(t1) use_nl(t2)*/count(*) from test1 t1, test2 t2 where
t1.object_id = t2.object_id and t1.object_id in (10, 11, 12)
---------------------------------------------------------------------------------------
| id | operation | name | starts | e-rows | a-rows | a-time | buffers |
---------------------------------------------------------------------------------------
| 1 | sort aggregate | | 1 | 1 | 1 |00:00:00.01 | 49 |
| 2 | nested loops | | 1 | 3 | 3 |00:00:00.01 | 49 |
|* 3 | table access full| test1 | 1 | 3 | 3 |00:00:00.01 | 4 |
|* 4 | table access full| test2 | 3 | 1 | 3 |00:00:00.01 | 45 |
---------------------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
3 - filter((t1.object_id=10 or t1.object_id=11 or
t1.object_id=12))
4 - filter((internal_function(t2.object_id) and
t1.object_id=t2.object_id))
sql> select /*+leading(t1) use_nl(t2)*/count(*)
2 from test1 t1, test2 t2
3 where t1.object_id = t2.object_id
4 and t1.object_id =10;
count(*)
----------
1
更多详情见请继续阅读下一页的精彩内容：