Valid optimization methods - Oracle Transaction

Discussion

• Nirja Shah   -Posted on 22 Sep 15
  - Cost based optimizer
  - Query optimization is the overall process of choosing the most efficient means of executing a SQL statement.
  
  - SQL is a nonprocedural language, so the optimizer is free to merge, reorganize, and process in any order.
  
  - The database optimizes each SQL statement based on statistics collected about the accessed data.
  
  - When generating execution plans, the optimizer considers different access paths and join methods. Factors considered by the optimizer include:
  
  1. System resources, which includes I/O, CPU, and memory
  2. Number of rows returned
  3. Size of the initial data sets
  
  - The cost is a number that represents the estimated resource usage for an execution plan.
  
  - The optimizer assigns a cost to each possible plan, and then chooses the plan with the lowest cost.
  
  - For this reason, the optimizer is sometimes called the cost-based optimizer (CBO) to contrast it with the legacy rule-based optimizer (RBO).
  
  - Rule based optimizer
  - The Rule Based Optimizer (RBO) is now obsolete in Oracle 10g.
  
  - The functionality is still present but no new functionality has been included in it and it is no longer supported by Oracle.
  
  - It is only present to provide backwards compatibility during the migration to the query optimizer (Cost Based Optimizer).
  
  - The results of this osolescence are:
  
  1. The CHOOSE and RULE options for the OPTIMIZER_MODE parameter still exist but are no longer supported.
  2. The default value for the OPTIMIZER_MODE parameter is ALL_ROWS.
  3. The CHOOSE and RULE optimizer hints still exist but are no longer supported.
  4. Code requiring the RBO must be migrated to use the query optimizer.

➨ Post your comment / Share knowledge