Search notes:
Oracle SQL Plan operation MERGE JOIN (CARTESIAN)
The second child row source is a
BUFFER SORT
operation.
drop table tq84_A;
drop table tq84_B;
begin
dbms_random.seed(42);
end;
/
create table tq84_A as
select
trunc(dbms_random.value(1, 50)) as nm,
dbms_random.string('a', 20) as tx
from
dual connect by level <= 12;
create table tq84_B as
select
trunc(dbms_random.value(1, 50)) as nm,
dbms_random.string('a', 20) as tx
from
dual connect by level <= 7;
begin
dbms_stats.gather_table_stats(user, 'tq84_A');
dbms_stats.gather_table_stats(user, 'tq84_B');
end;
/
explain plan for
select
a.nm nm_a,
b.nm nm_b,
a.tx tx_a,
b.tx tx_b
from
tq84_A a,
tq84_B b
;
--
-- ---------------------------------------
-- | Id | Operation | Name |
-- ---------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | MERGE JOIN CARTESIAN| |
-- | 2 | TABLE ACCESS FULL | TQ84_B |
-- | 3 | BUFFER SORT | |
-- | 4 | TABLE ACCESS FULL | TQ84_A |
-- ---------------------------------------
select * from table(dbms_xplan.display(format=>'basic'));