[Paper Review] Data Mining-based Materialized View and Index Selection in Data Warehouses

Kamel Aouiche, Jérôme Darmont|ArXiv.org|Jul 11, 2007

Data Mining Algorithms and Applications54 references4 citations

TL;DR

This paper proposes a data mining-based approach for jointly selecting materialized views and indexes in data warehouses to optimize query performance while minimizing storage overhead and maintenance costs. By leveraging data mining to identify high-value candidates and using cost models that account for view-index interactions, the method achieves up to 30% better performance gain than independent selection, especially under moderate to large storage constraints.

ABSTRACT

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.

Motivation & Objective

• To address the scalability and complexity of selecting materialized views and indexes in data warehouses.
• To overcome the limitations of existing approaches that treat view and index selection independently, ignoring their interactions.
• To reduce the dimensionality of the selection problem through data mining techniques that identify relevant candidate views and indexes.
• To develop cost models that evaluate the performance gain versus maintenance overhead of combined view and index configurations.
• To achieve optimal storage space sharing and query response time improvement through integrated selection of materialized views and indexes.

Proposed method

• Apply data mining techniques—specifically frequent itemset mining—on a representative query workload to generate a reduced, relevant set of candidate materialized views and indexes.
• Use cost models to evaluate the benefit-to-overhead ratio of each candidate configuration, incorporating interactions between materialized views and indexes.
• Formulate the joint selection problem as a constrained optimization: minimize query execution cost subject to a storage space limit S.
• Integrate view-index interactions into cost models, recognizing that indexing a materialized view may reduce its maintenance cost or improve query performance.
• Apply the method to three scenarios: isolated view selection, isolated index selection, and joint selection, with the latter being the primary contribution.
• Use a logarithmic scale in experiments to visualize performance across varying storage space allocations (S/S_VI).

Experimental results

Research questions

• RQ1 How can data mining techniques be effectively used to reduce the search space for materialized view and index candidates in data warehouses?
• RQ2 What is the impact of view-index interactions on the overall performance and storage efficiency of physical design configurations?
• RQ3 How does joint selection of materialized views and indexes compare to independent selection in terms of query response time and storage utilization?
• RQ4 Under what storage space constraints is joint selection more effective than isolated selection of views or indexes?
• RQ5 Can a generic, modular approach be designed to support various DBMS platforms and extend to other physical design techniques like clustering or partitioning?

Key findings

• Joint selection of materialized views and indexes achieved up to a 30% improvement in query response time compared to independent selection, particularly when storage space was moderately large.
• The proposed method reduced the number of candidate indexes by approximately 40% on average while maintaining similar performance, significantly lowering maintenance overhead.
• When storage space was severely constrained, isolated index selection outperformed joint selection due to the smaller size of indexes compared to materialized views.
• The cost models effectively captured view-index interactions, leading to more efficient configurations than those derived from separate optimization.
• The approach demonstrated scalability and modularity, allowing replacement of data mining and cost model components with alternative techniques for extensibility.
• Experimental results on Oracle-based systems confirmed the method's effectiveness, though direct comparison with existing systems was limited by proprietary and non-reusable implementations.