Lesson 5

We have now covered the basic principles behind the default optimizer modes for Oracle and we are now ready to delve deeper into the characteristics of each.
You should now be able to:

1. Describe each optimizer mode
2. View and reset the default optimizer mode
3. Override the default optimizer mode for a query

Here are the terms from this module that may have been new to you:

1. all_rows hint: The default cost-based optimizer method, all_rows maximizes overall execution time.
2. Choose optimizer: This is a default optimizer_mode in the init.ora file. If statistics exist for any table in a query, CHOOSR will invoke the cost-based optimizer.
3. Cost-based optimizer: This is the latest SQL optimizer that uses object statistics to make intelligent table access decisions.
4. Execution plan: This is the compiled execution path to Oracle data.
5. first_rows hint: This is a cost-based SQL optimizer hint that delivers rows as quickly as possible.
6. Optimizer mode: This is the default optimizer as set in the init.ora file.
7. Rule-based optimizer: This is the first Oracle SQL optimizer; it uses general rules to formulate execution plans.

You will explore the rule-based optimizer.

Click the Quiz link below to take a quiz and test your knowledge of the concepts in this module.
SQL Optimizer Mode Defaults- Quiz