oracle hint no_merge強制oracle不把子查詢inline view和outer view外層查詢合併為一個單一的查詢,即子查詢執行出結果集再和外層查詢的其它結果集進行關聯操作

下為測試結果

SQL> explain plan for select /*+ no_merge(d) */ e.empno,e.ename,d.dname from em
p e,(select deptno,dname from dept where deptno=20) d where e.deptno=d.deptno;

已解釋。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------
Plan hash value: 1228445785

--------------------------------------------------------------------------------
---------
| Id | Operation                     | Name    | Rows | Bytes | Cost (%CPU)| T
ime     |
--------------------------------------------------------------------------------
---------
|   0 | SELECT STATEMENT              |         |     4 |   140 |     4 (25)| 0
0:00:01 |
|* 1 | HASH JOIN                    |         |     4 |   140 |     4 (25)| 0
0:00:01 |
|   2 |   VIEW                        |         |     1 |    22 |     1   (0)| 0
0:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     1   (0)| 0
0:00:01 |
|* 4 |     INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 0
0:00:01 |
|   5 |   TABLE ACCESS FULL           | EMP     |    12 |   156 |     2   (0)| 0
0:00:01 |

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

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

   1 - access("E"."DEPTNO"="D"."DEPTNO")
   4 - access("DEPTNO"=20)

已選擇18行。

SQL> explain plan for select e.empno,e.ename,d.dname from emp e,(select deptno,d
name from dept where deptno=20) d where e.deptno=d.deptno;

已解釋。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------
Plan hash value: 568005898

--------------------------------------------------------------------------------
--------
| Id | Operation                    | Name    | Rows | Bytes | Cost (%CPU)| Ti
me     |
--------------------------------------------------------------------------------
--------
|   0 | SELECT STATEMENT             |         |     3 |    78 |     3   (0)| 00
:00:01 |
|   1 | NESTED LOOPS                |         |     3 |    78 |     3   (0)| 00
:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     1   (0)| 00
:00:01 |
|* 3 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00
:00:01 |
|* 4 |   TABLE ACCESS FULL          | EMP     |     3 |    39 |     2   (0)| 00
:00:01 |
--------------------------------------------------------------------------------
--------

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

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

   3 - access("DEPTNO"=20)
   4 - filter("E"."DEPTNO"=20)

已選擇17行。

小記:no_merge適用於子查詢結果集很小,試想如果讓oracle merge,可能子成本就很高多了.因為沒有filter無效的記錄,導致成本提升

oracle hint no_merge

相關文章