合 Oracle中的表和表之间的关联方式有哪几种
Tags: Oracle笛卡尔积排序合并连接嵌套循环哈希连接
目前为止,无论连接操作符如何,典型的连接类型共有3种:
① 排序合并连接(Sort Merge Join,简称SMJ),Oracle 6提供
② 嵌套循环(Nested Loops Join,简称NL),Oracle 6提供
③ 哈希连接(Hash Join,简称HJ),也叫散列连接,Oracle 7.3新增
另外,还有一种笛卡尔积(Merge Join Cartesian,简称MJC)连接,在Oracle 6版本的时候就已经提供,一般情况下,尽量避免使用。
对于Oracle 6提供的群集连接(Cluster Join)和Oracle 8提供的索引连接(Index Join),本书不做介绍。
在详细介绍这3类表连接方式之前,先创建表T_20161014_LHR_01共100行记录,T_20161014_LHR_02共100000行记录,创建脚本如下所示:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | DROP TABLE T_20161014_LHR_01 CASCADE CONSTRAINTS PURGE; DROP TABLE T_20161014_LHR_02 CASCADE CONSTRAINTS PURGE; CREATE TABLE T_20161014_LHR_01 ( ID NUMBER NOT NULL, N NUMBER, CONTENTS VARCHAR2(4000) ); CREATE TABLE T_20161014_LHR_02 ( ID NUMBER NOT NULL, T_20161014_LHR_01_ID NUMBER NOT NULL, N NUMBER, CONTENTS VARCHAR2(4000) ); EXECUTE DBMS_RANDOM.SEED(0); INSERT INTO T_20161014_LHR_01 SELECT ROWNUM, ROWNUM, DBMS_RANDOM.STRING('a', 50) FROM DUAL CONNECT BY LEVEL <= 100 ORDER BY DBMS_RANDOM.RANDOM; INSERT INTO T_20161014_LHR_02 SELECT ROWNUM, ROWNUM, ROWNUM, DBMS_RANDOM.STRING('b', 50) FROM DUAL CONNECT BY LEVEL <= 100000 ORDER BY DBMS_RANDOM.RANDOM; COMMIT; SELECT COUNT(*) FROM T_20161014_LHR_01; --100 SELECT COUNT(*) FROM T_20161014_LHR_02; --100000 |
(一)排序合并连接(SMJ)
如果连接属性上都建有索引,那么可利用索引已有的排序作合并连接。但如果在连接属性上没有索引时,那么需要首先对两表在连接属性上排序,对排序结果再作连接。
通常情况下,哈希连接的效果都比排序合并连接要好,然而如果行源已经被排过序,那么在执行排序合并连接时不需要再排序了,在这种情况下排序合并连接的性能会优于哈希连接。可以使用USE_MERGE(T1 T2)来强制使用排序合并连接。
如果相关联的表都是一个数量级,且其中一个或多个表在关联字段上有索引,那么此时使用该提示将可获得比其它两种JOIN方式更好的性能。需要注意的是,如果相关联的表是同一数量级,且相关联的表在关联字段上没有索引,那么该种方式下系统将会对所关联的表都进行全表扫描排序,其成本极高。所以,在有的数据库系统中,已不使用SMJ的关联方式,取而代之的是使用HJ的方式。
在Oracle数据库中有一个隐含参数“_OPTIMIZER_SORTMERGE_JOIN_ENABLED”控制着SMJ的启用和关闭,该参数默认值是TRUE,表示启用SMJ连接。