半连接(semi-join)
在我们查看一个数据集中某些字段存在于另一个数据集合中的记录时，常常会用到in 或者 exists。在执行计划中会看到join semi。
在这里给出以下测试用的数据表结构以及模拟数据
drop table table_1 purge;
drop table table_2 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
在table_2增加一条已有的数据
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);
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
先看看下面的语句是什么业务含义
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的含义为
可以理解为在主数据集作迭代时，如果在副数据集中找到第一个符合条件的数据，即完成本条迭代的操作，
在业务上可以理解为
即可以理解为为
select
a.a,a.b,b.a
from table_1a, (select distinct a from table_2) b
where a.a=b.a --and b.a=20
可以清楚看到上面语句与下面语句返回的结果不同
select
a.a,a.b,b.a
from table_1a, (select a from table_2) b
where a.a=b.a --and b.a=20
我们来看看上面写法的执行计划
EXPLAIN PLAN FOR
select
a.a,a.b,b.a
from table_1 a, (select distinct a from table_2) b
where 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 FOR
select
a.a,a.b
from table_1 a, (select distinct a from table_2) b
where 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 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 18 0 100
------- ------ -------- -------------------- ---------- ---------- ----------
total 3 0.00 0.01 0 18 0 100
EXPLAIN PLAN FOR
select
a.a,a.b
from table_1 a
where 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 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 10 0 100
------- ------ -------- -------------------- ---------- ---------- ----------
total 3 0.00 0.12 0 10 0 100
EXPLAIN PLAN FOR
select
a.a,a.b
from table_1 a
where 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 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 10 0 100
------- ------ -------- -------------------- ---------- ---------- ----------
total 3 0.00 0.00 0 10 0 100
将表table_1数据增加到50万条，执行计划与跟踪结果表明，in 与 exists的写法一样的效率。
在9i中，in 与 exists的写法在执行计划与跟踪结果结果是不一样的，认为
Select * from T1 where x in ( select y fromT2 )
与
select *
from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;
是很相近的。
而exists的写法
select * from t1 where exists ( select nullfrom t2 where y = x )
具有以下的逻辑过程
for x in ( select * from t1 )
loop
if ( exists ( select null from t2 where y = x.x )
then
OUTPUT THE RECORD
end if
end loop
这表明总会导致table_1执行全表扫描，并能使用table_2上的索引
It always results in a full scan of T1whereas the first query can make use of an index on T1(x).
如果( select afrom table_1)结果很大，而(select *from table_2很小)，这时候在table_2(a)存在索引的话，使用exists是有效率的写法。
而如果(select * from table_1)是很小的结果集，则采用in是更好的方法。
如果(select *from table_1) 与 (select *from table_2)都是很大的结果集合，要看其他的一些因素，如索引等。
半连接还有一种写法
select count(a.b)
from table_2 a, (select distinct a from table_1) b
where a.a=b.a
但是测试结果表明，无论数据呈现怎样的特征，这种写法在效率上都比不上in 或者exists
我们将table_1,table_2的位置颠倒一下，结果就更加清楚了
EXPLAIN PLAN FOR
select
a.a,a.b
from table_2a, (select distinct a from table_1) b
where a.a=b.a
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 |
--------------------------------------------------------------------------------------
EXPLAIN PLAN FOR
select
a.a,a.b
from table_2 a
where a.a in (select a from table_1);
SELECT * FROM TABLE(DBMS_XPLAN.display());
------------------------------------------------------------------------------------
| 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 |
------------------------------------------------------------------------------------
)
EXPLAIN PLAN FOR
select
a.a,a.b
from table_2 a
where exists (select null from table_1 b where a.a=b.a);
SELECT * FROM TABLE(DBMS_XPLAN.display());
------------------------------------------------------------------------------------
| 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
create index idx_tab_4 on table_4(a)
运行以下查询
select /*b*/ count(b)
from table_1 t1
where a in (select a from table_4)
call count cpu elapsed disk query current rows
------- ------ ------------------ ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.20 0.20 0 1811 0 1
------- ------ ------------------ ---------- ---------- ---------- ----------
total 3 0.20 0.20 0 1813 0 1
Misses in library cacheduring parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 92
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=1811 pr=0 pw=0 time=0 us)
2000 HASH JOIN RIGHT SEMI (cr=1811 pr=0 pw=0time=41 us cost=509 size=60000 card=2000)
2000 INDEX FAST FULL SCAN IDX_TAB_4 (cr=10 pr=0pw=0 time=31 us cost=4 size=26000 card=2000)(object id 80315)
500000 TABLE ACCESS FULL TABLE_1 (cr=1801 pr=0pw=0 time=7555 us cost=502 size=8500000 card=500000)
在这样的数据特征下，即使事先合并了副数据集成一张表，再进行半连接。测试结果表明，这样做并没有没有多大改进。
主数据集的记录数远大于副数据集的半关连
create table table_5
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<=10
explain plan for
select count(b)
from table_1
where exists(select null from table_3 where a=table_1.a);
--------------------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECTSTATEMENT | | 1 | 30 | 19 (6)| 00:00:01|
| 1 | SORT AGGREGATE | | 1 | 30 | | |
| 2| NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 10 | 300 | 19 (6)| 00:00:01 |
| 4 | SORT UNIQUE | | 10 | 130 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | TABLE_3 | 10 | 130 | 3 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX_TAB1_A | 1 | | 2 (0)| 00:00:01|
| 7 | TABLE ACCESS BY INDEX ROWID| TABLE_1 | 1 | 17 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
结果看到，这时候执行计划中，没有出现join semi半关连,在这种特殊情况下，转化为nested loop。在这里用上了table_1的a列上的索引了。
副数据集来自于集合的半关连
explain plan for
with temp_table_2 as
(select /*+cardinality(10)*/ to_number(column_value) a fromtable(str_to_table('1,2,3,4,5,6',',')))
select c
from table_1
where exists (select null fromtemp_table_2 where a= table_1.a) /*mmm*/
---------------------------------------------------------------------------------------------------
| Id |Operation |Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECTSTATEMENT | | 1 | 13 | 535 (2)| 00:00:07|
|* 1 | HASH JOIN RIGHT SEMI | | 1 | 13 | 535 (2)| 00:00:07|
| 2 | COLLECTION ITERATOR PICKLER FETCH|STR_TO_TABLE | | | | |
| 3 | TABLE ACCESS FULL | TABLE_1 | 500K| 5371K| 503 (1)| 00:00:07 |
---------------------------------------------------------------------------------------------------
explain plan for
with temp_table_2 as
(select /*+cardinality(10)*/ to_number(column_value) a fromtable(str_to_table('1,2,3,4,5,6',',')))
select c
from table_1
where a in (select a from temp_table_2) /*mmm*/
---------------------------------------------------------------------------------------------------
| Id |Operation |Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECTSTATEMENT | | 1 | 13 | 535 (2)| 00:00:07|
|* 1 | HASH JOIN RIGHT SEMI | | 1 | 13 | 535 (2)| 00:00:07 |
| 2 | COLLECTION ITERATOR PICKLER FETCH|STR_TO_TABLE | | | | |
| 3 | TABLE ACCESS FULL | TABLE_1 | 500K| 5371K| 503 (1)| 00:00:07 |
---------------------------------------------------------------------------------------------------
explain plan for
with temp_table_2 as
(select /*+cardinality(m,10)*/ to_number(column_value) a fromtable(str_to_table('1,2,3,4,5,6',',')) m)
select c
from table_1
where exists (select null fromtemp_table_2 where a= table_1.a) /*mmm*/
select * from table(dbms_xplan.display());
-----------------------------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECTSTATEMENT | | 10 | 130 | 45 (3)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 10 | 130 | 45 (3)| 00:00:01|
| 3 | SORT UNIQUE | | | | | |
| 4 | COLLECTION ITERATOR PICKLER FETCH|STR_TO_TABLE | | | | |
|* 5 | INDEX RANGE SCAN | IDX_TAB1_A | 1 | | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | TABLE_1 | 1 | 11 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
explain plan for
with temp_table_2 as
(select /*+cardinality(m,10)*/ to_number(column_value) a fromtable(str_to_table('1,2,3,4,5,6',',')) m)
select c
from table_1
where a in (select a from temp_table_2) /*mmm*/
select * from table(dbms_xplan.display());
-----------------------------------------------------------------------------------------------------
| Id |Operation |Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECTSTATEMENT | | 10 | 130 | 45 (3)| 00:00:01|
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 10 | 130 | 45 (3)| 00:00:01|
| 3 | SORT UNIQUE | | | | | |
| 4 | COLLECTION ITERATOR PICKLER FETCH|STR_TO_TABLE | | | | |
|* 5 | INDEX RANGE SCAN | IDX_TAB1_A | 1 | | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | TABLE_1 | 1 | 11 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
select * from table(dbms_xplan.display());
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 90 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.03 0.02 0 182 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.03 0.02 0 272 0 6
Misses in library cacheduring parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 92
Rows Row Source Operation
------- ---------------------------------------------------
6 HASH JOIN RIGHT SEMI (cr=182 pr=0 pw=0time=18 us cost=83 size=16 card=1)
6 COLLECTION ITERATOR PICKLER FETCHSTR_TO_TABLE (cr=0 pr=0 pw=0 time=2 us)
50000 TABLE ACCESS FULL TABLE_1 (cr=182 pr=0 pw=0time=856 us cost=53 size=700000 card=50000)
with temp_table_2 as
(select /*+cardinality(m,10)*/ to_number(column_value) a fromtable(str_to_table('1,2,3,4,5,6',',')) m)
select c
from table_1
where a in (select a from temp_table_2 where a= table_1.a)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 90 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.42 0.41 0 182 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.42 0.42 0 272 0 0
Misses in library cacheduring parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 92
Rows Row Source Operation
------- ---------------------------------------------------
0 FILTER (cr=182 pr=0 pw=0 time=0 us)
50000 TABLE ACCESS FULL TABLE_1 (cr=182 pr=0 pw=0time=868 us cost=54 size=700000 card=50000)
0 FILTER (cr=0 pr=0 pw=0 time=0 us)
0 COLLECTION ITERATOR PICKLER FETCHSTR_TO_TABLE (cr=0 pr=0 pw=0 time=0 us)
with temp_table_2 as
(select to_number(column_value)a from table(str_to_table('1,2,3,4,5,6',',')) m)
select c
from table_1
where a in (select a from temp_table_2 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 90 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.03 0.01 0 182 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.03 0.02 0 272 0 6
Misses in library cacheduring parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 92
Rows Row Source Operation
------- ---------------------------------------------------
6 HASH JOIN RIGHT SEMI (cr=182 pr=0 pw=0 time=10us cost=83 size=16 card=1)
6 COLLECTION ITERATOR PICKLER FETCHSTR_TO_TABLE (cr=0 pr=0 pw=0 time=2 us)
50000 TABLE ACCESS FULL TABLE_1 (cr=182 pr=0 pw=0time=751 us cost=53 size=700000 card=50000)
with temp_table_2 as
(select /*+cardinality(m,10)*/ to_number(column_value) a fromtable(str_to_table('1,2,3,4,5,6',',')) m)
select c
from table_1
where a in (select a from temp_table_2) /**/
call count cpu elapsed disk query current rows
------- ------ ------------------ ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 90 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 9 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.01 0 99 0 6
Misses in library cacheduring parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 92
Rows Row Source Operation
------- ---------------------------------------------------
6 NESTED LOOPS (cr=9 pr=0 pw=0 time=42 us)
6 NESTED LOOPS (cr=8 pr=0 pw=0 time=25 us cost=40 size=160 card=10)
6 SORT UNIQUE (cr=0 pr=0 pw=0 time=2 us)
6 COLLECTION ITERATOR PICKLER FETCHSTR_TO_TABLE (cr=0 pr=0 pw=0 time=2 us)
6 INDEX RANGE SCAN IDX_TABLE_1_A (cr=8 pr=0pw=0 time=0 us cost=1 size=0 card=1)(object id 80214)
6 TABLE ACCESS BY INDEX ROWID TABLE_1 (cr=1 pr=0 pw=0 time=0 us cost=2size=14 card=1)
with temp_table_2 as
(select /*+cardinality(m,10)*/ to_number(column_value) a from table(str_to_table('1,2,3,4,5,6',',')) m)
select c
from table_1
where exists (select null fromtemp_table_2 where a= table_1.a) /**/
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 90 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 9 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.00 0 99 0 6
Misses in library cacheduring parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 92
Rows Row Source Operation
------- ---------------------------------------------------
6 NESTED LOOPS (cr=9 pr=0 pw=0 time=42 us)
6 NESTED LOOPS (cr=8 pr=0 pw=0 time=26 us cost=40 size=160 card=10)
6 SORT UNIQUE (cr=0 pr=0 pw=0 time=2 us)
6 COLLECTION ITERATOR PICKLER FETCHSTR_TO_TABLE (cr=0 pr=0 pw=0 time=1 us)
6 INDEX RANGE SCAN IDX_TABLE_1_A (cr=8 pr=0pw=0 time=0 us cost=1 size=0 card=1)(object id 80214)
6 TABLE ACCESS BY INDEX ROWID TABLE_1 (cr=1pr=0 pw=0 time=0 us cost=2 size=14 card=1)
with temp_table_2 as
(
select /*+cardinality(m,10)*/ to_number(column_value) a fromtable(str_to_table('1,2,3,4,5,6',',')) m
)
select c
from table_1 a, temp_table_2 b
where a.a=b.a /**/
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 90 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 9 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 99 0 6
Misses in library cacheduring parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 92
Rows Row Source Operation
------- ---------------------------------------------------
6 NESTED LOOPS (cr=9 pr=0 pw=0 time=44 us)
6 NESTED LOOPS (cr=8 pr=0 pw=0 time=27 us cost=49 size=160 card=10)
6 COLLECTION ITERATOR PICKLER FETCHSTR_TO_TABLE (cr=0 pr=0 pw=0 time=2 us)
6 INDEX RANGE SCAN IDX_TABLE_1_A (cr=8 pr=0pw=0 time=0 us cost=1 size=0 card=1)(object id 80214)
6 TABLE ACCESS BY INDEX ROWID TABLE_1 (cr=1pr=0 pw=0 time=0 us cost=2 size=14 card=1)
with temp_table_2 as
(select /*+cardinality(m,10)*/ to_number(column_value) a fromtable(str_to_table('1,2,3,4,5,6',',')) m)
select c
from table_1
where a in (select a from temp_table_2) /*mmm*/
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 48 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 14 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.00 0 62 0 6
Misses in library cacheduring parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 92
Rows Row Source Operation
------- ---------------------------------------------------
6 NESTED LOOPS (cr=14 pr=0 pw=0 time=43 us)
6 NESTED LOOPS (cr=13 pr=0 pw=0 time=27 us cost=45 size=130 card=10)
6 SORT UNIQUE (cr=0 pr=0 pw=0 time=2 us)
6 COLLECTION ITERATOR PICKLER FETCHSTR_TO_TABLE (cr=0 pr=0 pw=0 time=2 us)
6 INDEX RANGE SCAN IDX_TAB1_A (cr=13 pr=0 pw=0 time=0 us cost=2 size=0card=1)(object id 80310)
6 TABLE ACCESS BY INDEX ROWID TABLE_1 (cr=1pr=0 pw=0 time=0 us cost=3 size=11 card=1)
********************************************************************************
with temp_table_2 as
(select /*+cardinality(m,10)*/ to_number(column_value) a fromtable(str_to_table('1,2,3,4,5,6',',')) m)
select c
from table_1
where exists (select null fromtemp_table_2 where a= table_1.a) /*mmm*/
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 48 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 14 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 62 0 6
Misses in library cacheduring parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 92
Rows Row Source Operation
------- ---------------------------------------------------
6 NESTED LOOPS (cr=14 pr=0 pw=0 time=43 us)
6 NESTED LOOPS (cr=13 pr=0 pw=0 time=27 us cost=45 size=130 card=10)
6 SORT UNIQUE (cr=0 pr=0 pw=0 time=2 us)
6 COLLECTION ITERATOR PICKLER FETCHSTR_TO_TABLE (cr=0 pr=0 pw=0 time=2 us)
6 INDEX RANGE SCAN IDX_TAB1_A (cr=13 pr=0pw=0 time=0 us cost=2 size=0 card=1)(object id 80310)
6 TABLE ACCESS BY INDEX ROWID TABLE_1 (cr=1pr=0 pw=0 time=0 us cost=3 size=11 card=1)
********************************************************************************
with temp_table_2 as
(select /*+cardinality(10)*/ to_number(column_value) a fromtable(str_to_table('1,2,3,4,5,6',',')))
select c
from table_1
where a in (select a from temp_table_2) /*mmm*/
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.03 0 48 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.20 0.41 1796 1799 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.20 0.45 1796 1847 0 6
Misses in library cacheduring parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 92
Rows Row Source Operation
------- ---------------------------------------------------
6 HASH JOIN RIGHT SEMI (cr=1799 pr=1796 pw=1796time=10 us cost=535 size=13 card=1)
6 COLLECTION ITERATOR PICKLER FETCHSTR_TO_TABLE (cr=0 pr=0 pw=0 time=2 us)
500000 TABLE ACCESS FULL TABLE_1 (cr=1799 pr=1796pw=1796 time=7604 us cost=503 size=5500000 card=500000)
********************************************************************************
with temp_table_2 as
(select /*+cardinality(10)*/ to_number(column_value) a fromtable(str_to_table('1,2,3,4,5,6',',')))
select c
from table_1
where exists (select null fromtemp_table_2 where a= table_1.a) /*mmm*/
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 48 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.15 0.48 1796 1799 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.15 0.49 1796 1847 0 6
Misses in library cacheduring parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 92
Rows Row Source Operation
------- ---------------------------------------------------
6 HASH JOIN RIGHT SEMI (cr=1799 pr=1796 pw=1796time=10 us cost=535 size=13 card=1)
6 COLLECTION ITERATOR PICKLER FETCHSTR_TO_TABLE (cr=0 pr=0 pw=0 time=2 us)
500000 TABLE ACCESS FULL TABLE_1 (cr=1799 pr=1796pw=1796 time=7559 us cost=503 size=5500000 card=500000)
some any all
在oracle数据库的比较运算符中 ANY SOME与 all
any 与 some是同样的意义，通常用在不等的比较运算中，当使用”=”的时候，则就是半连接
如
select ename, sal, comm from emp where sal < any( select commfrom emp );
与
select ename, sal, comm from emp where sal < ( select max(distinctcomm) from emp)
在业务含义上是相当的，但是oracle提供了不同的执行计划
select ename, sal, comm from emp where sal = some( select commfrom emp );
与
select ename, sal, comm from emp where sal in ( select comm from emp );
在商业含义上是一样的，在多数情况下oracle提供了半连接的执行计划
select ename, sal, comm from emp where sal > any( select commfrom emp);
与
select ename, sal, comm from emp where sal > ( select min(distinctcomm) from emp)
在业务含义上是相当的，但是oracle提供了不同的执行计划
some与 any可以互换。而all正好与some all的意思相反
select ename, sal, comm from emp where sal < all( select commfrom emp );
与
select ename, sal, comm from emp where sal < ( select min(distinctcomm) from emp)
在商业含义上是一样的.
与member一样，some any all在过程中
declare
i integer;
type type_num is table ofnumber;
c_num type_num :=type_num(1,2,3,4,5);
begin
select count(b) into i fromtable_1
where a = some(c_num);
dbms_output.put_line(i);
end;
第 8 行出现错误:
ORA-06550: 第 8 行, 第 19 列:
PLS-00642: 在 SQL 语句中不允许使用本地收集类型
原创文章，如果转载，请标注作者：田文 优快云地址：http://blog.youkuaiyun.com/tiwen818
扫一扫
618
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
