create table t1 as
select
level as id
, mod(level, 6) as val
from dual
connect by level < 50Table created.
create table t2 as
select
level as id
, mod(level, 3) as val
from dual
connect by level < 8Table created.
explain plan set statement_id = 'DIRECT IN' for
select *
from t1
where id in (
select f.id
from t2 f
where f.val = 2
)Statement processed.
select *
from table(dbms_xplan.display(
statement_id => 'DIRECT IN'
, format => 'BASIC +PREDICATE'
))| PLAN_TABLE_OUTPUT |
|---|
| Plan hash value: 1713220790 |
| ----------------------------------- |
| | Id | Operation | Name | |
| ----------------------------------- |
| | 0 | SELECT STATEMENT | | |
| |* 1 | HASH JOIN SEMI | | |
| | 2 | TABLE ACCESS FULL| T1 | |
| |* 3 | TABLE ACCESS FULL| T2 | |
| ----------------------------------- |
| Predicate Information (identified by operation id): |
| --------------------------------------------------- |
| 1 - access("ID"="F"."ID") |
| 3 - filter("F"."VAL"=2) |
explain plan set statement_id = 'PRECOMPUTE IN' for
select *
from t1
where id in (
select /*+precompute_subquery*/ f.id
from t2 f
where f.val = 2
)Statement processed.
select *
from table(dbms_xplan.display(
statement_id => 'PRECOMPUTE IN'
, format => 'BASIC +PREDICATE'
))| PLAN_TABLE_OUTPUT |
|---|
| Plan hash value: 3617692013 |
| ---------------------------------- |
| | Id | Operation | Name | |
| ---------------------------------- |
| | 0 | SELECT STATEMENT | | |
| |* 1 | TABLE ACCESS FULL| T1 | |
| ---------------------------------- |
| Predicate Information (identified by operation id): |
| --------------------------------------------------- |
| 1 - filter("ID"=2 OR "ID"=5) |