select * from employee, department where employee.id = department.id
department.id is primary key on department
My question is will index created on department.id(internally created index on primary key by oracle) be used while employee.id = department.id i.e. while finding corresponding row_id in department table
Best Answer
HASH JOIN - no
NESTED LOOPS JOIN - yes, depends on statistics
Oracle can not use index range scan for join predicates when performing a hash join. But it can use indexes for other predicates for the
probe table("second" table), so hash join does not automatically result in a full table scan of the probe table.