表的三大连接之merge

select /*+ ordered use_merge(t2) */ * from t1,t2 where t1.id=t2.t1_id;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
SQL_ID  2ghmkzagzhtgn, child number 0
-------------------------------------
select /*+ ordered use_merge(t2) */ * from t1,t2 where t1.id=t2.t1_id

Plan hash value: 412793182

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |     99 |00:00:00.05 |    1012 |       |       |          |
|   1 |  MERGE JOIN         |      |      1 |     99 |     99 |00:00:00.05 |    1012 |       |       |          |
|   2 |   SORT JOIN         |      |      1 |     99 |     99 |00:00:00.01 |       7 | 13312 | 13312 |12288  (0)|
|   3 |    TABLE ACCESS FULL| T1   |      1 |     99 |     99 |00:00:00.01 |       7 |       |       |          |
|*  4 |   SORT JOIN         |      |     99 |    111K|     99 |00:00:00.05 |    1005 |  9762K|  1209K| 8677K (0)|
|   5 |    TABLE ACCESS FULL| T2   |      1 |    111K|  99999 |00:00:00.01 |    1005 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

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

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

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

Note
-----
   - dynamic sampling used for this statement (level=2)


27 rows selected.