A BIT-SELECTOR TECHNIQUE FOR PERFORMANCE OPTIMIZATION OF DECISION-SUPPORT QUERIES

Ricardo Jorge Santos, Jorge Bernardino

2009

Abstract

Performance optimization of decision support queries has always been a major issue in data warehousing. A large amount of wide-ranging techniques have been used in research to overcome this problem. Bit-based techniques such as bitmap indexes and bitmap join indexes have been used and are generally accepted as standard common practice for optimizing data warehouses. These techniques are very promising due to their relatively low overhead and fast bitwise operations. In this paper, we propose a new technique which performs optimized row selection for decision support queries, introducing a bit-based attribute into the fact table. This attribute’s value for each row is set according to its relevance for processing each decision support query by using bitwise operations. Simply inserting a new column in the fact table’s structure and using bitwise operations for performing row selection makes it a simple and practical technique, which is easy to implement in any Database Management System. The experimental results, using benchmark TPC-H, demonstrates that it is an efficient optimization method which significantly improves query performance.

References

  1. S. Agrawal, S. Chaudhuri and V. R. Narasayya, “Automated Selection of Materialized Views and Indexes in SQL Databases”, 26th Int. Conference on Very Large Data Bases (VLDB), 2000.
  2. S. Agrawal, V. Narasayya and B. Yang, “Integrating Vertical and Horizontal Partitioning into Automated Physical Database Design”, ACM SIGMOD Conference, 2004.
  3. L. Bellatreche and K. Boukhalfa, “An Evolutionary Approach to Schema Partitioning Selection in a Data Warehouse Environment”, Intern. Conf. on Data Warehousing and Knowledge Discovery (DAWAK), 2005.
  4. L. Bellatreche, M. Schneider, H. Lorinquer and M. Mohania, “Bringing Together Partitioning, Materialized Views and Indexes to Optimize Performance of Relational Data Warehouses”, DAWAK, 2004.
  5. L. Bellatreche, M. Schneider, M. Mohania and B. Bhargava, “PartJoin: An Efficient Storage and Query Execution Design Strategy for Data Warehousing”, DAWAK, 2002.
  6. J. Bernardino, P. Furtado and H. Madeira, “Approximate Query Answering Using Data Warehouse Stripping”, DAWAK, 2001.
  7. P. Bizarro and H. Madeira, “Adding a PerformanceOriented Perspective to Data Warehouse Design”, DAWAK, 2002.
  8. S. Chaudhuri and V. Narasa11a, “An Efficient CostDriven Index Selection Tool for Microsoft SQL Server”, 23rd VLDB, 1997.
  9. C. Chee-Yong, “Indexing Techniques in Decision Support Systems”, PhD Thesis, Univ. of Wisconsin, 1999.
  10. P. Furtado and J. P. Costa, “Time-Interval Sampling for Improved Estimations in Data Warehouses”, DAWAK, 2002.
  11. H. Gupta et al., “Index Selection for OLAP”, Int. Conference on Data Engineering (ICDE), 1997.
  12. H. Gupta and I. S. Mumick, “Selection of Views to Materialize under a Maintenance Cost Constraint”, 8th Int. Conf. on Database Theory (ICDT), 1999.
  13. X. Hu, T. Y. Lin and E. Louie, “Bitmap Techniques for Optimizing Decision Support Queries and Association Rule Algorithms”, Int. Database Eng. and Applications Symposium (IDEAS), 2003.
  14. P. O'Neil and G. Graefe, “Multi-Table Joins Through Bitmapped Join Indices”, SIGMOD Record, Vol. 24, No. 3, September 1995.
  15. T. B. Pedersen, “How is BI Used in Industry?”, DAWAK, 2004.
  16. R. J. Santos and J. Bernardino, “PIN: A Partitioning & Indexing Optimization Method for OLAP”, Int. Conf. on Enterprise Information Systems (ICEIS), 2007.
  17. P. Vassiliadis and T. Sellis, “A Survey of Logical Models for OLAP Databases”, ACM SIGMOD Int. Conference on Management of Data (ICMD), 1999.
  18. M. C. Wu and A. P. Buchmann, “Encoded Bitmap Indexing for Data Warehouses”, 14th ICDE, 1998.
  19. R. Kimball and M. Ross, The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling, 2nd Edition, Wiley & Sons, 2002.
Download


Paper Citation


in Harvard Style

Santos R. and Bernardino J. (2009). A BIT-SELECTOR TECHNIQUE FOR PERFORMANCE OPTIMIZATION OF DECISION-SUPPORT QUERIES . In Proceedings of the 11th International Conference on Enterprise Information Systems - Volume 1: ICEIS, ISBN 978-989-8111-84-5, pages 151-157. DOI: 10.5220/0001993001510157


in Bibtex Style

@conference{iceis09,
author={Ricardo Jorge Santos and Jorge Bernardino},
title={A BIT-SELECTOR TECHNIQUE FOR PERFORMANCE OPTIMIZATION OF DECISION-SUPPORT QUERIES},
booktitle={Proceedings of the 11th International Conference on Enterprise Information Systems - Volume 1: ICEIS,},
year={2009},
pages={151-157},
publisher={SciTePress},
organization={INSTICC},
doi={10.5220/0001993001510157},
isbn={978-989-8111-84-5},
}


in EndNote Style

TY - CONF
JO - Proceedings of the 11th International Conference on Enterprise Information Systems - Volume 1: ICEIS,
TI - A BIT-SELECTOR TECHNIQUE FOR PERFORMANCE OPTIMIZATION OF DECISION-SUPPORT QUERIES
SN - 978-989-8111-84-5
AU - Santos R.
AU - Bernardino J.
PY - 2009
SP - 151
EP - 157
DO - 10.5220/0001993001510157