Valid optimization methods - Oracle Transaction

Q.  Which of the following are valid optimization methods?
- Published on 04 Aug 15

a. Rule based
b. Cost based
c. Both a and b
d. Query based

ANSWER: Both a and b
 

    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


Enter the code shown above:
 
(Note: If you cannot read the numbers in the above image, reload the page to generate a new one.)