Nested nested-loops is a technique designed to optimize an «ordinary» nested loop. Some sources seem to refer to nested nested-loops as «nested join loop batching».
create table tq84_P (
id number,
val_1 varchar2( 10),
val_2 varchar2(1000),
--
constraint tq84_P_pk primary key(id)
);
create table tq84_C (
col_1 number,
col_2 varchar2(20),
id_p not null,
--
constraint tq84_C_fk foreign key (id_p) references tq84_P
);
begin
dbms_random.seed(42);
end;
/
insert into tq84_P
select
mod(773*level, 1009), -- 773 and 1009 are primes
dbms_random.string('a', 10),
rpad('x', 1000, 'x')
from
dual connect by level <= 1009;
insert into tq84_C
select
round(dbms_random.value(1,1000), 1),
dbms_random.string('a', 20),
trunc(dbms_random.value(100, 105))
from
dual connect by level <= 100*1000;
create index tq84_C_ix on tq84_C(col_1);
begin
dbms_stats.gather_table_stats(user, 'tq84_P');
dbms_stats.gather_table_stats(user, 'tq84_C');
end;
/
explain plan for
select
c.col_2,
p.val_1
from
tq84_C c join
tq84_P p on c.id_p = p.id
where
c.col_1 = 12.3;
select * from table(dbms_xplan.display(format => 'basic +note'));
--
-- -----------------------------------------------------------
-- | Id | Operation | Name |
-- -----------------------------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | NESTED LOOPS | |
-- | 2 | NESTED LOOPS | |
-- | 3 | TABLE ACCESS BY INDEX ROWID BATCHED| TQ84_C |
-- | 4 | INDEX RANGE SCAN | TQ84_C_IX |
-- | 5 | INDEX UNIQUE SCAN | TQ84_P_PK |
-- | 6 | TABLE ACCESS BY INDEX ROWID | TQ84_P |
-- -----------------------------------------------------------
--
-- Note
-- -----
-- - this is an adaptive plan
drop table tq84_C;
drop table tq84_P;