Advertisement

Implementing Window Functions in a Column-Store with Late Materialization

  • Nadezhda Mukhaleva
  • Valentin Grigorev
  • George ChernishevEmail author
Conference paper
Part of the Lecture Notes in Computer Science book series (LNCS, volume 11815)

Abstract

A window function is a generalization of the aggregation operation. Unlike aggregation, the cardinality of its output is always the same as the cardinality of input. That is, the semantics of this operator imply computing values for extra attributes for each row, depending on its context, either expressed by a sliding window or a previously evaluated row. Window functions are a very powerful tool, which is also popular among data analysts and supported by the majority of industrial DBMSes. It allows to gracefully express quite complex use-cases, such as running sums and averages, local maximum and minimum, and different types of ranking. Since they can be expressed without self-joins and correlated subqueries, their evaluation can be performed much more efficiently.

In this paper we discuss an implementation of window functions inside a disk-based column-store with late materialization. Late materialization is a technique that aims to keep tuple reconstruction back from individual columns as long as possible. Initially popular in the late 00’s, it is rarely considered nowadays. However, in case of window functions it allows to substantially lower memory footprint. Another contribution of this paper is the application of a segment tree to computing RANGE-based window functions.

Keywords

Window function Analytical function Aggregation Column-store Query processing Late materialization OLAP PosDB 

References

  1. 1.
  2. 2.
    Abadi, D., Boncz, P., Harizopoulos, S.: The Design and Implementation of Modern Column-Oriented Database Systems. Now Publishers Inc., Hanover (2013)Google Scholar
  3. 3.
    Bellamkonda, S., Bozkaya, T., Gupta, B.G.A., Haydu, J., Subramanian, S., Witkowski, A.: Analytic Functions in Oracle 8i. Technical report (2000). http://infolab.stanford.edu/infoseminar/archive/SpringY2000/speakers/agupta/paper.pdf
  4. 4.
    Cao, Y., Chan, C.Y., Li, J., Tan, K.L.: Optimization of analytic window functions. Proc. VLDB Endow. 5(11), 1244–1255 (2012)CrossRefGoogle Scholar
  5. 5.
    Chernishev, G.A., Galaktionov, V.A., Grigorev, V.D., Klyuchikov, E.S., Smirnov, K.K.: PosDB: an architecture overview. Programm. Comput. Softw. 44(1), 62–74 (2018)MathSciNetCrossRefGoogle Scholar
  6. 6.
    Chernishev, G., Galaktionov, V., Grigorev, V., Klyuchikov, E., Smirnov, K.: PosDB: a distributed column-store engine. In: Petrenko, A.K., Voronkov, A. (eds.) PSI 2017. LNCS, vol. 10742, pp. 88–94. Springer, Cham (2018).  https://doi.org/10.1007/978-3-319-74313-4_7CrossRefGoogle Scholar
  7. 7.
    Coelho, F., Pereira, J., Vilaça, R., Oliveira, R.: Holistic shuffler for the parallel processing of SQL window functions. In: Jelasity, M., Kalyvianaki, E. (eds.) DAIS 2016. LNCS, vol. 9687, pp. 75–81. Springer, Cham (2016).  https://doi.org/10.1007/978-3-319-39577-7_6CrossRefGoogle Scholar
  8. 8.
    Graefe, G.: Query evaluation techniques for large databases. ACM Comput. Surv. 25(2), 73–169 (1993)CrossRefGoogle Scholar
  9. 9.
    Harizopoulos, S., Abadi, D., Boncz, P.: Column-Oriented Database Systems, VLDB 2009 Tutorial 2(2), 1664–1665 (2009). http://nms.csail.mit.edu/~stavros/pubs/tutorial2009- column_stores.pdf
  10. 10.
    Jacobson, N.: Semi-Groups and Groups, pp. 15–48. Springer, New York (1951).  https://doi.org/10.1007/978-1-4684-7301-8_2
  11. 11.
    Leis, V., Kundhikanjana, K., Kemper, A., Neumann, T.: Efficient processing of window functions in analytical SQL queries. Proc. VLDB Endow. 8(10), 1058–1069 (2015)CrossRefGoogle Scholar
  12. 12.
    O’Neil, P., Chen, X.: Star Schema Benchmark, June 2009. http://www.cs.umb.edu/~poneil/StarSchemaB.PDF
  13. 13.
    Wesley, R., Xu, F.: Incremental computation of common windowed holistic aggregates. Proc. VLDB Endow. 9(12), 1221–1232 (2016)CrossRefGoogle Scholar
  14. 14.
    Zuzarte, C., Pirahesh, H., Ma, W., Cheng, Q., Liu, L., Wong, K.: Winmagic: subquery elimination using window aggregation. In: SIGMOD 2003, pp. 652–656. ACM, New York (2003)Google Scholar

Copyright information

© Springer Nature Switzerland AG 2019

Authors and Affiliations

  1. 1.Information Systems Engineering LabJetBrains ResearchSaint PetersburgRussia
  2. 2.Saint Petersburg UniversitySaint PetersburgRussia
  3. 3.National Research University Higher School of EconomicsMoscowRussia

Personalised recommendations