Search notes:

Oracle Plan Operation FILTER for Uncorrelated Subqueries

Create two demonstration tables and fille them with some dummy data:

create table tq84_A (id integer, tx  varchar2(10));
create table tq84_B (id integer, val number);

begin
insert into tq84_A values(1, 'A');
insert into tq84_A values(2, 'B');
insert into tq84_A values(3, 'C');

insert into tq84_B values(10, 5 );
insert into tq84_B values(20, 2 );
insert into tq84_B values(30, 4 );
end;
/

Show the SQL execution plan for a query with an uncorrelated subquery (where exists …):

explain plan for
select
   *
from
   tq84_A
where
   exists (
      select
         null
      from
         tq84_B
      where
         val = 6
   );
select * from table(dbms_xplan.display(format=>'basic'));
--
-- -------------------------------------
-- | Id  | Operation          | Name   |
-- -------------------------------------
-- |   0 | SELECT STATEMENT   |        |
-- |   1 |  FILTER            |        |
-- |   2 |   TABLE ACCESS FULL| TQ84_A |
-- |   3 |   TABLE ACCESS FULL| TQ84_B |
-- -------------------------------------

Run the query and show if a row source was started. Because the subquery does not return a record, the SQL executor does not have run («start») the full table access on tq84_A:

select /*+ gather_plan_statistics */
   *
from
   tq84_A
where
   exists (
      select
         null
      from
         tq84_B
      where
         val = 6
   );

select * from table(dbms_xplan.display_cursor(format=>'adaptive rowstats -note'));
--
-- ----------------------------------------------------------------
-- | Id  | Operation          | Name   | Starts | E-Rows | A-Rows |
-- ----------------------------------------------------------------
-- |   0 | SELECT STATEMENT   |        |      1 |        |      0 |
-- |*  1 |  FILTER            |        |      1 |        |      0 |
-- |   2 |   TABLE ACCESS FULL| TQ84_A |      0 |      3 |      0 |
-- |*  3 |   TABLE ACCESS FULL| TQ84_B |      1 |      1 |      0 |
-- ----------------------------------------------------------------
--  
-- Predicate Information (identified by operation id):
-- ---------------------------------------------------
--  
--    1 - filter( IS NOT NULL)
--    3 - filter("VAL"=6)

Run a version of the query where the subquery returns a row and thus makes the outer query return all records of tq84_A (the corresponding full table access is «started»):

select /*+ gather_plan_statistics */
   *
from
   tq84_A
where
   exists (
      select
         null
      from
         tq84_B
      where
         val = 5
   );

select * from table(dbms_xplan.display_cursor(format=>'adaptive rowstats -note'));
--
-- ----------------------------------------------------------------
-- | Id  | Operation          | Name   | Starts | E-Rows | A-Rows |
-- ----------------------------------------------------------------
-- |   0 | SELECT STATEMENT   |        |      1 |        |      3 |
-- |*  1 |  FILTER            |        |      1 |        |      3 |
-- |   2 |   TABLE ACCESS FULL| TQ84_A |      1 |      3 |      3 |
-- |*  3 |   TABLE ACCESS FULL| TQ84_B |      1 |      1 |      1 |
-- ----------------------------------------------------------------
--  
-- Predicate Information (identified by operation id):
-- ---------------------------------------------------
--  
--    1 - filter( IS NOT NULL)
--    3 - filter("VAL"=5)

Cleaning up:

drop table tq84_A;
drop table tq84_B;

Index