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,讓最佳化器在產生執行計劃時評估子非合併子查詢的成本,如果此子查詢的成本太高就不要把此子查詢推入整個查詢語句中,\
即讓子查詢獨立產生結果集後再與整個語句進行關聯

oracle hint_no_push_pred_no_push_subq

相關文章