三大经典表连接探索（nested loop、hash、merge sort）

1、表的访问次数探索

• 表的访问次数之NL连接研究

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
     SELECT  rownum,  rownum, dbms_random.string('a', 50)
       FROM dual
     CONNECT BY level <= 100
      ORDER BY dbms_random.random;
INSERT INTO t2 SELECT rownum, rownum, rownum, dbms_random.string('b', 50) FROM dual CONNECT BY level <= 100000
    ORDER BY dbms_random.random;
COMMIT;

select count(*) from t1;

  COUNT(*)
----------
       100

select count(*) from t2;

  COUNT(*)
----------
    100000

设置statistics_level=all的方式来观察如下表连接语句的执行计划：

--T2表被访问100次(驱动表访问1次，被驱动表访问100次）
--这个set linesize 1000对dbms_xplan.display_cursor还是有影响的，如果没有设置，默认情况下的输出，将会少了很多列，如BUFFERS等
Set linesize 1000
alter session set statistics_level=all ;
SELECT /*+ leading(t1) use_nl(t2)*/ * FROM t1, t2 WHERE t1.id = t2.t1_id;

通过以上的实验说明：在NL连接中，驱动表返回多少条，被驱动表访问多少次。

E-Rows:预测返回的条数

A-Eows:实际返回的条数

Starts：相 关的表被访问几次

Buffer:使用多少内存

• 表的访问次数之hash连接

构造环境：

DROP TABLE t1 CASCADE CONSTRAINTS PURGE;
DROP TABLE t2 CASCADE CONSTRAINTS PURGE;
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
     SELECT  rownum,  rownum, dbms_random.string('a', 50)
       FROM dual
     CONNECT BY level <= 100
      ORDER BY dbms_random.random;
INSERT INTO t2 SELECT rownum, rownum, rownum, dbms_random.string('b', 50) FROM dual CONNECT BY level <= 100000
    ORDER BY dbms_random.random;
COMMIT;
select count(*) from t1;

100条
select count(*) from t2;

10000条

set serveroutput off

alter session set statistics_level=all ;

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'));

结论：驱动表与被驱动表只访问一次或者0次。

2、表的驱动顺序与性能的探索

• 表驱动顺序只nl连接

实验一：小结果集驱动大结果集（NL）

set linesize 1000
alter session set statistics_level=all;
SELECT /*+ leading(t1) use_nl(t2)*/  * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n = 19;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

实验二：大结果集驱动小结果集(NL)

alter session set statistics_level=all;
SELECT /*+ leading(t2) use_nl(t1)*/ * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n = 19;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

实验结论：实验1中，返回的小结果集的表做为了驱动表，实验二中返回结果集过的作为被驱动表。从上面buffes中可以明确的看到，返回小结果集作为驱动表消耗的内存更少，从start来看，实验二中的starts执行了1万次，性能有巨大的差异。

表驱动顺序之hash连接

实验1：小结果集驱动大结果集(HS)

set linesize 1000
alter session set statistics_level=all;
SELECT /*+ leading(t1) use_hash(t2)*/ * FROM t1, t2 WHERE t1.id = t2.t1_id and t1.n=19;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

实验二：大结果集驱动小结果集（HS）

set linesize 1000
SELECT /*+ leading(t2) use_hash(t1)*/ * FROM t1, t2 WHERE t1.id = t2.t1_id and t1.n=19;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

结论：从以上实验表明，hash连接中小结果集作为驱动表使用的开销更少。可以从OMem 、Used-Mem中可以看出。因为Hash需要排序。

3、表连接是否有排序

• 表连接之NL连接不需要排序

set linesize 1000
set autotrace traceonly
SELECT /*+ leading(t1) use_nl(t2)*/ * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n = 19;

• 表连接之HS连接不需要排序
• 表连接之排序合并连接

DROP TABLE t1 CASCADE CONSTRAINTS PURGE;
DROP TABLE t2 CASCADE CONSTRAINTS PURGE;
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
     SELECT  rownum,  rownum, dbms_random.string('a', 50)
       FROM dual
     CONNECT BY level <= 100
      ORDER BY dbms_random.random;
INSERT INTO t2 SELECT rownum, rownum, rownum, dbms_random.string('b', 50) FROM dual CONNECT BY level <= 100000
    ORDER BY dbms_random.random;
COMMIT;
select count(*) from t1;
select count(*) from t2;

set linesize 1000
set autotrace traceonly
SELECT /*+ leading(t1) use_merge(t2)*/ * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n = 19;

4、各个连接的限制条件使用限制

• NL连接使用的限制

支持各种限制 > < <> =

• HS连接使用的限制

Hash Join不支持大于，小于，不等，LIKE等连接条件！ 

• merge连接的限制

只支持> < 不支持 <> like等