| 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