PIN: A PARTITIONING & INDEXING OPTIMIZATION METHOD FOR OLAP

Ricardo Jorge Santos, Jorge Bernardino

Abstract

Optimizing the performance of OLAP queries in relational data warehouses (DW) has always been a major research issue. There are various techniques that can be used to achieve its goals, such as data partitioning, indexing, data aggregation, data sampling, redefinition of database (DB) schemas, among others. In this paper we present a simple and easy to implement method which links partitioning and indexing based on the features present in predefined major decision making queries to efficiently optimize a data warehouse’s performance. The evaluation of this method is also presented using the TPC-H benchmark, comparing it with standard partitioning and indexing techniques, demonstrating its efficiency with single and multiple simultaneous user scenarios.

References

  1. Agrawal, S., Chaudhuri, S., Narasayya, V., 2000. Automated selection of materialized views and indexes in SQL databases, 26th Int. Conf. on Very Large Data Bases (VLDB).
  2. Baralis, E., Paraboschi, S., Teniente, E., 1997. Materialized view selection in a multidimensional database, 23rd Int. Conference on Very Large Data Bases (VLDB).
  3. Bellatreche, L., Boukhalfa, K., 2005. An Evolutionary Approach to Schema Partitioning Selection in a Data Warehouse Environment, Int. Conf. on Data Warehousing and Knowledge Discovery (DAWAK).
  4. Bellatreche, L., Karlapalem, K., Schneider, M., Mohania, M., 2000. What can partitioning do for your data warehouses and data marts, Int. Database Engineering and Applications Symposium (IDEAS).
  5. Bellatreche, L., Karlapalem, K., Li, Q., 2000B. Evaluation of indexing materialized views in data warehousing environments, Int. Conference on Data Warehousing and Knowledge Discovery (DAWAK).
  6. Bellatreche, L., Schneider, M., Lorinquer, H., Mohania, M., 2004. Bringing Together Partitioning, Materialized Views and Indexes to Optimize Performance of Relational Data Warehouses, Int. Conference on. Data Warehousing and Knowledge Discovery (DAWAK).
  7. Bellatreche, L., Schneider, M., Mohania, M., Bhargava, B., 2002. PartJoin: an efficient storage and query execution design strategy for data warehousing, Int. Conf. on Data W. and Knowledge Discovery (DAWAK).
  8. Bizarro, P., Madeira, H., 2002. Adding a PerformanceOriented Perspective to Data Warehouse Design, International Conference on Data Warehousing and Knowledge Discovery (DAWAK).
  9. Chaudhuri, S., Narasayya, V., 1997. An efficient costdriven index selection tool for Microsoft SQL Server, 23rd Int. Conf. on Very Large Data Bases (VLDB).
  10. Chee-Yong, C., 1999. Indexing techniques in Decision Support Systems, PhD Thesis, University of Wisconsin, Madison.
  11. Furtado, P., Costa, J. P., 2002. Time-Interval Sampling for Improved Estimations in Data Warehouses, Int. Conf. on Data W. and Knowledge Discovery (DAWAK).
  12. Gupta, H., et al., 1997. Index selection for OLAP, Intern. Conference on Data Engineering (ICDE).
  13. Gupta, H., Mumick, I. S., 1999. Selection of views to materialize under a maintenance cost constraint, 8th Int. Conf. Database Theory (ICDT).
  14. Kalnis, P., Papadias, D., 2001. Proxy-server architecture for OLAP, ACM SIGMOD Int. Conf. on Management of Data (ICMD).
  15. O'Neil, P., Quass, D., 1997. Improved query performance with variant indexes, ACM SIGMOD International Conf. on Management of Data (ICMD).
  16. Sanjay, A., Narasayya, V. R., Yang, B., 2004. Integrating vertical and horizontal partitioning into automated physical database design, ACM SIGMOD Int. Conf. on Management of Data (ICMD).
  17. Vassiliadis, P., Sellis, T., 1999. A Survey of Logical Models for OLAP Databases, ACM SIGMOD Int. Conf. on Management of Data (ICMD).
Download


Paper Citation


in Harvard Style

Jorge Santos R. and Bernardino J. (2007). PIN: A PARTITIONING & INDEXING OPTIMIZATION METHOD FOR OLAP . In Proceedings of the Ninth International Conference on Enterprise Information Systems - Volume 1: ICEIS, ISBN 978-972-8865-88-7, pages 170-177. DOI: 10.5220/0002398301700177


in Bibtex Style

@conference{iceis07,
author={Ricardo Jorge Santos and Jorge Bernardino},
title={PIN: A PARTITIONING & INDEXING OPTIMIZATION METHOD FOR OLAP},
booktitle={Proceedings of the Ninth International Conference on Enterprise Information Systems - Volume 1: ICEIS,},
year={2007},
pages={170-177},
publisher={SciTePress},
organization={INSTICC},
doi={10.5220/0002398301700177},
isbn={978-972-8865-88-7},
}


in EndNote Style

TY - CONF
JO - Proceedings of the Ninth International Conference on Enterprise Information Systems - Volume 1: ICEIS,
TI - PIN: A PARTITIONING & INDEXING OPTIMIZATION METHOD FOR OLAP
SN - 978-972-8865-88-7
AU - Jorge Santos R.
AU - Bernardino J.
PY - 2007
SP - 170
EP - 177
DO - 10.5220/0002398301700177