This tutorial introduces the evolution of SQL optimization and the enhancements to cost-based SQL optimization.  The focus of this tutorial is on the evolution of SQL optimization and understanding the techniques for using the rule-based and cost-based optimizer to achieve optimal SQL execution plans.

 

Topics in this tutorial will also include new indexing structures and Oracle SQL, as well as new execution plan methods and the adoption by Oracle of the ISO 99 SQL standard for table joins.  We will also explore Oracle indexing and learn the different types of indexes and see how they are used to improve SQL execution speed.

 

Understanding SQL optimization is critical to managing SQL execution.  Oracle offers a wealth of tools that the database professional can use to improve the efficiency of SQL execution.  These tools include automatic query re-write, optimizer plan stability, query hints and function-based indexes.

 

In this tutorial we will explore the functionality of the SQL optimizers and perform some hands-on exercises to help you internalize the concepts.

 

Upon completion of this tutorial, you will be able to:

 

• Describe the history of the rule-based SQL optimizer
• Anticipate RBO behavior using driving tables and using order of the query predicates
• Implement optimizer plan stability
• Describe the history of the cost-based optimizer
• List the new indexing structures in Oracle SQL
• Learn advanced Oracle optimizer methods
• Describe the ISO 99 SQL standard