Hi,
I have a query in which I want to join 2 tables with nested loop joins.
The query does sometimes takes nested loop join and sometimes doesn't. I'm forcing an hint to make sure it always goes for nested loop.
I was just concerned about the order of the 2 tables in this hint. EMP1 is the smallest table and EMP2 is the largest table. EMP2 has 400 times more records than EMP1. What should be the order of the tables in hint?
/*+ USE_NL(emp1 emp2) */
Note: The actual query is something different. I has just put a below example.
with emp1 as
(
select * from
employee where dept_id = 1
),
emp2 as
(
select * from
employee where dept_id between 2 and 20
)
select /*+ USE_NL(emp1 emp2) */ *
from emp1, emp2
where emp1.role_id = emp2.role_id