oracle表连接-sortmergejoins排序合并连接 - 数据库编程

0.01 | 15 | 2048 | 2048 | 2048 (0)| |* 3 | TABLE ACCESS FULL| T3 | 1 | 1 | 1 |00:00:00.01 | 15 | | | | |* 4 | SORT JOIN | | 1 | 10000 | 10 |00:00:00.02 | 104 | 974K| 535K| 865K (0)| | 5 | TABLE ACCESS FULL| T4 | 1 | 10000 | 10000 |00:00:00.01 | 104 | | | | ----------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("T3"."N"=1100) 4 - access("T3"."ID"="T4"."T3_ID") filter("T3"."ID"="T4"."T3_ID") 使用 hint 让执行计划以 T4 作为驱动表

SQL> select /*+ leading(t4) use_merge(t3) */ *
  2  from t3, t4
  3  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  gxuwn06y1c1az, child number 0
-------------------------------------
select /*+ leading(t4) use_merge(t3) */ * from t3, t4 where t3.id =
t4.t3_id and t3.n = 1100


Plan hash value: 875334572


-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |     10 |00:00:00.04 |     119 |       |       |          |
|   1 |  MERGE JOIN         |      |      1 |     10 |     10 |00:00:00.04 |     119 |       |       |          |
|   2 |   SORT JOIN         |      |      1 |  10000 |   1001 |00:00:00.04 |     104 |   974K|   535K|  865K (0)|
|   3 |    TABLE ACCESS FULL| T4   |      1 |  10000 |  10000 |00:00:00.01 |     104 |       |       |          |
|*  4 |   SORT JOIN         |      |   1001 |      1 |     10 |00:00:00.01 |      15 |  2048 |  2048 | 2048  (0)|
|*  5 |    TABLE ACCESS FULL| T3   |      1 |      1 |      1 |00:00:00.01 |      15 |       |       |          |
-----------------------------------------------------------------------------------------------------------------


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


   4 - access("T3"."ID"="T4"."T3_ID")
       filter("T3"."ID"="T4"."T3_ID")
   5 - filter("T3"."N"=1100)

从返回的执行计划结果中我们可以看到:
1. 以 T3 为驱动表和以 T4 为驱动表, 两者的 cost (A-Time) 和 buffers 都差不多

2. 以 T3 为驱动表时, T3 访问一次, T4 也是访问一次; 以 T4 为驱动表时, T4 访问一次, T3 也是访问一次

3. 需要排序, 如果 PGA 空间重足时在 PGA 中排序, 不如果不足则交换到磁盘上排序

另外, 在执行计划中有几个统计信息列 0Mem, 1Mem, Use_Mem 需要介绍一下
0Mem 指的是预计在 PGA 中排序需要的内存大小1Mem 指的是当内存大小（PGA）不足以进行排序, 预计将数据一次交换到磁盘空间的内存大小Used-Mem 指的是执行时实际使用的内存大小, 其中括号中的数字代表进行磁盘交换的次数, 0 代表没有进行磁盘交换
三. sort merge joins连接(排序合并连接) 优化

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


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


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


SQL_ID  bg9h60c7ak3ud, child number 0
---------------------

oracle表连接-sortmergejoins排序合并连接(二)