PL SQL的三种连接.docx
- 文档编号:26282160
- 上传时间:2023-06-17
- 格式:DOCX
- 页数:16
- 大小:16.46KB
PL SQL的三种连接.docx
《PL SQL的三种连接.docx》由会员分享,可在线阅读,更多相关《PL SQL的三种连接.docx(16页珍藏版)》请在冰豆网上搜索。
PLSQL的三种连接
连接(join)
如果有主键的列连接,将带主键和唯一键约束的表放在连接的第一个位置,再考虑其它表连接
如果有外键连接,则将该表放在连接的最后。
NestedLoopJoins(嵌套循环连接)
外部表的每一行都和内部表的所有行连接。
当表的行较少的时候,数据库会选择这种连接。
提示:
USE_NL(table1table2)
SQL>CONNSCOTT/TIGER
Connected.
SQL>setautottraceonlyexplain
SQL>selectename,locfromemp,dept
2whereemp.deptno=dept.deptno;
ExecutionPlan
----------------------------------------------------------
Planhashvalue:
351108634
----------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
----------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||14|280|5(0)|00:
00:
01|
|1|NESTEDLOOPS||14|280|5(0)|00:
00:
01|
|2|TABLEACCESSFULL|EMP|14|126|4(0)|00:
00:
01|
|3|TABLEACCESSBYINDEXROWID|DEPT|1|11|1(0)|00:
00:
01|
|*4|INDEXUNIQUESCAN|PK_DEPT|1||0(0)|00:
00:
01|
HashJoins
适用于大数据量的连接。
将两个表中较小的表的连接列建立一个hash表,将hash表放入到内存中。
什么时候用HASH连接?
大量数据要连接,但要想使hash连接起到作用,必须有等值的条件.
使用hint:
USE_HASH
SQL>droptablet1purge;
Tabledropped.
SQL>droptablet2purge;
Tabledropped.
SQL>createtablet1asselect*fromdept;
Tablecreated.
SQL>createtablet2asselect*fromemp;
Tablecreated.
SQL>selectename,locfromt1,t2
2wheret1.deptno=t2.deptno;
ExecutionPlan
----------------------------------------------------------
Planhashvalue:
1838229974
---------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
---------------------------------------------------------------------------
|0|SELECTSTATEMENT||14|574|9(12)|00:
00:
01|
|*1|HASHJOIN||14|574|9(12)|00:
00:
01|
|2|TABLEACCESSFULL|T1|4|84|4(0)|00:
00:
01|
|3|TABLEACCESSFULL|T2|14|280|4(0)|00:
00:
01|
---------------------------------------------------------------------------
排序融合连接
HASH连接在大部分时候都比排序连接性能好。
但如果不是等值条件的时候,条件是>,>=,<,<=的时候,不能使用hash连接,使用排序连接和嵌套循环连接。
再有当连接的结果要排好序的时候,也可以选择排序融合连接。
SQL>selectename,gradefromemp,salgrade
2wheresalbetweenLOSALandhisal;
ExecutionPlan
SQL>--1.NESTLOOP
SQL>SETAUTOTTRACEEXP
SQL>SELECTENAME,LOCFROMEMPE,DEPTD
2WHEREE.DEPTNO=D.DEPTNOANDE.DEPTNO=10;
执行计划
----------------------------------------------------------
Planhashvalue:
568005898
----------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
----------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||3|60|4(0)|00:
00:
01|
|1|NESTEDLOOPS||3|60|4(0)|00:
00:
01|
|2|TABLEACCESSBYINDEXROWID|DEPT|1|11|1(0)|00:
00:
01|
|*3|INDEXUNIQUESCAN|PK_DEPT|1||0(0)|00:
00:
01|
|*4|TABLEACCESSFULL|EMP|3|27|3(0)|00:
00:
01|
----------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
3-access("D"."DEPTNO"=10)
4-filter("E"."DEPTNO"=10)
SQL>--2.SORTMERGE
SQL>SELECTENAME,GRADEFROMEMP,SALGRADEWHERESALBETWEENLOSALANDHISAL;
执行计划
----------------------------------------------------------
Planhashvalue:
270072162
---------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
---------------------------------------------------------------------------------
|0|SELECTSTATEMENT||1|20|8(25)|00:
00:
01|
|1|MERGEJOIN||1|20|8(25)|00:
00:
01|
|2|SORTJOIN||5|50|4(25)|00:
00:
01|
|3|TABLEACCESSFULL|SALGRADE|5|50|3(0)|00:
00:
01|
|*4|FILTER||||||
|*5|SORTJOIN||14|140|4(25)|00:
00:
01|
|6|TABLEACCESSFULL|EMP|14|140|3(0)|00:
00:
01|
---------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
4-filter("SAL"<="HISAL")
5-access("SAL">="LOSAL")
filter("SAL">="LOSAL")
SQL>--3.HASHJOIN
SQL>SELECTENAME,LOCFROMEMPE,DEPTD
2WHEREE.DEPTNO=D.DEPTNO;
执行计划
----------------------------------------------------------
Planhashvalue:
351108634
----------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
----------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||14|280|4(0)|00:
00:
01|
|1|NESTEDLOOPS||14|280|4(0)|00:
00:
01|
|2|TABLEACCESSFULL|EMP|14|126|3(0)|00:
00:
01|
|3|TABLEACCESSBYINDEXROWID|DEPT|1|11|1(0)|00:
00:
01|
|*4|INDEXUNIQUESCAN|PK_DEPT|1||0(0)|00:
00:
01|
----------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
4-access("E"."DEPTNO"="D"."DEPTNO")
SQL>DROPTABLET1PURGE;
表已删除。
SQL>CREATETABLET1ASSELECT*FROMEMP;
表已创建。
SQL>INSERTINTOT1SELECT*FROMT1;
已创建14行。
执行计划
----------------------------------------------------------
Planhashvalue:
3617692013
--------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
--------------------------------------------------------------------------
|0|INSERTSTATEMENT||327|28449|2(0)|00:
00:
01|
|1|TABLEACCESSFULL|T1|327|28449|2(0)|00:
00:
01|
--------------------------------------------------------------------------
SQL>/
已创建28行。
执行计划
----------------------------------------------------------
Planhashvalue:
3617692013
--------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
--------------------------------------------------------------------------
|0|INSERTSTATEMENT||327|28449|2(0)|00:
00:
01|
|1|TABLEACCESSFULL|T1|327|28449|2(0)|00:
00:
01|
--------------------------------------------------------------------------
SQL>/
已创建56行。
执行计划
----------------------------------------------------------
Planhashvalue:
3617692013
--------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
--------------------------------------------------------------------------
|0|INSERTSTATEMENT||327|28449|2(0)|00:
00:
01|
|1|TABLEACCESSFULL|T1|327|28449|2(0)|00:
00:
01|
--------------------------------------------------------------------------
SQL>/
已创建112行。
执行计划
----------------------------------------------------------
Planhashvalue:
3617692013
--------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
--------------------------------------------------------------------------
|0|INSERTSTATEMENT||327|28449|2(0)|00:
00:
01|
|1|TABLEACCESSFULL|T1|327|28449|2(0)|00:
00:
01|
--------------------------------------------------------------------------
SQL>SELECTENAME,LOCFROMT1E,DEPTD
2WHEREE.DEPTNO=D.DEPTNO;
执行计划
----------------------------------------------------------
Planhashvalue:
1715696576
----------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
----------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||327|10137|3(0)|00:
00:
01|
|1|NESTEDLOOPS||327|10137|3(0)|00:
00:
01|
|2|TABLEACCESSFULL|T1|327|6540|2(0)|00:
00:
01|
|3|TABLEACCESSBYINDEXROWID|DEPT|1|11|1(0)|00:
00:
01|
|*4|INDEXUNIQUESCAN|PK_DEPT|1||0(0)|00:
00:
01|
----------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
4-access("E"."DEPTNO"="D"."DEPTNO")
SQL>EXECDBMS_STATS.GATHER_TABLE_STATS('SCOTT','T1');
PL/SQL过程已成功完成。
SQL>SELECTENAME,LOCFROMT1E,DEPTD
2WHEREE.DEPTNO=D.DEPTNO;
执行计划
----------------------------------------------------------
Planhashvalue:
1715696576
----------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
----------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||224|4480|4(0)|00:
00:
01|
|1|NESTEDLOOPS||224|4480|4(0)|00:
00:
01|
|2|TABLEACCESSFULL|T1|224|2016|3(0)|00:
00:
01|
|3|TABLEACCESSBYINDEXROWID|DEPT|1|11|1(0)|00:
00:
01|
|*4|INDEXUNIQUESCAN|PK_DEPT|1||0(0)|00:
00:
01|
----------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
4-access("E"."DEPTNO"="D"."DEPTNO")
SQL>INSERTINTOT1SELECT*FROMT1;
已创建224行。
执行计划
----------------------------------------------------------
Planhashvalue:
3617692013
--------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
--------------------------------------------------------------------------
|0|INSERTSTATE
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- PL SQL的三种连接 SQL 连接