Ahmad Said Ghazal

Recursive SQL query optimization with k-iteration lookahead

Relational implementation of recursive queries is part of the ANSI SQL99 and was implemented in Teradata V2R6. Recursive queries allow processing of hierarchical data like air flight schedules, bill-of-materials, data cube dimension hierarchies, and ancestor-descendant information (e.g. XML data stored in relations). The initial Teradata recursive query implementation is based on a static (fixed) execution plan for all recursive iterations. This may not be optimal since the intermediate results from recursive iterations vary in size. To address this problem, this paper proposes dynamic re-optimization techniques to produce execution plans that are optimal for all recursive iterations. The approach employs a mix of multi-iteration pre-planning and dynamic feedback techniques that are generally applicable to any recursive query implementation in an RDBMS. We validate our proposed techniques by conducting experiments on a prototype implementation using airline flights data.

Outer Join Elimination in the Teradata RDBMS

Queries in relational DBMS are getting more and more complex especially in the decision support environment. Also, tools generate most SQL queries received by commercial DBMS engines with not much user control. Query rewrite plays an important role in optimizing these complex and tool-generated queries. One important such technique is eliminating redundant joins whenever possible. In this paper, we present a new solution for outer join elimination. This solution was implemented in the Teradata DBMS part of V2R5.1. As an example, we show how applications based on vertical partitioning and universal views can greatly benefit from our outer join elimination technique. Finally, we describe performance improvement results of applying these techniques to the TPC-H benchmark.

Block Optimization in the Teradata RDBMS

Block optimization involves several techniques used to avoid optimizing blocks or parts of a query separately. This process may involve integrating views, derived tables, and subqueries with the rest of the query. For those query blocks that cannot be integrated with the rest of the query, the Teradata optimizer tries to simplify and optimize these blocks. Such optimizations can be achieved using the satisfiability test (checking if a set of conditions are satisfiable) and generating transitive closure, which allows pushing constraints into and out of query blocks. Several new Block Optimization techniques, added to the new release of Teradata DBMS (V2R5), are described.

Dynamic Constraints Derivation and Maintenance in the Teradata RDBMS

We define a new algorithm that allows the Teradata query optimizer to automatically derive and maintain constraints between date type columns across tables that are related by referential integrity constraints. We provide a novel, quantitative measure of the usefulness of such rules. We show how the Teradata query optimizer utilizes these constraints in producing more optimal plans especially for databases that have tables that are value ordered by date. We also discuss our design for maintaining these constraints in the presence of inserts, deletes, and updates to the relevant tables. Finally, we give performance numbers for seven TPC-H queries from our prototype implementation based on the Teradata Database engine.