[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也越来越多。不能抱着一成不变的观点看问题，要与时俱进^_^。