Skip to main content
Springer Nature Link
Log in

Exploiting Interactions among Query Rewrite Rules in the Teradata DBMS

  • Conference paper
Database and Expert Systems Applications (DEXA 2008)

Part of the book series: Lecture Notes in Computer Science ((LNISA,volume 5181))

Included in the following conference series:

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 to check access.

Access this chapter

Log in via an institution

Institutional subscriptions

Preview

Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.

Similar content being viewed by others

References

  1. Pirahesh, H., Hellerstein, J.M., Hasan, W.: Extensible/rule Based Query Rewrite Optimization in Starburst. SIGMOD, 39–48 (1992)

    Google Scholar 

  2. 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)

    Google Scholar 

  3. Graefe, G., McKenna, W.J.: The Volcano Optimizer Generator: Extensibility and Efficient Search. In: ICDE, pp. 209–218 (1993)

    Google Scholar 

  4. Graefe, G.: The Cascade Framework for Query Optimization. IEEE Data Engineering Bulletin 18(3), 19–29 (1995)

    Google Scholar 

  5. Graefe, G., Dewitt, D.J.: The Exodus Optimizer Generator. SIGMOD, 160–172 (1987)

    Google Scholar 

  6. Cherniack, M., Zdonik, S.: Changing the Rules: Transformations for Rule-based Optimizers. SIGMOD, 61–72 (1998)

    Google Scholar 

  7. Warshaw, L.B., Miranker, D.P.: Rule-based Query Optimization, Revisited. In: CIKM, pp. 267–275 (1999)

    Google Scholar 

  8. Popa, L., Deutsch, A., Sahuguet, A., Tannen, V.: A Chase Too Far? In: SIGMOD, pp. 273–284 (2000)

    Google Scholar 

  9. Haas, L.M., Kossmann, D., Wimmers, E.L., Yang, J.: Optimizing Queries Across Diverse Data Sources. In: VLDB, pp. 276–285 (1997)

    Google Scholar 

  10. 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)

    Google Scholar 

  11. 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)

    Chapter  Google Scholar 

  12. Ahmed, R., Lee, A., Witkowski, A.: Cost-Based Query Transformation in Oracle. In: VLDB, pp. 1026–1036 (2007)

    Google Scholar 

  13. Elhemali, M., Galindo-Legaria, C.A., Grabs, T., Joshi, M.M.: Execution Strategies for SQL Subqueries. In: SIGMOD, pp. 993–1003 (2007)

    Google Scholar 

  14. Brownston, L., Farrell, R., Kant, E., Martin, N.: Programming Expert Systems in OPS5: An Introduction to Rule-based Programming. Addison-Wesley, Reading (1985)

    Google Scholar 

  15. Levy, A.Y., Mumick, I., Sagiv, Y.: Query Optimization by Predicate Move-around. In: VLDB, pp. 96–108 (1994)

    Google Scholar 

  16. TPC-H specification – Transaction Performance Council, www.tpc.org

  17. Aiken, A., Widon, J., Hellerstein, J.M.: Behavior of Database Production Rules: Termination, Confluence, and Observable Determinism. In: SIGMOD, pp. 59–68 (1992)

    Google Scholar 

Download references

Author information

Authors and Affiliations

  1. Teradata Corporation, 100 N. Sepulveda Blvd. El Segundo, CA, 90245

    Ahmad Ghazal, Dawit Seid, Alain Crolotte & Bill McKenna

Authors
  1. Ahmad Ghazal
    View author publications

    You can also search for this author in PubMed Google Scholar

  2. Dawit Seid
    View author publications

    You can also search for this author in PubMed Google Scholar

  3. Alain Crolotte
    View author publications

    You can also search for this author in PubMed Google Scholar

  4. Bill McKenna
    View author publications

    You can also search for this author in PubMed Google Scholar

Editor information

Sourav S. Bhowmick Josef Küng Roland Wagner

Rights and permissions

Reprints 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)

Publish with us

Policies and ethics