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;
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")
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;
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")
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- ORACLE基礎之oracle鎖(oracle lock mode)詳解Oracle
- [Oracle]Oracle良性SQL建議OracleSQL
- cx_Oracle 連線 OracleOracle
- 【Oracle】Oracle logminer功能介紹Oracle
- OracleOracle
- Oracle案例12——NBU Oracle恢復Oracle
- Oracle vs PostgreSQL DBA(21)- Oracle VPDOracleSQL
- 「Oracle」Oracle 資料庫安裝Oracle資料庫
- [Oracle] -- 配置Oracle環境變數Oracle變數
- Oracle案例13—— OGG-01163 Oracle GoldenGate Delivery for Oracle, reprpt01.prmOracleGo
- 4.1.6 Oracle Restart 與 Oracle Data Guard 整合OracleREST
- oracle資料庫與oracle例項Oracle資料庫
- 「Oracle」Oracle資料庫基本概念Oracle資料庫
- 「Oracle」Oracle 資料庫基本概念Oracle資料庫
- 「Oracle」Oracle高階查詢介紹Oracle
- Oracle系列Oracle
- oracle user$Oracle
- Oracle TimestampOracle
- 序章-oracleOracle
- ORACLE AUDITOracle
- Spotlight for OracleOracle
- Oracle exceptionOracleException
- Oracle 阻塞Oracle
- oracle plsqlOracleSQL
- oracle 字串Oracle字串
- Oracle JoinsOracle
- oracle RACOracle
- oracle 3Oracle
- Oracle TablesOracle
- oracle opsOracle
- Oracle CursorOracle
- Oracle CoherenceOracle
- oracle程序Oracle
- oracle 文件Oracle
- Oracle:SCNOracle
- Oracle 索引Oracle索引
- Oracle:RBOOracle
- ORACLE AWROracle
- Oracle 19c Concepts(01):Introduction to Oracle DatabaseOracleDatabase