ITPub博客

首页 > Linux操作系统 > Linux操作系统 > sort merge join

sort merge join

原创 Linux操作系统 作者:lsq_008 时间:2009-02-01 12:04:18 0 删除 编辑
什么情况下适合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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
十余年大型金融及电信系统数据库管理经验,曾服务于中国建设银行、中国移动。对oracle,mysql数据库有深入了解。 擅长python开发,独立开发了开源数据库自动化监控运维平台Power Monitor。

注册时间:2008-02-29

  • 博文量
    325
  • 访问量
    1254874