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.
This is a preview of subscription content, log in via an institution.
Buying options
Tax calculation will be finalised at checkout
Purchases are for personal use only
Learn about institutional subscriptionsPreview
Unable to display preview. Download preview PDF.
References
Pirahesh, H., Hellerstein, J.M., Hasan, W.: Extensible/rule Based Query Rewrite Optimization in Starburst. In: SIGMOD, pp. 39–48 (1992)
Pirahesh, H., Cliff Leung, T.Y., Hasan, W.: A Rule Engine for Query Transformation in Starburst and IBM DB2 C/S DBMS. In: ICDE, pp. 391–400 (1997)
Ahmed, R., Lee, A., Witkowski, A.: Cost-Based Query Transformation in Oracle. In: VLDB, pp. 1026–1036 (2007)
IBM Corp., DB2 Version 9.1 for z/OS: Performance Monitoring and Tuning Guide, 6th edn. (April 2009)
Oracle Corp., Oracle Database 11g Release 2 (11.2) Documentation
Microsoft Corp, SQL Server 2008 Product Documentation (2009), http://msdn.microsoft.com/en-us/library/bb418440SQL.10.aspx
Ott, N., Horländer, K.: Removing redundant join operations in queries involving views. Inf. Syst. 10(3), 279–288 (1985)
Südkamp, N., Linnemann, V.: Elimination of views and redundant variables in an SQL-like database language for extended NF2 structures. In: VLDB, pp.302–313 (1990)
Ghazal, A., Seid, D., Crolotte, A., McKenna, B.: Exploiting Interactions among Query Rewrite Rules in the Teradata DBMS. In: Bhowmick, S.S., Küng, J., Wagner, R. (eds.) DEXA 2008. LNCS, vol. 5181, pp. 596–609. Springer, Heidelberg (2008)
Larson, P., Zhou, J.: View matching for outer-join views. The VLDB Journal 16(1), 29–53 (2007)
TPC-H specification – Transaction Performance Council, www.tpc.org
Author information
Authors and Affiliations
Editor information
Editors and Affiliations
Rights and permissions
Copyright information
© 2010 Springer-Verlag Berlin Heidelberg
About this paper
Cite this paper
Ghazal, A., Seid, D., Crolotte, A. (2010). Merging Views Containing Outer Joins in the Teradata DBMS. In: Bringas, P.G., Hameurlain, A., Quirchmayr, G. (eds) Database and Expert Systems Applications. DEXA 2010. Lecture Notes in Computer Science, vol 6261. Springer, Berlin, Heidelberg. https://doi.org/10.1007/978-3-642-15364-8_29
Download citation
DOI: https://doi.org/10.1007/978-3-642-15364-8_29
Publisher Name: Springer, Berlin, Heidelberg
Print ISBN: 978-3-642-15363-1
Online ISBN: 978-3-642-15364-8
eBook Packages: Computer ScienceComputer Science (R0)