oracle表连接（一）

一个普通的语句select * from t1, t2 where t1.id = t2.id and t1.name = 'a'; 这个语句在什么情况下最高效？

表连接分类：

  1. 嵌套循环连接(Nested Loop Join)

  2. 排序合并连接(Merge Sort Join)：PGA 中的 SORT_AREA_SIZE 控制

  3. 哈希连接(Hash Join)：PGA中的HASH_AREA_SIZE控制

create table t1(id number not null,n number,contents varchar2(4000));

create table t2(id number not null,t1_id number not null,n number,contents varchar2(4000));

execute dbms_random.seed(0);
insert into t1
(id,n,contents)
select rownum,rownum,dbms_random.string('a', 50)
from dual connect by level <= 100 order by dbms_random.random;

insert into t2
(id,t1_id, n,contents)
select rownum,rownum,rownum,dbms_random.string('b', 50)
from dual connect by level <= 100000 order by dbms_random.random;
commit;

-- 1. NL连接表

HINT含义 leading(t1)表示强制先访问t1表，也就是t1表作为驱动表；use_nl表示强制用嵌套循环连接方式

select /*+leading(t1) use_nl(t2)*/ * from t1, t2 where t1.id = t2.t1_id;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

starts列是表访问的次数，t1表访问了1次，t2表访问了100次，在嵌套循环连接中，驱动表返回多少条记录，被驱动表就访问多少次。

--2.  哈希连接表

select /*+leading(t1) use_hash(t2)*/ * from t1, t2 where t1.id = t2.t1_id;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

t1 1次, t2 1次

在HASH连接中，驱动表和被驱动表都只会访问1次或0次

-- 3. 排序合并连接

select /*+ordered use_merge(t2)*/ * from t1, t2 where t1.id = t2.t1_id;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

t1 1次, t2 1次

 假设两张表，A和B，如果A作为驱动表，B作为被驱动表，那么，嵌套循环连接，就是先扫描A表，对A表的每一条记录，都扫描一遍B表；

而排序合并连接是 A表跟B表内的记录同时排序，然后连接; 哈希连接,类似排序合并，建立哈希表来连接。所以当返回少量记录的时候(OLTP系统常用，OLTP多用于电信、金融等系统)，嵌套循环连接更高效，而返回大量记录的时候，排序合并连接跟哈希连接更高效。