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