半连接(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