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



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.

  • why B tree index(created on department.id ) can not be used while employee.id = department.id to find corresponding row from department table ? Oct 29 '17 at 13:30
  • @user3198603 Because a hash join searches for hash values, not actual values. Indexes contain actual values, not hash values. Oct 29 '17 at 13:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.