-----------------------------------------------------------------------------------------------------
| 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(s