skip to main content
10.1145/1871940.1871951acmconferencesArticle/Chapter ViewAbstractPublication PagescikmConference Proceedingsconference-collections
research-article

Immediate materialized views with outerjoins

Published: 30 October 2010 Publication History

Abstract

Queries using outerjoins appear very frequently in traditional applications such as data warehousing. Lately, they have been widely used in newly emerged systems such as Object-Relational Mapping (ORM) tools, schema integration and information exchange systems, and probabilistic databases. Materialized views using outerjoins are allowed in many database management systems but without support for their incremental maintenance. In this paper we present the algorithms used in SQL Anywhere RDBMS for the incremental maintenance of materialized views with outerjoins. The algorithms achieve the following improvements over the previous work with respect to the class of materialized outerjoin views which can be incrementally maintained, and with respect to the performance of the view updates: (1) Relax the requirement for the existence of the primary key attribute in the select list of the view to only some of the relations (namely only the relations. referenced as a preserved side in an outerjoin predicate). 2) Relax the null-intolerant property requirement for only some predicates used in the view definition (namely, those outerjoin predicates referencing relations which can be null-supplied by another nested outerjoin). 3) The maintenance of outerjoin views is implemented by using exactly one update statement per view for each relation referenced in the view.
Another main characteristic of the algorithms is that they allow the design and implementation of the incremental maintenance of materialized views with outerjoins to be easily integrated into the SQL Anywhere Optimizer by relying on the normalized join tree representation used for optimizing queries with outerjoins.

References

[1]
I. T. Bowman, P. Bumbulis, D. Farrar, A. K. Goel, B. Lucier, A. Nica, G. N. Paulley, J. Smirnios, and M. Young-Lai. SQL Anywhere: A holistic approach to database self-management. In Proceedings, ICDE Workshops (Self-Managing Database Systems), pages 414--423, Istanbul, Turkey, Apr. 2007. ieee Computer Society Press.
[2]
E. F. Codd. Extending the database relational model to capture more meaning. acm Transactions on Database Systems, 4(4):397--434, Dec. 1979.
[3]
C. Galindo-Legaria. Outerjoins as disjunctions. In acm sigmod International Conference on Management of Data, pages 348--358, Minneapolis, Minnesota, May 1994. Association for Computing Machinery.
[4]
T. G. Griffin and B. Kumar. Algebraic change propagation for semijoin and outerjoin queries. acm sigmod Record, 27(3):22--27, 1998.
[5]
International Standards Organization. (ansi/iso) 9075--2, sql Foundation, July 2008.
[6]
P.-Å. Larson and J. Zhou. Efficient maintenance of materialized outer-join views. In Proceedings, 23rd ieee International Conference on Data Engineering, pages 56--65, 2007.
[7]
A. Nica. System and methodology for generating bushy trees using a left-deep tree join enumeration algorithm. US Patent 7,184,998, Feb. 2007.
[8]
A. Nica. Query optimization using materialized views in database management systems. US Patent 7,606,827, Oct. 2009.
[9]
A. Nica, D. S. Brotherston, and D. W. Hillis. Extreme visualisation of the query optimizer search spaces. In acm sigmod International Conference on Management of Data, pages 1067--1070, Providence, Rhode Island, June 2009.
[10]
J. Rao, H. Pirahesh, and C. Zuzarte. Canonical abstraction for outerjoin optimization. In acm sigmod International Conference on Management of Data, pages 671--682, Paris, France, June 2004. Association for Computing Machinery.

Cited By

View all
  • (2011)A call for order in search space generation process of query optimizationProceedings of the 2011 IEEE 27th International Conference on Data Engineering Workshops10.1109/ICDEW.2011.5767651(4-9)Online publication date: 11-Apr-2011
  • (2010)DOLAP 2010 workshop summaryProceedings of the 19th ACM international conference on Information and knowledge management10.1145/1871437.1871792(1973-1974)Online publication date: 26-Oct-2010

Recommendations

Comments

Information & Contributors

Information

Published In

cover image ACM Conferences
DOLAP '10: Proceedings of the ACM 13th international workshop on Data warehousing and OLAP
October 2010
112 pages
ISBN:9781450303835
DOI:10.1145/1871940
Permission to make digital or hard copies of all or part of this work for personal or classroom use is granted without fee provided that copies are not made or distributed for profit or commercial advantage and that copies bear this notice and the full citation on the first page. Copyrights for components of this work owned by others than ACM must be honored. Abstracting with credit is permitted. To copy otherwise, or republish, to post on servers or to redistribute to lists, requires prior specific permission and/or a fee. Request permissions from [email protected]

Sponsors

Publisher

Association for Computing Machinery

New York, NY, United States

Publication History

Published: 30 October 2010

Permissions

Request permissions for this article.

Check for updates

Author Tags

  1. incremental maintenance
  2. materialized views
  3. outerjoin views
  4. query optimization
  5. sql anywhere
  6. sql merge statement
  7. view maintenance

Qualifiers

  • Research-article

Conference

CIKM '10

Acceptance Rates

Overall Acceptance Rate 29 of 79 submissions, 37%

Upcoming Conference

CIKM '25

Contributors

Other Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

  • Downloads (Last 12 months)6
  • Downloads (Last 6 weeks)0
Reflects downloads up to 10 Feb 2025

Other Metrics

Citations

Cited By

View all
  • (2011)A call for order in search space generation process of query optimizationProceedings of the 2011 IEEE 27th International Conference on Data Engineering Workshops10.1109/ICDEW.2011.5767651(4-9)Online publication date: 11-Apr-2011
  • (2010)DOLAP 2010 workshop summaryProceedings of the 19th ACM international conference on Information and knowledge management10.1145/1871437.1871792(1973-1974)Online publication date: 26-Oct-2010

View Options

Login options

View options

PDF

View or Download as a PDF file.

PDF

eReader

View online with eReader.

eReader

Figures

Tables

Media

Share

Share

Share this Publication link

Share on social media