Workload acceleration by optimizing materialized view selection using local search
For database administrators managing large workloads, this method improves query efficiency while reducing maintenance overhead, though it is an incremental improvement over existing approaches.
The paper tackles materialized view selection in database workloads, proposing a method that integrates incremental view maintenance cost into an integer linear program and uses local search for optimization. Experiments on Redbench show it outperforms BIGSUBS in optimization utility and view quality.
The growing size of database workloads has made view selection a key performance challenge. Materializing frequent sub-queries in workloads improves query efficiency, but it incurs significant view maintenance costs due to updates. Although existing methods such as BIGSUBS address this trade-off between the benefit of using materialized views and the overhead of view maintenance, they have two drawbacks: insufficient maintenance cost modeling and ineffective view selection due to probabilistic techniques. We propose a novel view selection method that incorporates incremental view maintenance cost directly into the optimization objective of an integer linear program and applies local search to efficiently explore the solution space. In order to apply local search to the view selection problem, we develop neighboring solutions using sub-query containment, and select initial solutions based on sub-query frequency, utility, or utility per storage unit. Experiments using Redbench, a benchmark simulating real-world query workloads on Amazon Redshift, show that our approach outperforms BIGSUBS in both optimization utility and the quality of selected views.