31.读书笔记收获不止Oracle之表的合并排序连接

测试如下sql语句

sql>Alter session set statistics_level=all;

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

sql> select * fromtable(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

sql_ID 5jxwkr07a1jdp,child number 1

-------------------------------------

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

Plan hash value: 412793182

--------------------------------------------------------------------------------

---------------------------------

| Id| Operation | Name | Starts | E-Rows | A-Rows | A-Time| Buf

fers | OMem| 1Mem | Used-Mem |

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

---------------------------------

| 0| SELECT STATEMENT | | 1 | | 100 |00:00:00.08 |

1012 | | | |

| 1| MERGE JOIN | | 1 | 100 | 100|00:00:00.08 |

1012 | | | |

| 2| SORT JOIN | | 1 | 100 | 100|00:00:00.01 |

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

7| 13312 | 13312 |12288 (0)|

| 3| TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 |

7| | | |

|* 4| SORT JOIN | | 100 | 118K| 100 |00:00:00.08 |

1005 | 9762K| 1209K| 8677K (0)|

| 5| TABLE ACCESS FULL| T2 | 1 | 118K| 100K|00:00:00.02 |

1005 | | | |

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

---------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

4- access("T1"."ID"="T2"."T1_ID")

filter("T1"."ID"="T2"."T1_ID")

Note

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

-----

-dynamic statistics used: dynamic sampling (level=2)

27 rows selected.

从访问次数来看，排序合并连接和HASH链接是一样的，T1表和T2表都只会访问0次或者1次。

有一点需要牢记：排序合并连接根本就没有驱动和被驱动的概念，而嵌套循环和哈希连接要考虑驱动和被驱动的情况。