Query Rewrite Optimization Rules In IBM DB2 Universal Database

SQL is often portrayed as a declarative query language, in which one can request desired data without the need to provide a retrieval algorithm. This is not quite accurate; in fact event ehearliest, simplest versions of SQL offered a mixture of declarative and imperative features. Simple SQL query blicks can be used as constants within the scope of nested and "correlation" variables from outer query blocks can be used as constants within the scope of nested query blocks. Traditional Query plan optimizers operate on single query blocks at a time, and do no cross-block optimizations. Hence it is often important to rewrite complex queries by "unnesting" or "flattening" nested query blocks, and it is preferable for these optimizations to be automated by the database system. THis paper explains the issues and techniques involved in unnesting comples SQL queries. We present Query rules for flattening complex queries as well as decorrelating them. These rewrite rules have been implemented in IBM DB2 Universal Database, along with many other transformations. Our experience shows that these rewrite rules are extremely valuable and can improve query performance signicantly.