FIRST_ROWS and ALL_ROWS are cost based optimizer features.
- Default: ALL_ROWS
- Change: alter [system|session] OPTIMIZER_MODE = [ALL_ROWS|FIRST_ROWS|FIRST_ROWS_1|FIRST_ROWS_10|FIRST_ROWS_100|FIRST_ROWS_1000]
- Check: SHOW PARAMETER OPTIMIZER_MODE (with dba-role)
- SQL – Hint for retrieving a few rows: /*+ FIRST_ROWS*/
The optimizer uses a cost-based approach and optimizes with a goal of best response time to return the first n rows (where n = 1, 10, 100, 1000).
The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.
The optimizer uses a cost-based approach for all SQL statements in the session and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).