Non-blocking Materialized View Creation and Transformation of Schemas

  • Jørgen Løland
  • Svein-Olaf Hvasshovd
Part of the Lecture Notes in Computer Science book series (LNCS, volume 4152)


In existing systems, user transactions get blocked during materialized view creation and non-trivial database schema transformations. Blocking user transactions is not an option in systems with high availability requirements. A non-blocking method to perform such tasks is therefore needed.

In this paper, we present a method for non-blocking creation of derived tables, suitable for highly available databases. These derived tables can be used to create materialized views and to transform the database schema. Modified versions of well-known crash recovery techniques are used, thus making the method easy to integrate into existing DBMSs. Because the involved tables are not locked, the derived table creation may run as a low priority background process. As a result, the process has little impact on concurrent user transactions.


Database Schema Schema Transformation Horizontal Merge Insert Operation Source Table 
These keywords were added by machine and not by the authors. This process is experimental and the keywords may be updated as the learning algorithm improves.


Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.


  1. 1.
    Blakeley, J.A., Larson, P.-A., Tompa, F.W.: Efficiently updating materialized views. In: Proceedings of the 1986 ACM SIGMOD Conference on Management of Data, pp. 61–71 (1986)Google Scholar
  2. 2.
    Bratsberg, S.E., Hvasshovd, S.-O., Torbjørnsen, Ø.: Parallel solutions in ClustRa. IEEE Data Eng. Bull. 20(2), 13–20 (1997)Google Scholar
  3. 3.
    Colby, L.S., Griffin, T., Libkin, L., Mumick, I.S., Trickey, H.: Algorithms for deferred view maintenance. In: Proceedings of the 1996 ACM SIGMOD International Conference on Management of Data, pp. 469–480. ACM Press, New York (1996)CrossRefGoogle Scholar
  4. 4.
    Crus, R.A.: Data Recovery in IBM Database 2. IBM Systems Journal 23(2), 178 (1984)CrossRefGoogle Scholar
  5. 5.
    Garcia-Molina, H., Salem, K.: Sagas. In: Proceedings of the 1987 ACM SIGMOD International Conference on Management of Data, pp. 249–259. ACM Press, New York (1987)CrossRefGoogle Scholar
  6. 6.
    Griffin, T., Kumar, B.: Algebraic change propagation for semijoin and outerjoin queries. ACM SIGMOD Record 27(3), 22–27 (1998)CrossRefGoogle Scholar
  7. 7.
    Gupta, A., Katiyar, D., Mumick, I.S.: Counting solutions to the view maintenance problem. In: Workshop on Deductive Databases, JICSLP, pp. 185–194 (1992)Google Scholar
  8. 8.
    Gupta, A., Mumick, I.S., Subrahmanian, V.S.: Maintaining views incrementally. In: Proceedings of the 1993 ACM SIGMOD international conference on Management of data, pp. 157–166. ACM Press, New York (1993)CrossRefGoogle Scholar
  9. 9.
    Gupta, H., Mumick, I.S.: Incremental maintenance of aggregate and outerjoin expressions. In: Information Systems (in press, 2005)Google Scholar
  10. 10.
    Hvasshovd, S.-O.: Recovery in Parallel Database Systems, 2nd edn., Verlag Vieweg (1999)Google Scholar
  11. 11.
    Hvasshovd, S.-O., Sæter, T., Torbjørnsen, Ø., Moe, P., Risnes, O.: A continuously available and highly scalable transaction server: Design experience from the HypRa project. In: Proceedings of the 4th International Workshop on High Performance Transaction Systems (1991)Google Scholar
  12. 12.
    IBM. IBM DB2 Universal Database Glossary, Version 8.2. IBM.Google Scholar
  13. 13.
    IBM. IBM DB2 Universial Database Administration Guide: Implementation, version 8.2. IBM.Google Scholar
  14. 14.
    IBM. IBM DB2 Universial Database SQL Reference, Volume 2. IBM, 8nd edn.Google Scholar
  15. 15.
    Kawaguchi, A., Lieuwen, D.F., Mumick, I.S., Quass, D., Ross, K.A.: Concurrency control theory for deferred materialized views. In: Afrati, F.N., Kolaitis, P.G. (eds.) ICDT 1997. LNCS, vol. 1186, pp. 306–320. Springer, Heidelberg (1996)Google Scholar
  16. 16.
    Korth, H.F.: Locking primitives in a database system. Journal of the ACM 30(1), 55–79 (1983)MATHCrossRefMathSciNetGoogle Scholar
  17. 17.
    Lorentz, D., Gregoire, J.: Oracle Database SQL Reference 10g Release 1 (10.1) (2003)Google Scholar
  18. 18.
    Løland, J., Hvasshovd, S.-O.: Online, non-blocking relational schema changes. In: Ioannidis, Y., Scholl, M.H., Schmidt, J.W., Matthes, F., Hatzopoulos, M., Böhm, K., Kemper, A., Grust, T., Böhm, C. (eds.) EDBT 2006. LNCS, vol. 3896, pp. 405–422. Springer, Heidelberg (2006)CrossRefGoogle Scholar
  19. 19.
    Marche, S.: Measuring the stability of data. European Journal of Information Systems 2(1), 37–47 (1993)CrossRefGoogle Scholar
  20. 20.
    Microsoft Corporation. Microsoft sql server 2005 books online (December 6, 2005),
  21. 21.
    Mohan, C., Haderle, D., Lindsay, B., Pirahesh, H., Schwarz, P.: Aries: a transaction recovery method supporting fine- granularity locking and partial rollbacks using write-ahead logging. ACM Transactions on Database Systems 17(1), 94–162 (1992)CrossRefGoogle Scholar
  22. 22.
    Qian, X., Wiederhold, G.: Incremental recomputation of active relational expressions. Knowledge and Data Engineering 3(3), 337–341 (1991)CrossRefGoogle Scholar
  23. 23.
    Quass, D., Gupta, A., Mumick, I.S., Widom, J.: Making views self-maintainable for data warehousing. In: Proceedings of the Fourth International Conference on Parallel and Distributed Information Systems, 1996, USA, pp. 158–169. IEEE Computer Society Press, Los Alamitos (1996)CrossRefGoogle Scholar
  24. 24.
    Ronström, M.: On-line schema update for a telecom database. In: Proc. of the 16th International Conference on Data Engineering, pp. 329–338. IEEE Computer Society Press, Los Alamitos (2000)Google Scholar
  25. 25.
    Shmueli, O., Itai, A.: Maintenance of views. In: Proceedings of the 1984 ACM SIGMOD International Conference on Management of Data, pp. 240–255. ACM Press, New York (1984)CrossRefGoogle Scholar
  26. 26.
    Sjøberg, D.: Quantifying schema evolution. Information and Software Technology 35(1), 35–44 (1993)CrossRefGoogle Scholar
  27. 27.
    Widenius, M., Axmark, D.: MySQL 5.1 Reference Manual (2006)Google Scholar
  28. 28.
    Zhuge, Y., Garcia-Molina, H., Hammer, J., Widom, J.: View maintenance in a warehousing environment. In: Proceedings of the 1995 ACM SIGMOD international conference on Management of data, pp. 316–327. ACM Press, New York (1995)CrossRefGoogle Scholar

Copyright information

© Springer-Verlag Berlin Heidelberg 2006

Authors and Affiliations

  • Jørgen Løland
    • 1
  • Svein-Olaf Hvasshovd
    • 1
  1. 1.Dept. of Computer ScienceNTNUTrondheimNorway

Personalised recommendations