First page Back Continue Last page Graphics

Optimizer Mode


Notes:

This example demonstrates a nested loops join. Note that I altered the session’s optimizer mode to FIRST_ROWS_10, telling the optimizer to optimize with the goal of returning the first row of the result set back as quickly as possible – at the expense of larger cost and elapsed time to return all the rows. When the session was left with the default optimizer mode of CHOOSE, CBO chose a hash join for this query, which took longer to return the first row, but returned the entire result more quickly.
Refer to the supplied files nested_loops.sql, nested_loops_trace.txt and nested_loops_10053.sql for a demonstration of this and other nested loops concepts.
Supplemental Notes
The OPTIMIZER_MODE initialization parameter can be set at the database or session level to one of the following values: first_rows_1000, first_rows_100, first_rows_10, first_rows_1, first_rows, all_rows, choose or rule.
The USE_NL and FIRST_ROWS hints will also request a nested loops join.