[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博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2734347/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
