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无效的记录,导致成本提升