Complete yet practical search for minimal query reformulations under constraints

Abstract

We revisit the Chase&Backchase (C&B) algorithm for query reformulation under constraints, which provides a uniform solution to such particular-case problems as view-based rewriting under constraints, semantic query optimization, and physical access path selection in query optimization. For an important class of queries and constraints, C&B has been shown to be complete, i.e. guaranteed to find all (join-)minimal reformulations under constraints. C&B is based on constructing a canonical rewriting candidate called a universal plan, then inspecting its exponentially many sub-queries in search for minimal reformulations, essentially removing redundant joins in all possible ways. This inspection involves chasing the subquery. Because of the resulting exponentially many chases, the conventional wisdom has held that completeness is a concept of mainly theoretical interest. We show that completeness can be preserved at practically relevant cost by introducing Prov C &B , a novel reformulation algorithm that instruments the chase to maintain provenance information connecting the joins added during the chase to the universal plan subqueries responsible for adding these joins. This allows it to directly "read off" the minimal reformulations from the result of a single chase of the universal plan, saving exponentially many chases of its subqueries. We exhibit natural scenarios yielding speedups of over two orders of magnitude between the execution of the best view-based rewriting found by a commercial query optimizer and that of the best rewriting found by Prov C &B (which the optimizer misses because of limited reasoning about constraints).

Querying with access patterns and integrity constraints

“…For example we can restrict to configurations that generate BestPlan := ⊥ BestCost(n) = ∞ BestCost := ∞ ; 9 r := 1; 10 while r ≤ d and PlanDag has changed from previous r do 11 for all conf 1 , conf 2 of height at most r, conf 1 , conf 2 ∈ PlanDag with Compose(conf 1 , conf 2 ) satisfying any additional shape-restriction do 12 Let conf = Compose(conf 1 , conf 2 ); only left-deep plans. Recall that these are the "linear configurations" -those where every composition has an ApplyRule configuration as a second argument.…”

“…In particular, we do not identify better plans with some syntactic requirement on a query, such as minimality of the number of query atoms. Our last experiment compares the plans found by our system with those found by a minimal-rewriting approach, such as the chase and backchase of [17,14,11]. We mimicked the minimal-rewriting query-based approach (MR below) simply by setting our cost function to be the number of atoms, and then randomly re-ordering the atoms in the resulting plan.…”

“…Notice that the integrity constraints here contain "cycles", since Employee points to Manager and vice versa. Many methods for reasoning with queries under constraints (those based on a "terminating chase" [11]) do not deal with such constraint classes. 2 We will start with an idea from earlier theoretical work [5], which takes as input a query Q and schema S with access restrictions and constraints, and generates a proof goal which corresponds to reasoning that the query can be answered using the integrity constraints but abiding by the access restrictions.…”

“…The chase and backchase approach (C&B) originating in work of Deutsch, Popa, and Tannen [8,17] is the most mature method for performing reformulation, focusing on the case of TGD constraints where the chase terminates. A number of optimizations of the chase and backchase have appeared recently [14,11] that report drastic improvements in speed of reformulation.…”

Possible and certain SQL keys

“…Core problems investigate reasoning [21], Armstrong databases [18], and discovery [22,24,32,33,40]. Applications include anomaly detection [43], consistency management [4], consistent query answers [3,28], data cleaning [17], exchange [16], fusion [36], integration [9], profiling [35], quality [38], repairs [6], and security [7], schema design [14], query optimization [23], transaction processing [2], and view maintenance [37]. Surrogate keys ('autoincrement' fields) do not help with enforcing domain semantics or supporting applications while semantic keys do.…”