Multiple choice

The cost-based optimizer can choose between a nested loops join and a sort merge join operation. All tables are analyzed and the OPTIMIZER_MODE is set to FIRST_ROWS. Which of the following execution plans will be the result?

  1. The sort-merge join

  2. The nested loops join

  3. This depends on some sort parameter values

  4. This depends on the number of rows in each table
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

When OPTIMIZER_MODE is set to FIRST_ROWS, Oracle's cost-based optimizer prioritizes returning the first rows quickly rather than minimizing total resource usage. A nested loops join is typically preferred over sort-merge join in FIRST_ROWS mode because it can start returning rows immediately without waiting for the entire sorting operation to complete, making it more responsive for interactive queries.

Keep practicing — related questions