| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1001 | 20020 | 313 (2)| 00:00:04 |
|* 1 | HASH JOIN SEMI | | 1001 | 20020 | 313 (2)| 00:00:04 |
| 2 | TABLE ACCESS FULL |TABLE_2 | 1001 | 16016 | 4 (0)| 00:00:01 |
| 3 | INDEX FAST FULL SCAN| IDX_TAB1_A | 500K| 1953K| 306 (1)| 00:00:04 |
------------------------------------------------------------------------------------
对比上述2种写法，虽然cost差不多，但是第一种写法却发生了大得多的逻辑读取(LIO)
因此在应用开发中，不要使用
select count(a.b)
from table_2 a, (select distinct a from table_1) b
where a.a=b.a
从上面的结果看，oracle的半关联不仅在业务上表述了业务需要，还提供了一种优化算法，在其他数据库中你可以用in exists语法，但可能看不到这样优化的算法。
副数据集来自于多个表的半关连
drop table table_1 purge;
drop table table_2 purge;
drop table table_3 purge;
drop table table_4 purge;
create table table_1
as select
cast(rownum as int) a,
cast(rownum+10 as int) b,
cast(dbms_random.string('i',10) as varchar2(10)) c
from dual connect by level<=500000
create table table_2
as select
cast(rownum*2 as int) a,
cast(rownum*2+10 as int) b,
cast(dbms_random.string('i',10) as varchar2(10)) c
from dual connect by level<=1000
create table table_3
as select
cast(rownum*2+1 as int) a,
cast(rownum*2+10 as int) b,
cast(dbms_random.string('i',10) as varchar2(10)) c
from dual connect by level<=1000
insert into table_2 select * from table_2 where a=20
create index idx_tab1_a on table_1(a);
create index idx_tab2_a on table_2(a);
create index idx_tab3_a on table_3(a);
analyze table table_1 compute statistics
for table
for all indexes
for all indexed columns
analyze table table_2 compute statistics
for table
for all indexes
for all indexed columns
analyze table table_3 compute statistics
for table
for all indexes
for all indexed columns
select count(t1.b)
from table_1 t1
where a in
(
select a from table_2
union all
select a from table_3
)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.06 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.20 0.22 35 1814 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.21 0.28 35 1814 0 1
Misses inlibrary cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 92
Rows RowSource Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=1814 pr=35 pw=35 time=0us)
2000 HASH JOIN (cr=1814 pr=35 pw=35 time=39 us cost=513 size=60030 card=2001)
2000 VIEW VW_NSO_1 (cr=13 pr=0 pw=0 time=93 us cost=8 size=26013 card=2001)
2000 HASH UNIQUE (cr=13 pr=0 pw=0 time=32 uscost=8 size=6003 card=2001)
2001 UNION-ALL (cr=13 pr=0 pw=0 time=93 us)
1001 TABLE ACCESS FULL TABLE_2 (cr=7 pr=0pw=0 time=16 us cost=4 size=3003 card=1001)
我们看到这种应用没有出现半关连，也没有找到一个好的写法解决这个问题。
即使我们建立一个新表
create table table_4
as
select a from table_2
union
select a from table_3