[20160213]闭包传递5.txt

[20160213]闭包传递5.txt

--所谓闭包传递是指sql语句的谓词条件A=B and B=C 可以推出 A=C. oracle 的 优化器能够利用这个特性优化sql语句。
--前一阵子看电子电子书<Apress.Expert.Oracle.SQL.Optimization.Deployment.and.Statistics.1430259779.pdf>测试链接:

http://blog.itpub.net/267265/viewspace-1981803/
http://blog.itpub.net/267265/viewspace-1987668/
http://blog.itpub.net/267265/viewspace-1988061/

1.环境:
SCOTT@test01p> @ ver1

PORT_STRING          VERSION     BANNER                                                                       CON_ID
-------------------- ----------- ---------------------------------------------------------------------------- ------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0  Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production      0

create table t1 as select rownum   id,rownum||'t1' name from dual connect by level<=5;
create table t2 as select rownum+1 id,rownum||'t2' name from dual connect by level<=5;
create table t3 as select rownum+2 id,rownum||'t3' name from dual connect by level<=5;
create table t4 as select rownum+3 id,rownum||'t3' name from dual connect by level<=5;

-- 分析表.Method_Opt => 'FOR ALL COLUMNS SIZE 1 '
-- 正常的连接还包含其它字段的查询,测试看看. t1.id=t2.id  and t2.id=t3.id and t3.id=t4.id;

2.补充索引:

create unique index i_t1_id on t1(id);
create unique index i_t2_id on t2(id);
create unique index i_t3_id on t3(id);
create unique index i_t4_id on t4(id);
create index i_t1_name on t1(name);

3.继续测试:

select * from t1,t2,t3 where t1.id=t2.id  and t2.id=t3.id  and t1.name='4t1';

Plan hash value: 848093300
-----------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name      | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |           |        |       |     4 (100)|          |
|   1 |  NESTED LOOPS                          |           |        |       |            |          |
|   2 |   NESTED LOOPS                         |           |      1 |    21 |     4   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                        |           |      1 |    14 |     3   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| T1        |      1 |     7 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN                  | I_T1_NAME |      1 |       |     1   (0)| 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID        | T2        |      1 |     7 |     1   (0)| 00:00:01 |
|*  7 |      INDEX UNIQUE SCAN                 | I_T2_ID   |      1 |       |     0   (0)|          |
|*  8 |    INDEX UNIQUE SCAN                   | I_T3_ID   |      1 |       |     0   (0)|          |
|   9 |   TABLE ACCESS BY INDEX ROWID          | T3        |      1 |     7 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   4 - SEL$1 / T1@SEL$1
   5 - SEL$1 / T1@SEL$1
   6 - SEL$1 / T2@SEL$1
   7 - SEL$1 / T2@SEL$1
   8 - SEL$1 / T3@SEL$1
   9 - SEL$1 / T3@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("T1"."NAME"='4t1')
   7 - access("T1"."ID"="T2"."ID")
   8 - access("T3"."ID"="T2"."ID")
Note
-----
   - this is an adaptive plan
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

--可以看到连接顺序 t1,t2,t3.
--如果人为实现t1,t3,t2看看.

select /*+leading(t1 t3 t2) */ * from t1,t2,t3 where t1.id=t2.id  and t2.id=t3.id  and t1.name='4t1';

Plan hash value: 2643695722
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |           |        |       |     8 (100)|          |       |       |          |
|*  1 |  HASH JOIN                    |           |      1 |    21 |     8   (0)| 00:00:01 |  1451K|  1451K|  932K (0)|
|   2 |   MERGE JOIN CARTESIAN        |           |      5 |    70 |     5   (0)| 00:00:01 |       |       |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1        |      1 |     7 |     2   (0)| 00:00:01 |       |       |          |
|*  4 |     INDEX RANGE SCAN          | I_T1_NAME |      1 |       |     1   (0)| 00:00:01 |       |       |          |
|   5 |    BUFFER SORT                |           |      5 |    35 |     3   (0)| 00:00:01 |  2048 |  2048 | 2048  (0)|
|   6 |     TABLE ACCESS FULL         | T3        |      5 |    35 |     3   (0)| 00:00:01 |       |       |          |
|   7 |   TABLE ACCESS FULL           | T2        |      5 |    35 |     3   (0)| 00:00:01 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1 / T1@SEL$1
   4 - SEL$1 / T1@SEL$1
   6 - SEL$1 / T3@SEL$1
   7 - SEL$1 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="T2"."ID" AND "T2"."ID"="T3"."ID")
   4 - access("T1"."NAME"='4t1')
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

--可以看出没有显示的写出条件 t1.id = t3.id ,出现的是CARTESIAN,而且t3,t2没有选择id索引.继续人为指定看看:

select /*+leading(t1 t3 t2) index(t3 I_T3_ID) index(t2 I_T2_ID) */ * from t1,t2,t3 where t1.id=t2.id  and t2.id=t3.id  and t1.name='4t1';

Plan hash value: 1569866173

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name      | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |           |        |       |     6 (100)|          |       |       |          |
|*  1 |  HASH JOIN                             |           |      1 |    21 |     6   (0)| 00:00:01 |  1451K|  1451K|  932K (0)|
|   2 |   MERGE JOIN CARTESIAN                 |           |      5 |    70 |     4   (0)| 00:00:01 |       |       |          |
|   3 |    TABLE ACCESS BY INDEX ROWID         | T1        |      1 |     7 |     2   (0)| 00:00:01 |       |       |          |
|*  4 |     INDEX RANGE SCAN                   | I_T1_NAME |      1 |       |     1   (0)| 00:00:01 |       |       |          |
|   5 |    BUFFER SORT                         |           |      5 |    35 |     2   (0)| 00:00:01 |  2048 |  2048 | 2048  (0)|
|   6 |     TABLE ACCESS BY INDEX ROWID BATCHED| T3        |      5 |    35 |     2   (0)| 00:00:01 |       |       |          |
|   7 |      INDEX FULL SCAN                   | I_T3_ID   |      5 |       |     1   (0)| 00:00:01 |       |       |          |
|   8 |   TABLE ACCESS BY INDEX ROWID BATCHED  | T2        |      5 |    35 |     2   (0)| 00:00:01 |       |       |          |
|   9 |    INDEX FULL SCAN                     | I_T2_ID   |      5 |       |     1   (0)| 00:00:01 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1 / T1@SEL$1
   4 - SEL$1 / T1@SEL$1
   6 - SEL$1 / T3@SEL$1
   7 - SEL$1 / T3@SEL$1
   8 - SEL$1 / T2@SEL$1
   9 - SEL$1 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="T2"."ID" AND "T2"."ID"="T3"."ID")
   4 - access("T1"."NAME"='4t1')
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
--依旧出现MERGE JOIN CARTESIAN.

4.看来这个问题以前自己没有注意:
--补上t1.id=t3.id条件.
select /*+leading(t1 t3 t2) index(t3 I_T3_ID) index(t2 I_T2_ID) */ * from t1,t2,t3 where t1.id=t2.id  and t2.id=t3.id  and t1.name='4t1' and t1.id=t3.id;
Plan hash value: 1675215245
-----------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name      | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |           |        |       |     4 (100)|          |
|   1 |  NESTED LOOPS                          |           |        |       |            |          |
|   2 |   NESTED LOOPS                         |           |      1 |    21 |     4   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                        |           |      1 |    14 |     3   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| T1        |      1 |     7 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN                  | I_T1_NAME |      1 |       |     1   (0)| 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID        | T3        |      1 |     7 |     1   (0)| 00:00:01 |
|*  7 |      INDEX UNIQUE SCAN                 | I_T3_ID   |      1 |       |     0   (0)|          |
|*  8 |    INDEX UNIQUE SCAN                   | I_T2_ID   |      1 |       |     0   (0)|          |
|   9 |   TABLE ACCESS BY INDEX ROWID          | T2        |      1 |     7 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   4 - SEL$1 / T1@SEL$1
   5 - SEL$1 / T1@SEL$1
   6 - SEL$1 / T3@SEL$1
   7 - SEL$1 / T3@SEL$1
   8 - SEL$1 / T2@SEL$1
   9 - SEL$1 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("T1"."NAME"='4t1')
   7 - access("T1"."ID"="T3"."ID")
   8 - access("T2"."ID"="T3"."ID")
       filter("T1"."ID"="T2"."ID")
Note
-----
   - this is an adaptive plan
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

--这样就很好的使用索引.没有出现笛卡尔集.

时间： 2024-07-30 10:52:08

[20160213]闭包传递5.txt的相关文章

[20160212]闭包传递4.txt

[20160212]闭包传递4.txt --所谓闭包传递是指sql语句的谓词条件A=B and B=C 可以推出 A=C. oracle 的 优化器能够利用这个特性优化sql语句. --前一阵子看电子电子书<Apress.Expert.Oracle.SQL.Optimization.Deployment.and.Statistics.1430259779.pdf>测试链接: http://blog.itpub.net/267265/viewspace-1981803/ http://blog.

[20160211]闭包传递3.txt

[20160211]闭包传递3.txt --所谓闭包传递是指sql语句的谓词条件A=B and B=C 可以推出 A=C. oracle 的 优化器能够利用这个特性优化sql语句. --前一阵子看电子电子书<Apress.Expert.Oracle.SQL.Optimization.Deployment.and.Statistics.1430259779.pdf>测试链接: http://blog.itpub.net/267265/viewspace-1981803/ http://blog.

[20160210]闭包传递2.txt

[20160210]闭包传递2.txt --所谓闭包传递是指sql语句的谓词条件A=B and B=C 可以推出 A=C. oracle 的 优化器能够利用这个特性优化sql语句. --前一阵子看电子电子书<Apress.Expert.Oracle.SQL.Optimization.Deployment.and.Statistics.1430259779.pdf>测试链接: http://blog.itpub.net/267265/viewspace-1981803/ --当时并没有考虑许多细

[20160125]闭包传递问题.txt

[20160125]闭包传递问题.txt --所谓闭包传递是指sql语句的谓词条件A=B and B=C 可以推出 A=C. oracle 的 优化器能够利用这个特性优化sql语句. --但是这些依旧存在一些问题,在电子书<Apress.Expert.Oracle.SQL.Optimization.Deployment.and.Statistics.1430259779.pdf> --提到一个例子,无法实现闭包传递.自己重复测试看看: 1.环境: SCOTT@book> @ &r

[20160213]关于ansi语法.txt

[20160213]关于ansi语法.txt --曾经写过几篇关于ansi语法的blog,参考链接: [20120410]使用ANSI join syntax好吗?.txt http://blog.itpub.net/267265/viewspace-720875/ [20150423]left right join.txt http://blog.itpub.net/267265/viewspace-1593068/ --我以前并不是学习计算机,我一开始学习连接就非常不习惯写left join

[20160318]push_pred hint使用疑惑.txt

[20160318]push_pred hint使用疑惑.txt --前几天看的帖子,链接如下: http://www.itpub.net/thread-2054898-1-1.html --当时的第一感觉,就是闭包传递的问题,做1个记录: 1.环境: book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ------

Swift使用闭包表达式

Swift中的闭包表达式很灵活,其标准语法格式如下:{ (参数列表) ->返回值类型 in    语句组}其中,参数列表与函数中的参数列表形式一样,返回值类型类似于函数中的返回值类型,但不同的是后面有in关键字.Swift提供了多种闭包简化写法,这一节我们将介绍几种不同的形式.1.类型推断简化类型推断是Swift的强项,Swift可以根据上下文环境推断出参数类型和返回值类型.以下代码是标准形式的闭包:{(a:Int, b:Int) -> Int in    return a + b}Swift

《从零开始学Swift》学习笔记（Day 22）——闭包那些事儿！

 原创文章,欢迎转载.转载请注明:关东升的博客   我给Swift 中的闭包一个定义:闭包是自包含的匿名函数代码块,可以作为表达式.函数参数和函数返回值,闭包表达式的运算结果是一种函数类型. Swift中的闭包类似于Objective-C中的代码块.Java中的匿名内部类.   使用闭包表达式 Swift中的闭包表达式很灵活,其标准语法格式如下: { (参数列表) ->返回值类型 in     语句组 } 其中,参数列表与函数中的参数列表形式一样,返回值类型类似于函数中的返回值类型,但不同的

IOS swift3.0 下闭包语法整理_IOS

IOS swift3.0 下闭包语法整理 一.闭包的概念 有oc基础的都知道,闭包其实是oc里面的block,语法格式不一样,但作用是一样的.主要是用于callBack(异步回调)或者两个类之间的通信.它的本质一个函数,一个可执行的代码块,只是这个函数是没有名字的,也就是匿名函数.你也可以把他看作如 int.float一样,是一种数据类型,一种可以作为参数传递的数据类型. 二.基本语法 1.闭包的声明 //定义一个求和闭包 //闭包类型:(Int,Int)->(Int) let add:(Int