Effective and complete discovery of order dependencies via set-based axiomatization

Abstract

Integrity constraints (ICs) provide a valuable tool for expressing and enforcing application semantics. However, formulating constraints manually requires domain expertise, is prone to human errors, and may be excessively time consuming, especially on large datasets. Hence, proposals for automatic discovery have been made for some classes of ICs, such as functional dependencies (FDs), and recently, order dependencies (ODs). ODs properly subsume FDs, as they can additionally express business rules involving order; e.g., an employee never has a higher salary while paying lower taxes compared with another employee.We address the limitations of prior work on OD discovery which has factorial complexity in the number of attributes, is incomplete (i.e., it does not discover valid ODs that cannot be inferred from the ones found) and is not concise (i.e., it can result in "redundant" discovery and overly large discovery sets). We improve significantly on complexity, offer completeness, and define a compact canonical form. This is based on a novel polynomial mapping to a canonical form for ODs, and a sound and complete set of axioms (inference rules) for canonical ODs. This allows us to develop an efficient set-containment, lattice-driven OD discovery algorithm that uses the inference rules to prune the search space. Our algorithm has exponential worst-case time complexity, O(2 |R| ), in the number of attributes and linear complexity in the number of tuples. We prove that it produces a complete, minimal set of ODs (i.e., minimal with regards to the canonical representation). Finally, using real and synthetic datasets, we experimentally show orders-of-magnitude performance improvements over the current state-of-the-art algorithm and demonstrate effectiveness of our techniques.

Pattern functional dependencies for data cleaning

“…Integrity Constraints Discovery. Due to the importance of ICs, many techniques and systems have been proposed for discovering different kinds of ICs, namely, FDs [26,30,43], temporal FDs [2], differential dependencies [38], conditional differential dependencies [25], CFDs [14,35], and order dependencies [40]. These techniques work on entire values and adapting them to PFD discovery is quite challenging; we have to carefully combine pattern discovery and data dependency discovery, to effectively and efficiently discover PFDs.…”

Distributed implementations of dependency discovery algorithms

“…Consider the TANE [8] algorithm for discovering FDs (FastOD [19] is similar but it discovers ODs). For each lattice level, TANE performs three tasks: generate next level, compute dependencies, and prune.…”

“…As explained in Section 2.2, for each lattice level, schema-driven algorithms such as TANE compute dependencies holding in this level, prune the search space based on the discovered dependencies, and generate the next level. The execution plans presented in this section can be extended to discover order dependencies [19]. As discussed in Section 2.2, verifying ODs requires a refinement check and an ordering check.…”

Guided automated learning for query workload re-optimization

“…Cracks in the foundations of the mainstay approaches have begun to appear, however, with big data applications and ultra-complex SQL queries [12,22].There are two reasons for this. First, plans found by the optimizer are rarely now optimal.…”