What are the different approaches used by Optimizer in choosing an execution plan ?
Rule-based and Cost-based.
The OPTIMIZER_MODE initialization parameter Statistics in the Data Dictionary the OPTIMIZER_GOAL parameter of the ALTER SESSION command hints
COST and RULE.
Latest Answer : One can specify COST , RULE or CHOOSE for OPTIMIZER _MODE in init.ora file ...
What is the effect of setting the value "CHOOSE" for OPTIMIZER_GOAL, parameter of the ALTER SESSION Command ?
The Optimizer chooses Cost_based approach and optimizes with the goal of best throughput if statistics for atleast one of the tables accessed by the SQL statement exist in the data dictionary.
What is the effect of setting the value "ALL_ROWS" for OPTIMIZER_GOAL parameter of the ALTER SESSION command ?
This value causes the optimizer to the cost-based approach for all SQL statements in the session regardless of the presence of statistics and to optimize with
Choosing an executing planbased on the access paths available and the ranks of these access paths.
Considering available access paths and determining the most efficient execution plan based on statistics in the data dictionary for the tables accessed
Latest Answer : SQL> create table m( n number);Table created. 1* insert into m values(&1)SQL> /Enter value for 1: 1old 1: insert into m values(&1)new 1: insert into m values(1)1 row created.SQL> /Enter value for 1: 2old ...
