Granular Indices for HQL Analytic Queries

  • Michał Gawarkiewicz
  • Piotr Wiśniewski
  • Krzysztof Stencel
Part of the Communications in Computer and Information Science book series (CCIS, volume 424)

Abstract

Database management systems use numerous optimization techniques to accelerate complex analytical queries. Such queries have to scan enormous amounts of records. The usual technique to reduce their run-time is the materialization of partial aggregates of base data. In previous papers we have proposed the concept of metagranules, i.e. partially ordered aggregations of the fact table. When a query is posed, the actual aggregation level will be determined and the smallest fit metagranule (materialized aggregation) will be used instead of the fact table. In this paper we extend that idea with metagranular indices, i.e. indices on metagranules. Assume a user issuing an aggregate query to a fact table with a selective HAVING or small LIMIT-ORDER BY clause. The database engine can not only identify the best metagranule but it can also use the index on that metagranule in order not to scan its full content. In this paper we present the proposed optimization method based on metagranular indices. We also describe its proof-of-concept prototype implementation. Finally, we report the results of performance experiments on database instances up to 350GiB.

Keywords

analityc queries ORM databases 

Preview

Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.

References

  1. 1.
    Boniewicz, A., Gawarkiewicz, M., Wiśniewski, P.: Automatic selection of functional indexes for object relational mappings system. International Journal of Software Engineering and Its Applications 7, 189–195 (2013)Google Scholar
  2. 2.
    Bruno, N., Chaudhuri, S.: An online approach to physical design tuning. In: ICDE, pp. 826–835 (2007)Google Scholar
  3. 3.
    Chaudhuri, S., Narasayya, V.R.: An efficient cost-driven index selection tool for Microsoft SQL Server. In: Proceedings of the 23rd International Conference on Very Large Data Bases, VLDB 1997, pp. 146–155. Morgan Kaufmann Publishers Inc., San Francisco (1997), http://dl.acm.org/citation.cfm?id=645923.673646 Google Scholar
  4. 4.
    Choenni, S., Blanken, H., Chang, T.: Index selection in relational databases. In: Proc. International Conference on Computing and Information, pp. 491–496 (1993)Google Scholar
  5. 5.
    Choenni, S., Blanken, H.M., Chang, T.: On the automation of physical database design. In: Proceedings of the 1993 ACM/SIGAPP Symposium on Applied Computing: States of the Art and Practice, SAC 1993, pp. 358–367. ACM, New York (1993), http://doi.acm.org/10.1145/162754.162932 CrossRefGoogle Scholar
  6. 6.
    Finkelstein, S., Schkolnick, M., Tiberio, P.: Physical database design for relational databases. ACM Trans. Database Syst. 13(1), 91–128 (1988), http://doi.acm.org/10.1145/42201.42205 CrossRefGoogle Scholar
  7. 7.
    Gawarkiewicz, M., Wiśniewski, P.: Partial aggregation using Hibernate. In: Kim, T.-H., Adeli, H., Slezak, D., Sandnes, F.E., Song, X., Chung, K.-I., Arnett, K.P. (eds.) FGIT 2011. LNCS, vol. 7105, pp. 90–99. Springer, Heidelberg (2011)CrossRefGoogle Scholar
  8. 8.
    Graefe, G., Idreos, S., Kuno, H., Manegold, S.: Benchmarking adaptive indexing. In: Nambiar, R., Poess, M. (eds.) TPCTC 2010. LNCS, vol. 6417, pp. 169–184. Springer, Heidelberg (2011), http://dl.acm.org/citation.cfm?id=1946050.1946063 CrossRefGoogle Scholar
  9. 9.
    Graefe, G., Kuno, H.: Self-selecting, self-tuning, incrementally optimized indexes. In: Proceedings of the 13th International Conference on Extending Database Technology, EDBT 2010, pp. 371–381. ACM, New York (2010), http://doi.acm.org/10.1145/1739041.1739087 Google Scholar
  10. 10.
    Hammer, M., Chan, A.: Index selection in a self-adaptive data base management system. In: Proceedings of the 1976 ACM SIGMOD International Conference on Management of Data, SIGMOD 1976, pp. 1–8. ACM, New York (1976), http://dl.acm.org/citation.cfm?id=509383.509385 CrossRefGoogle Scholar
  11. 11.
    Idreos, S., Kersten, M.L., Manegold, S.: Database cracking. In: CIDR 2007, Third Biennial Conference on Innovative Data Systems Research, Asilomar, CA, USA, January 7-10, pp. 68–78 (2007) (Online Proceedings)Google Scholar
  12. 12.
    Idreos, S., Manegold, S., Kuno, H., Graefe, G.: Merging what’s cracked, cracking what’s merged: adaptive indexing in main-memory column-stores. Proc. VLDB Endow. 4(9), 586–597 (2011), http://dl.acm.org/citation.cfm?id=2002938.2002944 Google Scholar
  13. 13.
    Rozen, S., Shasha, D.: A framework for automating physical database design. In: Proceedings of the 17th International Conference on Very Large Data Bases, VLDB 1991, pp. 401–411. Morgan Kaufmann Publishers Inc., San Francisco (1991), http://dl.acm.org/citation.cfm?id=645917.758359 Google Scholar
  14. 14.
    Schnaitter, K., Polyzotis, N.: A benchmark for online index selection. In: Proceedings of the 2009 IEEE International Conference on Data Engineering, ICDE 2009, pp. 1701–1708. IEEE Computer Society, Washington, DC (2009), http://dx.doi.org/10.1109/ICDE.2009.166 Google Scholar
  15. 15.
    Winiewski, P., Stencel, K.: Query rewriting based on meta-granular aggregation, pp. 457–468, http://csp2013.mimuw.edu.pl/proceedings/PDF/paper-40.pdf

Copyright information

© Springer International Publishing Switzerland 2014

Authors and Affiliations

  • Michał Gawarkiewicz
    • 1
  • Piotr Wiśniewski
    • 1
  • Krzysztof Stencel
    • 1
  1. 1.Faculty of Mathematics and Computer ScienceNicolaus Copernicus UniversityToruńPoland

Personalised recommendations