Hi Gurus/Experts,
I need advice/suggestion on the below query plan. This plan is going for Nested Loops, am not able to force to go for Hash join or Merge Join by using hints.
Can some one please help me what actually happening, and is it possible to escape from nested loops.
I don't want taking help of indexes for this scenario.
D/B : Oracle 11gR2,
create table parent as
with main as (select --+materialize
round(dbms_random.value(low=>1,high=>100)) SAMPLE_ID,dbms_random.string(opt=>'U',len=>10) SAMPLE_TEXT,rownum id from dual
connect by level<=100000)
select sample_id,rpad(sample_text,10,'*') padding,sample_text,id from main order by id
create table child as
with main as (select --+materialize
round(dbms_random.value(low=>1,high=>100)) SAMPLE_ID,dbms_random.string(opt=>'U',len=>10) SAMPLE_TEXT,rownum id from dual
connect by level<=10000)
select sample_id,rpad(sample_text,10,'*') padding,sample_text,id from main order by id
select --+use_hash(p c)
p.id from parent p,child c where p.id<>c.id
Plan
SELECT STATEMENT ALL_ROWSCost: 973,076 Bytes: 8,999,910,000 Cardinality: 999,990,000
3 NESTED LOOPS Cost: 973,076 Bytes: 8,999,910,000 Cardinality: 999,990,000
1 TABLE ACCESS FULL TABLE APP.CHILD Cost: 12 Bytes: 40,000 Cardinality: 10,000
2 TABLE ACCESS FULL TABLE APP.PARENT Cost: 97 Bytes: 499,995 Cardinality: 99,999