|
CHOOSE
|
The optimizer chooses between a cost-based approach and a rule-based approach, depending on whether statistics are available. This is the default value.
- If the data dictionary contains statistics for at least one of the accessed tables, then the optimizer uses a cost-based approach and optimizes with a goal of best throughput.
- If the data dictionary contains only some statistics, then the cost-based approach is still used, but the optimizer must guess the statistics for the subjects without any statistics. This can result in suboptimal execution plans.
- If the data dictionary contains no statistics for any of the accessed tables, then the optimizer uses a rule-based approach.
|
|
ALL_ROWS
|
The optimizer uses a cost-based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).
|
|
FIRST_ROWS_n
|
The optimizer uses a cost-based approach, regardless of the presence of statistics, and optimizes with a goal of best response time to return the first n number of rows; n can equal 1, 10, 100, or 1000.
|
|
FIRST_ROWS
|
The optimizer uses a mix of cost and heuristics to find a best plan for fast delivery of the first few rows.
Note: Using heuristics sometimes leads the CBO to generate a plan with a cost that is significantly larger than the cost of a plan without applying the heuristic. FIRST_ROWS is available for backward compatibility and plan stability.
|
|
RULE
|
The optimizer chooses a rule-based approach for all SQL statements regardless of the presence of statistics.
|
