Outer join elimination in the Teradata RDBMS

Abstract

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. © Springer-Verlag Berlin Heidelberg 2004.