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, log in via an institution to check access.
Similar content being viewed by others
References
Kimball R, Ross M (2002) The data warehouse toolkit: the complete guide to dimensional modeling, 2nd edn. Wiley, New York
Mishra P, Eich M (1992) Join processing in relational databases. ACM Comput Surv 24(1):63–113
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
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
Zilio DC, Rao J, Lightstone S et al (2004) Db2 design advisor: integrated automatic physical database design. In: Proceedings of VLDB, pp 1087–1097
O’Neil P, Graefe G (1995) Multi-table joins through bitmapped join indices. ACM SIGMOD Rec 24(3):8–11
Johnson T (1999) Performance measurements of compressed bitmap indices. In: Proceedings of the international conference on very large databases, pp 278–289
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
Lemire D, Kaser O, Aouiche K (2010) Sorting improves word-aligned bitmap indexes. Data Knowl Eng 69(1):3–28
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
Comer D (1978) The difficulty of optimum index selection. ACM Trans Database Syst 3(4):440–445
Ozsu MT, Valduriez P (1999) Principles of distributed database systems, 2nd edn. Prentice Hall, New Jersey
Chaudhuri S (2004) Index selection for databases: a hardness study and a principle heuristic solution. IEEE Trans Knowl Data Eng 16(11):1313–1323
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
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
Hamid N (2010) A data mining approach for efficient selection bitmap join index. Int J Data Min Model Manag 2(3):177–194
Bouchakri R, Bellatreche L (2011) On simplifying integrated physical database design. In: Proceedings of 15th international conference ADBIS 2011, pp 333–346
Gacem A, Boukhalfa K (2012) Immune algorithm for bitmap join indexes. In: Proceedings of international conference ICONIP, pp 560–567
Bellatreche L, Boukhalfa K (2010) Yet another algorithms for selecting bitmap join indexes. In: Proceedings of international conference DaWaK, pp 105–116
Steinbrunn M, Moerkotte G, Kemper A (1997) Heuristic and randomized optimization for the join ordering problem. VLDB J 6(3):191–208
Kennedy J, Eberhart R (1995) Particle swarm optimization. In: Proceedings of the IEEE international conference on neural networks, pp 1942–1948
Karaboga D, Akay B (2009) A comparative study of artificial bee colony algorithm. Appl Math Comput 214(1):108–132
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
Garey R, Johnson DS (1979) Computers and intractability: a guide to the theory of NP-completeness. W.H. Freeman and Co., San Francisco
APB-I, OLAP Benchmark (1998) Release II, OLAP Council. http://www.olapcouncil.org/
Shi Y, Eberhart RC (1998) A modified particle swarm optimizer. In: Proceedings of IEEE international conference evolutionary computation, pp 4–9
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
Authors and Affiliations
Corresponding author
Rights 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
Published:
Issue Date:
DOI: https://doi.org/10.1007/s11227-013-1058-9