An integer programming approach for the view and index selection problem

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.