How does cost-based query optimization decide between a nested loop join and a hash join?
Correct! Well done.
Incorrect.
The correct answer is D) It estimates the cost of each strategy using table sizes, available indexes, and selectivity statistics — favoring nested loop joins when one side is small and indexed, and hash joins when both sides are large and unsorted with no useful index
Correct Answer
It estimates the cost of each strategy using table sizes, available indexes, and selectivity statistics — favoring nested loop joins when one side is small and indexed, and hash joins when both sides are large and unsorted with no useful index
The cost-based optimizer compares estimated I/O and CPU costs for each join strategy given current statistics. Nested loop joins excel when the outer side is small and the inner side has a usable index; hash joins are typically chosen for large, unindexed equi-joins because building an in-memory hash table avoids repeated scans.