sort merge join

什么情况下适合sort merge join：

The optimizer can choose a sort merge join over a hash join for joining large amounts of data if any of the following conditions are true:

The join condition between two tables is not an equi-join.

Because of sorts already required by other operations, the optimizer finds it is cheaper to use a sort merge than a hash join.

 

SQL> select a.last_name,b.order_mode,b.order_total

  2  from employees a,orders b where a.employee_id>b.customer_id;

 

Execution Plan

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

Plan hash value: 4219513868

 

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

| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT             |                 |   562 | 16298 |     5  (20)| 00:00:01 |

|   1 |  MERGE JOIN                  |                 |   562 | 16298 |     5  (20)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| ORDERS          |   105 |  1785 |     2   (0)| 00:00:01 |

|   3 |    INDEX FULL SCAN           | ORD_CUSTOMER_IX |   105 |       |     1   (0)| 00:00:01 |

|*  4 |   SORT JOIN                  |                 |   107 |  1284 |     3  (34)| 00:00:01 |

|   5 |    TABLE ACCESS FULL         | EMPLOYEES       |   107 |  1284 |     2   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   4 - access("A"."EMPLOYEE_ID">"B"."CUSTOMER_ID")

       filter("A"."EMPLOYEE_ID">"B"."CUSTOMER_ID")  

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10972173/viewspace-544929/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-544929/