Unit 3: Query Optimization
Queries written in SQL do not always run efficiently. However, there are a number of techniques that you can use to optimize them. In this unit, we will first look at indexes. When tables are indexed on certain fields, the relational database management system can take advantage of quicker retrieval time when searching through the table. Once you have a firm understanding of indexes, please move on to the discussion of query optimization.
Completing this unit should take you approximately 13 hours.
3.1: Stages in Query Processing
Watch this lecture for a introduction to query processing and optimization.As you watch, be sure to take notes outlining the differences between the various aspects of the Query Plan Language. Determine the strengths and weaknesses between the Logical and Physical Query Plan and languages.
3.2: Query Processing Algorithms
Watch this lecture for a introduction to query processing and optimization with a focus on various algorithms that can be used.As you watch, be sure to understand the differences between the various query processing and optimizing algorithms. Determine which scenarios would be optimal for using a single-pass versus a multi-pass algorithm, etc.
3.3: Query Plan Execution
Watch this lecture for a introduction to query processing and optimization with a focus Query plan execution.
3.4: Cost-Based Query Optimization
Read slides 3 through 20 (pages 2-10) for an overview of cost-based query optimization techniques. As you read this lecture be sure to understand the importance of utilizing the statistical methods for query optimization.