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) |
|---|