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

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

-------

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

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

-------

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博客

oracle hint_no_push_pred_no_push_subq

最新文章