oracle表连接-sortmergejoins排序合并连接(三)
----------------
select /*+ leading(t3) use_merge(t4) */ * from t3, t4 where t3.id =
t4.t3_id and t3.n = 1100 and t4.n = 10034
Plan hash value: 3831111046
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 119 | | | |
| 1 | MERGE JOIN | | 1 | 1 | 1 |00:00:00.01 | 119 | | | |
| 2 | SORT JOIN | | 1 | 1 | 1 |00:00:00.01 | 15 | 2048 | 2048 | 2048 (0)|
|* 3 | TABLE ACCESS FULL| T3 | 1 | 1 | 1 |00:00:00.01 | 15 | | | |
|* 4 | SORT JOIN | | 1 | 1 | 1 |00:00:00.01 | 104 | 2048 | 2048 | 2048 (0)|
|* 5 | TABLE ACCESS FULL| T4 | 1 | 1 | 1 |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")
5 - filter("T4"."N"=10034)
SQL> create index t4_n on t4(n);
Index created.
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
-------------------------------------
select /*+ leading(t3) use_merge(t4) */ * from t3, t4 where t3.id =
t4.t3_id and t3.n = 1100 and t4.n = 10034
Plan hash value: 1501658231
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 18 | 1 | | | |
| 1 | MERGE JOIN | | 1 | 1 | 1 |00:00:00.01 | 18 | 1 | | | |
| 2 | SORT JOIN | | 1 | 1 | 1 |00:00:00.01 | 15 | 0 | 2048 | 2048 | 2048 (0)|
|* 3 | TABLE ACCESS FULL | T3 | 1 | 1 | 1 |00:00:00.01 | 15 | 0 | | | |
|* 4 | SORT JOIN | | 1 | 1 | 1 |00:00:00.01 | 3 | 1 | 2048 | 2048 | 2048 (0)|
| 5 | TABLE ACCESS BY INDEX ROWID| T4 | 1 | 1 | 1 |00:00:00.01 | 3 | 1 | | | |
|* 6 | INDEX RANGE SCAN | T4_N | 1 | 1 | 1 |00:00:00.01 | 2 | 1 | | | |
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T3"."N"=1100)
4 - access("T3"."ID"="T4"."T3_ID")
filter("T3"."ID"="T4"."T3_ID")
6 - access("T4"."N"=10034)
SQL>
create index t3_n on t3(n);
Index created.
SQL> select /*+ leading(t3) use_merge(t4) */ *
2