Optimising Sargable Conjunctive Predicate Queries in the Context of Big Data

With the continued increase in the volume of data, the volume dimension of big data has become a significant factor in estimating query time. When all other factors are held constant, query time increases as the volume of data increases and vice versa. To enhance query time, several techniques have come out of research efforts in this direction. One of such techniques is factorisation of query predicates. Factorisation has been used as a query optimization technique for the general class of predicates but has been found inapplicable to the subclass of sargable conjunctive equality predicates. Experiments performed exposed a peculiar nature of sargable conjunctive equality predicates based on which insight, the concatenated predicate model was formulated as capable of optimising sargable conjunctive equality predicates. Equations from research results were combined in a way that theorems describing the application and optimality of the concatenated predicate model were derived and proved

Optimization of Queries with Conjunction of Predicates

A method to optimize the access at the objects of a relational database is through the optimization of the queries. This article presents an approach of the cost model used in optimization of Select-Project-Join (SPJ) queries with conjunction of predicates and proposes a join optimization algorithm named System RO-H (System Rank Ordering Heuristic). The System RO-H algorithm for optimizing SPJ queries with conjunction of predicates is a System R Dynamic Programming algorithm that extends optimal linear join subplans using a rank-ordering heuristic method as follows: choosing a predicate in ascending order according to the h-metric, where the h-metric depends on the selectivity and the cost per tuple of the predicate, using an expression with heuristic constants.The System Rank-Ordering Heuristic algorithm finds an optimal plan in the space of linear left deep join trees. The System RO-H algorithm saves not a single plan, but multiple optimal plans for every subset, one for each distinct such order, termed interesting order. In order to build an optimal execution plan for a set S of i relations, the optimal plan for each subset of S, consisting of i-1 relations is extended, using the Lemma based on a h-metric for predicates. Optimal plans for subsets are stored and reused. The optimization algorithm chooses a plan of least cost from the execution space

Cherry Picking: A Semantic Query Processing Strategy for the Evaluation of Expensive Predicates

A common requirement of many scienti c applications is the ability to process queries involving expensive predicates corresponding to user programs. Optimizing such queries is hard because static cost predictions and statistical estimates are not applicable. In this paper, we propose a novel approach, called Cherry Picking (CP), based on the modelling of data dependencies among expensive predicate input values as a k-partite graph. We show how CP can be easily integrated into a cost-based query processor. We propose a CP Greedy algorithm that processes the graph by selecting candidate values that minimize query execution cost, and the Epredicate algorithm that processes tuples in pipeline following the CP approach. Based on performance simulation, we show that these algorithms yields executions up to 86% faster than statically chosen pipeline strategies

Algebraic Query Optimization in Database Systems (Algebraische Anfrageoptimierung in Datenbanksystemen)

The thesis investigates different problem classes in algebraic query optimization. For the problem of computing optimal left-deep processing trees with cross products for chain queries and ASI cost functions we present two efficient algorithms. Although, in practice both algorithms yield identical results we have not been able to prove this. For the case of acyclic query graphs, left-deep processing trees, expensive selection and join predicates and ASI cost functions we describe a polynomial time algorithm which is based on a job sequencing algorithm. The algorithm assumes that the set of expensive selections that can be applied directly to the base relations can be guessed. The cheapest plans can be found within the search space of bushy processing trees with cross products. We prove that the problem is NP-hard in this case. The rest of the thesis deals with the general problem of computing optimal bushy processing trees for arbitrary query graphs and expensive selection and join predicates. For this problem we present three efficient dynamic programming algorithms. Our algorithms can handle different join algorithms, split conjunctive predicates, and exploit structural information from the join graph to speed up computation. The time and space complexities of the algorithms are analyzed carefully and efficient implementations based on bitvector arithmetic are presented