Defining the Optimizer Mode

Prior to Oracle 10g database, a SQL statement will default to the Cost Based Optimizer if any one of the tables involved in the statement has statistics present, unless the OPTIMIZER_MODE initialization parameter is set to RULE. In Oracle 10g database, a SQL statement will always use the CBO unless the /*+ RULE */ hint is present. The optimizer makes an educated guess as to the best access path for the other tables based on statistical information in the data dictionary.

The RDBMS kernel defaults to using the Cost Based Optimizer under a number of situations. The following list details those situations where the CBO will be used:

Initialization parameter: OPTIMIZER_MODE = CHOOSE (Oracle 7 to Oracle 9i, this parameter value is deprecated in Oracle 10g); statistics exist for at least one table involved in the statement

Initialization parameter: OPTIMIZER_MODE = FIRST_ROWS (Oracle 7 to Oracle 9i); statistics exist for at least one table involved in the statement

Initialization parameter: OPTIMIZER_MODE = FIRST_ROWS (Oracle 10g); regardless of presence of statistics, CBO will always be used with this parameter setting.

Initialization parameter: OPTIMIZER_MODE = FIRST_ROWS_n (where n = 1, 10, 100, or 1000; Oracle 9i to Oracle 10g); regardless of presence of statistics, CBO will always be used with this parameter setting.

Initialization parameter: OPTIMIZER_MODE = ALL_ROWS (Oracle 7 to Oracle 10g); regardless of presence of statistics, CBO will always be used with this parameter setting.

ALTER SESSION SET OPTIMIZER_MODE = CHOOSE (Oracle 7 to Oracle 9i, this parameter is deprecated in Oracle 10g); statistics exist for at least one table involved in the statement

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS (Oracle 7 to Oracle 9i); statistics exist for at least one table involved in the statement

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS (Oracle 10g); regardless of presence of statistics, CBO will always be used with this parameter setting.

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_n (where n = 1, 10, 100, or 1000; Oracle 9i to Oracle 10g); regardless of presence of statistics, CBO will always be used with this parameter setting.

ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS (Oracle 7 to Oracle 10g)

Hint: SELECT /*+ FIRST_ROWS */. . .

Hint: SELECT /*+ FIRST_ROWS_n */. . . (where n = 1, 10, 100, or 1000)

Hint: SELECT /*+ ALL_ROWS */. . .