oracle表连接-nestedloop嵌套循环连接 - 数据库编程

一. nested loop 原理

nested loop 连接(循环嵌套连接)指的是两个表连接时, 通过两层嵌套循环来进行依次的匹配, 最后得到返回结果集的表连接方法.

假如下面的 sql 语句中表 T1 和 T2 的连接方式是循环嵌套连接, T1 是驱动表

select *
from T1, T2
where T1.id = T2.id and T1.name = 'David';

那么将上述 sql 语句翻译为伪码应该如下所示:.

for each row in (select * from T1 where name = 'David') loop
for (select * from T2 where T2.id = outer.id) loop
If match then pass the row on to the next step
If no match then discard the row
end loop
end loop

具体来说, 如果上述 sql 语句执行循环嵌套连接的话, 那么实际的执行过程应该如下所示:
(1) 首先 oracle 会根据一定的规则(根据统计信息的成本计算或者 hint 强制)决定哪个表是驱动表, 哪个表是被驱动表 (假设 T1 是驱动表)
(2) 查询驱动表 "select * from T1 where name = 'David'" 然后得到驱动结果集 Q1
(3) 遍历驱动结果集 Q1 以及被驱动表 T2, 从驱动结果集 Q1 中取出一条记录, 接着遍历 T2 并按照连接条件 T2.id = T1.id 去判断 T2 中是否存在匹配的记录, 如果能够匹配则保留, 不能匹配则忽略此行, 然后再从 Q1 中取出下一条记录, 接着遍历 T2 进行匹配, 如此下去直到取完 Q1 中的所有记录

二. nested loop 特性

嵌套循环连接有以下特性:

(1) 通常 sql 语句中驱动表只访问一次, 被驱动表访问多次
(2) 不必等待处理完成所有行前可以先返回部分已经处理完成的数据
(3) 在限制条件以及连接条件列上建立索引, 能够提高执行效率
(4) 支持所有类型的连接 (等值连接, 非等值连接, like 等)

构造试验数据

SQL> CREATE TABLE t1 (
  2    id NUMBER NOT NULL,
  3    n NUMBER,
  4    pad VARCHAR2(4000),
  5    CONSTRAINT t1_pk PRIMARY KEY(id)
  6  );

Table created.

SQL> CREATE TABLE t2 (
  2    id NUMBER NOT NULL,
  3    t1_id NUMBER NOT NULL,
  4    n NUMBER,
  5    pad VARCHAR2(4000),
  6    CONSTRAINT t2_pk PRIMARY KEY(id),
  7    CONSTRAINT t2_t1_fk FOREIGN KEY (t1_id) REFERENCES t1
  8  );

Table created.

SQL> CREATE TABLE t3 (
  2    id NUMBER NOT NULL,
  3    t2_id NUMBER NOT NULL,
  4    n NUMBER,
  5    pad VARCHAR2(4000),
  6    CONSTRAINT t3_pk PRIMARY KEY(id),
  7    CONSTRAINT t3_t2_fk FOREIGN KEY (t2_id) REFERENCES t2
  8  );

Table created.
SQL> CREATE TABLE t4 (
  2    id NUMBER NOT NULL,
  3    t3_id NUMBER NOT NULL,
  4    n NUMBER,
  5    pad VARCHAR2(4000),
  6    CONSTRAINT t4_pk PRIMARY KEY(id),
  7    CONSTRAINT t4_t3_fk FOREIGN KEY (t3_id) REFERENCES t3
  8  );


Table created.

SQL> execute dbms_random.seed(0)

PL/SQL procedure successfully completed.


SQL> INSERT INTO t1 SELECT rownum, rownum, dbms_random.string('a',50) FROM dual CONNECT BY level <= 10 ORDER BY dbms_random.random;

10 rows created.

SQL> INSERT INTO t2 SELECT 100+rownum, t1.id, 100+rownum, t1.pad FROM t1, t1 dummy ORDER BY dbms_random.random;

100 rows created.

SQL> INSERT INTO t3 SELECT 1000+rownum, t2.id, 1000+rownum, t2.pad FROM t2, t1 dummy ORDER BY dbms_random.random;

1000 rows created.

SQL> INSERT INTO t4 SELECT 10000+rownum, t3.id, 10000+rownum, t3.pad FROM t3, t1 dummy ORDER BY dbms_random.random;

10000 rows created.

SQL> COMMIT;

Commit complete.

使用 hint 让 sql 语句通过 nested loop 连接, 并且指定 t3 为驱动表

SQL> select /*+ leading(t3) use_nl(t4) */ * from t3, t4
  2  where t3.id = t4.t3_id and t3.n = 1100;

10 rows selected.

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

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------

SQL_ID  89hnfwqakjghg, child number 0
-------------------------------------
select /*+ leading(t3) use_nl(t4) */ * from t3, t4 where t3.id =
t4.t3_id and t3.n = 1100

Plan hash value: 1907878852

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     10 |00:00:00.01 |     121 |
|   1 |  NESTED LOOPS      |      |      1 |     10 |     10 |00:00:00.01 |     121 |
|*  2 |   TABLE ACCESS FULL| T3   |      1 |      1 |      1 |00:00:00.01

oracle表连接-nestedloop嵌套循环连接(一)