arXiv:2606.03772v1 [cs.DB] 2 Jun 2026
Workload acceleration by optimizing materialized view selection using local search Kaina Anderson
Yohanes Yohanie Fridelin Panduman
The University of Osaka Suita, Osaka, Japan [email protected]
The University of Osaka Suita, Osaka, Japan [email protected]
Yuya Sasaki
Makoto Onizuka
The University of Osaka Suita, Osaka, Japan [email protected]
The University of Osaka Suita, Osaka, Japan [email protected]
Abstract
1
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 subquery 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.
In recent years, the growing number of database users has significantly increased the volume of executed queries in workloads, making the optimization of workload performance a critical challenge. [10, 14, 16, 20]. A materialized view is a common database technique that stores the physically materialized result set of a query. Materializing the results of frequent sub-queries in workloads is a simple but effective way to significantly improve the workload performance [11, 18]. However, when updates are made to the underlying tables, the corresponding materialized views must be maintained, which can be time-consuming. Therefore, there is a trade-off between the benefit of using materialized views and the overhead of view maintenance.
CCS Concepts • Information systems;
Keywords View selection, Materialized view, Integer linear programming ACM Reference Format: Kaina Anderson, Yohanes Yohanie Fridelin Panduman, Yuya Sasaki, and Makoto Onizuka. 2025. Workload acceleration by optimizing materialized view selection using local search. In Proceedings of the 34th ACM International Conference on Information and Knowledge Management (CIKM ’25), November 10–14, 2025, Coex, Seoul, Korea. ACM, New York, NY, USA, 10 pages. https://doi.org/XXXXXXX.XXXXXXX Permission to make digital or hard copies of all or part of this work for personal or classroom use is granted without fee provided that copies are not made or distributed for profit or commercial advantage and that copies bear this notice and the full citation on the first page. Copyrights for components of this work owned by others than the author(s) must be honored. Abstracting with credit is permitted. To copy otherwise, or republish, to post on servers or to redistribute to lists, requires prior specific permission and/or a fee. Request permissions from [email protected]. CIKM ’25, Coex, Seoul, Korea © 2025 Copyright held by the owner/author(s). Publication rights licensed to ACM. ACM ISBN 978-1-4503-XXXX-X/2018/06 https://doi.org/XXXXXXX.XXXXXXX
Introduction
Motivation. Several materialized view selection methods have been proposed to balance the trade-off [14, 20, 22, 27, 28]. Specifically, BIGSUBS [14] is designed for large-scale workloads, which accelerates view selection by iteratively choosing 1) which sub-queries to materialize and 2) which sub-queries to use for evaluating the queries in the workload. In addition, BIGSUBS limits the total size of selected views by imposing a storage capacity constraint, expecting that it mitigates significant performance degradation during updates. However, there are two issues with BIGSUBS caused by its design, 1) the storage capacity constraint is not sufficiently effective to reflect the overhead of view maintenance, and 2) the quality of solutions found by BIGSUBS tends to be low because it relies on probabilistic techniques for view selection. Contribution. To address the above two issues, we propose a novel method that balances the trade-off between the benefit of using materialized views and the overhead of view maintenance. Our method offers two key contributions. First, by incorporating incremental view maintenance costs into the objective function of an integer linear program, we ensure that the overhead of view maintenance is directly reflected in the optimization process. Second, we employ a well-known optimization heuristic, local search [1], which effectively explores the solution space to find high-quality approximations. In detail, applying local search to the materialized view selection poses two technical challenges: defining appropriate neighboring solutions and selecting suitable initial solutions. To define neighboring solutions, we leverage the insight that sub-queries with a containment relationship tend to provide similar benefits of using materialized views for workloads. We treat two sub-queries are
CIKM ’25, November 10–14, 2025, Coex, Seoul, Korea
Kaina Anderson, Yohanes Yohanie Fridelin Panduman, Yuya Sasaki, and Makoto Onizuka
neighbors if one contains the other or is contained by the other. For initial solution selection, we use simple three methods based on subquery characteristics: high frequency, high utility, and high utility per storage unit in the workload. We conduct experiments to evaluate our proposed method in terms of optimization speed of view selection, the quality of selected views, and the workload performance using Redbench, a recent benchmark that simulates query frequencies in real-world production queries in Amazon Redshift based on the Join Ordering Benchmark (JOB) [17]. These experiments demonstrate that our approach outperforms the state-of-the-art method, BIGSUBS. Organization. The remainder of this paper is organized as follows. Section 2 describes preliminaries. Section 3 presents the overview of our workflow, and Sections 4 and 5 describe methods of view selection and query rewriting in detail, respectively. Section 6 presents experimental results to demonstrate its effectiveness. Section 7 discusses related work, and Section 8 concludes the paper with a summary and directions for future work.
2
Preliminaries
A workload consists of a set of queries, 𝑸 = {𝑞 1, 𝑞 2, · · · , 𝑞𝑛 } where 𝑞𝑖 and 𝑞 𝑗 for all 𝑖, 𝑗 (𝑖 ̸= 𝑗) are different and |𝑄 |= 𝑛 denotes the number of queries in the workload. Let 𝑺 = {𝑠 1, 𝑠 2, · · · , 𝑠𝑚 } be a set of sub-queries extracted from 𝑸 where |𝑆 |= 𝑚 is the number of sub-queries. The set 𝑺 of sub-queries is denoted as follows: [ 𝑺= extractSubQueries(𝑞𝑖 ), (1) 𝑞𝑖 ∈𝑸
2.1
Sub-query Utility and Storage Size
View utility [14] is a common notion that represents the effectiveness of a view for a given query and database. The utility of a sub-query is defined as follows: DEFINITION 1. The utility of sub-query 𝑠 𝑗 for query 𝑞𝑖 in a database is defined as the reduction in the estimated execution cost of 𝑞𝑖 using the materialized result of 𝑠 𝑗 :
𝑢𝑖 𝑗 = costread (𝑞𝑖 ) − costread (𝑞𝑖 |𝑠 𝑗 ),
(2)
where costread (𝑞𝑖 ) and costread (𝑞𝑖 |𝑠 𝑗 ) are the estimated costs of executing 𝑞𝑖 without and with utilizing the materialized result of 𝑠 𝑗 , respectively. The total utility 𝑈 𝑗 of sub-query 𝑠 𝑗 is defined as the sum of its utility for each query 𝑞𝑖 across 𝑸: ∑︁ 𝑈𝑗 = 𝑢𝑖 𝑗 . (3) 𝑞𝑖 ∈𝑄
We hereby note that if 𝑞𝑖 does not include 𝑠 𝑗 , 𝑢𝑖 𝑗 is zero.
q2
!
# !"#. (274 ⋈ !"#. %&'()*+ = -./01. *+
!"# = 25
⋈ !"#. <4=)*+ = +!#1. *+ !234 = 25 !(274 = ′9:;!9′
!"#
!"#
s1
q3
!"#
!%&'() = >:#:0′ !
&'()%*+
$!#%
s3
s2
q4
# !"#. (274
# !"#. %&'()
⋈ !"#. <4=)*+ = +!#1. *+
⋈!"#. %&'()*+ = -./01. *+
!234 = 25
!(274 = ′9:;!9′ $!#%
!234 = 25 !"#
!%&'() = >:#:0′ !
&'()%*+
Figure 1: An example of workload. Sub-queries shared across multiple queries: sub-query 𝑠 1 (blue line) is shared by four queries 𝑞 1 -𝑞 4 , sub-query 𝑠 2 (green line) is shared by 𝑞 2 and 𝑞 4 , and sub-query 𝑠 3 (red line) is shared by 𝑞 2 and 𝑞 3 .
We define that a storage size 𝑏 𝑗 of 𝑠 𝑗 is the estimated size of the result executed by 𝑠 𝑗 .
2.2
where the function extractSubQueries(·) takes a query as input and returns the set of sub-queries corresponding to partial execution plans extracted from the execution plan of 𝑞𝑖 . Since execution plans are represented as tree structures, the resulting sub-queries may exhibit containment relationships, i.e., a sub-query may include another. Figure 1 shows a workload including four queries 𝑞 1 to 𝑞 4 . Each query has a tree-structured execution plan. For example, 𝑞 2 includes six sub-queries. Totally, there are 9 sub-queries in the workload.
q1
Problem Definition
We solve the following problem in this paper. Problem definition. Given a database, 𝑸 = {𝑞 1, 𝑞 2, · · · , 𝑞𝑛 }, and storage budget 𝐵𝑚𝑎𝑥 , we aim to find a set of sub-queries ⊆ 𝑺 as views that minimize workload execution time and view maintenance time where the total estimated size of views is less than 𝐵𝑚𝑎𝑥 . We assume that update operations are not given. Materializing common sub-queries shared across multiple queries enables improving workload performance. As illustrated in Figure 1, sub-query 𝑠 1 (blue line) is shared by four queries 𝑞 1 -𝑞 4 , and subquery 𝑠 2 (green line) is shared by 𝑞 2 and 𝑞 4 . However, in addition to the benefits of reusing materialized views, we have an additional cost of view maintenance when the related base tables are updated. This observation naturally raises a fundamental question: which sub-queries should be materialized to improve the workload performance? While 𝑠 1 appears in four queries, its relatively small query plan suggests limited benefit from materialization. Conversely, 𝑠 3 includes an expensive join operation, which may be effective for reuse; however, it may incur higher view maintenance costs. These trade-offs reveal the complexity of the materialized view selection problem.
3
Overview of Our Workflow for Selecting and Using Views
This section presents an overview of our workflow for selecting and using views to achieve a balance between the benefit of using materialized views and the overhead of view maintenance. Figure 2 provides a workflow, which consists of 1) sub-query extraction, 2)
Workload acceleration by optimizing materialized view selection using local search
MV Selection
View Materialization
Sub-query extraction
MV Definition
Generate MV
Query Workload Queries that uses MV
CIKM ’25, November 10–14, 2025, Coex, Seoul, Korea
𝐿(𝑸, 𝑺) is the objective function, which maximizes the sum of the utility of sub-queries over all queries (first term) minus the incremental view maintenance cost (second term) 2 . The utility of sub-queries is computed as 𝑢𝑖 𝑗 · 𝑦𝑖 𝑗 , where the decision variable 𝑦𝑖 𝑗 indicates whether query 𝑞𝑖 uses the materialized view of 𝑠 𝑗 . The view maintenance cost is computed as 𝑚 𝑗 · 𝑧 𝑗 , where the decision variable 𝑧 𝑗 indicates whether 𝑠 𝑗 is selected for materialization. Equations (4)–(6) define the constraints of the problem: • The storage constraint (4) ensures the total size of materialized views does not exceed 𝐵𝑚𝑎𝑥 . • The containment constraint (5) prevents overlapping views from being used in the same query. Binary variable 𝑥 𝑗𝑘 indicates whether 𝑠 𝑗 contains 𝑠𝑘 . • The materialization constraint (6) ensures that only materialized sub-queries can be used for query processing.
Rewrite Query
Figure 2: Workflow consisting of 1) sub-query extraction, 2) materialized view (MV) selection, 3) view materialization, and 4) rewriting queries using materialized views.
materialized view selection, 3) view materialization, and 4) rewriting queries using materialized views. The materialized view selection offers several key contributions. First, by incorporating incremental view maintenance costs into the objective function, we ensure that the overhead of view maintenance is directly reflected in the optimization process (Section 4.1). Second, we employ a well-known optimization heuristic, local search [1], which effectively explores the solution space to find high-quality approximations (Section 4.2). Third, we define the incremental view maintenance cost of materialized views in response to update operations in the workload (Section 4.3), and then we describe the algorithm of the materialized view selection (Section 4.4). Finally, we generate materialized views and rewrite the original query to use the views (Section 5).
4
Method for View Selection
We describe how to select views in detail.
4.1
Objective Function for View Selection
The objective of materialized view selection is to balance the tradeoff between the benefit of using materialized views and the overhead of view maintenance. This problem can be formulated as an integer linear program (ILP) that maximizes the difference between the utility of sub-queries and the view maintenance cost. DEFINITION 2 (V IEW SELECTION PROBLEM ). Let 𝑢𝑖 𝑗 be the sub-query utility as defined in Equation (2), 𝑚 𝑗 be the incremental maintenance cost of materialized sub-query 𝑠 𝑗 1 , 𝑏 𝑗 is the storage size of 𝑠 𝑗 , and 𝐵𝑚𝑎𝑥 be the storage budget. Given queries 𝑸 and sub-queries 𝑺, the view selection problem is defined as follows: ∑︁ ∑︁ ∑︁ 𝐿(𝑸, 𝑺) = maximize 𝑢𝑖 𝑗 · 𝑦𝑖 𝑗 − 𝑚𝑗 · 𝑧𝑗 𝑞𝑖 ∈𝑸 𝑠 𝑗 ∈𝑺
subject to
∑︁
𝑠 𝑗 ∈𝑺
𝑏 𝑗 · 𝑧 𝑗 ≤ 𝐵𝑚𝑎𝑥
(4)
𝑠 𝑗 ∈𝑺
𝑦𝑖𝑘 +
1 ∑︁ 𝑦𝑖 𝑗 · 𝑥 𝑗𝑘 ≤ 1 ∀𝑖 ∈ [1, |𝑸 |], ∀𝑘 ∈ [1, |𝑺 |] |𝑺 | 𝑗̸=𝑘
𝑦𝑖 𝑗 ≤ 𝑧 𝑗
∀𝑖 ∈ [1, |𝑸 |], ∀𝑗 ∈ [1, |𝑺 |]
1 Section 4.3 describes the detail of incremental view maintenance cost 𝑚 . 𝑗
(5) (6)
Solving this integer programming formulation yields: 1) which subqueries 𝑠 𝑗 should be materialized (𝑧 𝑗 = 1), and 2) which query 𝑞𝑖 should use which sub-query 𝑠 𝑗 (𝑦𝑖 𝑗 = 1).
4.2
Optimization using Local Search
Naively solving the optimization formulation presented in Section 4.1 as an Integer Linear Programming (ILP) leads to severe performance degradation as the number of queries increases. Indeed, ILP is known to be NP-hard and typically incurs an exponential time cost with respect to the number of decision variables, i.e., 𝑦𝑖 𝑗 and 𝑧 𝑗 where the size depends on the product of query size (|𝑸 |) and sub-query size (|𝑺 |). Furthermore, although BIGSUBS [14] is designed for large-scale workloads and accelerates view selection, the quality of its solutions tends to be low due to its reliance on probabilistic techniques for view selection. To address this, we employ a well-known optimization heuristic, local search [1], to iteratively approximate the optimal materialized views. Local search is a technique that avoids exhaustive solution enumeration by incrementally exploring the solution space through the enumeration of neighboring solutions to the current one, thereby enabling iterative refinement. In order to apply the local search to the materialized view selection problem, we first generate promising initial candidate subqueries (Section 4.2.1). Then, we explore neighboring solutions by modifying the current candidate sub-queries to explore the solution space. For the expanded solutions by adding neighboring solutions, we solve an ILP to select new candidates that maximize the objective function (Section 4.2.2). This process is repeated until no further improvement can be achieved in the objective function (Section 4.2.3). 4.2.1 Initial Solution Selection. We propose three methods for selecting promising initial candidate sub-queries. We use one of the following metrics for sub-query 𝑠 𝑗 : 1) frequency 𝐹 𝑗 in the workload, 2) total utility 𝑈 𝑗 , or 3) utility per unit storage 𝐸 𝑗 = 𝑈 𝑗 /𝑏 𝑗 . Based on the selected metric, the top 𝑘 sub-queries are chosen as the initial candidate sub-queries, such that the total storage cost does not exceed the storage budget 𝐵 max . topk-𝐹 : This method simply prioritizes sub-queries that appear most frequently in the workload. By favoring commonly shared subqueries, it may enhance the effectiveness of selecting initial views. 2 We refer to the contribution of each sub-query to this objective as its effective utility.
CIKM ’25, November 10–14, 2025, Coex, Seoul, Korea
Kaina Anderson, Yohanes Yohanie Fridelin Panduman, Yuya Sasaki, and Makoto Onizuka
topk-𝑈 : This method selects sub-queries with the highest total utility, as defined in Equation (3). It is expected to be more effective than topk-𝐹 because it explicitly accounts for the actual benefit of utilizing materialized views. While this approach successfully identifies high-utility candidates, it may also select sub-queries that require substantial storage, potentially reducing storage efficiency. topk-𝐸: This method balances utility and storage requirements by selecting sub-queries based on their utility per unit of storage. 4.2.2 Neighborhood-based sub-query exploration. To explore the solution space via local search, we first define the neighbors of sub-queries. Our approach leverages containment relationships in query plan trees: if the plan tree of one sub-query contains that of another, these two sub-queries are considered neighbors. A parent sub-query expresses a larger plan tree that fully contains those of its child sub-queries, thereby it materializes a greater portion of the overall query processing. In contrast, child sub-queries are more likely to be shared across multiple queries due to their smaller structure of plan trees. We define the neighbors of sub-queries as follows. DEFINITION 3 (N EIGHBORS OF SUB - QUERY ). The neighbors of a sub-query 𝑠 consist of 𝑠 itself, its parent sub-queries, and its child sub-queries:
neighbors(𝑠) = parent(𝑠) ∪ children(𝑠)
(7)
where parent(𝑠) denotes the set of sub-queries whose root nodes are parents of the root node of sub-query 𝑠, while children(𝑠) denotes the set of sub-queries whose root nodes are children of the root node of sub-query 𝑠. In Figure 1, parents and children of 𝑠 3 are indicated by solid-lined rectangles. The parents are sub-queries indicated by rectangles that cover red rectangles in 𝑞 2 and 𝑞 3 , and the children are sub-queries indicated by sub-queries within the red rectangle, respectively. The neighbor-based sub-query exploration enumerates promising sub-tree candidates that balance the greater utility of materialization (achieved by enumerating parent sub-queries) and the higher potential for reuse across multiple queries (achieved by enumerating child sub-queries). 4.2.3 Iterative Optimization. Starting from the initial candidate sub-queries, we explore the solution space by adding neighbor subqueries and then choose new candidate sub-queries that maximize the objective function. Let 𝐶𝑡 denote the candidate sub-queries at iteration 𝑡 (𝑡 > 0), and let 𝐸𝑡 denote the extended sub-queries obtained by adding neighbor sub-queries to 𝐶𝑡 , defined as follows: [ 𝐸𝑡 = 𝑠 ∪ neighbors(𝑠) (8) 𝑠 ∈𝐶𝑡
where 𝐶 0 is the initial solutions selected by the methods described in Section 4.2.1. Then, the new candidate sub-queries 𝐶𝑡 +1 are computed by solving an ILP over the extended sub-queries 𝐸𝑡 : 𝐶𝑡 +1 = ILP(𝑸, 𝐸𝑡 )
(9)
The function ILP(𝑸, 𝐸𝑡 ) selects the solutions to the view selection problem when the whole candidate sub-query set 𝑺 is localized to a subset 𝐸𝑡 , which corresponds to 𝐿(𝑸, 𝐸𝑡 ) in Definition 2. This localization is a key advantage of the local search approach, as it
significantly reduces the number of decision variables (𝑦𝑖 𝑗 and 𝑧 𝑗 ) compared to the whole sub-query set 𝑆, that is |𝐸𝑡 |<< |𝑺 |. The iteration continues until no further improvement can be achieved in the objective function.
4.3
Incremental View Maintenance Cost
This section describes the details of the incremental view maintenance cost 𝑚 𝑗 for a materialized view of subquery 𝑠 𝑗 in response to update operations. Since the workload contains update operations to the base tables, the materialized views must be maintained to reflect these changes. Numerous studies [2, 3, 7, 24] have explored incremental view maintenance, and some of these techniques have even been integrated into commercial systems, such as Oracle, SQL Server, and Tempura [24]. Therefore, we focus on computing the cost of incremental view maintenance rather than that of full refreshbased view maintenance. 4.3.1 Notations. The incremental view maintenance cost in response to updates on base tables is expressed using a cost function costIVM (∆), where the argument ∆ denotes the delta view expression for given update operations on base tables. ∆ is expressed as an algebraic expression of the view, in which each base table is updated with its corresponding delta table that reflects incremental update operations. As an example of a single-table view, ∆ is expressed using a delta table, such as ∆𝐴 for Table 𝐴, and the incremental view maintenance cost is denoted as costIVM (∆𝐴). If a view is defined as a multi-table view, for example, for a given view 𝐴 ⊲⊳ 𝐵 and an incremental update operation ∆𝐵 to the base table 𝐵, the incremental view maintenance cost is denoted as costIVM (𝐴 ⊲⊳ ∆𝐵). 4.3.2 Cost Estimation for Incremental Join View Maintenance. In order to solve the view selection problem, we need to estimate the view maintenance cost (𝑚 𝑗 ) in Definition 2. However, since the database stores only the base tables and does not materialize the views in advance, the view maintenance cost must be estimated without physically generating the views. We begin by decomposing the incremental view maintenance into three constituent operations, and then describe how to estimate their costs. Decomposing incremental view maintenance cost. First, the incremental view maintenance cost is calculated as the sum of the costs of three operations3 : 1) computing changes to the view, 2) identifying records to update in the view, and 3) applying the changes to the view. Since the view maintenance cost of a single-table view is intuitive, we describe the incremental view maintenance cost by focusing specifically on binary join views. This approach can be easily extended to views defined by multi-table joins, as they can be represented as sequences of binary join operations in the execution plan. Consider a binary join view, 𝑉 = 𝐴 ⊲⊳ 𝐵, and an incremental delete operation −∆𝐵 on Table 𝐵. We omit the cases for incremental insert and update operations, as they can be straightforwardly derived from the delete case. Let ∆𝑉 = 𝐴 ⊲⊳ ∆𝐵. The incremental view maintenance cost is decomposed into the costs of the three operations 3 If the update operations on the base table are insertion, the maintenance cost consists
only of 1) and 3), because we can skip 2).
Workload acceleration by optimizing materialized view selection using local search
CIKM ’25, November 10–14, 2025, Coex, Seoul, Korea
and is expressed as follows.
Algorithm 1 View selection optimization
costIVM (𝐴 ⊲⊳ ∆𝐵) = cost(𝐴 ⊲⊳ ∆𝐵) + cost(𝜎∆𝑉 (𝑉 )) + cost(𝑉 − ∆𝑉 ) (10)
Input: 𝑸 (queries), 𝑺 (sub-queries), 𝑼 , 𝒃, 𝒎, 𝑋 , and 𝐵 max (storage budget) Output: 𝒛, 𝑌 1: 𝒛 = initial_solution𝐵 max (𝑺, 𝑼 , 𝒃) // initial solution selection 2: 𝑈𝑝𝑟𝑒𝑣𝑖𝑜𝑢𝑠 = 0 3: while 𝑡𝑟𝑢𝑒 do 4: 𝐶 = candidate_views(𝒛) // 𝒛 to 𝐶 transformation S 5: 𝐸 = 𝑠 ∈𝐶 𝑠 ∪ neighbors(𝑠) // sub-query exploration 6: 𝒛, 𝑌 = ILP𝐵𝑚𝑎𝑥 ,𝑋 ,𝒎,𝑼 (𝑸, 𝐸) // applying ILP P P P 7: 𝑈𝑐𝑢𝑟𝑟𝑒𝑛𝑡 = 𝑞𝑖 ∈𝑸 𝑠 𝑗 ∈𝑺 𝑢𝑖 𝑗 · 𝑦𝑖 𝑗 − 𝑠 𝑗 ∈𝑺 𝑚 𝑗 · 𝑧 𝑗 8: // iteration termination check 9: if 𝑈𝑝𝑟𝑒𝑣𝑖𝑜𝑢𝑠 >= 𝑈𝑐𝑢𝑟𝑟𝑒𝑛𝑡 then 10: break 11: end if 12: // preparing for the next iteration 13: 𝑈𝑝𝑟𝑒𝑣𝑖𝑜𝑢𝑠 = 𝑈𝑐𝑢𝑟𝑟𝑒𝑛𝑡 14: end while 15: return 𝒛, 𝑌
Estimating incremental view maintenance cost: Then, we estimate the cost of the above three operations as follows. 1) Computing changes to the view: cost(𝐴 ⊲⊳ ∆𝐵) is approximated as the cost of querying 𝐴 ⊲⊳ 𝐵 times ∆𝐵 |𝐵 | (where |𝐵| is the number of records in B), because we only need to compute ratio ∆𝐵 |𝐵 | of 𝐴 ⊲⊳ 𝐵. cost(𝐴 ⊲⊳ ∆𝐵) ≃ costread (𝐴 ⊲⊳ 𝐵) ×
∆𝐵 |𝐵|
(11)
2) Identifying records to update in the view: The cost of identifying ∆𝑉 in the materialized view 𝑉 is approximated as the cost of evaluating 𝜎∆𝐵 (𝐵) times fanoutA⊲⊳B (𝐵 → 𝑉 ), where fanout𝐴⊲⊳𝐵 (𝐵 → 𝑉 ) denotes the fanout (i.e., output cardinality) from 𝐵 to 𝑉 when 𝑉 is obtained by 𝐴 ⊲⊳ 𝐵. cost(𝜎∆𝑉 (𝑉 )) ≃ costread (𝜎∆𝐵 (𝐵)) × fanout𝐴⊲⊳𝐵 (𝐵 → 𝑉 )
(12)
For example, if tables 𝐴 and 𝐵 are in a one-to-many relationship (i.e., one 𝐴 record corresponds to multiple 𝐵 records) and the view is defined using natural join, then fanout𝐴⊲⊳𝐵 (𝐵 → 𝑉 ) ≃ 1. Conversely, if they are in a many-to-one relationship, then fanout𝐴⊲⊳𝐵 (𝐵 → 𝑉 ) ≃ |𝐴|/|𝐵|. If the view is defined using ad-hoc join, we use a simple histogram-based approach commonly used in relational databases. It is our future work to employ machine-learning-based cardinality techniques [25, 26] for more precise fanout estimation. 3) Applying the changes to the view: The cost of applying the changes (−∆𝑉 ) to 𝑉 , cost(𝑉 − ∆𝑉 ), is approximated as the cost of deleting ∆𝐵 from the base table 𝐵 times fanout𝐴⊲⊳𝐵 (𝐵 → 𝑉 ). cost(𝑉 − ∆𝑉 ) ≃ costwrite (−∆𝐵) × fanoutA⊲⊳B (𝐵 → 𝑉 )
(13)
5
View generation. To define the materialized view, we extract all referenced tables, join conditions, and selection conditions from the execution plan of each sub-query 𝑠 𝑗 and construct a CREATE MATERIALIZED VIEW statement.
where costwrite𝐵 (−∆𝐵) denotes the cost of deleting ∆𝐵 from its base table.
4.4
• CREATE MATERIALIZED VIEW as [view_name] • SELECT clause: All columns from the referenced tables are included. The duplicate column names between different tables are resolved via renaming. • FROM clause: All tables from the referenced tables. • WHERE clause: All conditions from the referenced tables.
Algorithm Description
We define 𝑼 (sub-query utilities), 𝒃 (sub-query footprints), 𝒎 (subquery maintenance costs), and 𝒛 (sub-query for materialization) as vectors consisting of elements 𝑈 𝑗 , 𝑏 𝑗 , 𝑚 𝑗 , and 𝑧 𝑗 , respectively, for sub-query 𝑠 𝑗 (1 ≤ 𝑗 ≤ |𝑺 |). We also define X ∈ {0, 1} |𝑺 | × |𝑺 | as a sub-query containment matrix consisting of elements 𝑥 𝑗𝑘 , which indicates whether 𝑠 𝑗 contains 𝑠𝑘 , and Y ∈ {0, 1} |𝑸 | × |𝑺 | as a matrix consisting of elements 𝑦𝑖 𝑗 , which indicates whether query 𝑞𝑖 uses the materialized view of sub-query 𝑠 𝑗 . The view selection algorithm is outlined in Algorithm 1. Initially, an initial candidate sub-queries (expressed using 𝒛) is generated using one of the metrics topk-𝐹 , topk-𝑈 , or topk-𝐸, subject to the storage budget (Line 1-2) as detailed in Section 4.2.1. Then, we conduct a local search and ILP until no further improvement can be achieved (Lines 3-14). Line 5 corresponds to neighbor-based subquery exploration (detailed in Section 4.2.2), and Line 6 involves applying Integer Linear Programming (ILP) optimization for the current candidate sub-queries (𝐸). ILP updates the decision variables (𝒛, 𝑌 ) that maximize the objective function in Definition 2. Lines 9-11 determine termination based on convergence criteria, and Line 15 outputs the final decision variables (𝒛, 𝑌 ).
View Materialization and Query Rewriting
Once the optimization of view selection is completed, we generate the materialized views and rewrite the original queries using the views based on the decision variables (𝒛, 𝑌 ) where 𝑦𝑖 𝑗 indicates whether query 𝑞𝑖 uses the materialized view 𝑠 𝑗 , and 𝑧 𝑗 indicates whether 𝑠 𝑗 is selected for materialization.
When materialized views are defined, they are subsequently materialized within the database. Query rewrite using views. Each query is rewritten as follows based on 𝑦𝑖 𝑗 : query 𝑞𝑖 uses the materialized view 𝑠 𝑗 : • SELECT clause: Replace the columns of the base tables in 𝑞𝑖 with the corresponding columns in 𝑠 𝑗 . • FROM clause: Replace the corresponding base tables in 𝑞𝑖 with 𝑠 𝑗 . • WHERE clause: Remove the join and filter conditions in 𝑞𝑖 if they are included in 𝑠 𝑗 .
6
Experiments
The purpose of the experiments is to compare the performance of the proposed method with those of the baselines, a naive ILP-based method (Naive ILP) and BIGSUBS [14]. In detail, we conducted four experiments based on the following objectives:
CIKM ’25, November 10–14, 2025, Coex, Seoul, Korea
Kaina Anderson, Yohanes Yohanie Fridelin Panduman, Yuya Sasaki, and Makoto Onizuka
Table 1: Experimental environment Machine OS CPU Memory ILP solver Database system Database Database size
MacBook Pro (16-inch, 2024) macOS Sequoia 15.4.1(24E263) Apple M4 Pro (14-core CPU, 20-core GPU) 48GB Gurobi 12.0 PostgreSQL 17.4 IMDb 7085MB
Workload performance improvement: We evaluate the workload performance of the proposed method compared to the baselines. Ablation study: Effectiveness of local search: To evaluate the effectiveness of local search, we compare the performance of the proposed method with and without local search applied to three different initial solutions. Ablation study: Effectiveness of initial solution selections: We compare the workload performance of the three initial solution selections: topk-𝐹 , topk-𝑈 , and topk-𝐸 Parameter sensitivity: Impact of insert operations: We examine how the number of insert operations affects the optimization cost for the proposed method and the baselines. Parameter sensitivity: Impact of storage budget: We analyze how the storage budget size affects the total view utility and the optimization time by comparing the proposed method and the baselines.
6.1
Setup
6.1.1 Benchmark. We conduct the experiments using the Redbench4 , which is a recently published benchmark extended from the Join Ordering Benchmark (JOB) [17] by introducing query repetition. The benchmark runs on the IMDb database, consisting of 21 tables. The query repetition is generated based on the real-world workload from Redset5 , a dataset of query metadata published by Amazon Redshift, so Redbench is suitable for workload evaluations. Redbench contains 1073 JOB-based queries. Among these, there are 83 unique JOB queries6 , corresponding to distinct query templates. Those queries are categorized into ten repetition buckets based on query repetitiveness, which is defined as the proportion of queries that appeared in the query history. In addition, we manually added synthetic insert operations to the benchmark by randomly selecting base tables, because the Redbench workload does not support update operations. 6.1.2 Metrics. As the evaluation metrics, we use 1) the total time consisting of optimization time for materialized view selection, view materialization time, and workload execution time, and 4) total view utility defined in Equation (3). We also report the total time for each 4 https://github.com/utndatasystems/redbench 5 https://github.com/amazon-science/redset 6 While the complete JOB workload comprises 113 queries, Redbench selects a subset
of 83 queries that involve between 6 and 11 joined tables to construct its benchmark workload.
Table 2: Amortized total time (sec) between proposed method and baselines, including overhead across methods for different query repetition buckets indicating repetition ratio. The storage budget (𝐵 max ) is set to 50 MB. The bold indicates the lowest total time in each query repetition bucket, and the underline indicates the second lowest. Query repetition buckets
w/o MV
Naive ILP
BIGSUBS
Proposed (topk-𝐹 )
0%–10% 10%–20% 20%–30% 30%–40% 40%–50% 50%–60% 60%–70% 70%–80% 80%–90% 90%–100%
55.75 21.43 89.60 71.19 144.91 4.32 85.12 107.16 70.19 226.08
25.55 3.85 41.02 18.56 57.29 1.45 49.79 32.39 94.83 115.94
20.67 7.55 51.64 17.18 75.68 0.60 47.36 53.83 90.21 143.46
14.96 2.00 22.47 12.78 51.17 0.60 66.17 30.99 77.22 92.62
Total Time (s)
875.75
440.68
508.18
370.98
of the ten repetition buckets by distributing the total optimization time and view materialization time across the buckets in proportion to each bucket’s workload execution time relative to the total workload execution time. Regarding the view utility, we use the EXPLAIN command in PostgreSQL for estimating the sub-query execution cost (𝑐𝑜𝑠𝑡𝑟𝑒𝑎𝑑 (·)) in the view utility and the update operation cost (𝑐𝑜𝑠𝑡 𝑤𝑟𝑖𝑡𝑒 (·)). 6.1.3 Environment. Table 1 describes the environment we used in the experiments. In operating PostgreSQL, we have adopted the default configuration settings. Regarding the convergence criteria for the optimization process, we apply the same approach to all methods: we repeat the iteration until no further improvement can be achieved in the objective function.
6.2
Experimental Results
6.2.1 Workload Performance Improvement. This experiment demonstrates that our proposed method (topk-𝐹 ) provides the most effective and practical solutions for workload acceleration, delivering near-optimal query performance with minimal optimization overhead. We evaluate the overall performance of our method in terms of total time and compare it with the baselines. We use the topk-𝐹 initial solution selection as the representative configuration for our proposed method. Assuming a practical usage scenario, we first applied the view selection optimization using the original 113 JOB queries as the training queries, which serve as the query templates for the Redbench workload. We then evaluated the workload performance using the 1073 JOB-based queries as the test queries that constitute the Redbench workload. We generate 1000 synthetic insert operations. We set the default size of the storage budget to 50 MB, which is approximately 10% of the total size required to materialize all queries of the original JOB benchmark. First, Table 2 shows the total time results for each query repetition bucket. The results indicate that our proposed method is not only fast overall, but also consistently effective across diverse query repetition
Workload acceleration by optimizing materialized view selection using local search
Table 3: Comparison of total workload rerun (Total time) for view selection, including optimization time for view selection, materialized view (MV) generation time, and workload execution time, together with the utility of selected views. All times are reported in seconds..
CIKM ’25, November 10–14, 2025, Coex, Seoul, Korea
Table 4: Comparison of total view utility and optimization time with and without local search (𝐵 max : 50MB). diff indicates the difference between the proposed method with and without local search (proposed - w/o local search). Method
Method w/o MV Naive ILP BIGSUBS Prop. (topk-𝐹 )
Total Time (s)
Opt. Time (s)
MV Gen. Time (s)
Workload Exec. time (s)
MV Utility (K)
875.75 440.68 508.18 370.98
0 79.75 0.73 1.29
0 194 76 179
875.75 166.93 431.45 190.71
0 3374 1855 3272
buckets. In the lower-repetition buckets (0%–50%), Proposed (topk𝐹 ) demonstrates a marked performance advantage over the baselines. This suggests that the local search is effective in narrowing the search space: by prioritizing frequently appearing sub-queries, it explores efficiently promising regions of the solution space, achieving highquality solutions without exhaustive exploration. Second, Table 3 presents the breakdown of the total time, which includes optimization time, view materialization time, and workload execution time. Our proposed method (topk-F) maintains a high view utility (3272K), which is comparable to the near-optimal utility achieved by Naive ILP (3374K), while keeping the optimization overhead remarkably low (1.29 seconds). In contrast, Naive ILP produces high-quality views that yield the fastest workload execution (166.93 seconds), but its prohibitively high optimization time (79.75 seconds) limits its practicality for real-time or large-scale scenarios. BIGSUBS, on the other hand, incurs a negligible optimization time (0.73 seconds) but selects views with significantly lower utility (1855K), leading to a longer workload execution time (431.45 seconds). 6.2.2 Ablation study: Effectiveness of Local Search. This ablation experiment evaluates the effectiveness of the local search in the proposed methods. We compared the optimization time and the total utility obtained by the proposed methods with and without the local search. That is, the initial solutions are used as the selected views without using the local search. The number of insert queries was set to 1000. Table 4 shows that local search provides a substantial benefit in increasing total utility for topk-𝐹 and topk-𝐸, with only a marginal additional time. As an example of topk-𝐹 , local search improves total utility by a factor of 2.1, incurring only 1.18 seconds of additional optimization time: it is significantly smaller than the whole workload execution time of approximately 100 seconds (see Table 2). Note, in the case of topk-𝑈 , the total utility remains unchanged, indicating that the initial solutions are as good as or better than the ILP-refined solutions, as evidenced by the fact that the iteration terminates after the first round. 6.2.3 Ablation study: Effectiveness of initial solution selections. Having established the overall superiority of our proposed method, this experiment delves into a comparative analysis of the three distinct initial solution selections: topk-𝐹 , topk-𝑈 , and topk-𝐸.
Utility (K)
Optimization Time (sec)
3270 1570 +1700 3340 3340 +0 2710 2280 +430
1.29 0.11 +1.18 0.76 0.28 +0.48 0.75 0.02 +0.73
proposed (topk-𝐹 ) w/o local search (topk-𝐹 ) diff proposed (topk-𝑈 ) w/o local search (topk-𝑈 ) diff proposed (topk-𝐸) w/o local search (topk-𝐸) diff
Table 5: Amortized total time (sec) for proposed methods, including overhead across all query repetition buckets indicating repetition ratio. The storage budget (𝐵 max ) is set to 50 MB. The bold indicates the lowest total time in each query repetition bucket, and the underline indicates the second lowest. Query repetition buckets
Proposed topk-𝐹
topk-𝑈
topk-𝐸
0%–10% 10%–20% 20%–30% 30%–40% 40%–50% 50%–60% 60%–70% 70%–80% 80%–90% 90%–100%
14.96 2.00 22.47 12.78 51.17 0.60 66.17 30.99 77.22 92.62
16.79 9.59 64.46 7.29 28.06 0.39 38.40 20.43 41.54 261.94
26.78 14.66 63.42 21.88 69.98 4.93 41.76 68.87 59.09 230.21
TOTAL
370.98
488.90
601.89
Table 6: Comparison of total workload rerun (Total time) for view selection, including optimization time for view selection, materialized view (MV) generation time, and workload execution time, together with the utility of selected views. All times are reported in seconds.
Method Prop. (topk-𝐹 ) Prop. (topk-𝑈 ) Prop. (topk-𝐸)
Total Time (s)
Opt. Time (s)
MV Gen. Time (s)
Workload Exec. time (s)
MV Utility (K)
370.98 488.90 601.59
1.29 0.76 0.75
179 110 113
190.71 497.59 387.89
3272 3344 2707
The objective is to understand the trade-offs inherent in each heuristic and to justify the selection of topk-𝐹 as our representative method. The experimental setup remains consistent with that in Section 6.2.1.
CIKM ’25, November 10–14, 2025, Coex, Seoul, Korea
Kaina Anderson, Yohanes Yohanie Fridelin Panduman, Yuya Sasaki, and Makoto Onizuka
Figure 3: Total view utility with respect to the number of insert operations.
First, Table 5 shows the total time results for each query repetition bucket. This reveals the distinct strengths of each selection. topk𝐹 excels in both the low-repetition (0%-20%) and high-repetition (90%-100%) buckets. This behavior is intuitive: a frequency-based heuristic is adept at identifying sub-queries that are either broadly shared across many distinct queries or are part of extremely common queries. In contrast, topk-𝑈 demonstrates its strength in the mid-range repetition buckets (30%-80%), where it often outperforms topk-𝐹 . This suggests that topk-𝑈 is particularly effective at identifying high-cost, high-impact sub-queries that may not be the most frequent but offer significant optimization potential. Second, Table 3 provides the breakdown of the total time. The results indicate that topk-𝐹 achieves the best total time, with a total time of 370.98 seconds. Although topk-𝑈 identifies a set of views with the highest estimated utility (3344 K), this does not necessarily lead to faster workload execution. Instead, topk-𝐹 yields the lowest workload execution time (190.71 s), demonstrating that selecting initial views based on their frequency of appearance results in a more practically effective set of materialized views for this workload. The slightly longer optimization time for topk-𝐹 (1.29s) suggests its initial solution was further from the final local optimum, requiring more iterative refinement, yet the superior quality of the final view set justified this marginal overhead. The topk-𝐸 selection, which aims to balance utility and storage, proves least effective, resulting in both the lowest utility (2707K) and the longest execution time. This suggests that normalizing utility by storage cost may lead the local search to converge on a suboptimal solution. In conclusion, while each initial solution selection has its merits, topk-𝐹 emerges as the most robust and well-rounded approach. Its ability to achieve the best total time by effectively reducing the actual workload execution time makes it the most practical choice for general-purpose workload acceleration. 6.2.4 Parameter sensitivity: Impact of the number of insert operations. This experiment evaluates how the number of insert operations affects the optimization cost of our proposed method and the baselines. We manually generated synthetic insert operations and changed the number of inserted records from 0 to 2,000 in increments of 100.
Figure 3 illustrates the total utility using the selected views as a function of the number of insert operations for our proposed method (topk-𝐹 ), Naive ILP, and BIGSUBS. The utility of both topk-𝐹 and Naive ILP gradually decreases as the number of inserts increases. This decline is expected, since the objective function explicitly penalizes view maintenance cost; as more insert operations occur, the cumulative maintenance overhead reduces the achievable utility. Nevertheless, topk-𝐹 successfully adapts to this change by selecting views whose expected benefits remain close to optimal even under higher maintenance costs. In contrast, the total utility achieved by BIGSUBS is generally the lowest and it does not exhibit a consistent decline but rather fluctuates. This instability in the total utility is attributed to its probabilistic approach, as noted in prior work [14]. 6.2.5 Parameter sensitivity: Impact of storage budget. This experiment analyzes how the storage budget size (𝐵 max ) affects the total view utility and optimization time by comparing the proposed method with the baselines. The number of insert operations was fixed at 1000, and the storage budget size 𝐵𝑚𝑎𝑥 was changed across 5 MB, 50 MB, and 500 MB.
(a) View utility
(b) Optimization time
Figure 4: Comparison of total view utility and optimization time with respect to 𝐵𝑚𝑎𝑥 (5 MB, 50 MB, and 500 MB). Figure 4a illustrates the total utility of the materialized views selected by each method. Our proposed method topk-𝐹 consistently identifies the views with a utility nearly comparable to that of the
Workload acceleration by optimizing materialized view selection using local search
Naive ILP approach, which provides a theoretical optimum. This stands in contrast to BIGSUBS, which selects a substantially less beneficial set of views across all budget constraints, indicating its lower selection accuracy. As expected, the utility for all methods increases with a larger 𝐵𝑚𝑎𝑥 , as a greater budget allows for the materialization of more effective views. Regarding optimization overhead, Figure 4b shows the optimization time on a logarithmic scale. Both our proposed method and BIGSUBS achieve optimization times that are orders of magnitude faster than the Naive ILP approach. Although the optimization time of our method is almost the same as that of BIGSUBS, it achieves better overall performance while completing the optimization process within a few seconds. This minimal time investment is a stark contrast to the prohibitive computational cost of the Naive ILP method. Overall, these results confirm that the proposed method effectively balances optimization quality and computational cost, delivering near-optimal performance under realistic resource constraints. 6.2.6 Validation of the Utility Metric. Furthermore, we confirmed the validity of our utility metric as a surrogate objective. For view sets selected by our proposed method, we observed a strong inverse correlation (Pearson’s r = -0.920) between their total estimated utility and the resulting workload execution time. This validation underpins the effectiveness of our optimization approach.
7
Related Work
The selection of materialized views has been extensively studied, with comprehensive surveys [11, 18] providing overviews of a variety of strategies and their trade-offs, including view selection, query rewriting, and answering queries using views. A recent survey [29] reviews the techniques for both AI4DB and DB4AI, including database configuration tuning, optimizer, and index/view advisor. The techniques for view selection are categorized into heuristicbased approaches [6, 8, 9, 12, 13, 23] or optimization-based approaches that formulate the problem using integer linear programming (ILP) [5, 14, 19, 27].
7.1
Heuristic-based approaches
Classical techniques for view selection in data warehouses [8, 9, 23] clarify the theoretical aspect of view selection and propose greedy approaches. These techniques rely on heuristics to search the space of candidate views and are effective for small workloads, but they suffer from limited scalability due to their local search nature and reliance on simplifying assumptions.
7.2
ILP-based approaches
ILP-based approaches have emerged as a principled strategy for optimizing view selection under complex constraints. These methods formulate the problem as an integer linear program where the objective is to maximize overall benefit or minimize total cost subject to storage and maintenance budgets. Papadomanolakis and Ailamaki [19] proposed one of the first ILP-based frameworks for database physical design, originally targeting index selection but extensible to materialized view selection. PigReuse [5] applies ILP-based selection over AND/OR DAGs for Pig Latin scripts, enabling the reuse of common subplans. A prominent ILP-based system, BIGSUBS [14],
CIKM ’25, November 10–14, 2025, Coex, Seoul, Korea
reformulates the view selection problem as a labeling task over a bipartite graph. It decomposes the large ILP into smaller subproblems and iteratively updates the set of materialized sub-queries. While scalable to large workloads, BIGSUBS relies on probabilistic heuristics and does not guarantee convergence to a global optimum. Wide-deep [27] extends BIGSUBS by introducing a neural model to estimate view utility and uses deep reinforcement learning to guide view selection. However, this approach introduces additional complexity and may lead to instability in training and convergence. In contrast, our work is the first work that employs local search, and it achieves high fast optimization speed and high quality of selected views.
7.3
Multi-query optimization
Multi-query optimization (MQO) is a related but distinct problem that focuses on identifying opportunities to share computation across a batch of concurrent queries. MQO techniques typically use temporary views, which are materialized only during the execution of the batch. Roy et al. [21] extend the Volcano optimizer with AND/OR DAGs to identify and reuse common subplans across queries, achieving significant cost savings. Koch and Schlegel [15] propose an algebraic approach that merges multiple queries into a unified representation, allowing efficient factoring of shared computations beyond pairwise reuse. More recently, Anneser et al. [4] introduce AutoSteer, a learning-based framework that tunes optimizer parameters to improve performance across batches of similar queries, achieving up to 40% speedup on engines like PostgreSQL and SparkSQL.
8
Conclusion
We proposed a new method that incorporates incremental view maintenance cost directly into the optimization objective and applies local search to efficiently explore the solution space. In order to apply local search to the view selection problem, we define neighboring solutions using sub-query containment, and select initial solutions based on sub-query frequency, utility, or utility per storage unit. We confirmed the practical effectiveness of our view selection method using a recent benchmark, RedBench. Future work includes the following directions: (1) Evaluating the effectiveness of materialized views on more generalized workloads (2) Utilizing PostgreSQL’s incremental view maintenance functionality7 (3) Applying the wvlet framework for extracting logical plans, generating view definitions, and rewriting queries8 Since the JOB workload used in the experiments does not include common query patterns such as nested queries, it is necessary to investigate how the effectiveness of the proposed method and BIGSUBS changes when applied to more generalized workloads. Moreover, by implementing PostgreSQL’s incremental view maintenance feature, we plan to evaluate the update cost during workload execution. In addition, since wvlet, a flow-based query language, allows direct extraction of logical plans, we aim to explore the feasibility of integrating our method with this framework. 7 https://wiki.postgresql.org/wiki/Incremental_View_Maintenance 8 https://github.com/wvlet/wvlet
CIKM ’25, November 10–14, 2025, Coex, Seoul, Korea
9
Kaina Anderson, Yohanes Yohanie Fridelin Panduman, Yuya Sasaki, and Makoto Onizuka
GenAI Usage Disclosure
We gratefully acknowledge the use of generative AI tools in the preparation of this manuscript. Specifically, we used [ChatGPT-4o] for the following purposes: • Translation Assistance: The AI tool was employed for English proofreading and for translating our native language text into English. The authors thoroughly reviewed all translated content and made revisions to ensure accuracy and clarity. • LaTeX Code Generation for Tables: Based on data collected and organized by the authors, the AI tool was used to generate LaTeX code for formatting tables. The authors verified the correctness of the generated code and ensured appropriate data presentation. • Programming Support: During the development of the programs used in the experiments, the AI tool assisted in debugging and suggesting modifications. All code modifications proposed by the AI were reviewed, tested, and validated by the authors. The authors take full responsibility for the final content and integrity of this research.
References [1] E. H. L. Aarts and J. K. Lenstra. 1997. Local Search in Combinatorial Optimization. Princeton University Press. [2] Supun Abeysinghe, Qiyang He, and Tiark Rompf. 2022. Efficient incrementialization of correlated nested aggregate queries using relative partial aggregate indexes (RPAI). In Proceedings of SIGMOD. 136–149. [3] Yanif Ahmad and Christoph Koch. 2009. DBToaster: a SQL compiler for highperformance delta processing in main-memory databases. Proc. VLDB Endow. 2, 2 (2009), 1566–1569. [4] Christoph Anneser, Nesime Tatbul, David Cohen, Zhenggang Xu, Prithviraj Pandian, Nikolay Laptev, and Ryan Marcus. 2023. AutoSteer: Learned Query Optimization for Any SQL Database. PVLDB 16, 12 (2023), 3515–3527. [5] J. Camacho-Rodríguez, D. Colazzo, M. Herschel, I. Manolescu, and S. R. Chowdhury. 2016. Reuse-Based Optimization for Pig Latin. In Proceedings of CIKM. 2215–2220. [6] Tansel Dokeroglu, Murat Ali Bayir, and Ahmet Cosar. 2015. Robust heuristic algorithms for exploiting the common tasks of relational cloud database queries. Applied Soft Computing 30 (2015), 72–82. [7] Ashish Gupta, Inderpal Singh Mumick, and V. S. Subrahmanian. 1993. Maintaining views incrementally. In Proceedings of SIGMOD. [8] H. Gupta. 1997. Selection of Views to Materialize in a Data Warehouse. In Proceedings of ICDT. 98–112. [9] H. Gupta and I. S. Mumick. 2005. Selection of views to materialize in a data warehouse. IEEE Transactions on Knowledge and Data Engineering 17, 1 (2005), 24–43. [10] Alon Y Halevy. 2001. Answering queries using views: A survey. The VLDB Journal 10 (2001), 270–294. [11] A. Y. Halevy. 2001. Answering queries using views: A survey. The VLDB Journal 10, 4 (2001), 270–294. [12] Venky Harinarayan, Anand Rajaraman, and Jeffrey D Ullman. 1996. Implementing data cubes efficiently. ACM Sigmod Record 25, 2 (1996), 205–216. [13] V. Harinarayan, A. Rajaraman, and J. D. Ullman. 1996. Implementing Data Cubes Efficiently. In Proceedings of SIGMOD. 205–216. [14] Alekh Jindal, Konstantinos Karanasos, Sriram Rao, and Hiren Patel. 2018. Selecting subexpressions to materialize at datacenter scale. Proc. VLDB Endow. 11, 7 (2018). [15] Christoph Koch and Thomas Schlegel. 2004. Multi-Query Optimization Revisited: A Full-Query Algebraic Method. In Proceedings of VLDB. 539–550. [16] Hai Lan, Zhifeng Bao, and Yuwei Peng. 2021. A survey on advancing the DBMS query optimizer: Cardinality estimation, cost model, and plan enumeration. Data Science and Engineering 6 (2021), 86–101. [17] Viktor Leis, Andrey Gubichev, Atanas Mirchev, Peter Boncz, Alfons Kemper, and Thomas Neumann. 2015. How good are query optimizers, really? Proceedings of the VLDB Endowment 9, 3 (2015), 204–215. [18] I. Mami and Z. Bellahsene. 2012. A Survey of View Selection Methods. SIGMOD Record 41, 1 (2012), 20–29.
[19] Stratos Papadomanolakis and Anastassia Ailamaki. 2007. An Integer Linear Programming Approach to Database Design. In Proceedings of ICDE Workshop. 442–449. [20] Raghu Ramakrishnan, Baskar Sridharan, John R Douceur, Pavan Kasturi, Balaji Krishnamachari-Sampath, Karthick Krishnamoorthy, Peng Li, Mitica Manu, Spiro Michaylov, Rogério Ramos, et al. 2017. Azure data lake store: a hyperscale distributed file service for big data analytics. In Proceedings of SIGMOD. 51–63. [21] Prasan Roy, S. Seshadri, and S. Sudarshan. 2000. Efficient and Extensible Algorithms for Multi-Query Optimization. In Proceedings of SIGMOD. 249–260. [22] Yasin N Silva, Paul-Ake Larson, and Jingren Zhou. 2012. Exploiting common subexpressions for cloud query processing. In Proceedings of ICDE. IEEE, 1337– 1348. [23] D. Theodoratos and T. K. Sellis. 1997. Data Warehouse Configuration. In Proceedings of VLDB. 126–135. [24] Zuozhi Wang, Kai Zeng, Botong Huang, Wei Chen, Xiaozong Cui, Bo Wang, Ji Liu, Liya Fan, Dachuan Qu, Zhenyu Hou, Tao Guan, Chen Li, and Jingren Zhou. 2023. Tempura: a general cost-based optimizer framework for incremental data processing (Journal Version). The VLDB Journal 32, 6 (2023), 1315–1342. [25] Ziniu Wu, Parimarjan Negi, Mohammad Alizadeh, Tim Kraska, and Samuel Madden. 2023. FactorJoin: A New Cardinality Estimation Framework for Join Queries. Proceedings of SIGMOD 1, 1 (2023). [26] Zongheng Yang, Amog Kamsetty, Sifei Luan, Eric Liang, Yan Duan, Xi Chen, and Ion Stoica. 2020. NeuroCard: one cardinality estimator for all tables. Proc. VLDB Endow. 14, 1 (2020), 61–73. [27] Haitao Yuan, Guoliang Li, Ling Feng, Ji Sun, and Yue Han. 2020. Automatic view generation with deep learning and reinforcement learning. In Proceedings of ICDE. IEEE, 1501–1512. [28] Jingren Zhou, Per-Ake Larson, Johann-Christoph Freytag, and Wolfgang Lehner. 2007. Efficient exploitation of similar subexpressions for query processing. In Proceedings of SIGMOD. 533–544. [29] Xuanhe Zhou, Chengliang Chai, Guoliang Li, and JI SUN. 2020. Database Meets Artificial Intelligence: A Survey. IEEE Transactions on Knowledge and Data Engineering 34, 3 (2020).