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