research-article

Open access

Foreign Keys Open the Door for Faster Incremental View Maintenance

Authors:

Christoforos Svingos,

Hinnerk Gildhoff,

Yannis Papakonstantinou,

Yannis IoannidisAuthors Info & Claims

Proceedings of the ACM on Management of Data, Volume 1, Issue 1

Article No.: 40, Pages 1 - 25

https://doi.org/10.1145/3588720

Published: 30 May 2023 Publication History

Abstract

Serverless cloud-based warehousing systems enable users to create materialized views in order to speed up predictable and repeated query workloads. Incremental view maintenance (IVM) minimizes the time needed to bring a materialized view up-to-date. It allows the refresh of a materialized view solely based on the base table changes since the last refresh. In serverless cloud-based warehouses, IVM uses computations defined as SQL scripts that update the materialized view based on updates to its base tables. However, the scripts set up for materialized views with inner joins are not optimal in the presence of foreign key constraints. For instance, for a join of two tables, the state of the art IVM computations use a UNION ALL operator of two joins - one computing the contributions to the join from updates to the first table and the other one computing the remaining contributions from the second table. Knowing that one of the join keys is a foreign-key would allow us to prune all but one of the UNION ALL branches and obtain a more efficient IVM script. In this work, we explore ways of incorporating knowledge about foreign key into IVM in order to speed up its performance. Experiments in Redshift showed that the proposed technique improved the execution times of the whole refresh process up to 2 times, and up to 2.7 times the process of calculating the necessary changes that will be applied into the materialized view.

References

[1]

Supun Abeysinghe, Qiyang He, and Tiark Rompf. 2022. Efficient Incrementialization of Correlated Nested Aggregate Queries using Relative Partial Aggregate Indexes (RPAI). (2022).

[2]

Swarup Acharya, Phillip B Gibbons, Viswanath Poosala, and Sridhar Ramaswamy. 1999. Join synopses for approximate query answering. In Proceedings of the 1999 ACM SIGMOD international conference on Management of data. 275--286.

Digital Library

[3]

Randall G Bello, Karl Dias, Alan Downing, James Feenan, Jim Finnerty, William D Norcott, Harry Sun, Andrew Witkowski, and Mohamed Ziauddin. 1998. Materialized views in Oracle. In VLDB, Vol. 98. 24--27.

Digital Library

[4]

Jose A Blakeley, Per-Ake Larson, and Frank Wm Tompa. 1986. Efficiently updating materialized views. ACM SIGMOD Record 15, 2 (1986), 61--71.

Digital Library

[5]

O Peter Buneman and Eric K Clemons. 1979. Efficiently monitoring relational databases. ACM Transactions on Database Systems (TODS) 4, 3 (1979), 368--382.

Digital Library

[6]

Rada Chirkova and Jun Yang. 2011. Materialized views. Foundations and Trends in Databases 4, 4 (2011), 295--405.

[7]

Latha S Colby, Akira Kawaguchi, Daniel F Lieuwen, Inderpal Singh Mumick, and Kenneth A Ross. 1997. Supporting multiple view maintenance policies. In Proceedings of the 1997 ACM SIGMOD international conference on Management of data. 405--416.

Digital Library

[8]

Timothy Griffin and Bharat Kumar. 1998. Algebraic change propagation for semijoin and outerjoin queries. ACM SIGMOD Record 27, 3 (1998), 22--27.

Digital Library

[9]

Timothy Griffin and Leonid Libkin. 1995. Incremental maintenance of views with duplicates. In Proceedings of the 1995 ACM SIGMOD international conference on Management of data. 328--339.

Digital Library

[10]

Timothy Griffin, Leonid Libkin, and Howard Trickey. 1997. An improved algorithm for the incremental recomputation of active relational expressions. IEEE Transactions on Knowledge and Data Engineering 9, 3 (1997), 508--511.

Digital Library

[11]

Ashish Gupta, Inderpal Singh Mumick, et al. 1995. Maintenance of materialized views: Problems, techniques, and applications. IEEE Data Eng. Bull. 18, 2 (1995), 3--18.

[12]

Ashish Gupta, Inderpal Singh Mumick, and Venkatramanan Siva Subrahmanian. 1993. Maintaining views incrementally. ACM SIGMOD Record 22, 2 (1993), 157--166.

Digital Library

[13]

Himanshu Gupta and Inderpal Singh Mumick. 2006. Incremental maintenance of aggregate and outerjoin expressions. Information Systems 31, 6 (2006), 435--464.

Digital Library

[14]

Eric N Hanson. 1987. A performance analysis of view materialization strategies. ACM SIGMOD Record 16, 3 (1987), 440--453.

Digital Library

[15]

Muhammad Idris, Martín Ugarte, and Stijn Vansummeren. 2017. The dynamic yannakakis algorithm: Compact and efficient query processing under updates. In Proceedings of the 2017 ACM International Conference on Management of Data. 1259--1274.

Digital Library

[16]

Yannis Katsis, Kian Win Ong, Yannis Papakonstantinou, and Kevin Keliang Zhao. 2015. Utilizing ids to accelerate incremental view maintenance. In Proceedings of the 2015 ACM SIGMOD International Conference on Management of Data. 1985--2000.

Digital Library

[17]

Christoph Koch, Yanif Ahmad, Oliver Kennedy, Milos Nikolic, Andres Nötzli, Daniel Lupei, and Amir Shaikhha. 2014. DBToaster: higher-order delta processing for dynamic, frequently fresh views. The VLDB Journal 23, 2 (2014), 253--278.

[18]

Wilburt Labio, Jun Yang, Yingwei Cui, Hector Garcia-Molina, and Jennifer Widom. 1999. Performance issues in incremental warehouse maintenance. In In Proceedings of the 26th International Conference on Very Large Data Bases (VLDB'00), Cairo, Egypt, September 2000. Stanford InfoLab.

[19]

Per-Ake Larson and Jingren Zhou. 2007. Efficient maintenance of materialized outer-join views. In 2007 IEEE 23rd International Conference on Data Engineering. IEEE, 56--65.

[20]

Viktor Leis, Bernhard Radke, Andrey Gubichev, Atanas Mirchev, Peter Boncz, Alfons Kemper, and Thomas Neumann. 2018. Query optimization through the looking glass, and what we found running the join order benchmark. The VLDB Journal 27, 5 (2018), 643--668.

Digital Library

[21]

Inderpal Singh Mumick, Dallan Quass, and Barinderpal Singh Mumick. 1997. Maintenance of data cubes and summary tables in a warehouse. ACM Sigmod Record 26, 2 (1997), 100--111.

Digital Library

[22]

Milos Nikolic, Mohammad Dashti, and Christoph Koch. 2016. How to win a hot dog eating contest: Distributed incremental view maintenance with batch updates. In Proceedings of the 2016 International Conference on Management of Data. 511--526.

Digital Library

[23]

Themistoklis Palpanas, Richard Sidle, Roberta Cochrane, and Hamid Pirahesh. 2002. Incremental maintenance for non-distributive aggregate functions. In VLDB'02: Proceedings of the 28th International Conference on Very Large Databases. Elsevier, 802--813.

[24]

Xiaolei Qian and Gio Wiederhold. 1991. Incremental recomputation of active relational expressions. IEEE transactions on knowledge and data engineering 3, 3 (1991), 337--341.

Digital Library

[25]

Dallan Quass. 1996. Maintenance expressions for views with aggregation. (1996).

[26]

Dallan Quass, Ashish Gupta, Inderpal Singh Mumick, and Jennifer Widom. 1996. Making views self-maintainable for data warehousing. In Fourth International Conference on Parallel and Distributed Information Systems. IEEE, 158--169.

[27]

Kenneth A Ross, Divesh Srivastava, and S Sudarshan. 1996. Materialized view maintenance and integrity constraint checking: Trading space for time. In Proceedings of the 1996 ACM SIGMOD international conference on Management of data. 447--458.

Digital Library

[28]

Qichen Wang and Ke Yi. 2020. Maintaining Acyclic Foreign-Key Joins under Updates. In Proceedings of the 2020 ACM SIGMOD International Conference on Management of Data. 1225--1239.

Digital Library

[29]

PostgreSQL wiki. 2020. Incremental View Maintenance. https://wiki.postgresql.org/wiki/Incremental_View_Maintenance. [Online; accessed 9-May-2021].

[30]

Chaoqun Zhan, Maomeng Su, Chuangxian Wei, Xiaoqiang Peng, Liang Lin, Sheng Wang, Zhe Chen, Feifei Li, Yue Pan, Fang Zheng, et al. 2019. Analyticdb: Real-time olap database system at alibaba cloud. Proceedings of the VLDB Endowment 12, 12 (2019), 2059--2070.

Digital Library

[31]

Jingren Zhou, Per-Ake Larson, and Hicham G Elmongui. 2007. Lazy maintenance of materialized views. In Proceedings of the 33rd international conference on Very large data bases. 231--242.

Digital Library

Cited By

Ding JAbrams MBandyopadhyay SDi Palma LJi YPagano DPaliwal GParchas PPfeil PPolychroniou OSaxena GShah AVoloder AXiao SZhang DKraska TBarcelo PSanchez-Pi NMeliou ASudarshan S(2024)Automated Multidimensional Data Layouts in Amazon RedshiftCompanion of the 2024 International Conference on Management of Data10.1145/3626246.3653379(55-67)Online publication date: 9-Jun-2024
https://dl.acm.org/doi/10.1145/3626246.3653379
Manavi M(2024)Multi-Objective Genetic Algorithm for Materialized View Optimization in Data Warehouses2024 4th Interdisciplinary Conference on Electrics and Computer (INTCEC)10.1109/INTCEC61833.2024.10602830(1-6)Online publication date: 11-Jun-2024
https://doi.org/10.1109/INTCEC61833.2024.10602830

Index Terms

Foreign Keys Open the Door for Faster Incremental View Maintenance
1. Information systems
  1. Data management systems
    1. Database management system engines

Recommendations

Incremental Recomputation of Active Relational Expressions

Database updates are small and incremental compared to database contents. It is therefore desirable that recomputations of active relational expressions-such as views, derived data, integrity constraints, active queries, and monitors-can also be ...
Incremental View Maintenance For Collection Programming
PODS '16: Proceedings of the 35th ACM SIGMOD-SIGACT-SIGAI Symposium on Principles of Database Systems

In the context of incremental view maintenance (IVM), delta query derivation is an essential technique for speeding up the processing of large, dynamic datasets. The goal is to generate delta queries that, given a small change in the input, can update ...
Incremental maintenance of materialized views with outerjoins

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

Comments

Information & Contributors

Information

Published In

cover image Proceedings of the ACM on Management of Data

Proceedings of the ACM on Management of Data Volume 1, Issue 1

PACMMOD

May 2023

2807 pages

EISSN:2836-6573

DOI:10.1145/3603164

Editor:
Divyakant Agrawal
UC Santa Barbara, United States

Issue’s Table of Contents

Copyright © 2023 Owner/Author.

This work is licensed under a Creative Commons Attribution-NonCommercial International 4.0 License.

Publisher

Association for Computing Machinery

New York, NY, United States

Publication History

Published: 30 May 2023

Published in PACMMOD Volume 1, Issue 1

Author Tags

Qualifiers

Research-article

Contributors

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

2
Total Citations
View Citations
453
Total Downloads

Downloads (Last 12 months)350
Downloads (Last 6 weeks)25

Reflects downloads up to 30 Aug 2024

Other Metrics

View Author Metrics

Citations

Cited By

Ding JAbrams MBandyopadhyay SDi Palma LJi YPagano DPaliwal GParchas PPfeil PPolychroniou OSaxena GShah AVoloder AXiao SZhang DKraska TBarcelo PSanchez-Pi NMeliou ASudarshan S(2024)Automated Multidimensional Data Layouts in Amazon RedshiftCompanion of the 2024 International Conference on Management of Data10.1145/3626246.3653379(55-67)Online publication date: 9-Jun-2024
https://dl.acm.org/doi/10.1145/3626246.3653379
Manavi M(2024)Multi-Objective Genetic Algorithm for Materialized View Optimization in Data Warehouses2024 4th Interdisciplinary Conference on Electrics and Computer (INTCEC)10.1109/INTCEC61833.2024.10602830(1-6)Online publication date: 11-Jun-2024
https://doi.org/10.1109/INTCEC61833.2024.10602830

View Options

View options

PDF

View or Download as a PDF file.

eReader

View online with eReader.

Get Access

Login options

Check if you have access through your login credentials or your institution to get full access on this article.

Full Access

Get this Article

Media

Figures

Other

Tables

View Issue’s Table of Contents