Oracle半连接学习总结

半连接（semi-join）在我们查看一个数据集中某些字段存在于另一个数据集合中的记录时，常常会用到in 或者 exists。在执行计划中会看到join semi。在这里给出以下测试用的数据表结构以及模拟数据drop table table_1 purge;drop table table_2 purge;create table table_1as selectcast（rownum as int） a,cast（rownum+10 as int） b,cast（dbms_random.string（"i",10） as varchar2（10）） cfrom dual connect by level<=500000create table table_2as selectcast（rownum*2 as int） a,cast（rownum*2+10 as int） b,cast（dbms_random.string（"i",10） as varchar2（10）） cfrom dual connect by level<=1000在table_2增加一条已有的数据insert into table_2 select * from table_2 where a=20create index idx_tab1_a on table_1（a）;create index idx_tab2_a on table_2（a）;analyze table table_1 compute statisticsfor tablefor all indexesfor all indexed columnsanalyze table table_2 compute statisticsfor tablefor all indexesfor all indexed columns先看看下面的语句是什么业务含义select * from table_1 ,table_2 wheretable_1.a=table_2.a即使找到符合条件的数据，本次迭代也不会停止在table_2中继续往下寻找下一条符合条件的数据，如果再次找到，则迭代继续返回满足条件的数据。

…
18	28	LYZDKBNGLN	18	28	SICWAOITLK
20	30	DQCETGYPWE	20	30	HEFBMTNBQL
20	30	DQCETGYPWE	20	30	HEFBMTNBQL
22	32	URPNGTEIBW	22	32	TQNIVPFQUP
…

select /*d*/ table_1.*from table_1 ,table_2 where table_1.a=table_2.a in exists的含义为可以理解为在主数据集作迭代时，如果在副数据集中找到第一个符合条件的数据，即完成本条迭代的操作，在业务上可以理解为即可以理解为为selecta.a,a.b,b.afrom table_1a, （select distinct a from table_2） bwhere a.a=b.a --and b.a=20 可以清楚看到上面语句与下面语句返回的结果不同selecta.a,a.b,b.afrom table_1a, （select a from table_2） bwhere a.a=b.a --and b.a=20 我们来看看上面写法的执行计划EXPLAIN PLAN FORselecta.a,a.b,b.afrom table_1 a, （select distinct a from table_2） bwhere a.a=b.a and b.a=20; SELECT * FROM TABLE（DBMS_XPLAN.display（））; --------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |--------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1001 | 39039 | 509 （2）| 00:00:07 || 1 | VIEW | VM_NWVW_1 | 1001 | 39039 | 509 （2）| 00:00:07 || 2 | HASH UNIQUE | | 1001 | 27027 | 509 （2）| 00:00:07 ||* 3 | HASH JOIN | | 1001 | 27027 | 508 （2）| 00:00:07 || 4 | INDEX FAST FULL SCAN| IDX_TAB2_A | 1001 | 3003 | 3 （0）| 00:00:01 || 5 | TABLE ACCESS FULL |TABLE_1 | 500K| 11M| 502 （1）| 00:00:07 |-------------------------------------------------------------------------------------- 虽然有业务上明确意义的写法，但是这种写法并不是高效率的写法，Oracle提供了IN ESISTS的解法来提高效率 EXPLAIN PLAN FORselecta.a,a.bfrom table_1 a, （select distinct a from table_2） bwhere a.a=b.a --and b.a=20; SELECT * FROM TABLE（DBMS_XPLAN.display（））; --------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |--------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1001 | 26026 | 509 （2）| 00:00:07 || 1 | VIEW | VM_NWVW_1 | 1001 | 26026 | 509 （2）| 00:00:07 || 2 | HASH UNIQUE | | 1001 | 27027 | 509 （2）| 00:00:07 ||* 3 | HASH JOIN | | 1001 | 27027 | 508 （2）| 00:00:07 || 4 | INDEX FAST FULL SCAN| IDX_TAB2_A | 1001 | 3003 | 3 （0）| 00:00:01 || 5 | TABLE ACCESS FULL |TABLE_1 | 500K| 11M| 502 （1）| 00:00:07 |-------------------------------------------------------------------------------------- call count cpu elapsed disk query current rows------- ------ -------- -------------------- ---------- ---------- ----------Parse 1 0.00 0.01 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 1 0.00 0.00 0 18 0 100------- ------ -------- -------------------- ---------- ---------- ----------total 3 0.00 0.01 0 18 0 100 EXPLAIN PLAN FORselecta.a,a.bfrom table_1 awhere a.a in （select a from table_2）; SELECT * FROM TABLE（DBMS_XPLAN.display（））; ------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1000 | 20000 | 508 （2）| 00:00:07 ||* 1 | HASH JOIN RIGHT SEMI | | 1000 | 20000 | 508 （2）| 00:00:07 || 2 | INDEX FAST FULL SCAN| IDX_TAB2_A | 1001 | 3003 | 3 （0）| 00:00:01 || 3 | TABLE ACCESS FULL |TABLE_1 | 500K| 8300K| 502 （1）| 00:00:07 |------------------------------------------------------------------------------------ call count cpu elapsed disk query current rows------- ------ -------- -------------------- ---------- ---------- ----------Parse 1 0.00 0.12 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 1 0.00 0.00 0 10 0 100------- ------ -------- -------------------- ---------- ---------- ----------total 3 0.00 0.12 0 10 0 100 EXPLAIN PLAN FORselecta.a,a.bfrom table_1 awhere exists （select null from table_2 b where a.a=b.a）;SELECT * FROM TABLE（DBMS_XPLAN.display（））;------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1000 | 20000 | 508 （2）| 00:00:07 ||* 1 | HASH JOIN RIGHT SEMI | | 1000 | 20000 | 508 （2）| 00:00:07 || 2 | INDEX FAST FULL SCAN| IDX_TAB2_A | 1001 | 3003 | 3 （0）| 00:00:01 || 3 | TABLE ACCESS FULL |TABLE_1 | 500K| 8300K| 502 （1）| 00:00:07 |------------------------------------------------------------------------------------ call count cpu elapsed disk query current rows------- ------ -------- -------------------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 1 0.00 0.00 0 10 0 100------- ------ -------- -------------------- ---------- ---------- ----------total 3 0.00 0.00 0 10 0 100 将表table_1数据增加到50万条，执行计划与跟踪结果表明，in 与 exists的写法一样的效率。

Oracle按照一定顺序提取数据Oracle包XMLDOM创建XML文件及其缺点相关资讯 Oracle基础教程 Oracle半连接

关于Oracle中的半连接（11/24/2014 14:16:24）
Oracle基础教程之tkprof程序详解（10/22/2013 11:49:50）
Oracle基础教程之sqlplus汉字乱码（07/18/2013 16:30:00）

Oracle块编程返回结果集详解（11/10/2013 10:45:58）
Oracle基础教程之设置系统全局区（08/22/2013 14:24:00）
Oracle基础教程知识点总结（06/18/2013 07:43:32）

本文评论查看全部评论（0）