Advanced query optimization techniques for relational database systems

TL;DR: A new technique called parametric query optimization is developed which attempts to identify several execution plans, each one of which is optimal for a subset of all possible values of the run-time parameters.

Abstract: Current query optimizers make certain assumptions about many important run-time parameters whose values are unknown at query optimization time. Thus, when these assumptions are violated at run-time, re-optimization is needed or performance suffers. To overcome this shortcoming, we develop a new technique called parametric query optimization which attempts to identify several execution plans, each one of which is optimal for a subset of all possible values of the run-time parameters. We adopt randomized algorithms as the main approach and enhance them with a sideways information passing feature that increases their effectiveness in the new task. Experimental results show that they optimize queries for large numbers of optimizer parameter values in the same time needed by their conventional versions for a single parameter value, without much sacrifice in the output quality. We then investigate the multiple query optimization problem. When more than one query is presented to a database system, current database systems optimize and process each query separately ignoring common subexpressions between the queries. To avoid this inefficiency, we develop a new heuristic search algorithm that generates a globally optimal plan, which computes the results of all queries simultaneously evaluating common subexpressions only once. Through both theoretical analysis and experimental results, we show that our algorithm is superior to the algorithms that have been proposed in the past. Finally, we present a query optimization technique in the presence of foreign functions such as operating system libraries, mathematical functions and user defined procedures. Our key observation is that the optimization must be cost-based and take into account semantic information about foreign functions. Therefore, after presenting a simple declarative rule language to express such semantics, we provide an algorithm to enumerate equivalent queries applying the semantic rules and develop an optimization algorithm based on the dynamic programming approach, which finds an optimal plan from the enriched space of the equivalent queries. We also provide query processing techniques and extensions to the cost model that are necessary for foreign functions.