we use Oracle11gR2 on win2008R2.
we have very long complex query with multiple WITH phrase and multiple table join.
I looked EXECUTION PLAN from SQL Developper(F10).
In the plan it looks it has large cost MERGE JOIN using full table scan of 2 tables which has large cost.
If we have table joins like below
Select productA, productB ..... from
TableA A
inner join TableB B
on A.id=B.id
inner join TableC C
on B.id=C.id
inner join TableD D
on C.id=D.id
inner join TableE E
on D.id=E.id ;
How coukd I write hint to use USE_NL for TableC and TableD join ??