The Journal of Supercomputing

, Volume 68, Issue 2, pp 672–708 | Cite as

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

  • Lyazid Toumi
  • Abdelouahab Moussaoui
  • Ahmet Ugur


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.


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



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.


  1. 1.
    Kimball R, Ross M (2002) The data warehouse toolkit: the complete guide to dimensional modeling, 2nd edn. Wiley, New YorkGoogle Scholar
  2. 2.
    Mishra P, Eich M (1992) Join processing in relational databases. ACM Comput Surv 24(1):63–113CrossRefGoogle 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–49Google Scholar
  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–505Google Scholar
  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–1097Google Scholar
  6. 6.
    O’Neil P, Graefe G (1995) Multi-table joins through bitmapped join indices. ACM SIGMOD Rec 24(3):8–11CrossRefGoogle Scholar
  7. 7.
    Johnson T (1999) Performance measurements of compressed bitmap indices. In: Proceedings of the international conference on very large databases, pp 278–289Google Scholar
  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–1026Google Scholar
  9. 9.
    Lemire D, Kaser O, Aouiche K (2010) Sorting improves word-aligned bitmap indexes. Data Knowl Eng 69(1):3–28CrossRefGoogle 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–290Google Scholar
  11. 11.
    Comer D (1978) The difficulty of optimum index selection. ACM Trans Database Syst 3(4):440–445CrossRefGoogle Scholar
  12. 12.
    Ozsu MT, Valduriez P (1999) Principles of distributed database systems, 2nd edn. Prentice Hall, New JerseyGoogle 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–1323CrossRefGoogle 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–73Google Scholar
  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–223Google 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–194MathSciNetGoogle Scholar
  17. 17.
    Bouchakri R, Bellatreche L (2011) On simplifying integrated physical database design. In: Proceedings of 15th international conference ADBIS 2011, pp 333–346Google Scholar
  18. 18.
    Gacem A, Boukhalfa K (2012) Immune algorithm for bitmap join indexes. In: Proceedings of international conference ICONIP, pp 560–567Google Scholar
  19. 19.
    Bellatreche L, Boukhalfa K (2010) Yet another algorithms for selecting bitmap join indexes. In: Proceedings of international conference DaWaK, pp 105–116Google Scholar
  20. 20.
    Steinbrunn M, Moerkotte G, Kemper A (1997) Heuristic and randomized optimization for the join ordering problem. VLDB J 6(3):191–208CrossRefGoogle Scholar
  21. 21.
    Kennedy J, Eberhart R (1995) Particle swarm optimization. In: Proceedings of the IEEE international conference on neural networks, pp 1942–1948Google Scholar
  22. 22.
    Karaboga D, Akay B (2009) A comparative study of artificial bee colony algorithm. Appl Math Comput 214(1):108–132CrossRefzbMATHMathSciNetGoogle 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–4108Google Scholar
  24. 24.
    Garey R, Johnson DS (1979) Computers and intractability: a guide to the theory of NP-completeness. W.H. Freeman and Co., San FranciscozbMATHGoogle Scholar
  25. 25.
    APB-I, OLAP Benchmark (1998) Release II, OLAP Council.
  26. 26.
    Shi Y, Eberhart RC (1998) A modified particle swarm optimizer. In: Proceedings of IEEE international conference evolutionary computation, pp 4–9Google Scholar

Copyright information

© Springer Science+Business Media New York 2013

Authors and Affiliations

  • Lyazid Toumi
    • 1
  • Abdelouahab Moussaoui
    • 1
  • Ahmet Ugur
    • 2
  1. 1.Department of Computer SciencesUnversity of Setif 1SétifAlgeria
  2. 2.Department of Computer ScienceCentral Michigan UniversityMount PleasantUSA

Personalised recommendations