userimage

What is the internal join strategy of Oracle db engine?

We do know there are different type of joins like outer join, left join, right join, self join etc. But my interviewer wanted to know the actually joins used by oracle db engines while executing the query like after parsing the SQL query. 

userimage
Moe

Yeah, for a developer the joins are like you have mentioned but actually oracle db engine internally uses 3 type of joins

1) Nested loop joins

This join oracle uses when we use:
a) Can be use equi or non equi join.
b) For small tables
c) Index on join columns

2) Hash join

This join oracle uses when we use:
a) For large/small tables
b) Only if there are equality predicates
c) A large portion of table selected for join

3) Short merge join.

This join oracle uses when we use:
a) Can be use equi or non equi join.
b) For large tables
c) No indexed column joins

Answer is