Merging views containing outer joins in the Teradata DBMS

Authors:
Ahmad Ghazal;Dawit Seid;Alain Crolotte
Affiliations:
Teradata Corporation, El Segundo, CA;Teradata Corporation, El Segundo, CA;Teradata Corporation, El Segundo, CA
Venue:
DEXA'10 Proceedings of the 21st international conference on Database and expert systems applications: Part I
Year:
2010

Citing 7
Cited 0

Removing redundant join operations in queries involving views

Information Systems
Extensible/rule based query rewrite optimization in Starburst

SIGMOD '92 Proceedings of the 1992 ACM SIGMOD international conference on Management of data
A Rule Engine for Query Transformation in Starburst and IBM DB2 C/S DBMS

ICDE '97 Proceedings of the Thirteenth International Conference on Data Engineering
Elimination of View and Redundant Variables in a SQL-like Database Language for Extended NF2 Structures

VLDB '90 Proceedings of the 16th International Conference on Very Large Data Bases
Cost-based query transformation in Oracle

VLDB '06 Proceedings of the 32nd international conference on Very large data bases
View matching for outer-join views

The VLDB Journal — The International Journal on Very Large Data Bases
Exploiting Interactions among Query Rewrite Rules in the Teradata DBMS

DEXA '08 Proceedings of the 19th international conference on Database and Expert Systems Applications

Quantified Score

Hi-index	0.00

Visualization

Abstract

Query rewrite (QRW) optimizations apply algebraic transformations to a SQL query Q producing another SQL query Q' such that Q and Q' are semantically equivalent (i.e. produce the same result) but Q' can be executed more efficiently than Q. Merging views (as well as derived tables) to their parent SQL block is an important part of QRW which creates more opportunities for numerous other query optimizations like optimal join order and enabling other rewrites like redundant join elimination. This paper presents novel and practical techniques of view merging in the presence of outer joins which are implemented in the Teradata 12.0 DBMS. We also present experimental results that demonstrate performance improvements achieved using these techniques.