Particle swarm optimization for bitmap join indexes selection problem in data warehouses

Abstract

Data warehouses are very large databases usually designed using the star schema. Queries defined on data warehouses are generally complex due to join operations involved. The performance of star schema queries in data warehouses is highly critical and its optimization is hard in general. Several query performance optimization methods exist, such as indexes and table partitioning. In this paper, we propose a new approach based on binary particle swarm optimization for solving the bitmap join index selection problem in data warehouses. This approach selects the optimal set of bitmap join indexes based on a mathematical cost model. Several experiments are performed to demonstrate the effectiveness of the proposed method on the bitmap join index selection problem. Further testing of the method is performed using a database environment specific cost function. The binary particle swarm optimization is found to be more effective than both the genetic algorithm and data mining based approaches.

This is a preview of subscription content, access via your institution.

Fig. 1
Fig. 2
Fig. 3
Fig. 4
Fig. 5
Fig. 6
Fig. 7
Fig. 8
Fig. 9

Notes

  1. 1.

    http://jenetics.sourceforge.net/.

  2. 2.

    http://www.philippe-fournier-viger.com/spmf/.

References

  1. 1.

    Kimball R, Ross M (2002) The data warehouse toolkit: the complete guide to dimensional modeling, 2nd edn. Wiley, New York

    Google Scholar 

  2. 2.

    Mishra P, Eich M (1992) Join processing in relational databases. ACM Comput Surv 24(1):63–113

    Article  Google Scholar 

  3. 3.

    O’Neil P, Quass D (1997) Improved query performance with variant indexes. In: Proceedings of the ACM SIGMOD international conference on management of data, pp 38–49

  4. 4.

    Sanjay A, Surajit C, Narasayya VR (2000) Automated selection of materialized views and indexes in microsoft sql server. In: Proceedings of VLDB, pp 496–505

  5. 5.

    Zilio DC, Rao J, Lightstone S et al (2004) Db2 design advisor: integrated automatic physical database design. In: Proceedings of VLDB, pp 1087–1097

  6. 6.

    O’Neil P, Graefe G (1995) Multi-table joins through bitmapped join indices. ACM SIGMOD Rec 24(3):8–11

    Article  Google Scholar 

  7. 7.

    Johnson T (1999) Performance measurements of compressed bitmap indices. In: Proceedings of the international conference on very large databases, pp 278–289

  8. 8.

    Madduri K, Wu K (2009), Efficient joins with compressed bitmap indexes. In: Proceedings of the 18th ACM conference on information and, knowledge management, pp 1017–1026

  9. 9.

    Lemire D, Kaser O, Aouiche K (2010) Sorting improves word-aligned bitmap indexes. Data Knowl Eng 69(1):3–28

    Article  Google Scholar 

  10. 10.

    Kratica J, Ljubic I, Tosic D (2003) A genetic algorithm for the index selection problem. In: Proceedings of EvoWorkshops’03: the 2003 international conference on applications of evolutionary, computing, pp 280–290

  11. 11.

    Comer D (1978) The difficulty of optimum index selection. ACM Trans Database Syst 3(4):440–445

    Article  Google Scholar 

  12. 12.

    Ozsu MT, Valduriez P (1999) Principles of distributed database systems, 2nd edn. Prentice Hall, New Jersey

    Google Scholar 

  13. 13.

    Chaudhuri S (2004) Index selection for databases: a hardness study and a principle heuristic solution. IEEE Trans Knowl Data Eng 16(11):1313–1323

    Article  Google Scholar 

  14. 14.

    Aouiche K, Boussaid O, Bentayeb F (2005) Automatic selection of bitmap join indexes in data warehouses. In: Proceedings of international conference on data warehousing and knowledge discovery, pp 64–73

  15. 15.

    Bellatreche L, Missaoui R, Necir H et al (2008) A data mining approach for selecting bitmap join indices. J Comput Sci Eng 1(2):206–223

    Google Scholar 

  16. 16.

    Hamid N (2010) A data mining approach for efficient selection bitmap join index. Int J Data Min Model Manag 2(3):177–194

    MathSciNet  Google Scholar 

  17. 17.

    Bouchakri R, Bellatreche L (2011) On simplifying integrated physical database design. In: Proceedings of 15th international conference ADBIS 2011, pp 333–346

  18. 18.

    Gacem A, Boukhalfa K (2012) Immune algorithm for bitmap join indexes. In: Proceedings of international conference ICONIP, pp 560–567

  19. 19.

    Bellatreche L, Boukhalfa K (2010) Yet another algorithms for selecting bitmap join indexes. In: Proceedings of international conference DaWaK, pp 105–116

  20. 20.

    Steinbrunn M, Moerkotte G, Kemper A (1997) Heuristic and randomized optimization for the join ordering problem. VLDB J 6(3):191–208

    Article  Google Scholar 

  21. 21.

    Kennedy J, Eberhart R (1995) Particle swarm optimization. In: Proceedings of the IEEE international conference on neural networks, pp 1942–1948

  22. 22.

    Karaboga D, Akay B (2009) A comparative study of artificial bee colony algorithm. Appl Math Comput 214(1):108–132

    Article  MATH  MathSciNet  Google Scholar 

  23. 23.

    Kennedy J, Eberhart RC (1997) A discrete binary version of the particle swarm algorithm. In: Proceedings of IEEE international conference on systems, man, and cybernetics, pp 4104–4108

  24. 24.

    Garey R, Johnson DS (1979) Computers and intractability: a guide to the theory of NP-completeness. W.H. Freeman and Co., San Francisco

    Google Scholar 

  25. 25.

    APB-I, OLAP Benchmark (1998) Release II, OLAP Council. http://www.olapcouncil.org/

  26. 26.

    Shi Y, Eberhart RC (1998) A modified particle swarm optimizer. In: Proceedings of IEEE international conference evolutionary computation, pp 4–9

Download references

Acknowledgments

This project is partially supported by the key of high research fund of Algerian government under of national project of research support (PNR Grant No. 43/TIC/2011). The authors would like to thank the Department of Computer Science, Central Michigan University for performing some of the experiments in their labs. The authors would like to thank the anonymous reviewers for their detailed and constructive feedback, as well as the editors, who greatly helped improve this manuscript.

Author information

Affiliations

Authors

Corresponding author

Correspondence to Lyazid Toumi.

Rights and permissions

Reprints and Permissions

About this article

Cite this article

Toumi, L., Moussaoui, A. & Ugur, A. Particle swarm optimization for bitmap join indexes selection problem in data warehouses. J Supercomput 68, 672–708 (2014). https://doi.org/10.1007/s11227-013-1058-9

Download citation

Keywords

  • Data warehouse physical design
  • Bitmap join index
  • Bitmap join index selection problem
  • Particle swarm optimization