Abstract
Query rewrite (QRW) optimizations apply algebraic transformations to a SQL query Q producing a SQL query Q’. Both Q and Q’ are semantically equivalent (i.e. they produce the same result) but the execution of Q’ is generally faster than that of Q. Folding views/derived tables, applying transitive closure on predicates, and converting outer joins to inner joins are some examples of QRW optimizations. In this paper, we carefully analyze the interactions among a number of rewrite rules and show how this knowledge is used to devise a triggering mechanism in the new Teradata extensible QRW subsystem thereby enabling efficient application of the rewrite rules. We also present results from experimental studies that show that, as compared to a conventional recognize-act cycle strategy, exploiting these interactions yields significant reduction in the time and space cost of query optimization while producing the same re-written queries.
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. SIGMOD, 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)
Graefe, G., McKenna, W.J.: The Volcano Optimizer Generator: Extensibility and Efficient Search. In: ICDE, pp. 209–218 (1993)
Graefe, G.: The Cascade Framework for Query Optimization. IEEE Data Engineering Bulletin 18(3), 19–29 (1995)
Graefe, G., Dewitt, D.J.: The Exodus Optimizer Generator. SIGMOD, 160–172 (1987)
Cherniack, M., Zdonik, S.: Changing the Rules: Transformations for Rule-based Optimizers. SIGMOD, 61–72 (1998)
Warshaw, L.B., Miranker, D.P.: Rule-based Query Optimization, Revisited. In: CIKM, pp. 267–275 (1999)
Popa, L., Deutsch, A., Sahuguet, A., Tannen, V.: A Chase Too Far? In: SIGMOD, pp. 273–284 (2000)
Haas, L.M., Kossmann, D., Wimmers, E.L., Yang, J.: Optimizing Queries Across Diverse Data Sources. In: VLDB, pp. 276–285 (1997)
Ghazal, A., Bhashyam, R., Crolotte, A.: Block Optimization in the Teradata RDBMS. In: Mařík, V., Štěpánková, O., Retschitzegger, W. (eds.) DEXA 2003. LNCS, vol. 2736, pp. 782–791. Springer, Heidelberg (2003)
Ghazal, A., Crolotte, A., Bhashyam, R.: Dynamic Constraints Derivation and Maintenance in the Teradata RDBMS. In: Mayr, H.C., Lazanský, J., Quirchmayr, G., Vogel, P. (eds.) DEXA 2001. LNCS, vol. 2113, pp. 390–399. Springer, Heidelberg (2001)
Ahmed, R., Lee, A., Witkowski, A.: Cost-Based Query Transformation in Oracle. In: VLDB, pp. 1026–1036 (2007)
Elhemali, M., Galindo-Legaria, C.A., Grabs, T., Joshi, M.M.: Execution Strategies for SQL Subqueries. In: SIGMOD, pp. 993–1003 (2007)
Brownston, L., Farrell, R., Kant, E., Martin, N.: Programming Expert Systems in OPS5: An Introduction to Rule-based Programming. Addison-Wesley, Reading (1985)
Levy, A.Y., Mumick, I., Sagiv, Y.: Query Optimization by Predicate Move-around. In: VLDB, pp. 96–108 (1994)
TPC-H specification – Transaction Performance Council, www.tpc.org
Aiken, A., Widon, J., Hellerstein, J.M.: Behavior of Database Production Rules: Termination, Confluence, and Observable Determinism. In: SIGMOD, pp. 59–68 (1992)
Author information
Authors and Affiliations
Editor information
Rights and permissions
Copyright information
© 2008 Springer-Verlag Berlin Heidelberg
About this paper
Cite this paper
Ghazal, A., Seid, D., Crolotte, A., McKenna, B. (2008). Exploiting Interactions among Query Rewrite Rules in the Teradata DBMS. In: Bhowmick, S.S., Küng, J., Wagner, R. (eds) Database and Expert Systems Applications. DEXA 2008. Lecture Notes in Computer Science, vol 5181. Springer, Berlin, Heidelberg. https://doi.org/10.1007/978-3-540-85654-2_50
Download citation
DOI: https://doi.org/10.1007/978-3-540-85654-2_50
Publisher Name: Springer, Berlin, Heidelberg
Print ISBN: 978-3-540-85653-5
Online ISBN: 978-3-540-85654-2
eBook Packages: Computer ScienceComputer Science (R0)