Efficient Processing of Relational Queries with Constraints Over the Sum of Multiple Attributes

We identify and study an important class of relational queries involving constraints over the sum of multiple attributes (sum constraint queries). Finding all or a given number of results of these queries requires expensive join operations. These joins, in the absence of any other join conditions, effectively become cartesian products. We develop rewriting techniques to rewrite a sum constraint query in order to enable its efficient processing by conventional relational database engines. Experimental results show that query rewriting achieves notable performance improvement for sum constraint queries without modifying database search engine. For queries asking for a given number of results, we propose a ranking algorithm to order tuples based on their probability to satisfy all sum constraints in a query. We compare it with traditional ranking algorithms that rank tuples based on value of one attribute, and show that our method is more stable and efficient to handle sum constraint queries. We also study a special but common type of sum constraint queries: self-join of a relation with symmetric sum constraints as join conditions. Considering the large number of possible execution plans, we prove that left-deep tree is always the best execution plan for this type of queries.