A framework for database optimization and workload control

Abstract

Nowadays, database optimizers take advantage of indexes and materialized views (MVs) to produce query execution plans. While indexes and MVs can speed up the execution of queries, there are costs to store and manage them. This paper presents a mathematical model and a greedy implementation to recommend a set of indexes and MVs in order to optimize the database, given a database workload and a disk space constraint. Our approach is generic, so it can be used to any relational database system that takes advantage of MVs in plan selection. While it was developed for frequently updated databases, it also produced interesting results in read only databases and to estimate the behavior of new databases (with predefined workloads).

Keywords

• Database optimization
• materialized view
• index
• query execution plan