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

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 8 attempt to write a readonly database in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php:78 Stack trace: #0 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(78): PDOStatement->execute(Array) #1 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(30): insert_webrequest_('/notes/developm...', 1758199316, '216.73.216.150', 'Mozilla/5.0 App...', NULL) #2 /home/httpd/vhosts/renenyffenegger.ch/httpsdocs/notes/development/databases/Oracle/SQL/statement/execution/plan/operations/hash-join/semi/index(74): insert_webrequest() #3 {main} thrown in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php on line 78