HI ALL:
My Oracle version is 11.2.0.3.0
SQL> create table t1 (c1 char(2) primary key, c2 char(1) not null);
Table created.
SQL> insert into t1 values('A ','A');
1 row created.
SQL> select c1 from t1
where c1 in (select c2 from t1 union all select '0'||c2 from t1 2 );
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3464558641
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 14 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 14 | 4 (0)| 00:00:01 |
| 2 | VIEW | VW_NSO_1 | 2 | 6 | 4 (0)| 00:00:01 |
| 3 | HASH UNIQUE | | 2 | 6 | 4 (50)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
| 5 | TABLE ACCESS FULL| T1 | 1 | 3 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL| T1 | 1 | 3 | 2 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | SYS_C003900 | 1 | 4 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("C1"="C2")
Note
-----
- dynamic sampling used for this statement (level=2)
Use hint :
SQL> select /*+ full(t1) */ t1.c1 from t1
w 2 here t1.c1 in (select c2 from t1 union all select '0'||c2 from t1);
C1
--
A
Execution Plan
----------------------------------------------------------
Plan hash value: 3789789730
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 8 | 6 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | T1 | 1 | 4 | 2 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
|* 4 | TABLE ACCESS FULL| T1 | 1 | 3 | 2 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| T1 | 1 | 3 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS ( (SELECT "C2" FROM "T1" "T1" WHERE "C2"=:B1)
UNION ALL (SELECT '0'||"C2" FROM "T1" "T1" WHERE '0'||"C2"=:B2)))
4 - filter("C2"=:B1)
5 - filter('0'||"C2"=:B1)
Note
-----
- dynamic sampling used for this statement (level=2)
SQL>
Appreciate your help.