An integer programming approach for the view and index selection problem

Authors:
Zohreh Asgharzadeh Talebi;Rada Chirkova;Yahya Fathi
Affiliations:
SAS Institute, United States;Computer Science Department, NC State University, United States;Department of Industrial and Systems Engineering, NC State University, United States
Venue:
Data & Knowledge Engineering
Year:
2013

Citing 33
Cited 0

Integer and combinatorial optimization

Integer and combinatorial optimization
Implementing data cubes efficiently

SIGMOD '96 Proceedings of the 1996 ACM SIGMOD international conference on Management of data
An overview of data warehousing and OLAP technology

ACM SIGMOD Record
On the complexity of the view-selection problem

PODS '99 Proceedings of the eighteenth ACM SIGMOD-SIGACT-SIGART symposium on Principles of database systems
On efficient storage space distribution among materialized views and indices in data warehousing environments

Proceedings of the ninth international conference on Information and knowledge management
Database Systems: The Complete Book

Database Systems: The Complete Book
View selection using randomized search

Data & Knowledge Engineering
Exact and Approximate Algorithms for the Index Selection Problem in Physical Database Design

IEEE Transactions on Knowledge and Data Engineering
Index Selection for OLAP

ICDE '97 Proceedings of the Thirteenth International Conference on Data Engineering
Materialized Views Selection in a Multidimensional Database

VLDB '97 Proceedings of the 23rd International Conference on Very Large Data Bases
Materialized View Selection for Multidimensional Datasets

VLDB '98 Proceedings of the 24rd International Conference on Very Large Data Bases
Automated Selection of Materialized Views and Indexes in SQL Databases

VLDB '00 Proceedings of the 26th International Conference on Very Large Data Bases
An Efficient Cost-Driven Index Selection Tool for Microsoft SQL Server

VLDB '97 Proceedings of the 23rd International Conference on Very Large Data Bases
Algorithms for Materialized View Design in Data Warehousing Environment

VLDB '97 Proceedings of the 23rd International Conference on Very Large Data Bases
Processing OLAP queries in hierarchically clustered databases

Data & Knowledge Engineering - Special issue: Advances in OLAP
DB2 Advisor: An Optimizer Smart Enough to Recommend its own Indexes

ICDE '00 Proceedings of the 16th International Conference on Data Engineering
Materialization of fragmented views in multidimensional databases

Data & Knowledge Engineering
Index Selection for Databases: A Hardness Study and a Principled Heuristic Solution

IEEE Transactions on Knowledge and Data Engineering
Recommending Materialized Views and Indexes with IBM DB2 Design Advisor

ICAC '04 Proceedings of the First International Conference on Autonomic Computing
A framework for using materialized XPath views in XML query processing

VLDB '04 Proceedings of the Thirtieth international conference on Very large data bases - Volume 30
Exact and inexact methods for selecting views and indexes for OLAP performance improvement

EDBT '08 Proceedings of the 11th international conference on Extending database technology: Advances in database technology
The Data Warehouse Lifecycle Toolkit

The Data Warehouse Lifecycle Toolkit
Exact and inexact methods for solving the problem of view selection for aggregate queries

International Journal of Business Intelligence and Data Mining
Editorial: Data warehousing and OLAP (DOLAP'08)

Data & Knowledge Engineering
A genetic algorithm for the index selection problem

EvoWorkshops'03 Proceedings of the 2003 international conference on Applications of evolutionary computing
An integer programming based approach for verification and diagnosis of workflows

Data & Knowledge Engineering
A uniform approach for selecting views and indexes in a data warehouse

IDEAS'97 Proceedings of the 1997 international conference on International database engineering and applications symposium
CORADD: correlation aware database designer for materialized views and indexes

Proceedings of the VLDB Endowment
An overview of business intelligence technology

Communications of the ACM
Exact and inexact methods for solving the view and index selection problem for olap performance improvement

Exact and inexact methods for solving the view and index selection problem for olap performance improvement
A formal model for the problem of view selection for aggregate queries

ADBIS'05 Proceedings of the 9th East European conference on Advances in Databases and Information Systems
Optimizing index deployment order for evolving OLAP

Proceedings of the 15th International Conference on Extending Database Technology
Deterministic view selection for data-analysis queries: properties and algorithms

ADBIS'12 Proceedings of the 16th East European conference on Advances in Databases and Information Systems

Quantified Score

Hi-index	0.00

Visualization

Abstract

The view- and index-selection problem is a combinatorial optimization problem that arises in the context of on-line analytical processing (OLAP) in database-management systems. We propose an integer programming (IP) model for this problem and study the properties of the views and indexes that appear in the optimal solution for this model. We then use these properties to remove a number of variables and constraints from the corresponding IP model and obtain a model that is significantly smaller, yet its optimal solution is guaranteed to be optimal for the original problem. This allows us to solve realistic-size instances of the problem in reasonable time using commercial IP solvers. Subsequently, we propose heuristic strategies to further reduce the size of this IP model and dramatically reduce its execution time, although we no longer guarantee that the reduced IP model offers a globally optimal solution for the original problem. Finally, we carry out an extensive computational study to evaluate the effectiveness of these IP models for solving the OLAP view- and index-selection problem.