[20160330]关于连接顺序3.txt

[20160330]关于连接顺序3.txt

--关于连接顺序，曾经写过两篇blog，链接如下：
http://blog.itpub.net/267265/viewspace-1991306/
http://blog.itpub.net/267265/viewspace-1991787/

--今天才发现自己犯了一个严重错误，使用外连接的情况，连接顺序是可以改变的，以前的blog存在严重错误-(:)

--还是通过例子来讲解：

1.环境：

SCOTT@book> @ &r/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.测试：
select * from dept,emp where dept.deptno=emp.deptno(+);

Plan hash value: 2251696546

-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |       |     6 (100)|          |     15 |00:00:00.01 |      10 |       |       |          |
|   1 |  MERGE JOIN OUTER            |         |      1 |     14 |   812 |     6  (17)| 00:00:01 |     15 |00:00:00.01 |      10 |       |       |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      4 |    80 |     2   (0)| 00:00:01 |      4 |00:00:00.01 |       4 |       |       |          |
|   3 |    INDEX FULL SCAN           | PK_DEPT |      1 |      4 |       |     1   (0)| 00:00:01 |      4 |00:00:00.01 |       2 |       |       |          |
|*  4 |   SORT JOIN                  |         |      4 |     14 |   532 |     4  (25)| 00:00:01 |     14 |00:00:00.01 |       6 |  2048 |  2048 | 2048  (0)|
|   5 |    TABLE ACCESS FULL         | EMP     |      1 |     14 |   532 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       6 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------

--我前面的测试使用提示leading来指定连接顺序，不生效。

select /*+ full(dept) full(emp) leading( emp dept) */ * from dept,emp where dept.deptno=emp.deptno(+);
Plan hash value: 3713469723
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |     6 (100)|          |     15 |00:00:00.01 |      13 |       |       |          |
|*  1 |  HASH JOIN OUTER   |      |      1 |     14 |   812 |     6   (0)| 00:00:01 |     15 |00:00:00.01 |      13 |  1321K|  1321K| 1040K (0)|
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |    80 |     3   (0)| 00:00:01 |      4 |00:00:00.01 |       6 |       |       |          |
|   3 |   TABLE ACCESS FULL| EMP  |      1 |     14 |   532 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       7 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------
--可以发现leading并没有改变连接顺序。感觉在oracle的提示上很奇怪。时间上昨天看链接：
--https://jonathanlewis.wordpress.com/2016/03/17/hinting-4/
--https://jonathanlewis.wordpress.com/2010/12/10/quiz-night-10/
--才发现自己犯了一个低级错误。通过提示swap_join_inputs就可以改变连接顺序，很奇怪的是这种情况下leading提示无效。

3.继续测试：
select /*+ full(dept) full(emp) leading( emp dept) swap_join_inputs(emp)*/ * from dept,emp where dept.deptno=emp.deptno(+);

Plan hash value: 3590956717
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |      1 |        |       |     6 (100)|          |     15 |00:00:00.01 |      13 |       |       |          |
|*  1 |  HASH JOIN RIGHT OUTER|      |      1 |     14 |   812 |     6   (0)| 00:00:01 |     15 |00:00:00.01 |      13 |  1048K|  1048K|  656K (0)|
|   2 |   TABLE ACCESS FULL   | EMP  |      1 |     14 |   532 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       6 |       |       |          |
|   3 |   TABLE ACCESS FULL   | DEPT |      1 |      4 |    80 |     3   (0)| 00:00:01 |      4 |00:00:00.01 |       7 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------

--可以发现连接顺序发生了变化。连接操作变成了HASH JOIN RIGHT OUTER(原来是HASH JOIN OUTER)。
--也就是将使用外连接是可以改变连接顺序的。还可以发现在这样的情况前面的leading实际上是错误的提示。

select /*+ full(dept) full(emp) leading( dept emp) swap_join_inputs(emp)*/ * from dept,emp where dept.deptno=emp.deptno(+);
Plan hash value: 3590956717
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |      1 |        |       |     6 (100)|          |     15 |00:00:00.01 |      13 |       |       |          |
|*  1 |  HASH JOIN RIGHT OUTER|      |      1 |     14 |   812 |     6   (0)| 00:00:01 |     15 |00:00:00.01 |      13 |  1048K|  1048K|  654K (0)|
|   2 |   TABLE ACCESS FULL   | EMP  |      1 |     14 |   532 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       6 |       |       |          |
|   3 |   TABLE ACCESS FULL   | DEPT |      1 |      4 |    80 |     3   (0)| 00:00:01 |      4 |00:00:00.01 |       7 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------

4.但是如果连接是nested loop，merge无法改变连接顺序的。

select /*+ full(dept) full(emp) leading( dept emp) swap_join_inputs(emp) use_nl(dept) */ * from dept,emp where dept.deptno=emp.deptno(+);
Plan hash value: 3590956717
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |      1 |        |       |     6 (100)|          |     15 |00:00:00.01 |      13 |       |       |          |
|*  1 |  HASH JOIN RIGHT OUTER|      |      1 |     14 |   812 |     6   (0)| 00:00:01 |     15 |00:00:00.01 |      13 |  1048K|  1048K|  717K (0)|
|   2 |   TABLE ACCESS FULL   | EMP  |      1 |     14 |   532 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       6 |       |       |          |
|   3 |   TABLE ACCESS FULL   | DEPT |      1 |      4 |    80 |     3   (0)| 00:00:01 |      4 |00:00:00.01 |       7 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------

select /*+ full(dept) full(emp) leading( dept emp) swap_join_inputs(emp) use_nl(emp) */ * from dept,emp where dept.deptno=emp.deptno(+);
Plan hash value: 2022884187
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |    10 (100)|          |     15 |00:00:00.01 |      32 |
|   1 |  NESTED LOOPS OUTER|      |      1 |     14 |   812 |    10   (0)| 00:00:01 |     15 |00:00:00.01 |      32 |
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |    80 |     3   (0)| 00:00:01 |      4 |00:00:00.01 |       7 |
|*  3 |   TABLE ACCESS FULL| EMP  |      4 |      4 |   152 |     2   (0)| 00:00:01 |     14 |00:00:00.01 |      25 |
---------------------------------------------------------------------------------------------------------------------

select /*+ full(dept) full(emp) leading( dept emp) swap_join_inputs(emp) use_merge(dept) */ * from dept,emp where dept.deptno=emp.deptno(+);
Plan hash value: 3590956717
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |      1 |        |       |     6 (100)|          |     15 |00:00:00.01 |      13 |       |       |          |
|*  1 |  HASH JOIN RIGHT OUTER|      |      1 |     14 |   812 |     6   (0)| 00:00:01 |     15 |00:00:00.01 |      13 |  1048K|  1048K|  659K (0)|
|   2 |   TABLE ACCESS FULL   | EMP  |      1 |     14 |   532 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       6 |       |       |          |
|   3 |   TABLE ACCESS FULL   | DEPT |      1 |      4 |    80 |     3   (0)| 00:00:01 |      4 |00:00:00.01 |       7 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------

select /*+ full(dept) full(emp) leading( dept emp) swap_join_inputs(emp) use_merge(emp) */ * from dept,emp where dept.deptno=emp.deptno(+);
Plan hash value: 1800246806
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |       |     8 (100)|          |     15 |00:00:00.01 |      12 |       |       |          |
|   1 |  MERGE JOIN OUTER   |      |      1 |     14 |   812 |     8  (25)| 00:00:01 |     15 |00:00:00.01 |      12 |       |       |          |
|   2 |   SORT JOIN         |      |      1 |      4 |    80 |     4  (25)| 00:00:01 |      4 |00:00:00.01 |       6 |  2048 |  2048 | 2048  (0)|
|   3 |    TABLE ACCESS FULL| DEPT |      1 |      4 |    80 |     3   (0)| 00:00:01 |      4 |00:00:00.01 |       6 |       |       |          |
|*  4 |   SORT JOIN         |      |      4 |     14 |   532 |     4  (25)| 00:00:01 |     14 |00:00:00.01 |       6 |  2048 |  2048 | 2048  (0)|
|   5 |    TABLE ACCESS FULL| EMP  |      1 |     14 |   532 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       6 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------

--视乎不存在NESTED LOOPS RIGHT OUTER,MERGE JOIN RIGHT OUTER操作，也许我还是不对。

--google找到一些连接：

http://newleague.iteye.com/blog/1001842

那么我们为什么不支持right outer join呢。在这里,我们想返回符合条件的行对(R1,R2) 和不符合连接条件的(NULL,R2)。问题是我们会
多次扫描内部表-对于外部表的每行都要扫描一次。 在多次扫描过程中我们可能会多次处理内部表的同一行。这样我们就无法来判断某一
行到底符合 不符合连接条件。更进一步,如果我们使用index join,一些内部行可能都不会被处理,但是这些行在 外连接时是应该返回的
。

时间： 2024-07-31 01:45:48

[20160330]关于连接顺序3.txt的相关文章

[20160220]关于连接顺序2.txt

[20160220]关于连接顺序2.txt --前几天被问一个问题,如果使用外连接的情况,连接顺序可以改变吗?我只能说我给测试看看,再回答这个问题: --链接测试已经说明存在外连接的情况下,无法改变连接顺序的. --今天想想忘记考虑全连接的情况,继续测试: 1.环境: SCOTT@test01p> @ ver1 PORT_STRING                    VERSION        BANNER                                         

[20160219]关于连接顺序.txt

[20160219]关于连接顺序.txt --今天被问一个问题,如果使用外连接的情况,连接顺序可以改变吗?我只能说我给测试看看,再回答这个问题: 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ------------------------------------------------

急用 万分感谢-C++顺序读取txt的问题

问题描述 C++顺序读取txt的问题 0,-1145.1,2037.8,-0,-494.02,0 0,-2285.2,4060.2,-0,-982.1,0 0,-3415.9,6063,-0,-1464.1,0 0,-4541.8,8048.5,-0,-1939,0 0,-5659.5,10018,-0,-2409.6,0 我想顺序的读取这样的数据,然后把每一行数据顺序赋给x y z a b c,然互按照行的顺序不断刷新 x y z a b c,请大神指导,我有个思路是用两个循环,但不知有什么好

SQL优化之针对count、表的连接顺序、条件顺序、in及exist的优化_oracle

本文详述了SQL优化中针对count.表的连接顺序.条件顺序.in及exist的优化,非常具有实用价值!详述如下: 一.关于count 看过一些网上关于count(*)和count(列)的文章,count(列)的效率一定比count(*)高吗? 其实个人觉得count(*)和count(列)根本就没有可比性,count(*)统计的是表里面的总条数,而count(列)统计的是当列的非空记录条数. 不过我们可以通过实验来比较一下: 首先创建测试表: drop table test purge; cr

[20161021]显示记录顺序问题.txt

[20161021]显示记录顺序问题.txt --同事在维护数据库时,发现记录显示顺序发生变化,看了一下操作过程,可以猜测可能维护后发生了行迁移导致的情况. --通过例子说明: 1.建立测试环境: SCOTT@book> @ &r/ver1 PORT_STRING         VERSION    BANNER ------------------- ---------- ----------------------------------------------------------

[20150508]列顺序问题.txt

[20150508]列顺序问题.txt --链接: https://viveklsharma.wordpress.com/2015/04/30/cpu-cycles-for-column-skipping/ --测试列顺序对CPU cost的影响: SCOTT@test> @ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- --------

Oracle性能优化学习笔记之WHERE子句中的连接顺序

        ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.         例如:        (低效,执行时间156.3秒) SELECT - FROM EMP E WHERE SAL > 50000 AND JOB = 'MANAGER' AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO);      

[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.