Query Optimization - Special Topics in Database Systems

Explore the intricate world of database query optimization through this comprehensive graduate-level course that delves deep into the theoretical foundations and practical implementations of modern query optimizers. Master the fundamental concepts of SQL optimization, beginning with essential background knowledge and progressing through the historical evolution of query optimization systems, including the groundbreaking IBM System R and the sophisticated IBM Starburst Query Rewriter. Examine the architectural principles behind optimizer generators such as EXODUS and Volcano, and understand the advanced Cascades query optimizer framework that forms the backbone of many contemporary database systems. Learn to implement and analyze query plan transformations, with detailed coverage of join ordering strategies using both bottom-up and top-down/transformational approaches. Discover techniques for parallelizing search processes in query optimization, exploring both bottom-up and top-down methodologies to enhance performance in multi-core environments. Develop expertise in unnesting complex queries to improve execution efficiency, and gain comprehensive knowledge of query cost models, including statistical analysis, cardinality estimation techniques, and advanced cardinality estimation methods that enable optimizers to make intelligent decisions about query execution plans.