oracle hint_no_push_pred_no_push_subq

no_push_pred
  1,不会把连接谓词推入视图或子查询内部
  2,oracle优化器默认会把连接谓词推入子查询内部
SQL> explain plan for select * from emp,(select mgr from emp) v where emp.mgr=v.
mgr and emp.empno=20;

已解释。

--通过如下的执行计划的4 - filter("MGR" IS NOT NULL AND "EMP"."MGR"="MGR")
  4 |   TABLE ACCESS FULL          | EMP
  明确可以看到优化器把连接谓词推入了子查询内部,本来子查询此时仅查出mgr,但未与emp的mgr关联呢
SQL> select * from table(dbms_xplan.display);

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

Plan hash value: 3180893058

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

-------

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

e     |

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

-------

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

|   0 | SELECT STATEMENT             |        |     2 |    86 |     3   (0)| 00:

00:01 |

|   1 |  NESTED LOOPS                |        |     2 |    86 |     3   (0)| 00:

00:01 |

|*  2 |   TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    39 |     1   (0)| 00:

00:01 |

|*  3 |    INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:

00:01 |

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

|*  4 |   TABLE ACCESS FULL          | EMP    |     2 |     8 |     2   (0)| 00:

00:01 |

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

-------

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

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

   2 - filter("EMP"."MGR" IS NOT NULL)
   3 - access("EMP"."EMPNO"=20)
   4 - filter("MGR" IS NOT NULL AND "EMP"."MGR"="MGR")

已选择18行。

--大家注意看,对比上述的执行计划,步骤多了一步,即view
   no_push_pred未把连接谓词推入子查询,即子查询独立作完它的事
   而是把连接谓词的工作放到如下步骤view
   4 - filter("EMP"."MGR"="V"."MGR")
   |*  4 |   VIEW
SQL> explain plan for select /*+ no_merge(v)  no_push_pred(v) */ * from emp,(sel
ect mgr from emp) v where emp.mgr=v.mgr and emp.empno=20;

已解释。

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

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

Plan hash value: 1031192437

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

-------

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

e     |

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

-------

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

|   0 | SELECT STATEMENT             |        |     2 |   104 |     3   (0)| 00:

00:01 |

|   1 |  NESTED LOOPS                |        |     2 |   104 |     3   (0)| 00:

00:01 |

|*  2 |   TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    39 |     1   (0)| 00:

00:01 |

|*  3 |    INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:

00:01 |

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

|*  4 |   VIEW                       |        |     2 |    26 |     2   (0)| 00:

00:01 |

|   5 |    TABLE ACCESS FULL         | EMP    |    12 |    48 |     2   (0)| 00:

00:01 |

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

-------

 

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

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

   2 - filter("EMP"."MGR" IS NOT NULL)
   3 - access("EMP"."EMPNO"=20)
   4 - filter("EMP"."MGR"="V"."MGR")

已选择19行。

SQL>

hint
 no_push_subq
  1,让优化器在产生执行计划时评估子非合并子查询的成本,如果此子查询的成本太高就不要把此子查询推入整个查询语句中,\
    即让子查询独立产生结果集后再与整个语句进行关联

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

转载于:http://blog.itpub.net/9240380/viewspace-751471/