oracle 排序-合并联结

用scott/tiger登录。

SQL> set linesize 120
SQL> set autotrace traceonly
SQL> alter system flush buffer_cache;

系统已更改。

SQL> alter system flush shared_pool;

系统已更改。

SQL> select /*+ ordered */ empno, ename, dname, loc from dept, emp where emp.deptno = dept.deptno;

已选择14行。


执行计划
----------------------------------------------------------
Plan hash value: 844388907

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   462 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    14 |   462 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   182 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   182 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
       filter("EMP"."DEPTNO"="DEPT"."DEPTNO")


统计信息
----------------------------------------------------------
        672  recursive calls
          0  db block gets
        129  consistent gets
         25  physical reads
          0  redo size
        850  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         11  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL>

或者用use_merge提示：

SQL> alter system flush buffer_cache;

系统已更改。

SQL> alter system flush shared_pool;

系统已更改。

SQL> select /*+ use_merge(dept, emp) */ empno, ename, dname, loc from dept, emp where emp.deptno = dept.deptno;

已选择14行。


执行计划
----------------------------------------------------------
Plan hash value: 844388907

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   462 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    14 |   462 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   182 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   182 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
       filter("EMP"."DEPTNO"="DEPT"."DEPTNO")


统计信息
----------------------------------------------------------
        652  recursive calls
          0  db block gets
        127  consistent gets
         25  physical reads
          0  redo size
        850  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         11  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL>

算法：

排序前

排序后

合并后