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

| 16 | |* 3 | TABLE ACCESS FULL| T4 | 1 | 10 | 10 |00:00:00.01 | 105 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("T3"."N"=1100) 3 - filter("T3"."ID"="T4"."T3_ID")
在执行计划中我们可以看到驱动表 T3 访问一次, 因为驱动表上有谓词条件 t3.n = 1100, 通过执行谓词条件后驱动结果集的记录数为 1, 所以 T4 也只访问一次(starts 列)

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

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

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

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

Plan hash value: 3886808168

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     10 |00:00:00.25 |     150K|
|   1 |  NESTED LOOPS      |      |      1 |     10 |     10 |00:00:00.25 |     150K|
|   2 |   TABLE ACCESS FULL| T4   |      1 |  10000 |  10000 |00:00:00.01 |     105 |
|*  3 |   TABLE ACCESS FULL| T3   |  10000 |      1 |     10 |00:00:00.21 |     150K|
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(("T3"."N"=1100 AND "T3"."ID"="T4"."T3_ID"))

在执行计划中我们可以看到驱动表 T4 访问一次, 因为驱动表上 T4 结果集的记录数为 10000, 所以 T4 访问了 10000 次, buffers 和 A-time(实际执行时间) 都比较高.

三. nested loop 优化

在 nested loop 被驱动表上的连接列上 (T4 表的 t3_id 列) 建立索引

SQL> CREATE INDEX t4_t3_id ON t4(t3_id);

Index created.

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: 2039660043

------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |      1 |        |     10 |00:00:00.01 |      29 |   1 |
|   1 |  NESTED LOOPS                |          |      1 |        |     10 |00:00:00.01 |      29 |   1 |
|   2 |   NESTED LOOPS               |          |      1 |     10 |     10 |00:00:00.01 |      19 |   1 |
|*  3 |    TABLE ACCESS FULL         | T3       |      1 |      1 |      1 |00:00:00.01 |      16 |   0 |
|*  4 |    INDEX RANGE SCAN          | T4_T3_ID |      1 |     10 |     10 |00:00:

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