ORACLE数据库SQL优化表连接类型.docx
- 文档编号:9113576
- 上传时间:2023-02-03
- 格式:DOCX
- 页数:8
- 大小:16.72KB
ORACLE数据库SQL优化表连接类型.docx
《ORACLE数据库SQL优化表连接类型.docx》由会员分享,可在线阅读,更多相关《ORACLE数据库SQL优化表连接类型.docx(8页珍藏版)》请在冰豆网上搜索。
ORACLE数据库SQL优化表连接类型
一,当优化器解析含表连接的目标SQL时,它除了会根据目标SQL的SQL文本的写法来决定表连接的类型之外,还必须决定如下三件事情才能得到最终的执行计划。
1,表连接顺序
不管目标SQL中有多少个表做表连接,ORACLE在实际执行该SQL时都只能先两两做表连接,再依次执行这样的两两表连接过程,直到目标SQL中所有的表都已连接完毕。
所以这里的表连接顺序包含两层含义:
一层含义是当两个表做连接时,优化器需要决定这两个表中谁是驱动表(outertable),谁是被驱动表(innertable);另一层含义是当多个表(超过2个表)做表连接时,优化器需要决定这些表中谁和谁先做连接,然后决定这个表连接结果所在的结果集再和剩余表中哪一个再做表连接,这个两两做表连接的过程会一直持续下去,直到目标SQL中所有的表都已连接完为止。
2,表连接方法
在ORACLE中,两个表之间的连接方法有排序合并连接(SortMerge),嵌套循环连接(NestedLoops),哈希连接(HashJoin),笛卡尔连接(CrossJoin)这四种。
3,访问单表的方法
对于优化器来说,仅决定表的连接顺序和表的连接方法是不够的,这不足以得到目标SQL的最终执行计划,因为优化器在对目标SQL中的各个表两两连接时,还必须决定如何去获取存储在这些表里的不同维度的数据,即优化器还要决定访问单表的方法。
比如在访问单表的时候,是采用全表扫描还是走索引,如果是走索引,应该是走什么样的索引(Indexuniquescan,indexrangescan,indexfullscan,indexfastfullscan,indexskipscan)。
二,表连接类型
1,内连接(InnerJoin):
是指表连接的连接结果只包含哪些完全满足条件的记录。
下面来看一个内连接的例子:
createtablet1(col1number,col2varchar2(10));
createtablet2(col2varchar2(10),col3varchar2(10));
insertintot1values(1,'A');
insertintot1values(2,'B');
insertintot1values(3,'C');
insertintot2values('A','A2');
insertintot2values('B','B2');
insertintot2values('D','D2');
两个表里的数据如下:
SQL>select*fromt1;
COL1COL2
--------------------
1A
2B
3C
SQL>select*fromt2;
COL2COL3
--------------------
AA2
BB2
DD2
我们来看下如下的内连接:
SQL>selectt1.col1,t1.col2,t2.col3fromt1,t2wheret1.col2=t2.col2;
COL1COL2COL3
------------------------------
1AA2
2BB2
从上面的内连接的结果可以看出,内连接的结果确实只包含了哪些完全满足连接条件的记录。
上面的写法是ORACLE自己的写法。
这个和标准的SQL中的内连接写法不同,下面我们来看下标准的SQL的内连接的写法如下:
SQL>selectt1.col1,t1.col2,t2.col3fromt1joint2on(t1.col2=t2.col2);
COL1COL2COL3
------------------------------
1AA2
2BB2
从上面可以看出查询出来的结果是一样的。
2,外连接(OuterJoin)是对内连接的一种扩展,它是指表连接结果除了包含那些完全满足连接条件的记录之外还会包含驱动表(outertable)中所有不满足该连接条件的记录。
标准的SQL外连接分为左连接(leftouterjoin),右连接(rightouterjoin)和全连接(fullouterjoin)这三种。
他们在标准的SQL中所对应的关键字分别是leftoutjoin,rightoutjoin,fullouterjoin。
这里的Outertable就是驱动表。
当做外连接的时候,驱动表中所有不满足该连接条件的记录所对应的被驱动表中的查询列均会为NULL值来填充。
可以简单得把全连接理解为先做左连接,再做右连接,最后对左连接和右连接的连接结果做一个UNION操作。
(注意,虽然可以这么理解,但ORACLE在实际执行全连接时不会这么做)。
下面来看几个外连接的例子:
如下是左连接的例子:
SQL>selectt1.col1,t1.col2,t2.col3fromt1 leftjoin t2on(t1.col2=t2.col2);
COL1COL2COL3
------------------------------
1AA2
2BB2
3C
SQL>selectt1.col1,t1.col2,t2.col3fromt1 leftouterjoin t2on(t1.col2=t2.col2);
COL1COL2COL3
------------------------------
1AA2
2BB2
3C
注意:
leftouterjoin可以简写成leftjoin.
从上面可以看出,做连接的执行结果确实是除了包含所有满足条件连接条件的记录外,还包含驱动表(T1)中所有不满足该连接条件的记录。
同时,驱动表中所有不满足该连接条件的记录所对应的被驱动表中的查询列均以NULL值来填充。
如下是右连接的例子:
SQL>selectt1.col1,t1.col2,t2.col3fromt1rightjoint2on(t1.col2=t2.col2);
COL1COL2COL3
------------------------------
1AA2
2BB2
D2
SQL>selectt1.col1,t1.col2,t2.col3fromt1rightouterjoint2on(t1.col2=t2.col2);
COL1COL2COL3
------------------------------
1AA2
2BB2
D2
注意:
rightouterjoin可以简写成rightjoin.
从上面可以看出,做连接的执行结果确实是除了包含所有满足条件连接条件的记录外,还包含驱动表(T2)中所有不满足该连接条件的记录。
同时,驱动表中所有不满足该连接条件的记录所对应的被驱动表中的查询列均以NULL值来填充。
如下是全连接的例子:
SQL>selectt1.col1,t1.col2,t2.col3fromt1fullouterjoint2on(t1.col2=t2.col2);
COL1COL2COL3
------------------------------
1AA2
2BB2
3C
D2
SQL>selectt1.col1,t1.col2,t2.col3fromt1fulljoint2on(t1.col2=t2.col2);
COL1COL2COL3
------------------------------
1AA2
2BB2
3C
D2
从上面可以看出,全连接的结果确实是除了包含目标表T1和目标表T2中所有满足条件的记录外,还包含了目标表T2和目标表T2中所有不满足该连接条件的记录,同时所有不满足条件的以NULL填充。
3,除了带连接条件,还带上其他限制条件
上面的例子都是除了连接条件,没有带其他的限制条件,如果目标SQL中除了表连接条件之外还带有其他的限制条件,则目标SQL中表连接烈性和该额外限制条件在目标SQL中的SQL文本中出现的位置都可能会对最终执行结果产生影响。
看如下的例子:
SQL例子1
SQL>selectt1.col1,t1.col2,t2.col3fromt1joint2on(t1.col2=t2.col2andt1.col1=1);
COL1COL2COL3
------------------------------
1AA2
SQL例子2
SQL>selectt1.col1,t1.col2,t2.col3fromt1joint2on(t1.col2=t2.col2)wheret1.col1=1;
COL1COL2COL3
------------------------------
1AA2
从上面例子1和例子2可以看出除了表连接条件”t1.col2=t2.col2”之外,还多了一个额外的限制条件"t1.col1=1",对于该限制条件,一个在joinon所对应的括号内,而一个在JOINON所对应的括号外,虽然该限制条件在SQL文本中所处的位置不同,但因为都是内连接,所以该限制条件的位置并不会影响实际的表的连接结果。
下面在看一下外连接有额外限制条件的SQL语句:
SQL例子3
SQL>selectt1.col1,t1.col2,t2.col3fromt1leftjoint2on(t1.col2=t2.col2andt1.col1=1);
COL1COL2COL3
------------------------------
1AA2
2B
3C
SQL例子4
SQL>selectt1.col1,t1.col2,t2.col3fromt1leftjoint2on(t1.col2=t2.col2)wheret1.col1=1;
COL1COL2COL3
------------------------------
1AA2
由于此时是外连接,从上面的SQL例子3和SQL例子4可以看出此时的结果是不一样的了。
具体来说是这样的:
对于该限制条件(t1.col1=1),它在例子3的SQL文本位于leftjoin所对应的括号内,这表示该限制条件会在表T1和表T2做左连接之前就被应用在T1上。
而在例子4中,限制条件(t1.col1=1)是在左连接的所对应的括号外,这表示该限制条件在表T1和表T2做完左连接后,才会被应用在表T1和表T2的连接结果集上,
从上面的结果我们可以得出如下的结论:
对于外连接而言,除了表连接条件之外的额外的限制条件在目标SQL的SQL文本中的所处的位置确实可能会影响该SQL的实际执行
结果。
对于ORACLE用自定义的关键字”(+)“来表示外连接。
关键字“(+)”的位置在目标SQL连接条件中某一个表的连接列的后面,其含义是关键字“(+)”出现在那个表的连接列后面,就表明哪个表会以NULL来填充那些不满足连接条件并位于该表中的查询列,此时应该以关键字“(+)”对面的表来作为外连接的驱动表,这里的关键是决定哪个表是驱动表。
看如下的ORACLE的写法的例子:
t2是驱动表
SQL>selectt1.col1,t1.col2,t2.col3fromt1,t2wheret1.col2(+)=t2.col2;
COL1COL2COL3
------------------------------
1AA2
2BB2
D2
t1是驱动表
SQL>selectt1.col1,t1.col2,t2.col3fromt1,t2wheret1.col2=t2.col2(+);
COL1COL2COL3
------------------------------
1AA2
2BB2
3C
下面两种写法(例子5和例子6)是一致的:
SQL例子5
SQL>selectt1.col1,t1.col2,t2.col3fromt1,t2wheret1.col2=t2.col2(+)andt1.col1=1;
COL1COL2COL3
------------------------------
1AA2
SQL例子6
SQL>selectt1.col1,t1.col2,t2.col3fromt1leftjoint2on(t1.col2=t2.col2)wheret1.col1=1;
COL1COL2COL3
------------------------------
1AA2
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- ORACLE 数据库 SQL 优化 连接 类型