Thanks for the question, Prateek.
Asked: May 22, 2018 - 2:18 pm UTC
Last updated: May 23, 2018 - 8:14 am UTC
Version: 12 C
Viewed 10K+ times! This question is
You Asked
Hi Tom,
Can you help me in understanding how optimizer decides which join ( hash or nested loop) it will use for joining. Also which is the driving table in nested loop.
There are lot of confusing answers on this on internet, which one to rely.
and Connor said...
Here's a little video I made about the types of joins from a conceptual point
In terms of the optimizer decisions, it is the same as *any* optimizer decision, namely:
- evaluate the estimated cost (resource consumption) and hence estimated elapsed time of each potential strategy
- pick the one that looks cheapest
To understand a driving table, recall that a nested loop is:
A- read one table
B- use the data from that table to perform a lookup into a second table
The table that you read from initially (A) is the "driving" table.
Is this answer out of date? If it is, please let us know via a Comment