Materialized views and indexes are physical structures for accelerating data access that are casually used in data warehouses. However, these data structures generate some maintenance overhead. They also share the same storage space. Most existing studies about materialized view and index selection consider these structures separately. In this paper, we adopt the opposite stance and couple materialized view and index selection to take view-index interactions into account and achieve efficient storage space sharing. Candidate materialized views and indexes are selected through a data mining process. We also exploit cost models that evaluate the respective benefit of indexing and view materialization, and help select a relevant configuration of indexes and materialized views among the candidates. Experimental results show that our strategy performs better than an independent selection of materialized views and indexes.
Let @math and @math be two sets of materialized views and indexes, respectively, that are termed candidate and are susceptible to reduce the execution cost of a given query set @math (generally supposed representative of system workload). Let @math . Let @math be the storage space allotted by the data warehouse administrator to build objects (materialized views or indexes) from set @math . The joint materialized view and index selection problem consists in building an object configuration @math that minimizes the execution cost of @math , under storage space constraint. This NP-hard problem @cite_56 @cite_17 may be formalized as follows: @math ; @math , where @math is the disk space occupied by object @math .
@cite_17 @cite_56
1607470293 2049859567
A data warehouse is a repository (database) that integrates information extracted from various remote sources, with the purpose of efficiently supporting decision support queries. The information stored at the warehouse is in the form of database tables, referred to as materialized views, derived from the data in the sources. In order to keep a materialized view consistent with the data at sources, the view needs to be incrementally maintained. The two important issues that arise in the design of a data warehouse are selection of views to materialize and incremental maintenance of materialized views. This doctoral thesis looks at these two design issues and presents comprehensive solutions to both problems. Selection of views to materialize. We develop a theoretical framework for the general problem of selection of views in a data warehouse. Given a set of queries to be supported, the view selection problem is to select a set of views to materialize minimizing the query response time given some resource constraint. For different resource constraints and settings, we have designed approximation algorithms that provably return a set of views having a query benefit within a constant factor of the optimal. Incremental maintenance of general view expressions. Traditional maintenance algorithms maintain view expressions in response to changes at the base relations by computing and propagating insertions and deletions through intermediate subexpressions. In this thesis, we have developed a change-table technique, that computes and propagates “change-tables” through subexpressions, for incremental maintenance of general view expressions involving aggregate and outerjoin operators. We show that the presented change-table technique outperforms the previously proposed techniques by orders of magnitude. Dwarf is a highly compressed structure for computing, storing, and querying data cubes. Dwarf identifies prefix and suffix structural redundancies and factors them out by coalescing their store. Prefix redundancy is high on dense areas of cubes but suffix redundancy is significantly higher for sparse areas. Putting the two together fuses the exponential sizes of high dimensional full cubes into a dramatically condensed data structure. The elimination of suffix redundancy has an equally dramatic reduction in the computation of the cube because recomputation of the redundant suffixes is avoided. This effect is multiplied in the presence of correlation amongst attributes in the cube. A Petabyte 25-dimensional cube was shrunk this way to a 2.3GB Dwarf Cube, in less than 20 minutes, a 1:400000 storage reduction ratio. Still, Dwarf provides 100 precision on cube queries and is a self-sufficient structure which requires no access to the fact table. What makes Dwarf practical is the automatic discovery,in a single pass over the fact table, of the prefix and suffix redundancies without user involvement or knowledge of the value distributions.This paper describes the Dwarf structure and the Dwarf cube construction algorithm. Further optimizations are then introduced for improving clustering and query performance. Experiments with the current implementation include comparisons on detailed measurements with real and synthetic datasets against previously published techniques. The comparisons show that Dwarfs by far out-perform these techniques on all counts: storage space, creation time, query response time, and updates of cubes.