Skip to main content
SpringerLink
Log in

Optimization and Tuning in Data Warehouses

  • Living reference work entry
  • First Online:
Encyclopedia of Database Systems

  • 49 Accesses

Synonyms

Physical design

Definition

Optimization and tuning in data warehouses (\(\mathcal{D}\mathcal{W}\)) are the processes of selecting and managing adequate and optimal techniques in order to make queries and updates run faster and to maintain their performance by maximizing the use of \(\mathcal{D}\mathcal{W}\) system resources and satisfying specific constraints. A \(\mathcal{D}\mathcal{W}\) is usually accessed by complex queries for key business operations. They must be completed in seconds not days to satisfy the decision-makers’ requirements. To continuously improve the query performance, two main phases are required: physical design and tuning. In the physical design phase, a \(\mathcal{D}\mathcal{W}\) administrator selects the best techniques such as materialized views, advanced indexes, data compression, horizontal partitioning, and parallel processingby exploiting advanced high-performance computing (HPC) and emerging hardware. Generally, this selection is based on most...

This is a preview of subscription content, log in via an institution to check access.

Access this chapter

Log in via an institution

Institutional subscriptions

Recommended Reading

  1. Abadi D, Boncz PA, Harizopoulos S, Idreos S, Madden S. The design and implementation of modern column-oriented database systems. Found Trends Databases. 2013;5(3):197–280.

    Article  Google Scholar 

  2. Bellatreche L, Boukhalfa K, Mohania MK. Pruning search space of physical database design. In: 18 International Conference on Database and Expert Systems Applications (DEXA’07); Sept 2007. p. 479–88.

    Google Scholar 

  3. Bellatreche L, Boukhalfa K, Richard P, Woameno KY. Referential horizontal partitioning selection problem in data warehouses: hardness study and selection algorithms. Int J Data Warehouse Min. 2009;5(4):1–23.

    Article  Google Scholar 

  4. Bellatreche L, Cuzzocrea A, Benkrid S. Effectively and efficiently designing and querying parallel relational data warehouses on heterogeneous database clusters: the f&a approach. J Database Manag. 2012;23(4):17–51.

    Article  Google Scholar 

  5. Bellatreche L, Missaoui R, Necir H, Drias H. Selection and pruning algorithms for bitmap index selection problem using data mining. In: International Conference on Data Warehousing and Knowledge Discovery (DaWaK’07); Sept 2007. p. 221–30.

    Google Scholar 

  6. Benkrid S, Bellatreche L, Cuzzocrea A. Designing parallel relational data warehouses: a global, comprehensive approach. In: ADBIS; 2013. p. 141–50.

    Google Scholar 

  7. Chambi S, Lemire D, Kaser O, Godin R. Better bitmap performance with roaring bitmaps. Softw Pract Exper. 2016;46(5):709–19.

    Article  Google Scholar 

  8. Chaudhuri S, Narasayya V. Self-tuning database systems: a decade of progress. In: Proceedings of the International Conference on Very Large Databases; Sept 2007. p. 3–14.

    Google Scholar 

  9. Chaudhuri S, Weikum G. Self-management technology in databases. In: Encyclopedia of Database Systems; 2009. p. 2550–55.

    Google Scholar 

  10. Deliège F, Pedersen TB. Position list word aligned hybrid: optimizing space and performance for compressed bitmaps. In: 13th International Conference on Extending Database Technology (EDBT); 2010. p. 228–39.

    Google Scholar 

  11. Du J, Miller RJ, Glavic B, Tan W. Deepsea: progressive workload-aware partitioning of materialized views in scalable data analytics. In: 20th International Conference on Extending Database Technology (EDBT); 2017. p. 198–209.

    Google Scholar 

  12. Goswami R, Bhattacharyya DK, Dutta M. Materialized view selection using evolutionary algorithm for speeding up big data query processing. J Intell Inf Syst. 2017;1–27.

    Google Scholar 

  13. Gupta H. Selection of views to materialize in a data warehouse. In: 6th International Conference on Database Theory (ICDT); 1997. p. 98–112.

    Google Scholar 

  14. Gupta H. Selection and maintenance of views in a data warehouse. Ph.d. thesis, Stanford University, Sept 1999.

    Google Scholar 

  15. Ibragimov D, Hose K, Pedersen TB, Zimányi E. Optimizing aggregate SPARQL queries using materialized RDF views. In: 15th International Semantic Web Conference (ISWC); 2016. p. 341–59.

    Google Scholar 

  16. Idreos S, Groffen F, Nes N, Manegold S, Sjoerd Mullender K, Kersten ML. Monetdb: two decades of research in column-oriented database architectures. IEEE Data Eng Bull. 2012;35(1):40–45.

    Google Scholar 

  17. Kotidis Y, Roussopoulos N. Dynamat: a dynamic view management system for data warehouses. In: Proceedings of the ACM SIGMOD International Conference on Management of Data; 1999. p. 371–82.

    Google Scholar 

  18. Lamb A, Fuller M, Varadarajan R, Tran N, Vandier B, Doshi L, Bear C. The vertica analytic database: C-store 7 years later. PVLDB. 2012;5(12):1790–801.

    Google Scholar 

  19. Lübcke A. Automated query interface for hybrid relational architectures. PhD thesis, University of Magdeburg; 2017.

    Google Scholar 

  20. MacNicol R, French B. Sybase IQ multiplex – designed for analytics. In: Proceedings of the International Conference on Very Large Databases; 2004. p. 1227–30.

    Google Scholar 

  21. Mahboubi H, Darmont J. Data mining-based fragmentation of xml data warehouses. In: DOLAP; 2008. p. 9–16.

    Google Scholar 

  22. Mami I, Bellahsene Z. A survey of view selection methods. SIGMOD Rec. 2012;41(1):20–29.

    Article  Google Scholar 

  23. O’Neil PE, Quass D. Improved query performance with variant indexes. In: Proceedings of the ACM SIGMOD International Conference on Management of Data; 1997. p. 38–49.

    Google Scholar 

  24. Oracle Data Sheet. Oracle partitioning. White Paper: http://www.oracle.com/technology/products/bi/db/11g/; 2007.

  25. Özsu MT, Valduriez P. Principles of distributed database systems. 2nd ed. Upper Saddle River: Prentice Hall; 1999.

    Google Scholar 

  26. Papadomanolakis S, Ailamaki A. Autopart: automating schema design for large scientific databases using data partitioning. In: Proceedings of the 16th International Conference on Scientific and Statistical Database Management (SSDBM’04); June 2004. p. 383–92.

    Google Scholar 

  27. Perriot R, Pfeifer J, d’Orazio L, Bachelet B, Bimonte S, Darmont J. Cost models for selecting materialized views in public clouds. Int J Data Warehouse Min. 2014;10(4):1–25.

    Article  Google Scholar 

  28. Phan T, Li W. Dynamic materialization of query views for data warehouse workloads. In: Proceedings of the International Conference on Data Engineering (ICDE); 2008. p. 436–45.

    Google Scholar 

  29. Ross KA, Srivastava D, Sudarshan S. Materialized view maintenance and integrity constraint checking: trading space for time. In: Proceedings of the ACM SIGMOD International Conference on Management of Data; 1996. p. 447–458.

    Google Scholar 

  30. Roukh A, Bellatreche L, Bouarar S, Boukorca A. Eco-physic: eco-physical design initiative for very large databases. Inf Syst. 2017;68:44–63.

    Article  Google Scholar 

  31. Sanjay A, Narasayya VR, Yang B. Integrating vertical and horizontal partitioning into automated physical database design. In: Proceedings of the ACM SIGMOD International Conference on Management of Data; June 2004. p. 359–70.

    Google Scholar 

  32. Schuhknecht FM, Jindal A, Dittrich J. An experimental evaluation and analysis of database cracking. VLDB J. 2016;25(1):27–52.

    Article  Google Scholar 

  33. Tang N, Xu Yu J, Tang H, Tamer Özsu M, Boncz PA. Materialized view selection in XML databases. In: 14th International Conference on Database Systems for Advanced Applications (DASFAA); 2009. p. 616–30.

    Google Scholar 

  34. Thusoo A, Sen Sarma J, Jain N, Shao Z, Chakka P, Zhang N, Anthony S, Liu H, Murthy R. Hive – a petabyte scale data warehouse using hadoop. In: Proceedings of the International Conference on Data Engineering (ICDE); 2010. p. 996–1005.

    Google Scholar 

  35. Yang J, Karlapalem K, Li Q. Algorithms for materialized view design in data warehousing environment. In: Proceedings of the International Conference on Very Large Databases; Aug 1997. p. 136–45.

    Google Scholar 

  36. Zhang C, Yang J. Genetic algorithm for materialized view selection in data warehouse environments. In: International Conference on Data Warehousing and Knowledge Discovery (DAWAK); 1999. p. 116–25.

    Google Scholar 

Download references

Author information

Authors and Affiliations

  1. LIAS/ISAE-ENSMA, Poitiers University, 86960, Futuroscope, France

    Ladjel Bellatreche

Authors
  1. Ladjel Bellatreche
    View author publications

    You can also search for this author in PubMed Google Scholar

Corresponding author

Correspondence to Ladjel Bellatreche .

Editor information

Editors and Affiliations

  1. Georgia Institute of Technology College of Computing, Atlanta, Georgia, USA

    Ling Liu

  2. University of Waterloo School of Computer Science, Waterloo, Ontario, Canada

    M. Tamer Özsu

Section Editor information

  1. Department of Computer Science, Aalborg University, Selma Lagerlöfs Vej 300, 9220, Aalborg, Denmark

    Torben Bach Pedersen

  2. DISI – University of Bologna, Viale Risorgimento, 2, 40136, Bologna, Italy

    Stefano Rizzi

Rights and permissions

Reprints and permissions

Copyright information

© 2017 Springer Science+Business Media LLC

About this entry

Cite this entry

Bellatreche, L. (2017). Optimization and Tuning in Data Warehouses. In: Liu, L., Özsu, M. (eds) Encyclopedia of Database Systems. Springer, New York, NY. https://doi.org/10.1007/978-1-4899-7993-3_259-3

Download citation

  • DOI: https://doi.org/10.1007/978-1-4899-7993-3_259-3

  • Received:

  • Accepted:

  • Published:

  • Publisher Name: Springer, New York, NY

  • Print ISBN: 978-1-4899-7993-3

  • Online ISBN: 978-1-4899-7993-3

  • eBook Packages: Springer Reference Computer SciencesReference Module Computer Science and Engineering

Publish with us

Policies and ethics

Search

Navigation