Simple demonstration
The following two tables are used for a simple demostration for subquery unnesting:
create table tq84_A (id number, tx varchar2(3));
create table tq84_B (id_a number);
insert into tq84_A select level*37, 'X' from dual connect by level < 1000;
insert into tq84_B select level+91 from dual connect by level < 600;
Gathering the tables' statistics:
begin
dbms_stats.gather_table_stats(user, 'tq84_A');
dbms_stats.gather_table_stats(user, 'tq84_B');
end;
/
Let Oracle explain the
execution plan for a query that involves subquery unnesting:
explain plan for
select
id,
tx
from
tq84_A
where
id in (select id_a from tq84_B);
select * from table(dbms_xplan.display(format=>'basic'));
--
-- ---------------------------------------
-- | Id | Operation | Name |
-- ---------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | HASH JOIN RIGHT SEMI| |
-- | 2 | TABLE ACCESS FULL | TQ84_B |
-- | 3 | TABLE ACCESS FULL | TQ84_A |
-- ---------------------------------------
Cleaning up:
drop table tq84_A;
drop table tq84_B;