Search notes:

Oracle: Execution plan for subquery in WHERE clause

set serveroutput off

drop table tq84_A;
create table tq84_A (
   id        integer,
   grp       char(1),
   val       number(3,1)
);

begin
   insert into tq84_A values (1, 'A', 21.4);
   insert into tq84_A values (2, 'A',  7.1);
   insert into tq84_A values (3, 'B', 55.9);
   insert into tq84_A values (4, 'B', 36.8);
   insert into tq84_A values (5, 'B', 15.8);
   insert into tq84_A values (6, 'C', 62.4);

   dbms_stats.gather_table_stats(user, 'tq84_A');
end;
/

select /*+ monitor qb_name(main) */
   *
from
  tq84_A a
where
  id = (select /*+ qb_name(sub) */ min(i.id) from tq84_A i where i.grp = a.grp);

select * from table(dbms_xplan.display_cursor(format => 'rowstats -rows alias last'));
--
-- ----------------------------------------------------------
-- | Id  | Operation            | Name    | Starts | A-Rows |
-- ----------------------------------------------------------
-- |   0 | SELECT STATEMENT     |         |      1 |      3 |
-- |*  1 |  HASH JOIN           |         |      1 |      3 |
-- |   2 |   VIEW               | VW_SQ_1 |      1 |      3 |
-- |   3 |    HASH GROUP BY     |         |      1 |      3 |
-- |   4 |     TABLE ACCESS FULL| TQ84_A  |      1 |      6 |
-- |   5 |   TABLE ACCESS FULL  | TQ84_A  |      1 |      6 |
-- ----------------------------------------------------------
--
-- Query Block Name / Object Alias (identified by operation id):
-- -------------------------------------------------------------
--
--    1 - SEL$43BFB197
--    2 - SEL$75CB63B7 / VW_SQ_1@SEL$EDD5E67B
--    3 - SEL$75CB63B7
--    4 - SEL$75CB63B7 / I@SUB
--    5 - SEL$43BFB197 / A@MAIN
--
-- Predicate Information (identified by operation id):
-- ---------------------------------------------------
--
--    1 - access("ID"="MIN(I.ID)" AND "ITEM_1"="A"."GRP")

See also

Subqueries in where clauses
SQL execution plans

Index