Chase & Backchase: A Method for Query Optimization With Materialized Views and Integrity Constraints

We have previously proposed chase and backchase as a novel method for using materialized views and integrity constraints in query optimization. In this paper, we show that the method is usable in realistic optimizers by extending it to bag and mixed (i.e. bag-set) semantics as well as to grouping views and by showing how to integrate it with standard cost-based optimization. We understand materialized views broadly, including user-defined views, cached queries and physical access structures (such as join indexes, access support relations, and gmaps). Moreover, our internal query representation supports object features hence the method applies to OQL and (extended) SQL: 1999 queries. Chase and backchase supports a very general class of integrity constraints, thus being able to find execution plans using views that do not fall in the scope of other methods. In fact, we prove completeness theorems that show that our method will find the best plan in the presence of common and practically important classes of constraints and views, even when bag and set semantics are mixed. We report on a series of experiments that demonstrate the practicality of our new ideas.