Schlagwort-Archive: Optimizer

dwh-consult

Optimizer-Modes ALL_ROWS vs FIRST_ROWS

Schreibe einen Kommentar

FIRST_ROWS and ALL_ROWS are cost based optimizer features.

Overview:

  • 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*/

 

http://docs.oracle.com/cd/E18283_01/server.112/e17110/initparams167.htm

first_rows_n
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).

first_rows
The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.

all_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).

http://oracle-online-help.blogspot.de/2007/03/optimizermode-allrows-or-firstrows.html