Abstract
Materialized views are logically excess stored query results in SQL-oriented databases. This technology can significantly improve the performance of database systems. Although the idea of materialized views came up in the 1980s, only three database management systems, i.e. DB2, Oracle, SQL Server, have been successfully developed completely enough with materialized views so far. The barrier lies in building a module that can incrementally update the materialized views automatically, which corresponds to data changes in the base tables. This paper presents the algorithm to incrementally update the materialized views with inner join, focusing on one with aggregate functions, and building of a program that automatically generates codes inPL/pgSQL for triggers, which can undertake synchronous incremental updates of the materialized views in PostgreSQL.
Article PDF
Similar content being viewed by others
Explore related subjects
Discover the latest articles, news and stories from top researchers in related subjects.Avoid common mistakes on your manuscript.
References
J. A. Blakeley, P.-A. Larson, and F. W. Tompa, “Efficiently updating materialized views,” SIGMOD Rec. 15 (2), 61–71 (1986).
A. Gupta, I. S. Mumich, and V. S. Subrahmanian, “Maintaining views incrementally,” Materialized Views, Ed. by A. Gupta and I. S. Mumick (MIT Press, 1999), pp. 177–190.
A. Gupta and I. S. Mumick, “Maintenance of materialized views: Problems, techniques, and applications,” Materialized Views, Ed. by G. Ashish and M. Iderpal Singh (MIT Press, 1999), pp. 145–157.
J. Zhou, P.-A. Larson, and H. G. Elmongui, “Lazy maintenance of materialized views,” Proceedings of the 33rd International Conference on Very Large Data Bases (VLDB Endowment, Vienna, Austria, 2007), pp. 231–242.
D. K. Gupta and I. S. Mumick, Counting Solutions to the View Maintenance Problem, Tech. Rep. AT&T Bell Laboratories, 1992.
K. Y. Lee and M. H. Kim, “Optimizing the incremental maintenance of multiple join views,” Proceedings of the 8th ACM International Workshop on Data Warehousing and OLAP (ACM, Germany, Bremen, 2005), pp. 107–113.
P.-A. Larson, “Maintenance of materialized views with outer-joins,” Encyclopedia of Database Systems, Ed. by L. Liu and M. T. ÖZsu (Springer US, 2009), pp. 1670–1674.
A. Nica, “Incremental maintenance of materialized views with outerjoins,” Inf. Syst. 37 (5), 430–442 (2012).
O. Shmueli and A. Itai, “Maintenance of views,” SIGMOD Rec. 14 (2), 240–255 (1984).
D. Chak, Materialized views that really work, 2008. http://www.pgcon.org/2008/schedule/attachments/64_BSDCan2008-MaterializedViews-paper.pdf. Cited September 15, 2014.
H. Gupta and I. S. Mumick, “Incremental maintenance of aggregate and outerjoin expressions,” Inf. Syst. 31 (6), 435–464 (2006).
D. Srivastava, S. Dar, H. V. Jagadish, and A. Y. Levy, “Answering Queries with aggregation using views,” Proceedings of the 22th International Conferences on Very Large Data Bases (Morgan Kaufmann Publishers Inc., 1996), pp. 318–329.
Author information
Authors and Affiliations
Corresponding author
Additional information
Original Russian Text © Nguyen Tran Quoc Vinh, 2016, published in Programmirovanie, 2016, Vol. 42, No. 5.
The article is published in the original.
Rights and permissions
About this article
Cite this article
Quoc Vinh, N.T. Synchronous incremental update of materialized views for PostgreSQL. Program Comput Soft 42, 307–315 (2016). https://doi.org/10.1134/S0361768816050066
Received:
Published:
Issue Date:
DOI: https://doi.org/10.1134/S0361768816050066