Referring to the article How to split comma separated string and pass to IN clause of select statement, the query has the following plan:
Query1:
select * from emp where ename in (
select regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) from dual
connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null );
Plan1:
Plan hash value: 4242290184
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 133 | 7 (29)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 133 | 7 (29)| 00:00:01 |
| 2 | VIEW | VW_NSO_1 | 1 | 46 | 3 (34)| 00:00:01 |
| 3 | HASH UNIQUE | | 1 | | 3 (34)| 00:00:01 |
|* 4 | CONNECT BY WITHOUT FILTERING| | | | | |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMP | 14 | 1218 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ENAME"="$nso_col_1")
4 - filter( REGEXP_SUBSTR ('SMITH,ALLEN,WARD,JONES','[^,]+',1,LEVEL) IS NOT NULL)
However the below query generates the plan that i want:
Query2:
select * from emp where ename in ('SMITH','ALLEN','WARD','JONES');
Plan2:
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 348 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 4 | 348 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"='ALLEN' OR "ENAME"='JONES' OR "ENAME"='SMITH' OR
"ENAME"='WARD')
Can I modify the query1 to have plan2?