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;

See also

Semi joins
Plan operations

Index