Search notes:

Oracle: Example for Adaptive Plan

This is an example to demonstrate Oracle's adaptive execution plan.

Preparation of tables and indexes

We're going to join two tables.
Both tables have an index, a primary key and an «ordinary index»: 
create table tq84_A(id integer primary key, num integer, pad varchar2(100));
create table tq84_B(id integer            , num integer, pad varchar2(100));

create index tq84_B_ix on tq84_B(id);

Inserting data

We populate the tables each with 1000 records.
The value of of num for every 2nd row is 999999, for the other rows, num is unique 
insert into tq84_A
select
   level,
   case when mod(level, 2) = 0 then 999999 else level/2 end,
   rpad('x', 100, 'x')
from
   dual connect by level <= 1000;
   
insert into tq84_B
select
   level,
   case when mod(level, 2) = 1 then 999999 else level/2 end,
   rpad('x', 100, 'x')
from
   dual connect by level <= 1000;

Gather statistics:

It never hurts to gather table statistics. 
begin
   dbms_stats.gather_table_stats(user, 'tq84_A');
   dbms_stats.gather_table_stats(user, 'tq84_B');
end;
/

Running first query

We execute the first query.
Because we select with a.num = 4, we will join both tables on a unique value and the query will return count(*) of 1.
The STATISTICS COLLECTOR will determine that a nested loop is more optimal for the query than a hash join (note the minus sign (-) signs in the Id column of the output): 
select /*+ gather_plan_statistics */
   count(*) cnt
from
   TQ84_A a                   join
   TQ84_B b on a.id = b.id
where
   a.num = 4
;

select * from table(dbms_xplan.display_cursor(format=>'adaptive rowstats -predicate'));
--
-- -------------------------------------------------------------------------
-- | Id  | Operation                | Name      | Starts | E-Rows | A-Rows |
-- -------------------------------------------------------------------------
-- |   0 | SELECT STATEMENT         |           |      1 |        |      1 |
-- |   1 |  SORT AGGREGATE          |           |      1 |      1 |      1 |
-- |-  2 |   HASH JOIN              |           |      1 |      2 |      1 |
-- |   3 |    NESTED LOOPS          |           |      1 |      2 |      1 |
-- |-  4 |     STATISTICS COLLECTOR |           |      1 |        |      1 |
-- |   5 |      TABLE ACCESS FULL   | TQ84_A    |      1 |      2 |      1 |
-- |   6 |     INDEX RANGE SCAN     | TQ84_B_IX |      1 |      1 |      1 |
-- |-  7 |    INDEX FAST FULL SCAN  | TQ84_B_IX |      0 |      1 |      0 |
-- -------------------------------------------------------------------------
--  
-- Note
-- -----
--    - this is an adaptive plan (rows marked '-' are inactive)

Execute another query

This time, we select on a.num = 999999 which blows up the query because every second row of TQ84_A must be joined to TQ84_B, hence count(*) will be 500.
The statement executor will determine that a hash join is more beneficial for the query (note again the minus sign in the output): 
select /*+ gather_plan_statistics */
  count(*) cnt
from
   TQ84_A a                   join
   TQ84_B b on a.id = b.id
where
   a.num = 999999
;

select * from table(dbms_xplan.display_cursor(format=>'adaptive rowstats -predicate'));
--
-- -------------------------------------------------------------------------
-- | Id  | Operation                | Name      | Starts | E-Rows | A-Rows |
-- -------------------------------------------------------------------------
-- |   0 | SELECT STATEMENT         |           |      1 |        |      1 |
-- |   1 |  SORT AGGREGATE          |           |      1 |      1 |      1 |
-- |   2 |   HASH JOIN              |           |      1 |      2 |    500 |
-- |-  3 |    NESTED LOOPS          |           |      1 |      2 |    500 |
-- |-  4 |     STATISTICS COLLECTOR |           |      1 |        |    500 |
-- |   5 |      TABLE ACCESS FULL   | TQ84_A    |      1 |      2 |    500 |
-- |-  6 |     INDEX RANGE SCAN     | TQ84_B_IX |      0 |      1 |      0 |
-- |   7 |    INDEX FAST FULL SCAN  | TQ84_B_IX |      1 |      1 |   1000 |
-- -------------------------------------------------------------------------
--  
-- Note
-- -----
--    - this is an adaptive plan (rows marked '-' are inactive)
Cleaning up: 
drop table tq84_A;
drop table tq84_B;

Index