Search notes:
Oracle SQL Plan operation: HASH JOIN SEMI
create table tq84_dest( id number, val varchar2(5));
create table tq84_src ( id number, val varchar2(5));
begin
insert into tq84_dest values(1, 'i' );
insert into tq84_dest values(3, 'iii');
insert into tq84_dest values(4, 'iv' );
insert into tq84_src values (1, 'one');
insert into tq84_src values (2, 'two');
insert into tq84_src values (4, 'four');
commit;
end;
/
explain plan for
update tq84_dest d
set d.val = (select s.val from tq84_src s where s.id = d.id)
where
exists (select null from tq84_src s where s.id = d.id);
select * from table(dbms_xplan.display(format=>'basic'));
--
-- -----------------------------------------
-- | Id | Operation | Name |
-- -----------------------------------------
-- | 0 | UPDATE STATEMENT | |
-- | 1 | UPDATE | TQ84_DEST |
-- | 2 | HASH JOIN SEMI | |
-- | 3 | TABLE ACCESS FULL| TQ84_DEST |
-- | 4 | TABLE ACCESS FULL| TQ84_SRC |
-- | 5 | TABLE ACCESS FULL | TQ84_SRC |
-- -----------------------------------------
drop table tq84_dest;
drop table tq84_src;