[20140718]12c-Partial Join Evaluation

[20140718]12c执行计划新特性- Partial Join Evaluation (PJE).txt

--以前经常一些blogl讲什么使用in还是exists好的相关讨论,实际上11g以上的版本查询转换多数情况下会选择好的执行计划,只要建立好
--对应的约束,索引建立好,oracle多会选择好的执行计划.

--12c在这些基础上引入了Partial Join Evaluation (PJE),能够进一步减少逻辑读,还是通过例子来说明问题:

SCOTT@test01p> @ver
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

create table emp1 as select * from emp;
alter table EMP1 modify empno number(10);
insert into EMP1(empno,deptno) select rownum+10000,40 from EMP,(select * from dual connect by level execute dbms_stats.gather_table_stats(user,'emp1',cascade=>true,method_opt=>'for all columns size 1',no_invalidate=>false);

--例子带的emp表没有deptno=40的雇员,增加deptno=40的雇员是更好的说明Partial Join Evaluation (PJE).

SCOTT@test01p> select owner,table_name,blocks from dba_tables where owner=user and table_name='EMP2';
OWNER  TABLE_NAME     BLOCKS
------ ---------- ----------
SCOTT  EMP2               46
--emp2的blocks=46.

SCOTT@test01p> select /*+full(dept) */deptno from dept;
    DEPTNO
----------
        10
        20
        30
        40
--dept表的depno仅仅有10,20,30,40.

SCOTT@test01p> alter session set statistics_level=all;
Session altered.

SCOTT@test01p> select /*+full(dept) */ deptno,dname from dept  where deptno in ( select deptno from emp1);
    DEPTNO DNAME
---------- --------------
        20 RESEARCH
        30 SALES
        10 ACCOUNTING
        40 OPERATIONS

--注:我不加提示执行计划使用MERGE JOIN SEMI,而不是hash join semi.我加了这个提示.

SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  0u9dzjua9uc24, child number 0
-------------------------------------
select /*+full(dept) */ deptno,dname from dept  where deptno in (select deptno from emp1)
Plan hash value: 1309553802
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |    17 (100)|      4 |00:00:00.01 |      13 |       |       |          |
|*  1 |  HASH JOIN SEMI    |      |      1 |      4 |    17   (0)|      4 |00:00:00.01 |      13 |  1599K|  1599K| 1010K (0)|
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |     3   (0)|      4 |00:00:00.01 |       7 |       |       |          |
|   3 |   TABLE ACCESS FULL| EMP1 |      1 |  14000 |    14   (0)|     15 |00:00:00.01 |       6 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("DEPTNO"="DEPTNO")

--注意看执行ID=3行,A-ROWS=15行,buffers=6,也就是没有全表扫描完成!仅仅扫描15条EMP1的记录,就输出了结果,这个是因为前面15条记录已经
--包括了deptno=10,20,30,40的记录,继续扫描emp1表已经无意义.

--使用exists也是一样.
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  7xbwbhv50rcvt, child number 0
-------------------------------------
select /*+ full(dept) */ deptno,dname from dept where exists (select 1 from emp1 where emp1.deptno=dept.deptno)
Plan hash value: 1309553802
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |    17 (100)|      4 |00:00:00.01 |      13 |       |       |          |
|*  1 |  HASH JOIN SEMI    |      |      1 |      4 |    17   (0)|      4 |00:00:00.01 |      13 |  1599K|  1599K| 1007K (0)|
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |     3   (0)|      4 |00:00:00.01 |       7 |       |       |          |
|   3 |   TABLE ACCESS FULL| EMP1 |      1 |  14000 |    14   (0)|     15 |00:00:00.01 |       6 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("EMP1"."DEPTNO"="DEPT"."DEPTNO")

--如果建立emp2表不包含deptno=40的记录,情况如何呢?

create table emp2 as select * from emp;
alter table EMP2 modify empno number(10);
insert into EMP2(empno,deptno) select rownum+10000,10 from EMP,(select * from dual connect by level execute dbms_stats.gather_table_stats(user,'emp2',cascade=>true,method_opt=>'for all columns size 1',no_invalidate=>false);

SCOTT@test01p> select /*+full(dept) */ deptno,dname from dept  where deptno in ( select deptno from emp2);
    DEPTNO DNAME
---------- --------------
        20 RESEARCH
        30 SALES
        10 ACCOUNTING

SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  106cp3bm86r84, child number 0
-------------------------------------
select /*+full(dept) */ deptno,dname from dept  where deptno in (select deptno from emp2)
Plan hash value: 4213155305
--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |    17 (100)|      3 |00:00:00.03 |      54 |     44 |       |       |          |
|*  1 |  HASH JOIN SEMI    |      |      1 |      3 |    17   (0)|      3 |00:00:00.03 |      54 |     44 |  1599K|  1599K| 1007K (0)|
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |     3   (0)|      4 |00:00:00.01 |       7 |      0 |       |       |          |
|   3 |   TABLE ACCESS FULL| EMP2 |      1 |  14000 |    14   (0)|  14000 |00:00:00.03 |      47 |     44 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("DEPTNO"="DEPTNO")

--注意看执行ID=3行,A-ROWS=14000行,buffers=47,也就是因为emp2表不存在deptno=40的记录,直到结束也没有deptno=40的记录.
--我看执行计划的Outline,并没有PARTIAL_JOIN,也许是版本的问题.

@dpc '' ''
/*+
     BEGIN_OUTLINE_DATA
     IGNORE_OPTIM_EMBEDDED_HINTS
     OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
     DB_VERSION('12.1.0.1')
     ALL_ROWS
     OUTLINE_LEAF(@"SEL$5DA710D3")
     UNNEST(@"SEL$2")
     OUTLINE(@"SEL$1")
     OUTLINE(@"SEL$2")
     FULL(@"SEL$5DA710D3" "DEPT"@"SEL$1")
     FULL(@"SEL$5DA710D3" "EMP1"@"SEL$2")
     LEADING(@"SEL$5DA710D3" "DEPT"@"SEL$1" "EMP1"@"SEL$2")
     USE_HASH(@"SEL$5DA710D3" "EMP1"@"SEL$2")
     END_OUTLINE_DATA
*/

--这个特性受隐含参数_optimizer_partial_join_eval的控制.
SYS@test> set linesize 200
SYS@test> @hide PARTIAL_JOIN
old  10:  and lower(a.ksppinm) like lower('%&1%')
new  10:  and lower(a.ksppinm) like lower('%PARTIAL_JOIN%')
NAME                          DESCRIPTION                       DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
----------------------------- --------------------------------- ------------- ------------- ------------
_optimizer_partial_join_eval  partial join evaluation parameter TRUE          TRUE          TRUE

--这个执行计划的特性仅仅有全部的之值在dept表.如果有一些记录没有匹配emp2表,只能全表扫描第2个表才能知道结果.
--这样像emp2表这种情况,以前会如何优化呢?
--通过在emp2上建立deptno索引来解决,而这个索引在正常的业务中很少会使用,重复值很多,许多执行计划会不使用,这样
--为了这样一条语句,代价有点大.看看具体的情况:

SCOTT@test01p> create index i_emp_deptno on emp2(deptno);
Index created.

SCOTT@test01p> select /*+f1ull(dept) */ deptno,dname from dept  where deptno in ( select deptno from emp2);
    DEPTNO DNAME
---------- --------------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES

SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  32ckk3kt5z7hn, child number 0
-------------------------------------
select /*+f1ull(dept) */ deptno,dname from dept  where deptno in (select deptno from emp2)
Plan hash value: 3987593338
----------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name         | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |      1 |        |     7 (100)|      3 |00:00:00.01 |      14 |
|   1 |  NESTED LOOPS SEMI |              |      1 |      3 |     7   (0)|      3 |00:00:00.01 |      14 |
|   2 |   TABLE ACCESS FULL| DEPT         |      1 |      4 |     3   (0)|      4 |00:00:00.01 |       8 |
|*  3 |   INDEX RANGE SCAN | I_EMP_DEPTNO |      4 |  10500 |     1   (0)|      3 |00:00:00.01 |       6 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("DEPTNO"="DEPTNO")

SCOTT@test01p> select /*+ full(dept) */ deptno,dname from dept where exists (select 1 from emp2 where emp2.deptno=dept.deptno);
    DEPTNO DNAME
---------- --------------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES

SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  f0gzx28d1hz3z, child number 0
-------------------------------------
select /*+ full(dept) */ deptno,dname from dept where exists (select 1 from emp2 where emp2.deptno=dept.deptno)
Plan hash value: 3987593338
----------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name         | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |      1 |        |     7 (100)|      3 |00:00:00.01 |      14 |
|   1 |  NESTED LOOPS SEMI |              |      1 |      3 |     7   (0)|      3 |00:00:00.01 |      14 |
|   2 |   TABLE ACCESS FULL| DEPT         |      1 |      4 |     3   (0)|      4 |00:00:00.01 |       8 |
|*  3 |   INDEX RANGE SCAN | I_EMP_DEPTNO |      4 |  10500 |     1   (0)|      3 |00:00:00.01 |       6 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("EMP2"."DEPTNO"="DEPT"."DEPTNO")

--我们可以发现使用in 和 exists ,执行计划都是一样的.
--感到奇怪的是我这个版本修改参数并没有改变执行计划.不知道为什么?
SYS@test> alter session set "_optimizer_partial_join_eval"=false;
Session altered.

--继续看看11g的情况.

SCOTT@test> @ver

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

create table emp1 as select * from emp;
alter table EMP1 modify empno number(10);
insert into EMP1(empno,deptno) select rownum+10000,40 from EMP,(select * from dual connect by level execute dbms_stats.gather_table_stats(user,'emp1',cascade=>true,method_opt=>'for all columns size 1',no_invalidate=>false);

SCOTT@test> alter session set statistics_level=all;
Session altered.
SCOTT@test> select /*+full(dept) */ deptno,dname from dept  where deptno in ( select deptno from emp1);
    DEPTNO DNAME
---------- --------------
        20 RESEARCH
        30 SALES
        10 ACCOUNTING
        40 OPERATIONS

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  0u9dzjua9uc24, child number 0
-------------------------------------
select /*+full(dept) */ deptno,dname from dept  where deptno in (
select deptno from emp1)

Plan hash value: 1309553802

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |    19 (100)|      4 |00:00:00.06 |      54 |       |       |          |
|*  1 |  HASH JOIN SEMI    |      |      1 |      3 |    19   (6)|      4 |00:00:00.06 |      54 |  1180K|  1180K| 1066K (0)|
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      6 |     4   (0)|      6 |00:00:00.01 |       8 |       |       |          |
|   3 |   TABLE ACCESS FULL| EMP1 |      1 |  14000 |    14   (0)|  14000 |00:00:00.01 |      46 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("DEPTNO"="DEPTNO")

--很明显,11g下id=3,emp1执行全部扫描.
--使用exists 也是这种情况.

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  fzz6dafumdjgz, child number 0
-------------------------------------
select /*+ full(dept) */ deptno,dname from dept where exists (select 1 from emp1 where emp1.deptno=dept.deptno)
Plan hash value: 1309553802
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |    19 (100)|      4 |00:00:00.06 |      54 |       |       |          |
|*  1 |  HASH JOIN SEMI    |      |      1 |      3 |    19   (6)|      4 |00:00:00.06 |      54 |  1180K|  1180K|  768K (0)|
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      6 |     4   (0)|      6 |00:00:00.01 |       8 |       |       |          |
|   3 |   TABLE ACCESS FULL| EMP1 |      1 |  14000 |    14   (0)|  14000 |00:00:00.01 |      46 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("EMP1"."DEPTNO"="DEPT"."DEPTNO")

SCOTT@test> @hide PARTIAL_JOIN
no rows selected

--11g下一个简单的改进版本是:
SCOTT@test> create index i_emp_deptno on emp1(deptno);
Index created.

SCOTT@test> select /*+ full(dept) */ deptno,dname from dept where exists (select 1 from emp1 where emp1.deptno=dept.deptno and rownum    DEPTNO DNAME
---------- --------------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS

SCOTT@test> @dpc '' advanced
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  c3ukmu0mxfs4n, child number 0
-------------------------------------
select /*+ full(dept) */ deptno,dname from dept where exists (select 1
from emp1 where emp1.deptno=dept.deptno and rownum

Plan hash value: 4010202402

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name         | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |      1 |        |       |     7 (100)|          |      4 |00:00:00.01 |      21 |
|*  1 |  FILTER            |              |      1 |        |       |            |          |      4 |00:00:00.01 |      21 |
|   2 |   TABLE ACCESS FULL| DEPT         |      1 |      6 |    66 |     4   (0)| 00:00:01 |      6 |00:00:00.01 |       9 |
|*  3 |   COUNT STOPKEY    |              |      6 |        |       |            |          |      4 |00:00:00.01 |      12 |
|*  4 |    INDEX RANGE SCAN| I_EMP_DEPTNO |      6 |      1 |     3 |     1   (0)| 00:00:01 |      4 |00:00:00.01 |      12 |
-----------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / DEPT@SEL$1
   3 - SEL$2
   4 - SEL$2 / EMP1@SEL$2
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "DEPT"@"SEL$1")
      INDEX(@"SEL$2" "EMP1"@"SEL$2" ("EMP1"."DEPTNO"))
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( IS NOT NULL)
   3 - filter(ROWNUM   4 - access("EMP1"."DEPTNO"=:B1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "DEPTNO"[NUMBER,22], "DNAME"[VARCHAR2,14]
   2 - "DEPTNO"[NUMBER,22], "DNAME"[VARCHAR2,14]

--使用rownum

--总结:
--12c下引入了Partial Join Evaluation (PJE)减少了逻辑读,但是要第1个表的相关信息在第2个表里面,而且与表2的数据分布有关,如果
--表2没有包含表1的全部信息一样要全表扫描.当然也要注意不要看到全表扫描,就武断的认为执行计划不好.

时间： 2024-07-30 13:30:28

[20140718]12c-Partial Join Evaluation的相关文章

[20130812]12c Partial Indexes For Partitioned Tables Part I.txt

[20130812]12c Partial Indexes For Partitioned Tables Part I.txt 参考链接:http://richardfoote.wordpress.com/2013/07/08/12c-partial-indexes-for-partitioned-tables-part-i-ignoreland/ 更多的是重复作者的测试,加深理解: 1.测试环境: SQL> @ver BANNER                                

[20130812]12c Partial Indexes For Partitioned Tables Part II.txt

[20130812]12c Partial Indexes For Partitioned Tables Part II.txt 参考链接:http://richardfoote.wordpress.com/2013/07/12/12c-partial-indexes-for-partitioned-tables-part-ii-vanishing-act/ 更多的是重复作者的测试,加深理解: 1.测试环境: SQL> @ver BANNER                           

ORACLE 12C Partial Global/Local Indexes for Partitioned Tables

以前我有个想法,我只想对其中的某个或者某几个分区上创建索引,其他分区不想创建,在12C之前的版本,无论是Local还是Global index,都不能实现该需求,但是从ORACLE 12C开始引进了Partial Global/Local Indexes for Partitioned Tables,解决了该问题,可以在指定的分区上创建本地索引或者全局索引,主要语法是在表或者分区,子分区级别设置[INDEXING { ON | OFF }]创建测试表  代码如下 复制代码 CDB_PDB@CHF

[20140823]12c join convert连接转换.txt

[20140823]12c join convert连接转换.txt --前面提高12c执行计划的Partial Join Evaluation.现在看看12c join convert. --链接: 1.建立测试环境: SCOTT@test01p> @ver BANNER                                                                               CON_ID ---------------------------

Oracle 12c 新SQL提示（hint）

Oracle 12c 新SQL提示(hint) Oracle 12c中引入了许多新特性,其中部分是和SQL相关的特性．而一些新的SQL提示也随着这些新特性被引入． enable_parallel_dml Syntax: enable_parallel_dml Description: Enable parallel dml. Same effect as "alter session enable parallel dml" SQL?? HelloDBA.com> create 

浅析Js(Jquery)中,字符串与JSON格式互相转换的示例

这几天,遇到了json格式在JS和Jquey的环境中,需要相互转换,在网上查了一下,大多为缺胳膊少腿,也许咱是菜鸟吧,终于测试成功后,还是给初学者们一个实例吧   首先,准备新建一个js文件.以下是JSON2.js的内容,把内容拷到js文件中,以便调用: 复制代码 代码如下: /*     http://www.JSON.org/json2.js     Public Domain.     NO WARRANTY EXPRESSED OR IMPLIED. USE AT YOUR OWN RI

ASP调用WebService转化成JSON数据，附json.min.asp

首先定义SOAP数据,然后创建HTTP对象,然后使用POST提交,获取状态码为200,就说明调用成功,再进行下一步操作-- 看一下具体实现的代码吧 <!--#Include virtual="/Include/json.min.asp"--> <% Dim strxml Dim str '定义soap消息 strxml = "<?xml version='1.0' encoding='utf-8'?>" strxml = strxml

JavaScript 保存数组到Cookie的代码_javascript技巧

JavaScript中数组是无法直接保存为Cookie的(PHP可以),那要将数组转存为字符串,再保存在Cookie中,简单的一维数组我们直接用toString()或者join就可以了: JavaScript中toString函数方法是返回对象的字符串表示. 使用方法:objectname.toString([radix]) 其中objectname是必选项.要得到字符串表示的对象. radix是可选项.指定将数字值转换为字符串时的进制. join是其中一个方法. 格式:objArray.joi

ASP调用WebService转化成JSON数据，附json.min.asp_应用技巧

首先定义SOAP数据,然后创建HTTP对象,然后使用POST提交,获取状态码为200,就说明调用成功,再进行下一步操作-- 看一下具体实现的代码吧 <!--#Include virtual="/Include/json.min.asp"--> <% Dim strxml Dim str '定义soap消息 strxml = "<?xml version='1.0' encoding='utf-8'?>" strxml = strxml