Scalar subquery
create table tq84_lookup (
txt varchar2(10),
id number (8 ) not null,
--
constraint lookup_pk primary key (txt)
);
create table tq84_val (
id number(8 ) ,
val number(5,1) not null,
--
constraint val_pk primary key (id)
);
begin
insert into tq84_lookup values ('one' , 1);
insert into tq84_lookup values ('two' , 2);
insert into tq84_lookup values ('three', 3);
insert into tq84_val values(1, 1.1);
insert into tq84_val values(2, 22.2);
insert into tq84_val values(3, 333.3);
end;
/
explain plan for
select
val
from
tq84_val
where
id = (select id from tq84_lookup where txt = 'two')
;
select * from table(dbms_xplan.display(format=>'basic'));
--
-- ----------------------------------------------------
-- | Id | Operation | Name |
-- ----------------------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | NESTED LOOPS | |
-- | 2 | TABLE ACCESS BY INDEX ROWID| TQ84_LOOKUP |
-- | 3 | INDEX UNIQUE SCAN | LOOKUP_PK |
-- | 4 | TABLE ACCESS BY INDEX ROWID| TQ84_VAL |
-- | 5 | INDEX UNIQUE SCAN | VAL_PK |
-- ----------------------------------------------------