Skip to main content
SpringerLink
Log in

View matching for outer-join views

  • Special Issue Paper
  • Published:
The VLDB Journal Aims and scope Submit manuscript

Abstract

Prior work on computing queries from materialized views has focused on views defined by expressions consisting of selection, projection, and inner joins, with an optional aggregation on top (SPJG views). This paper provides a view matching algorithm for views that may also contain outer joins (SPOJG views). The algorithm relies on a normal form for outer-join expressions and is not based on bottom-up syntactic matching of expressions. It handles any combination of inner and outer joins, deals correctly with SQL bag semantics, and exploits not-null constraints, uniqueness constraints and foreign key constraints.

This is a preview of subscription content, log in via an institution to check access.

Access this article

Log in via an institution

Price excludes VAT (USA)
Tax calculation will be finalised during checkout.

Instant access to the full article PDF.

Institutional subscriptions

Similar content being viewed by others

References

  1. Bello, R.G., Dias, K., Downing, A., Feenan Jr., J.J., , J.L., Norcott, W.D., Sun, H., Witkowski, A., Ziauddin, M.: Materialized views in Oracle. In: Proceedings of VLDB Conference, pp. 659–664 (1998)

  2. Bhargava, G., Goel, P., Iyer, B.R.: Hypergraph based reorderings of outer join queries with complex predicates. In: Proceedings of SIGMOD Conference, pp. 304–315 (1995)

  3. Chang, J.-Y., Lee, S.-G.: Query reformulation using materialized views in data warehouse environment. In: Proceedings of DOLAP, pp. 54–59 (1998)

  4. Chaudhuri, S., Krishnamurthy, R., Potamianos, S., Shim, K.: Optimizing queries with materialized views. In: Proceedings of ICDE Conference, pp. 190–200 (1995)

  5. Galindo-Legaria,C.: Outerjoins as disjunctions.In: Proceedings of SIGMOD Conference, pp. 348–358 (1994)

  6. Galindo-Legaria, C., Rosenthal, A.: Outerjoin simplification and reordering for query optimization. ACM Tran. Database Syst. 22(1) (1997)

  7. Goel, P., Iyer, B.R.: Sql query optimization: reordering for a general class of queries. In: Proceedings of SIGMOD Conference, pp. 47–56 (1996)

  8. Goldstein, J., Larson, P.-Å.: Optimizing queries using materialized views: a practical, scalable solution. In: Proceedings of SIGMOD Conference, pp. 331–342 (2001)

  9. Larson, P.-Å., Yang, H.Z.: Computing queries from derived relations. In: Proceedings of VLDB Conference, pp. 259–269 (1985)

  10. Larson, P.-Å., Zhou, J.: View matching for outer-join views. In: Proceedings of VLDB Conference, pp. 445–456 (2005)

  11. Levy, A.Y., Mendelzon, A.O., Sagiv, Y., Srivastava, D.: Answering queries using views. In: Proceedings of PODS Conference, pp. 95–104 (1995)

  12. Pottinger, R., Levy, A.Y.: A scalable algorithm for answering queries using views. In: Proceedings of VLDB Conference, pp. 484–495 (2000)

  13. Rao, J., Lindsay, B.G., Lohman, G.M., Pirahesh, H., Simmen, D.E.: Using eels, a practical approach to outerjoin and antijoin reordering. In: Proceedings of ICDE, pp. 585–594 (2001)

  14. Rao, J., Pirahesh, H., Zuzarte, C.: Canonical abstraction for outerjoin optimization. In: Proceedings of SIGMOD Conference, pp. 671–682 (2004)

  15. Srivastava, D., Dar, S., Jagadish, H.V., Levy, A.Y.: Answering queries with aggregation using views. In: Proceedings of VLDB Conference, pp. 318–329 (1996)

  16. Yang, H.Z., Larson,P.-Å.: Eager aggregation and lazy aggregation. In: Proceedings of VLDB Conference, pp. 345–357 (1995)

  17. Yan, W.P., Larson,P.-Å.: Query transformation for PSJ-queries. In: Proceedings of VLDB Conference, pp. 245–254 (1987)

  18. Zaharioudakis, M., Cochrane, R., Lapis, G., Pirahesh, H., Urata, M.: Answering complex sql queries using automatic summary tables. In: Proceedings of SIGMOD Conference, pp. 105–116 (2000)

Download references

Author information

Authors and Affiliations

  1. Microsoft Research, One Microsoft Way, Redmond, WA, 98052, USA

    Per-Åke Larson & Jingren Zhou

Authors
  1. Per-Åke Larson
    View author publications

    You can also search for this author in PubMed Google Scholar

  2. Jingren Zhou
    View author publications

    You can also search for this author in PubMed Google Scholar

Corresponding author

Correspondence to Per-Åke Larson.

Rights and permissions

Reprints and permissions

About this article

Cite this article

Larson, PÅ., Zhou, J. View matching for outer-join views. The VLDB Journal 16, 29–53 (2007). https://doi.org/10.1007/s00778-006-0027-9

Download citation

  • Received:

  • Accepted:

  • Published:

  • Issue Date:

  • DOI: https://doi.org/10.1007/s00778-006-0027-9

Keywords

Search

Navigation