[20201116]11g連線謂詞推入push_pred問題.txt
[20201116]11g連線謂詞推入push_pred問題.txt
--//看崔華<基於oracle的sql最佳化>,裡面提到11g連線謂詞推入問題的問題自己重複測試看看.
1.環境:
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.測試環境建立:
--//注書中的例子好像不是scott.emp表,不過應該不影響測試.
create table emp1 as select * from emp ;
create index i_emp1_empno on emp1(empno);
create or replace view emp_view as select emp1.empno as empno1 from emp1;
3.測試:
SCOTT@book> alter session set statistics_level = all;
Session altered.
SCOTT@book> select emp.empno from emp,emp_view where emp.empno=emp_view.empno1 and emp.ename='FORD';
EMPNO
----------
7902
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID b7up3a8rsctnz, child number 0
-------------------------------------
select emp.empno from emp,emp_view where emp.empno=emp_view.empno1 and emp.ename='FORD'
Plan hash value: 1061644130
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 1 |00:00:00.01 | 9 |
| 1 | NESTED LOOPS | | 1 | 1 | 14 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 9 |
|* 2 | TABLE ACCESS FULL| EMP | 1 | 1 | 10 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 7 |
|* 3 | INDEX RANGE SCAN | I_EMP1_EMPNO | 1 | 1 | 4 | 0 (0)| | 1 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1
2 - SEL$F5BB74E1 / EMP@SEL$1
3 - SEL$F5BB74E1 / EMP1@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMP"."ENAME"='FORD')
3 - access("EMP"."EMPNO"="EMP1"."EMPNO")
29 rows selected.
--//這裡僅僅簡單檢視合併.可以透過提示merge確定,修改如下:
SCOTT@book> select /*+ merge(emp_view) */ emp.empno from emp,emp_view where emp.empno=emp_view.empno1 and emp.ename='FORD';
EMPNO
----------
7902
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 35ybv5cn4vx32, child number 0
-------------------------------------
select /*+ merge(emp_view) */ emp.empno from emp,emp_view where
emp.empno=emp_view.empno1 and emp.ename='FORD'
Plan hash value: 1061644130
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 1 |00:00:00.01 | 9 |
| 1 | NESTED LOOPS | | 1 | 1 | 14 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 9 |
|* 2 | TABLE ACCESS FULL| EMP | 1 | 1 | 10 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 7 |
|* 3 | INDEX RANGE SCAN | I_EMP1_EMPNO | 1 | 1 | 4 | 0 (0)| | 1 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1
2 - SEL$F5BB74E1 / EMP@SEL$1
3 - SEL$F5BB74E1 / EMP1@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMP"."ENAME"='FORD')
3 - access("EMP"."EMPNO"="EMP1"."EMPNO")
4.使用提示no_merge看看:
SCOTT@book> select /*+ no_merge(emp_view)*/ emp.empno from emp,emp_view where emp.empno=emp_view.empno1 and emp.ename='FORD';
EMPNO
----------
7902
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 7kghy2nb3bnr9, child number 0
-------------------------------------
select /*+ no_merge(emp_view)*/ emp.empno from emp,emp_view where
emp.empno=emp_view.empno1 and emp.ename='FORD'
Plan hash value: 1565533375
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 1 |00:00:00.01 | 6 | 1 | | | |
| 1 | MERGE JOIN | | 1 | 1 | 23 | 6 (17)| 00:00:01 | 1 |00:00:00.01 | 6 | 1 | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 1 | 10 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 | 1 | | | |
| 3 | INDEX FULL SCAN | PK_EMP | 1 | 14 | | 1 (0)| 00:00:01 | 14 |00:00:00.01 | 2 | 1 | | | |
|* 4 | SORT JOIN | | 1 | 14 | 182 | 4 (25)| 00:00:01 | 1 |00:00:00.01 | 2 | 0 | 2048 | 2048 | 2048 (0)|
| 5 | VIEW | EMP_VIEW | 1 | 14 | 182 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 2 | 0 | | | |
| 6 | TABLE ACCESS FULL | EMP1 | 1 | 14 | 56 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 2 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / EMP@SEL$1
3 - SEL$1 / EMP@SEL$1
5 - SEL$2 / EMP_VIEW@SEL$1
6 - SEL$2 / EMP1@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMP"."ENAME"='FORD')
4 - access("EMP"."EMPNO"="EMP_VIEW"."EMPNO1")
filter("EMP"."EMPNO"="EMP_VIEW"."EMPNO1")
35 rows selected.
--//11g無法實現連線謂詞推入push_pred
--//透過如下提示控制如何呢？
SCOTT@book> select /*+ no_merge(emp_view) use_nl(emp_view) push_pred(emp_view) */ emp.empno from emp,emp_view where emp.empno=emp_view.empno1 and emp.ename='FORD';
EMPNO
----------
7902
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID dd885z1b7u4nz, child number 0
-------------------------------------
select /*+ no_merge(emp_view) use_nl(emp_view) push_pred(emp_view) */
emp.empno from emp,emp_view where emp.empno=emp_view.empno1 and
emp.ename='FORD'
Plan hash value: 3774177413
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 1 |00:00:00.01 | 10 |
| 1 | NESTED LOOPS | | 1 | 1 | 23 | 6 (0)| 00:00:01 | 1 |00:00:00.01 | 10 |
|* 2 | TABLE ACCESS FULL | EMP | 1 | 1 | 10 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 7 |
|* 3 | VIEW | EMP_VIEW | 1 | 1 | 13 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
| 4 | TABLE ACCESS FULL| EMP1 | 1 | 14 | 56 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 3 |
--------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / EMP@SEL$1
3 - SEL$2 / EMP_VIEW@SEL$1
4 - SEL$2 / EMP1@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMP"."ENAME"='FORD')
3 - filter("EMP"."EMPNO"="EMP_VIEW"."EMPNO1")
32 rows selected.
--//確實像作者介紹那樣，11g這種情況無法實現謂詞推入。也許我哪裡做錯了........
4.繼續測試:
--//12c呢？
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
--//建表以及測試相關檢視略。
SCOTT@test01p> select /*+ no_merge(emp_view) use_nl(emp_view) push_pred(emp_view) */ emp.empno from emp,emp_view where emp.empno=emp_view.empno1 and emp.ename='FORD';
EMPNO
----------
7902
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID dd885z1b7u4nz, child number 0
-------------------------------------
select /*+ no_merge(emp_view) use_nl(emp_view) push_pred(emp_view) */
emp.empno from emp,emp_view where emp.empno=emp_view.empno1 and
emp.ename='FORD'
Plan hash value: 3352622377
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 1 |00:00:00.01 | 10 |
| 1 | NESTED LOOPS | | 1 | 1 | 12 | 4 (0)| 00:00:01 | 1 |00:00:00.01 | 10 |
|* 2 | TABLE ACCESS FULL | EMP | 1 | 1 | 10 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 8 |
| 3 | VIEW PUSHED PREDICATE | EMP_VIEW | 1 | 1 | 2 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|* 4 | INDEX RANGE SCAN | I_EMP1_EMPNO | 1 | 1 | 4 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |
----------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / EMP@SEL$1
3 - SEL$639F1A6F / EMP_VIEW@SEL$1
4 - SEL$639F1A6F / EMP1@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMP"."ENAME"='FORD')
4 - access("EMP1"."EMPNO"="EMP"."EMPNO")
--//很明顯不像作者書中介紹那樣,12c是可以最佳化的.
--//說明一點許多東西會變，oracle也在不斷完善自己，當然bug也越來越多。不能抱著一成不變的觀點看問題，要與時俱進^_^。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2734347/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle查詢轉換（四）連線謂詞推入Oracle
- CBO的查詢轉換（謂詞推入與子查詢展開（Subquery Unnesting））
- CodeQL學習筆記(1)-QL語法（邏輯連線詞、量詞、聚合詞、謂詞和類）筆記
- 【大資料】SparkSql連線查詢中的謂詞下推處理(一)大資料SparkSQL
- 【大資料】SparkSql 連線查詢中的謂詞下推處理 (二)大資料SparkSQL
- C++謂詞C++
- Oracle 11g RAC通過SCAN IP連線資料庫 - JDBC連線串問題Oracle資料庫JDBC
- Java 8謂詞鏈Java
- [20201116]測試CURSOR_SPACE_FOR_TIME=false(11g).txtFalse
- [20171120]關於find 軟連線問題.txt
- MySQL SSL連線問題MySql
- HTTP 長連線問題HTTP
- jive 連線 mysql 問題MySql
- JDBC連線ORACLE問題JDBCOracle
- 遠端連線問題
- [20150610]sql的謂詞中使用函式.txtSQL函式
- 解決用PLSQL Developer連線VMWare中的Oracle 11g問題SQLDeveloperOracle
- java8-謂詞（predicate）Java
- 小解謂詞 access 與 filterFilter
- [20170824]11G備庫啟用DRCP連線.txt
- [20181128]toad連線資料庫的問題.txt資料庫
- 【問題記錄】—SignalR連線斷線重連SignalR
- Github ssh的連線問題Github
- tomcat連線池問題Tomcat
- jdbc連線mysql問題-helpJDBCMySql
- WTC連線阻塞的問題
- 檢測ftp連線問題FTP
- oracle 謂詞表示式對基數的影響(及11G改進)Oracle
- 原子謂詞公式和合式公式公式
- [20181006]12c使用toad連線問題.txt
- Mac出現WiFi連線問題怎麼辦 Mac WiFi連線問題解決方法MacWiFi
- [20170825]11G備庫啟用DRCP連線3.txt
- 【Github】 Github訪問不是私密連線問題Github
- 問個jrun連線池的問題
- 連線oracle中出現的問題Oracle
- navicate oracle 連線報錯問題Oracle
- Tomcat最大連線數問題Tomcat
- ssh連線反應慢問題