Workload acceleration by optimizing materialized view selection using local search
Extracted source text · arXiv 2606.03772
%%
%% This is file `sample-sigconf.tex',
%% generated with the docstrip utility.
%%
%% The original source files were:
%%
%% samples.dtx (with options: `all,proceedings,bibtex,sigconf')
%%
%% IMPORTANT NOTICE:
%%
%% For the copyright see the source file.
%%
%% Any modified versions of this file must be renamed
%% with new filenames distinct from sample-sigconf.tex.
%%
%% For distribution of the original source see the terms
%% for copying and modification in the file samples.dtx.
%%
%% This generated file may be distributed as long as the
%% original source files, as listed above, are part of the
%% same distribution. (The sources need not necessarily be
%% in the same archive or directory.)
%%
%%
%% Commands for TeXCount
%TC:macro \cite [option:text,text]
%TC:macro \citep [option:text,text]
%TC:macro \citet [option:text,text]
%TC:envir table 0 1
%TC:envir table* 0 1
%TC:envir tabular [ignore] word
%TC:envir displaymath 0 word
%TC:envir math 0 word
%TC:envir comment 0 0
%%
%% The first command in your LaTeX source must be the \documentclass
%% command.
%%
%% For submission and review of your manuscript please change the
%% command to \documentclass[manuscript, screen, review]{acmart}.
%%
%% When submitting camera ready or to TAPS, please change the command
%% to \documentclass[sigconf]{acmart} or whichever template is required
%% for your publication.
%%
%%
\documentclass[sigconf]{acmart}
%--- 基本フォント・エンコーディング関連 ---%
\usepackage[utf8]{inputenc} % UTF-8 文字コード
\usepackage{times} % Times フォント
\usepackage{latexsym} % LaTeX symbols
\usepackage{bm} % 数学記号と太字ベクトル
%--- 数式関連 ---%
\usepackage{amsmath} % 数式
\usepackage{amsthm} % 定理・証明環境
\usepackage{breqn} % 複雑な数式の折り返し対応
%--- 図表・レイアウト ---%
\usepackage{graphicx} % 画像挿入
\usepackage{booktabs} % 表のルール改善
\usepackage{multirow} % 表セルの結合
\usepackage{makecell} % 表セルの柔軟な書式
\usepackage{subcaption}
%--- アルゴリズム環境 ---%
\usepackage{algorithm}
\usepackage{algorithmic} % algorithm環境用（pseudocode書きたい場合）
%--- 文書構成・補助 ---%
% \usepackage{url} % URL表示
% \usepackage{cite} % 引用の統合
% \usepackage{enumitem} % 箇条書きの調整
% \usepackage{comment} % コメントアウト用
% \usepackage{soul} % ハイライト用（例：\hl{}）
% \usepackage[small]{caption} % キャプションのサイズ調整
\usepackage{graphicx} % プリアンブルに必要
\newcommand{\bmU}{\bm{U}}
\newcommand{\bmP}{\bm{P}}
\newcommand{\bmL}{\bm{L}}
\newcommand{\bmK}{\bm{K}}
\newcommand{\bmM}{\bm{M}}
\newcommand{\bmX}{\bm{X}}
\newcommand{\bmY}{\bm{Y}}
\newcommand{\bmI}{\bm{I}}
\newcommand{\bmJ}{\bm{J}}
\newcommand{\bmZ}{\bm{Z}}
\newcommand{\bmT}{\bm{T}}
\newcommand{\bmA}{\bm{A}}
\newcommand{\bmB}{\bm{B}}
\newcommand{\bmG}{\bm{G}}
\newcommand{\bmS}{\bm{S}}
\newcommand{\bmD}{\bm{D}}
\newcommand{\bmW}{\bm{W}}
\newcommand{\bmQ}{\bm{Q}}
\newcommand{\bmH}{\bm{H}}
\renewcommand{\algorithmicrequire}{\textbf{Input:}}
\renewcommand{\algorithmicensure}{\textbf{Output:}}
\newtheorem{theorem}{Theorem}
\newtheorem{definition}{definition}
\newtheorem{example}{Example}
\newcommand{\yuya}[1]{{\color{black}#1}}
\newcommand{\kaina}[1]{{\color{black}#1}}
%%
%% \BibTeX command to typeset BibTeX logo in the docs
\AtBeginDocument{%
\providecommand\BibTeX{{%
Bib\TeX}}}
%% Rights management information. This information is sent to you
%% when you complete the rights form. These commands have SAMPLE
%% values in them; it is your responsibility as an author to replace
%% the commands and values with those provided to you when you
%% complete the rights form.
\setcopyright{acmlicensed}
\copyrightyear{2025}
\acmYear{2025}
\acmDOI{XXXXXXX.XXXXXXX}
%% These commands are for a PROCEEDINGS abstract or paper.
\acmConference[CIKM '25]{the 34th ACM International Conference on Information and Knowledge Management}{November 10--14, 2025}{Coex, Seoul, Korea}
% \acmConference[Conference acronym 'XX]{Make sure to enter the correct conference title from your rights confirmation email}{June 03--05, 2018}{Woodstock, NY}
%% Uncomment \acmBooktitle if the title of the proceedings is different
%% from ``Proceedings of ...''!
%%
\acmBooktitle{Proceedings of the 34th ACM International Conference on Information and Knowledge Management (CIKM ’25), November 10--14, 2025, Coex, Seoul, Korea}
%%\acmBooktitle{Woodstock '18: ACM Symposium on Neural Gaze Detection,
%% June 03--05, 2018, Woodstock, NY}
\acmISBN{978-1-4503-XXXX-X/2018/06}
%%
%% Submission ID.
%% Use this when submitting an article to a sponsored event. You'll
%% receive a unique submission ID from the organizers
%% of the event, and this ID should be used as the parameter to this command.
%%\acmSubmissionID{123-A56-BU3}
%%
%% For managing citations, it is recommended to use bibliography
%% files in BibTeX format.
%%
%% You can then either use BibTeX with the ACM-Reference-Format style,
%% or BibLaTeX with the acmnumeric or acmauthoryear sytles, that include
%% support for advanced citation of software artefact from the
%% biblatex-software package, also separately available on CTAN.
%%
%% Look at the sample-*-biblatex.tex files for templates showcasing
%% the biblatex styles.
%%
%%
%% The majority of ACM publications use numbered citations and
%% references. The command \citestyle{authoryear} switches to the
%% "author year" style.
%%
%% If you are preparing content for an event
%% sponsored by ACM SIGGRAPH, you must use the "author year" style of
%% citations and references.
%% Uncommenting
%% the next command will enable that style.
%%\citestyle{acmauthoryear}
%%
%% end of the preamble, start of the body of the document source.
\begin{document}
%%
%% The "title" command has an optional parameter,
%% allowing the author to define a "short title" to be used in page headers.
\title{Workload acceleration by optimizing materialized view selection using local search}
%%
%% The "author" command and its associated commands are used to define
%% the authors and their affiliations.
%% Of note is the shared affiliation of the first two authors, and the
%% "authornote" and "authornotemark" commands
%% used to denote shared contribution to the research.
% \author{Anonymous Author(s)}
% \affiliation{%
% \institution{Paper under review}
% \city{Confidential}
% \country{}
% }
\author{Kaina Anderson}
% \authornote{Both authors contributed equally to this research.}
\affiliation{%
\institution{The University of Osaka}
\city{Suita}
\state{Osaka}
\country{Japan}
}
\email{anderson.kaina@ist.osaka-u.ac.jp}
\orcid{1234-5678-9012}
\author{Yohanes Yohanie Fridelin Panduman}
% \authornotemark[1]
\affiliation{%
\institution{The University of Osaka}
\city{Suita}
\state{Osaka}
\country{Japan}
}
\email{yohanes@ist.osaka-u.ac.jp}
\author{Yuya Sasaki}
% \authornotemark[2]
\affiliation{%
\institution{The University of Osaka}
\city{Suita}
\state{Osaka}
\country{Japan}
}
\email{sasaki@ist.osaka-u.ac.jp}
\author{Makoto Onizuka}
% \authornotemark[2]
\affiliation{%
\institution{The University of Osaka}
\city{Suita}
\state{Osaka}
\country{Japan}
}
\email{onizuka@ist.osaka-u.ac.jp}
% \author{Lars Th{\o}rv{\"a}ld}
% \affiliation{%
% \institution{The Th{\o}rv{\"a}ld Group}
% \city{Hekla}
% \country{Iceland}}
% \email{larst@affiliation.org}
% \author{Valerie B\'eranger}
% \affiliation{%
% \institution{Inria Paris-Rocquencourt}
% \city{Rocquencourt}
% \country{France}
% }
% \author{Aparna Patel}
% \affiliation{%
% \institution{Rajiv Gandhi University}
% \city{Doimukh}
% \state{Arunachal Pradesh}
% \country{India}}
% \author{Huifen Chan}
% \affiliation{%
% \institution{Tsinghua University}
% \city{Haidian Qu}
% \state{Beijing Shi}
% \country{China}}
% \author{Charles Palmer}
% \affiliation{%
% \institution{Palmer Research Laboratories}
% \city{San Antonio}
% \state{Texas}
% \country{USA}}
% \email{cpalmer@prl.com}
% \author{John Smith}
% \affiliation{%
% \institution{The Th{\o}rv{\"a}ld Group}
% \city{Hekla}
% \country{Iceland}}
% \email{jsmith@affiliation.org}
% \author{Julius P. Kumquat}
% \affiliation{%
% \institution{The Kumquat Consortium}
% \city{New York}
% \country{USA}}
% \email{jpkumquat@consortium.net}
%%
%% By default, the full list of authors will be used in the page
%% headers. Often, this list is too long, and will overlap
%% other information printed in the page headers. This command allows
%% the author to define a more concise list
%% of authors' names for this purpose.
%\renewcommand{\shortauthors}{Kaina at el.}
%%
%% The abstract is a short summary of the work to be presented in the
%% article.
\begin{abstract}
The growing size of database workloads has made view selection a key performance challenge. Materializing frequent sub-queries \yuya{in workloads} improves \yuya{query efficiency, but it incurs significant view maintenance costs due to updates.}
%performance, maintaining these views during updates incurs significant overhead.
\yuya{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 \yuya{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.
\end{abstract}
%%
%% The code below is generated by the tool at http://dl.acm.org/ccs.cfm.
%% Please copy and paste the code instead of the example below.
%%
% \begin{CCSXML}
% <ccs2012>
% <concept>
% <concept_id>00000000.0000000.0000000</concept_id>
% <concept_desc>Do Not Use This Code, Generate the Correct Terms for Your Paper</concept_desc>
% <concept_significance>500</concept_significance>
% </concept>
% <concept>
% <concept_id>00000000.00000000.00000000</concept_id>
% <concept_desc>Do Not Use This Code, Generate the Correct Terms for Your Paper</concept_desc>
% <concept_significance>300</concept_significance>
% </concept>
% <concept>
% <concept_id>00000000.00000000.00000000</concept_id>
% <concept_desc>Do Not Use This Code, Generate the Correct Terms for Your Paper</concept_desc>
% <concept_significance>100</concept_significance>
% </concept>
% <concept>
% <concept_id>00000000.00000000.00000000</concept_id>
% <concept_desc>Do Not Use This Code, Generate the Correct Terms for Your Paper</concept_desc>
% <concept_significance>100</concept_significance>
% </concept>
% </ccs2012>
% \end{CCSXML}
% \ccsdesc[500]{Do Not Use This Code~Generate the Correct Terms for Your Paper}
% \ccsdesc[300]{Do Not Use This Code~Generate the Correct Terms for Your Paper}
% \ccsdesc{Do Not Use This Code~Generate the Correct Terms for Your Paper}
% \ccsdesc[100]{Do Not Use This Code~Generate the Correct Terms for Your Paper}
\begin{CCSXML}
<ccs2012>
<concept>
<concept_id>10002951</concept_id>
<concept_desc>Information systems</concept_desc>
<concept_significance>500</concept_significance>
</concept>
</ccs2012>
\end{CCSXML}
\ccsdesc[500]{Information systems}
%%
%% Keywords. The author(s) should pick words that accurately describe
%% the work being presented. Separate the keywords with commas.
\keywords{View selection, Materialized view, Integer linear programming}
%% A "teaser" image appears between the author and affiliation
%% information and the body of the document, and typically spans the
%% page.
% \begin{teaserfigure}
% \includegraphics[width=\textwidth]{img/sampleteaser.pdf}
% \caption{Seattle Mariners at Spring Training, 2010.}
% \Description{Enjoying the baseball game from the third-base
% seats. Ichiro Suzuki preparing to bat.}
% \label{fig:teaser}
% \end{teaserfigure}
%\received{20 February 2007}
%\received[revised]{12 March 2009}
%\received[accepted]{5 June 2009}
%%
%% This command processes the author and affiliation and title
%% information and builds the first part of the formatted document.
\maketitle
\input{1-intro}
\input{2-preliminaries}
\input{3-prop}
\input{4-ex}
\input{5-rela}
\input{6-conc}
\input{7-AIdisclose}
\begin{comment}
\input{9:appendix}
\end{comment}
% \section{Introduction}
% ACM's consolidated article template, introduced in 2017, provides a
% consistent \LaTeX\ style for use across ACM publications, and
% incorporates accessibility and metadata-extraction functionality
% necessary for future Digital Library endeavors. Numerous ACM and
% SIG-specific \LaTeX\ templates have been examined, and their unique
% features incorporated into this single new template.
% If you are new to publishing with ACM, this document is a valuable
% guide to the process of preparing your work for publication. If you
% have published with ACM before, this document provides insight and
% instruction into more recent changes to the article template.
% The ``\verb|acmart|'' document class can be used to prepare articles
% for any ACM publication --- conference or journal, and for any stage
% of publication, from review to final ``camera-ready'' copy, to the
% author's own version, with {\itshape very} few changes to the source.
% \section{Template Overview}
% As noted in the introduction, the ``\verb|acmart|'' document class can
% be used to prepare many different kinds of documentation --- a
% double-anonymous initial submission of a full-length technical paper, a
% two-page SIGGRAPH Emerging Technologies abstract, a ``camera-ready''
% journal article, a SIGCHI Extended Abstract, and more --- all by
% selecting the appropriate {\itshape template style} and {\itshape
% template parameters}.
% This document will explain the major features of the document
% class. For further information, the {\itshape \LaTeX\ User's Guide} is
% available from
% \url{https://www.acm.org/publications/proceedings-template}.
% \subsection{Template Styles}
% The primary parameter given to the ``\verb|acmart|'' document class is
% the {\itshape template style} which corresponds to the kind of publication
% or SIG publishing the work. This parameter is enclosed in square
% brackets and is a part of the {\verb|documentclass|} command:
% \begin{verbatim}
% \documentclass[STYLE]{acmart}
% \end{verbatim}
% Journals use one of three template styles. All but three ACM journals
% use the {\verb|acmsmall|} template style:
% \begin{itemize}
% \item {\texttt{acmsmall}}: The default journal template style.
% \item {\texttt{acmlarge}}: Used by JOCCH and TAP.
% \item {\texttt{acmtog}}: Used by TOG.
% \end{itemize}
% The majority of conference proceedings documentation will use the {\verb|acmconf|} template style.
% \begin{itemize}
% \item {\texttt{sigconf}}: The default proceedings template style.
% \item{\texttt{sigchi}}: Used for SIGCHI conference articles.
% \item{\texttt{sigplan}}: Used for SIGPLAN conference articles.
% \end{itemize}
% \subsection{Template Parameters}
% In addition to specifying the {\itshape template style} to be used in
% formatting your work, there are a number of {\itshape template parameters}
% which modify some part of the applied template style. A complete list
% of these parameters can be found in the {\itshape \LaTeX\ User's Guide.}
% Frequently-used parameters, or combinations of parameters, include:
% \begin{itemize}
% \item {\texttt{anonymous,review}}: Suitable for a ``double-anonymous''
% conference submission. Anonymizes the work and includes line
% numbers. Use with the \texttt{\string\acmSubmissionID} command to print the
% submission's unique ID on each page of the work.
% \item{\texttt{authorversion}}: Produces a version of the work suitable
% for posting by the author.
% \item{\texttt{screen}}: Produces colored hyperlinks.
% \end{itemize}
% This document uses the following string as the first command in the
% source file:
% \begin{verbatim}
% \documentclass[sigconf]{acmart}
% \end{verbatim}
% \section{Modifications}
% Modifying the template --- including but not limited to: adjusting
% margins, typeface sizes, line spacing, paragraph and list definitions,
% and the use of the \verb|\vspace| command to manually adjust the
% vertical spacing between elements of your work --- is not allowed.
% {\bfseries Your document will be returned to you for revision if
% modifications are discovered.}
% \section{Typefaces}
% The ``\verb|acmart|'' document class requires the use of the
% ``Libertine'' typeface family. Your \TeX\ installation should include
% this set of packages. Please do not substitute other typefaces. The
% ``\verb|lmodern|'' and ``\verb|ltimes|'' packages should not be used,
% as they will override the built-in typeface families.
% \section{Title Information}
% The title of your work should use capital letters appropriately -
% \url{https://capitalizemytitle.com/} has useful rules for
% capitalization. Use the {\verb|title|} command to define the title of
% your work. If your work has a subtitle, define it with the
% {\verb|subtitle|} command. Do not insert line breaks in your title.
% If your title is lengthy, you must define a short version to be used
% in the page headers, to prevent overlapping text. The \verb|title|
% command has a ``short title'' parameter:
% \begin{verbatim}
% \title[short title]{full title}
% \end{verbatim}
% \section{Authors and Affiliations}
% Each author must be defined separately for accurate metadata
% identification. As an exception, multiple authors may share one
% affiliation. Authors' names should not be abbreviated; use full first
% names wherever possible. Include authors' e-mail addresses whenever
% possible.
% Grouping authors' names or e-mail addresses, or providing an ``e-mail
% alias,'' as shown below, is not acceptable:
% \begin{verbatim}
% \author{Brooke Aster, David Mehldau}
% \email{dave,judy,steve@university.edu}
% \email{firstname.lastname@phillips.org}
% \end{verbatim}
% The \verb|authornote| and \verb|authornotemark| commands allow a note
% to apply to multiple authors --- for example, if the first two authors
% of an article contributed equally to the work.
% If your author list is lengthy, you must define a shortened version of
% the list of authors to be used in the page headers, to prevent
% overlapping text. The following command should be placed just after
% the last \verb|\author{}| definition:
% \begin{verbatim}
% \renewcommand{\shortauthors}{McCartney, et al.}
% \end{verbatim}
% Omitting this command will force the use of a concatenated list of all
% of the authors' names, which may result in overlapping text in the
% page headers.
% The article template's documentation, available at
% \url{https://www.acm.org/publications/proceedings-template}, has a
% complete explanation of these commands and tips for their effective
% use.
% Note that authors' addresses are mandatory for journal articles.
% \section{Rights Information}
% Authors of any work published by ACM will need to complete a rights
% form. Depending on the kind of work, and the rights management choice
% made by the author, this may be copyright transfer, permission,
% license, or an OA (open access) agreement.
% Regardless of the rights management choice, the author will receive a
% copy of the completed rights form once it has been submitted. This
% form contains \LaTeX\ commands that must be copied into the source
% document. When the document source is compiled, these commands and
% their parameters add formatted text to several areas of the final
% document:
% \begin{itemize}
% \item the ``ACM Reference Format'' text on the first page.
% \item the ``rights management'' text on the first page.
% \item the conference information in the page header(s).
% \end{itemize}
% Rights information is unique to the work; if you are preparing several
% works for an event, make sure to use the correct set of commands with
% each of the works.
% The ACM Reference Format text is required for all articles over one
% page in length, and is optional for one-page articles (abstracts).
% \section{CCS Concepts and User-Defined Keywords}
% Two elements of the ``acmart'' document class provide powerful
% taxonomic tools for you to help readers find your work in an online
% search.
% The ACM Computing Classification System ---
% \url{https://www.acm.org/publications/class-2012} --- is a set of
% classifiers and concepts that describe the computing
% discipline. Authors can select entries from this classification
% system, via \url{https://dl.acm.org/ccs/ccs.cfm}, and generate the
% commands to be included in the \LaTeX\ source.
% User-defined keywords are a comma-separated list of words and phrases
% of the authors' choosing, providing a more flexible way of describing
% the research being presented.
% CCS concepts and user-defined keywords are required for for all
% articles over two pages in length, and are optional for one- and
% two-page articles (or abstracts).
% \section{Sectioning Commands}
% Your work should use standard \LaTeX\ sectioning commands:
% \verb|\section|, \verb|\subsection|, \verb|\subsubsection|,
% \verb|\paragraph|, and \verb|\subparagraph|. The sectioning levels up to
% \verb|\subsusection| should be numbered; do not remove the numbering
% from the commands.
% Simulating a sectioning command by setting the first word or words of
% a paragraph in boldface or italicized text is {\bfseries not allowed.}
% Below are examples of sectioning commands.
% \subsection{Subsection}
% \label{sec:subsection}
% This is a subsection.
% \subsubsection{Subsubsection}
% \label{sec:subsubsection}
% This is a subsubsection.
% \paragraph{Paragraph}
% This is a paragraph.
% \subparagraph{Subparagraph}
% This is a subparagraph.
% \section{Tables}
% The ``\verb|acmart|'' document class includes the ``\verb|booktabs|''
% package --- \url{https://ctan.org/pkg/booktabs} --- for preparing
% high-quality tables.
% Table captions are placed {\itshape above} the table.
% Because tables cannot be split across pages, the best placement for
% them is typically the top of the page nearest their initial cite. To
% ensure this proper ``floating'' placement of tables, use the
% environment \textbf{table} to enclose the table's contents and the
% table caption. The contents of the table itself must go in the
% \textbf{tabular} environment, to be aligned properly in rows and
% columns, with the desired horizontal and vertical rules. Again,
% detailed instructions on \textbf{tabular} material are found in the
% \textit{\LaTeX\ User's Guide}.
% Immediately following this sentence is the point at which
% Table~\ref{tab:freq} is included in the input file; compare the
% placement of the table here with the table in the printed output of
% this document.
% \begin{table}
% \caption{Frequency of Special Characters}
% \label{tab:freq}
% \begin{tabular}{ccl}
% \toprule
% Non-English or Math&Frequency&Comments\\
% \midrule
% \O & 1 in 1,000& For Swedish names\\
% $\pi$ & 1 in 5& Common in math\\
% \$ & 4 in 5 & Used in business\\
% $\Psi^2_1$ & 1 in 40,000& Unexplained usage\\
% \bottomrule
% \end{tabular}
% \end{table}
% To set a wider table, which takes up the whole width of the page's
% live area, use the environment \textbf{table*} to enclose the table's
% contents and the table caption. As with a single-column table, this
% wide table will ``float'' to a location deemed more
% desirable. Immediately following this sentence is the point at which
% Table~\ref{tab:commands} is included in the input file; again, it is
% instructive to compare the placement of the table here with the table
% in the printed output of this document.
% \begin{table*}
% \caption{Some Typical Commands}
% \label{tab:commands}
% \begin{tabular}{ccl}
% \toprule
% Command &A Number & Comments\\
% \midrule
% \texttt{{\char'134}author} & 100& Author \\
% \texttt{{\char'134}table}& 300 & For tables\\
% \texttt{{\char'134}table*}& 400& For wider tables\\
% \bottomrule
% \end{tabular}
% \end{table*}
% Always use midrule to separate table header rows from data rows, and
% use it only for this purpose. This enables assistive technologies to
% recognise table headers and support their users in navigating tables
% more easily.
% \section{Math Equations}
% You may want to display math equations in three distinct styles:
% inline, numbered or non-numbered display. Each of the three are
% discussed in the next sections.
% \subsection{Inline (In-text) Equations}
% A formula that appears in the running text is called an inline or
% in-text formula. It is produced by the \textbf{math} environment,
% which can be invoked with the usual
% \texttt{{\char'134}begin\,\ldots{\char'134}end} construction or with
% the short form \texttt{\$\,\ldots\$}. You can use any of the symbols
% and structures, from $\alpha$ to $\omega$, available in
% \LaTeX~\cite{Lamport:LaTeX}; this section will simply show a few
% examples of in-text equations in context. Notice how this equation:
% \begin{math}
% \lim_{n\rightarrow \infty}x=0
% \end{math},
% set here in in-line math style, looks slightly different when
% set in display style. (See next section).
% \subsection{Display Equations}
% A numbered display equation---one set off by vertical space from the
% text and centered horizontally---is produced by the \textbf{equation}
% environment. An unnumbered display equation is produced by the
% \textbf{displaymath} environment.
% Again, in either environment, you can use any of the symbols and
% structures available in \LaTeX\@; this section will just give a couple
% of examples of display equations in context. First, consider the
% equation, shown as an inline equation above:
% \begin{equation}
% \lim_{n\rightarrow \infty}x=0
% \end{equation}
% Notice how it is formatted somewhat differently in
% the \textbf{displaymath}
% environment. Now, we'll enter an unnumbered equation:
% \begin{displaymath}
% \sum_{i=0}^{\infty} x + 1
% \end{displaymath}
% and follow it with another numbered equation:
% \begin{equation}
% \sum_{i=0}^{\infty}x_i=\int_{0}^{\pi+2} f
% \end{equation}
% just to demonstrate \LaTeX's able handling of numbering.
% \section{Figures}
% The ``\verb|figure|'' environment should be used for figures. One or
% more images can be placed within a figure. If your figure contains
% third-party material, you must clearly identify it as such, as shown
% in the example below.
% \begin{figure}[h]
% \centering
% \includegraphics[width=\linewidth]{sample-franklin}
% \caption{1907 Franklin Model D roadster. Photograph by Harris \&
% Ewing, Inc. [Public domain], via Wikimedia
% Commons. (\url{https://goo.gl/VLCRBB}).}
% \Description{A woman and a girl in white dresses sit in an open car.}
% \end{figure}
% Your figures should contain a caption which describes the figure to
% the reader.
% Figure captions are placed {\itshape below} the figure.
% Every figure should also have a figure description unless it is purely
% decorative. These descriptions convey what’s in the image to someone
% who cannot see it. They are also used by search engine crawlers for
% indexing images, and when images cannot be loaded.
% A figure description must be unformatted plain text less than 2000
% characters long (including spaces). {\bfseries Figure descriptions
% should not repeat the figure caption – their purpose is to capture
% important information that is not already provided in the caption or
% the main text of the paper.} For figures that convey important and
% complex new information, a short text description may not be
% adequate. More complex alternative descriptions can be placed in an
% appendix and referenced in a short figure description. For example,
% provide a data table capturing the information in a bar chart, or a
% structured list representing a graph. For additional information
% regarding how best to write figure descriptions and why doing this is
% so important, please see
% \url{https://www.acm.org/publications/taps/describing-figures/}.
% \subsection{The ``Teaser Figure''}
% A ``teaser figure'' is an image, or set of images in one figure, that
% are placed after all author and affiliation information, and before
% the body of the article, spanning the page. If you wish to have such a
% figure in your article, place the command immediately before the
% \verb|\maketitle| command:
% \begin{verbatim}
% \begin{teaserfigure}
% \includegraphics[width=\textwidth]{sampleteaser}
% \caption{figure caption}
% \Description{figure description}
% \end{teaserfigure}
% \end{verbatim}
% \section{Citations and Bibliographies}
% The use of \BibTeX\ for the preparation and formatting of one's
% references is strongly recommended. Authors' names should be complete
% --- use full first names (``Donald E. Knuth'') not initials
% (``D. E. Knuth'') --- and the salient identifying features of a
% reference should be included: title, year, volume, number, pages,
% article DOI, etc.
% The bibliography is included in your source document with these two
% commands, placed just before the \verb|\end{document}| command:
% \begin{verbatim}
% \bibliographystyle{ACM-Reference-Format}
% \bibliography{bibfile}
% \end{verbatim}
% where ``\verb|bibfile|'' is the name, without the ``\verb|.bib|''
% suffix, of the \BibTeX\ file.
% Citations and references are numbered by default. A small number of
% ACM publications have citations and references formatted in the
% ``author year'' style; for these exceptions, please include this
% command in the {\bfseries preamble} (before the command
% ``\verb|\begin{document}|'') of your \LaTeX\ source:
% \begin{verbatim}
% \citestyle{acmauthoryear}
% \end{verbatim}
% Some examples. A paginated journal article \cite{Abril07}, an
% enumerated journal article \cite{Cohen07}, a reference to an entire
% issue \cite{JCohen96}, a monograph (whole book) \cite{Kosiur01}, a
% monograph/whole book in a series (see 2a in spec. document)
% \cite{Harel79}, a divisible-book such as an anthology or compilation
% \cite{Editor00} followed by the same example, however we only output
% the series if the volume number is given \cite{Editor00a} (so
% Editor00a's series should NOT be present since it has no vol. no.),
% a chapter in a divisible book \cite{Spector90}, a chapter in a
% divisible book in a series \cite{Douglass98}, a multi-volume work as
% book \cite{Knuth97}, a couple of articles in a proceedings (of a
% conference, symposium, workshop for example) (paginated proceedings
% article) \cite{Andler79, Hagerup1993}, a proceedings article with
% all possible elements \cite{Smith10}, an example of an enumerated
% proceedings article \cite{VanGundy07}, an informally published work
% \cite{Harel78}, a couple of preprints \cite{Bornmann2019,
% AnzarootPBM14}, a doctoral dissertation \cite{Clarkson85}, a
% master's thesis: \cite{anisi03}, an online document / world wide web
% resource \cite{Thornburg01, Ablamowicz07, Poker06}, a video game
% (Case 1) \cite{Obama08} and (Case 2) \cite{Novak03} and \cite{Lee05}
% and (Case 3) a patent \cite{JoeScientist001}, work accepted for
% publication \cite{rous08}, 'YYYYb'-test for prolific author
% \cite{SaeediMEJ10} and \cite{SaeediJETC10}. Other cites might
% contain 'duplicate' DOI and URLs (some SIAM articles)
% \cite{Kirschmer:2010:AEI:1958016.1958018}. Boris / Barbara Beeton:
% multi-volume works as books \cite{MR781536} and \cite{MR781537}. A
% couple of citations with DOIs:
% \cite{2004:ITE:1009386.1010128,Kirschmer:2010:AEI:1958016.1958018}. Online
% citations: \cite{TUGInstmem, Thornburg01, CTANacmart}.
% Artifacts: \cite{R} and \cite{UMassCitations}.
% \section{Acknowledgments}
% Identification of funding sources and other support, and thanks to
% individuals and groups that assisted in the research and the
% preparation of the work should be included in an acknowledgment
% section, which is placed just before the reference section in your
% document.
% This section has a special environment:
% \begin{verbatim}
% \begin{acks}
% ...
% \end{acks}
% \end{verbatim}
% so that the information contained therein can be more easily collected
% during the article metadata extraction phase, and to ensure
% consistency in the spelling of the section heading.
% Authors should not prepare this section as a numbered or unnumbered {\verb|\section|}; please use the ``{\verb|acks|}'' environment.
% \section{Appendices}
% If your work needs an appendix, add it before the
% ``\verb|\end{document}|'' command at the conclusion of your source
% document.
% Start the appendix with the ``\verb|appendix|'' command:
% \begin{verbatim}
% \appendix
% \end{verbatim}
% and note that in the appendix, sections are lettered, not
% numbered. This document has two appendices, demonstrating the section
% and subsection identification method.
% \section{Multi-language papers}
% Papers may be written in languages other than English or include
% titles, subtitles, keywords and abstracts in different languages (as a
% rule, a paper in a language other than English should include an
% English title and an English abstract). Use \verb|language=...| for
% every language used in the paper. The last language indicated is the
% main language of the paper. For example, a French paper with
% additional titles and abstracts in English and German may start with
% the following command
% \begin{verbatim}
% \documentclass[sigconf, language=english, language=german,
% language=french]{acmart}
% \end{verbatim}
% The title, subtitle, keywords and abstract will be typeset in the main
% language of the paper. The commands \verb|\translatedXXX|, \verb|XXX|
% begin title, subtitle and keywords, can be used to set these elements
% in the other languages. The environment \verb|translatedabstract| is
% used to set the translation of the abstract. These commands and
% environment have a mandatory first argument: the language of the
% second argument. See \verb|sample-sigconf-i13n.tex| file for examples
% of their usage.
% \section{SIGCHI Extended Abstracts}
% The ``\verb|sigchi-a|'' template style (available only in \LaTeX\ and
% not in Word) produces a landscape-orientation formatted article, with
% a wide left margin. Three environments are available for use with the
% ``\verb|sigchi-a|'' template style, and produce formatted output in
% the margin:
% \begin{description}
% \item[\texttt{sidebar}:] Place formatted text in the margin.
% \item[\texttt{marginfigure}:] Place a figure in the margin.
% \item[\texttt{margintable}:] Place a table in the margin.
% \end{description}
%%
%% The acknowledgments section is defined using the "acks" environment
%% (and NOT an unnumbered section). This ensures the proper
%% identification of the section in the article metadata, and the
%% consistent spelling of the heading.
\begin{comment}
\begin{acks}
To Robert, for the bagels and explaining CMYK and color spaces.
\end{acks}
\end{comment}
%%
%% The next two lines define the bibliography style to be used, and
%% the bibliography file.
\bibliographystyle{ACM-Reference-Format}
\bibliography{sample-base}
%%
%% If your work has an appendix, this is the place to put it.
% \appendix
% \section{Research Methods}
% \subsection{Part One}
% Lorem ipsum dolor sit amet, consectetur adipiscing elit. Morbi
% malesuada, quam in pulvinar varius, metus nunc fermentum urna, id
% sollicitudin purus odio sit amet enim. Aliquam ullamcorper eu ipsum
% vel mollis. Curabitur quis dictum nisl. Phasellus vel semper risus, et
% lacinia dolor. Integer ultricies commodo sem nec semper.
% \subsection{Part Two}
% Etiam commodo feugiat nisl pulvinar pellentesque. Etiam auctor sodales
% ligula, non varius nibh pulvinar semper. Suspendisse nec lectus non
% ipsum convallis congue hendrerit vitae sapien. Donec at laoreet
% eros. Vivamus non purus placerat, scelerisque diam eu, cursus
% ante. Etiam aliquam tortor auctor efficitur mattis.
% \section{Online Resources}
% Nam id fermentum dui. Suspendisse sagittis tortor a nulla mollis, in
% pulvinar ex pretium. Sed interdum orci quis metus euismod, et sagittis
% enim maximus. Vestibulum gravida massa ut felis suscipit
% congue. Quisque mattis elit a risus ultrices commodo venenatis eget
% dui. Etiam sagittis eleifend elementum.
% Nam interdum magna at lectus dignissim, ac dignissim lorem
% rhoncus. Maecenas eu arcu ac neque placerat aliquam. Nunc pulvinar
% massa et mattis lacinia.
\end{document}
\endinput
%%
%% End of file `sample-sigconf.tex'.
\section{Introduction}
In recent years, the growing number of database users has significantly increased the volume of executed queries in workloads, \yuya{making the optimization of workload performance a critical challenge}.~\cite{lan2021survey,intro:survey-answer,azure,bigsubs}.
%This paper addresses this challenge by leveraging the observation that large-scale workloads often contain many common sub-queries \cite{azure,bigsubs,wide-deep}.
\yuya{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~\cite{rela:MV_survey, halevy2001answering}.
However, when updates are made to the underlying tables, the corresponding materialized views must be maintained, which can be time-consuming. % Additionally, materialized views consume storage space, necessitating a storage capacity constraint to manage their size effectively.
\yuya{Therefore, there is a trade-off between the benefit of using materialized views and the overhead of view maintenance.}
\smallskip
\noindent
{\bf Motivation}.
Several materialized view selection methods have been proposed to balance the trade-off~\cite{bigsubs,azure,wide-deep,silva2012exploiting,zhou2007efficient}.
%Considering these trade-offs and constraints, we formulate the problem of selecting optimal materialized views as an integer programming problem. Solving this allows us to determine the best set of views to materialize under storage limitations.
Specifically, BIGSUBS \cite{bigsubs} 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.
%It selects only views with high utility per unit of storage space.
%However, BIGSUBS can select views that deviate from the optimal solution or lead to oscillations in the view selection process, because it relies on probabilistic techniques for view selection.
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.
\smallskip
\noindent
{\bf 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.
%accelerates optimizing view selection by adopting a metaheuristic technique based on local search \cite{neighbor_search,local_serch:easy,local_search:book}.
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~\cite{aarts1997local}, 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 neighbors if one contains the other or is contained by the other.
% For example, a parent sub-query (which contains another) is typically more complex and yields higher utility when materialized, while a child sub-query is likely to appear in more queries. We exploit this property to guide the local search and continue exploring until convergence.
For initial solution selection, we use simple three methods based on sub-query 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) \cite{job}.
%, which contains many join operations and results in a large number of variables for integer programming formulation, making it a suitable workload to test optimization strategies.
These experiments demonstrate that our approach outperforms the state-of-the-art method, BIGSUBS.
\smallskip
\noindent
{\bf Organization}.
The remainder of this paper is organized as follows. Section \ref{sec:prel} describes preliminaries. Section \ref{sec:proposal_overview} presents the overview of our workflow, and Sections~\ref{sec:proposal_viewselection} and \ref{sec:mv_create_query_rewrite} describe methods of view selection and query rewriting in detail, respectively. Section \ref{sec:exp} presents experimental results to demonstrate its effectiveness. Section \ref{sec:rela} discusses related work, and Section \ref{sec:conc} concludes the paper with a summary and directions for future work.
\section{Preliminaries}
\label{sec:prel}
%This chapter defines the integer programming problem addressed in this paper.
%\subsection{Sub-query Materialization}
% Let Q= {q1, q2, . . . , qn}be a set of queries over a set of data sources D, and CD(qi) be the cost of evaluating query qi over D. Let S= {s1, s2, . . . , sm}be a set of materialized (stored) subex-pressions and CS(qi) be the cost of evaluating qi over D∪S.
A workload consists of a set of queries, $\bmQ = \{q_1, q_2, \cdots, q_n\}$ where $q_i$ and $q_j$ for all $i,j~(i \neq j)$ are different and $|Q|=n$ denotes the number of queries in the workload.
%a set of update operations applied to the database.
Let $\bmS = \{s_1, s_2, \cdots, s_m\}$ be a set of sub-queries extracted from $\bmQ$ where $|S|=m$ is the number of sub-queries.
The set $\bmS$ of sub-queries is denoted as follows:
\begin{align}
\label{sub-queries}
\bmS = \bigcup_{q_i \in \bmQ} \texttt{extractSubQueries}(q_i),
\end{align}
where the function $\texttt{extractSubQueries}(\cdot)$ takes a query as input and returns the set of sub-queries corresponding to partial execution plans extracted from the execution plan of $q_i$.
Since execution plans are represented as tree structures, the resulting sub-queries may exhibit containment relationships, i.e., a sub-query may include another.
%A single sub-query may also be contained in multiple other sub-queries.
\yuya{
Figure~\ref{fig:subq_over_query} shows a workload including four queries $q_1$ to $q_4$.
Each query has a tree-structured execution plan. For example, $q_2$ includes six sub-queries. Totally, there are 9 sub-queries in the workload.
}
\begin{figure}[h]
\centering
\includegraphics[width=\hsize]{img/sj_over_qi_EN.pdf}
\caption{An example of workload. Sub-queries shared across multiple queries: sub-query $s_1$ (blue line) is shared by four queries $q_1$-$q_4$, sub-query $s_2$ (green line) is shared by $q_2$ and $q_4$, and sub-query $s_3$ (red line) is shared by $q_2$ and $q_3$. }
\label{fig:subq_over_query}
\end{figure}
\begin{comment}
A materialized view is a database object that stores the precomputed results of a query in physical storage. Once created, it continues to serve the same data until explicitly updated. By reusing materialized views for frequently executed complex queries, the overall computational cost and query response time can be significantly reduced.
\end{comment}
% \subsection{Sub-query Sharing Across Multiple Queries}
% Materializing common sub-queries shared across multiple queries enables improving workload performance.
% %Sub-queries may be shared among different queries.
% As illustrated in Figure~\ref{fig:subq_over_query}, sub-query $s_1$ (blue circle) is shared by four queries $q_1$-$q_4$, and sub-query $s_2$ (green circle) is shared by $q_2$ and $q_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 $s_1$ appears in four queries, its relatively small query plan suggests limited benefit from materialization. Conversely, $s_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.
\subsection{Sub-query Utility and Storage Size}
%The materialized view selection problem involves identifying a subset of sub-queries to materialize in advance to minimize the total workload cost.
%We adopt the definition of sub-query utility from BIGSUBS~\cite{bigsubs}:
\yuya{
View utility~\cite{bigsubs} 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:
%To optimize the trade-off between the benefit of using materialized views and the cost of view maintenance, we employ the view utility~\cite{bigsubs}. Then, we define an objective function that incorporates view maintenance cost in Section~\ref{sec:proposal}.
\begin{definition}
\label{def:view_utility}
The utility of sub-query $s_j$ for query $q_i$ in a database is defined as the reduction in the estimated execution cost of $q_i$ %\footnote{We employ \texttt{EXPLAIN} command for estimating the query execution cost provided by the relational database system.}
using the materialized result of $s_j$:
\begin{align} \label{def:utility}
u_{ij} = \mathsf{cost_{read}}(q_i) - \mathsf{cost_{read}}(q_i|s_j),
\end{align}
where $\mathsf{cost_{read}}(q_i)$ and $\mathsf{cost_{read}}(q_i|s_j)$ are the estimated costs of executing $q_i$ without and with utilizing the materialized result of $s_j$, respectively.
\end{definition}
The total utility $U_j$ of sub-query $s_j$ is defined as the sum of its utility for each query $q_i$ across $\bmQ$:
\begin{align}
\label{U_j}
U_j = \sum_{q_i\in Q} u_{ij}.
\end{align}
We hereby note that if $q_i$ does not include $s_j$, $u_{ij}$ is zero.
%In addition, $U$ denotes a vector consisting of elements $U_j$ for $0 \leq j \leq |S|$.
We define that a storage size $b_j$ of $s_j$ is the estimated size of the result executed by $s_j$.
}
\subsection{Problem Definition}
\yuya{
We solve the following problem in this paper.
\smallskip
\noindent
{\bf Problem definition}. {\it Given a database, $\bmQ = \{q_1, q_2, \cdots, q_n\}$, and storage budget $B_{max}$, we aim to find a set of sub-queries $\subseteq \bmS$ as views that minimize workload execution time and view maintenance time where the total estimated size of views is less than $B_{max}$. We assume that update operations are not given.}
\smallskip
}
Materializing common sub-queries shared across multiple queries enables improving workload performance.
%Sub-queries may be shared among different queries.
As illustrated in Figure~\ref{fig:subq_over_query}, sub-query $s_1$ (blue line) is shared by four queries $q_1$-$q_4$, and sub-query $s_2$ (green line) is shared by $q_2$ and $q_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 $s_1$ appears in four queries, its relatively small query plan suggests limited benefit from materialization. Conversely, $s_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.
\section{Overview of Our Workflow for Selecting and Using Views} \label{sec:proposal_overview}
\begin{figure}[t] \centering
\includegraphics[width=\hsize]{img/proposed_EN.pdf}
\caption{Workflow consisting of
1) sub-query extraction, 2) materialized view (MV) selection, 3) view materialization, and 4) rewriting queries using materialized views.} \label{fig:proposed} \end{figure}
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~\ref{fig:proposed} provides a workflow, which consists of
1) sub-query extraction,
2) materialized view selection,
3) view materialization, and
4) rewriting queries using materialized views.
%accelerates optimizing view selection by adopting a metaheuristic technique based on local search \cite{neighbor_search,local_serch:easy,local_search:book}.
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 \ref{ssec:objective}).
Second, we employ a well-known optimization heuristic, local search~\cite{aarts1997local}, which effectively explores the solution space to find high-quality approximations (Section \ref{ssec:viewSelection}).
Third, we define the incremental view maintenance cost of materialized views in response to update operations in the workload (Section \ref{ssec:view_maintenane}), and then we describe the algorithm of the materialized view selection (Section \ref{ssec: algorithm}).
Finally, we generate materialized views and rewrite the original query to use the views (Section \ref{sec:mv_create_query_rewrite}).
%using the sub-query set that is selected, we define materialized views and create them, as well as rewriting the original query to reference views (Section \ref{ssec:mv_create_query_rewrite}).
\begin{comment}
The method proceeds through the following three steps:
\begin{enumerate}
\item Selection of an initial solution
\item Generation of neighboring solutions
\item Selection of the optimal solution via integer programming
\end{enumerate}
By iteratively performing steps (2) and (3), the method incrementally refines the solution toward optimality. Each of these steps is detailed in the following subsections.
\end{comment}
\section{Method for View Selection}
\label{sec:proposal_viewselection}
We describe how to select views in detail.
\subsection{Objective Function for View Selection}
\label{ssec:objective}
The objective of materialized view selection is to balance the trade-off 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.
%Under these constraints, the problem is to maximize the total utility obtained through materialization.
% The following definition formalizes this optimization problem:
\begin{definition} [View selection problem]
\label{def:loss}
Let $u_{ij}$ be the sub-query utility as defined in Equation (\ref{def:utility}),
$m_j$ be the incremental maintenance cost of materialized sub-query $s_j$\footnote{Section \ref{ssec:view_maintenane} describes the detail of incremental view maintenance cost $m_j$.},
$b_j$ is the storage size of $s_j$, and $B_{max}$ be the storage budget.
Given queries $\bmQ$ and sub-queries $\bmS$, the view selection problem is defined as follows:
\begin{align}
L(\bmQ,\bmS)=\text{maximize } &\sum_{q_i\in \bmQ} \sum_{s_j\in \bmS} u_{ij} \cdot y_{ij} - \sum_{s_j\in \bmS} m_j \cdot z_j\notag \\
\label{st:storage} \text{subject to } \sum_{s_j\in \bmS} b_j &\cdot z_j \le B_{max} \\
\label{st:subq} y_{ik} + \frac{1}{|\bmS|} \sum_{j \ne k} &y_{ij} \cdot x_{jk} \le 1 \quad \forall i \in [1,|\bmQ|], \forall k \in [1,|\bmS|] \\
\label{st:materialize} y_{ij} \le z_j \quad \forall i &\in [1,|\bmQ|], \forall j \in [1,|\bmS|]
\end{align}
\end{definition}
\noindent
$L(\bmQ,\bmS)$ 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)
\footnote{We refer to the contribution of each sub-query to this objective as its \textit{effective utility}.}.
The utility of sub-queries is computed as $u_{ij} \cdot y_{ij}$, where the decision variable $y_{ij}$ indicates whether query $q_i$ uses the materialized view of $s_j$.
The view maintenance cost is computed as $m_j \cdot z_j$, where the decision variable $z_j$ indicates whether $s_j$ is selected for materialization.
Equations~(\ref{st:storage})–(\ref{st:materialize}) define the constraints of the problem:
\begin{itemize}
\item The storage constraint (\ref{st:storage}) ensures the total size of materialized views does not exceed $B_{max}$.
\item The containment constraint (\ref{st:subq}) prevents overlapping views from being used in the same query. Binary variable $x_{jk}$ indicates whether $s_j$ contains $s_k$.
\item The materialization constraint (\ref{st:materialize}) ensures that only materialized sub-queries can be used for query processing.
\end{itemize}
\begin{comment}
\begin{itemize}
\item \textbf{Storage constraint:} The storage size of materialized views must not exceed the available storage budget. Binary variable $z_j$ indicates whether $s_j$ is selected for materialization.
\item \textbf{Overlapping View constraint:} A query can use the materialized results of distinct sub-queries, so overlapping sub-queries can not be used for each query. Binary variable $x_{jk}$ indicates whether $s_j$ contains $s_k$.
\item \textbf{Materialization constraint:} A query can use the materialized sub-queries only if they are materialized. Binary variable $y_{ij}$ indicates whether query $q_i$ uses the materialized view of $s_j$.
\end{itemize}
\end{comment}
\noindent
Solving this integer programming formulation yields: 1) which sub-queries $s_j$ should be materialized ($z_j = 1$), and 2) which query $q_i$ should use which sub-query $s_j$ ($y_{ij} = 1$).
\subsection{Optimization using Local Search}
\label{ssec:viewSelection}
Naively solving the optimization formulation presented in Section~\ref{ssec:objective} 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., $y_{ij}$ and $z_j$ where the size depends on the product of query size ($|\bmQ|$) and sub-query size ($|\bmS|$).
Furthermore, although BIGSUBS~\cite{bigsubs} is designed for large-scale workloads and accelerates view selection,
%by iteratively choosing (1) which sub-queries to materialize and (2) which sub-queries to use for evaluating queries in the workload,
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~\cite{aarts1997local}, 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.
%To address the view selection problem, we approximate a solution by iteratively restricting the solution space to a small subset of candidate views, solving an ILP over this reduced space, and then expanding the solution space to its neighborhood.
In order to apply the local search to the materialized view selection problem, we first generate promising initial candidate sub-queries (Section~\ref{sssec:initial}).
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~\ref{sssec:neighbor}).
This process is repeated until no further improvement can be achieved in the objective function (Section~\ref{sssec:iteration}).
%The following subsections describe the procedures for initial solution selection and neighbor generation in detail.
\subsubsection{Initial Solution Selection} \label{sec:initial}
\label{sssec:initial}
We propose three methods for selecting promising initial candidate sub-queries.
We use one of the following metrics for sub-query $s_j$:
1) frequency $F_j$ in the workload,
2) total utility $U_j$, or
3) utility per unit storage $E_j = U_j / b_j$.
Based on the selected metric, the top $k$ sub-queries are chosen as the initial candidate sub-queries, such that the total storage cost does not exceed the storage budget $B_{\text{max}}$.
\subparagraph{\textbf{topk-}$F$:}
This method simply prioritizes sub-queries that appear most frequently in the workload. By favoring commonly shared sub-queries, it may enhance the effectiveness of selecting initial views.
% However, it may yield a large candidate set, increasing the computational cost of solving the ILP.
\subparagraph{\textbf{topk-}$U$:}
This method selects sub-queries with the highest total utility, as defined in Equation~(\ref{U_j}). It is expected to be more effective than topk-$F$ 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.
\subparagraph{\textbf{topk-}$E$:}
This method balances utility and storage requirements by selecting sub-queries based on their utility per unit of storage.
% This approach is more storage-efficient than topk-$U$. However, it may overlook frequently occurring sub-queries with high utility but large storage requirements.
\subsubsection{Neighborhood-based sub-query exploration}
\label{sssec:neighbor}
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.
\begin{definition} [Neighbors of sub-query]
The neighbors of a sub-query $s$ consist of $s$ itself, its parent sub-queries, and its child sub-queries:
\begin{align}
\mathrm{neighbors}(s) = \mathrm{parent}(s) \cup \mathrm{children}(s) \end{align}
\noindent
where $\mathrm{parent}(s)$ denotes the set of sub-queries whose root nodes are parents of the root node of sub-query $s$,
while $\mathrm{children}(s)$ denotes the set of sub-queries whose root nodes are children of the root node of sub-query $s$.
\end{definition}
\noindent
In Figure~\ref{fig:subq_over_query}, parents and children of $s_3$ are indicated by solid-lined rectangles. The parents are sub-queries indicated by rectangles that cover red rectangles in $q_2$ and $q_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).
\subsubsection{Iterative Optimization}
\label{sssec:iteration}
Starting from the initial candidate sub-queries,
we explore the solution space by adding neighbor sub-queries and then choose new candidate sub-queries that maximize the objective function.
%We repeat the neighborhood exploration until no further improvement can be achieved.
Let $C_t$ denote the candidate sub-queries at iteration $t$ ($t > 0$), and let $E_t$ denote the extended sub-queries obtained by adding neighbor sub-queries to $C_t$, defined as follows:
\begin{align} E_{t} = \bigcup_{s \in C_t} s \cup \mathrm{neighbors}(s) \end{align}
\noindent
where $C_0$ is the initial solutions selected by the methods described in Section~\ref{sec:initial}.
Then, the new candidate sub-queries $C_{t+1}$ are computed by solving an ILP over the extended sub-queries $E_{t}$:
\begin{align} C_{t+1} = \mathrm{ILP}(\bmQ, E_{t}) \end{align}
\noindent
The function $\mathrm{ILP}(\bmQ, E_{t})$ selects the solutions to the view selection problem when the whole candidate sub-query set $\bmS$ is localized to a subset $E_{t}$, which corresponds to $L(\bmQ, E_{t})$ in Definition~\ref{def:loss}.
This localization is a key advantage of the local search approach, as it significantly reduces the number of decision variables ($y_{ij}$ and $z_j$) compared to the whole sub-query set $S$, that is $|E_{t}|<<|\bmS|$.
The iteration continues until no further improvement can be achieved in the objective function.
% Since each candidate set includes the previously selected solution, the ILP’s objective value is guaranteed to converge.
\subsection{Incremental View Maintenance Cost} \label{ssec:view_maintenane}
This section describes the details of the incremental view maintenance cost $m_j$ for a materialized view of subquery $s_j$ 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~\cite{incremental:RPAI,DBToaster,incremental:tempura,10.1145/170035.170066} have explored incremental view maintenance, and some of these techniques have even been integrated into commercial systems, such as Oracle, SQL Server, and Tempura~\cite{incremental:tempura}.
Therefore, we focus on computing the cost of incremental view maintenance rather than that of full refresh-based view maintenance.
\subsubsection{Notations}
The incremental view maintenance cost in response to updates on base tables is expressed using a cost function $\mathsf{cost_{IVM}}(\Delta)$, where the argument $\Delta$ denotes the delta view expression for given update operations on base tables.
%Given a view definition expressed in relational algebra, $\Delta$ can be obtained by replacing the base table in the view expression with its corresponding delta table that receives incremental update operations.
%For \textbf{single-table views}, $\Delta$ is expressed using a delta table, such as $\Delta A$ for Table~A.
%For \textbf{multi-table views},
$\Delta$ 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, $\Delta$ is expressed using a delta table, such as $\Delta A$ for Table~$A$, and the incremental view maintenance cost is denoted as $\mathsf{cost_{IVM}}(\Delta A)$.
If a view is defined as a multi-table view, for example, for a given view $A \bowtie B$ and an incremental update operation $\Delta B$ to the base table $B$, the incremental view maintenance cost is denoted as $\mathsf{cost_{IVM}}(A \bowtie \Delta B)$.
\subsubsection{Cost Estimation for Incremental Join View Maintenance}
In order to solve the view selection problem, we need to estimate the view maintenance cost ($m_j$) in Definition \ref{def:loss}.
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 describe the details of the incremental view maintenance cost by particularly focusing on join views, which are commonly selected as materialized views due to their high utility.
We begin by decomposing the incremental view maintenance into three constituent operations, and then describe how to estimate their costs.
\paragraph{Decomposing incremental view maintenance cost}
First, the incremental view maintenance cost is calculated as the sum of the costs of three operations\footnote{If the update operations on the base table are insertion, the maintenance cost consists only of 1) and 3), because we can skip 2).}:
1) computing changes to the view,
2) identifying records to update in the view, and
3) applying the changes to the view.
\begin{comment}
If the update operations on the base table are insertion, the maintenance cost consists only of 1) and 3), because we can skip 2).
If the update operations are modification/deletion, the maintenance cost consists of all three operations.
\end{comment}
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, $V = A \bowtie B$, and an incremental delete operation $-\Delta B$ on Table $B$.
We omit the cases for incremental insert and update operations, as they can be straightforwardly derived from the delete case.
Let $\Delta V = A \bowtie \Delta B$. The incremental view maintenance cost is decomposed into the costs of the three operations and is expressed as follows.
\begin{align}
\mathsf{cost_{IVM}}(A \bowtie \Delta B)
= \mathsf{cost}(A\bowtie \Delta B)
+ \mathsf{cost}(\sigma_{\Delta V}(V))
+ \mathsf{cost}(V -\Delta V)\label{eq:ivm_cost}
\end{align}
\paragraph{Estimating incremental view maintenance cost:}
Then, we estimate the cost of the above three operations as follows.
\smallskip
\noindent
{\bf{1) Computing changes to the view:}}
$\mathsf{cost}(A\bowtie \Delta B)$ is approximated as
the cost of querying $A \bowtie B$ times $\frac{\Delta B}{|B|}$ (where $|B|$ is the number of records in B),
because we only need to compute ratio $ \frac{\Delta B}{|B|}$ of $A \bowtie B$.
\begin{align}
\mathsf{cost}(A \bowtie \Delta B) \simeq \mathsf{cost_{read}}(A \bowtie B) \times \frac{\Delta B}{|B|}
\end{align}
\smallskip
\noindent
{\bf{2) Identifying records to update in the view:}}
The cost of identifying $\Delta V$ in the materialized view $V$ is approximated as
the cost of evaluating $\sigma_{\Delta B}(B)$ times
$\mathsf{fanout_{A \bowtie B}}(B\rightarrow V)$,
where $\mathsf{fanout}_{A \bowtie B}(B \rightarrow V)$ denotes the fanout (i.e., output cardinality) from $B$ to $V$ when $V$ is obtained by $A \bowtie B$.
% Since tables A and B are in a one-to-many relationship, the number of records in the materialized result of the natural join $MV(A\bowtie B)$ can be approximated by the number of records in table B\footnote{In the case of outer joins, the number of records in the materialized result may exceed that of table B, requiring a more detailed definition.}.
\begin{align}
\mathsf{cost}(\sigma_{\Delta V}(V)) \simeq \mathsf{cost_{read}}(\sigma_{\Delta B}(B)) \times \mathsf{fanout}_{A \bowtie B}(B \rightarrow V)
\end{align}
For example, if tables $A$ and $B$ are in a one-to-many relationship (i.e., one $A$ record corresponds to multiple $B$ records) and the view is defined using natural join, then $\mathsf{fanout}_{A \bowtie B}(B \rightarrow V) \simeq 1$.
Conversely, if they are in a many-to-one relationship, then $\mathsf{fanout}_{A \bowtie B}(B \rightarrow V)\simeq |A|/|B|$.
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~\cite{NeuroCard, FactorJoin} for more precise fanout estimation.
\smallskip
\noindent
{\bf{3) Applying the changes to the view:}}
The cost of applying the changes ($-\Delta V$) to $V$, $\mathsf{cost}(V -\Delta V)$, is approximated as
the cost of deleting $\Delta B$ from the base table $B$ times
$\mathsf{fanout}_{A \bowtie B}(B\rightarrow V)$.
% multiplied by the ratio of the record length of Table~$V$ to that of the materialized view $B$.
%This cost is based on the update cost of table B ($cost(B\oplus\Delta B)$) and the widths of tables A and B:
\begin{align}
\mathsf{cost}(V -\Delta V)\simeq \mathsf{cost_{write}}(-\Delta B) \times \mathsf{fanout_{A \bowtie B}}(B\rightarrow V)
\end{align}
\begin{comment}
\begin{align}
\mathsf{cost}(V -\Delta V)\simeq \alpha\cdot\mathsf{cost_{write}}(-\Delta B) \times \mathsf{fanout_{A \bowtie B}}(B\rightarrow V) \times \frac{\mathsf{len}(V)}{\mathsf{len}(B)}
\end{align}
\end{comment}
where $\mathsf{cost_{write}}_{B}(-\Delta B)$ denotes the cost of deleting $\Delta B$ from its base table. %, and $\mathsf{len}(\cdot)$ is a function that returns the record length of the input table.
%The parameter $\alpha$ is a trade-off coefficient\footnote{Since $\alpha$ varies depending on the execution environment (e.g., disk systems or SSDs), we calibrate it by empirically measuring the query processing and write performance under the target environment.} that adjusts the relative weight of $\mathsf{cost_{write}}$ with respect to $\mathsf{cost_{read}}$.
\subsection{Algorithm Description}
\label{ssec: algorithm}
We define $\bm{U}$ (sub-query utilities), $\bm{b}$ (sub-query footprints), $\bm{m}$ (sub-query maintenance costs), and $\bm{z}$ (sub-query for materialization) as vectors consisting of elements $U_j$, $b_j$, $m_j$, and $z_j$, respectively, for sub-query $s_j$ ($1 \leq j \leq |\bm{S}|$).
We also define $\mathbf{X} \in \{0,1\}^{|\bm{S}| \times |\bm{S}|}$ as a sub-query containment matrix consisting of elements $x_{jk}$, which indicates whether $s_j$ contains $s_k$, and
$\mathbf{Y} \in \{0,1\}^{|\bm{Q}| \times |\bm{S}|}$ as a matrix consisting of elements $y_{ij}$, which indicates whether query $q_i$ uses the materialized view of sub-query $s_j$.
The view selection algorithm is outlined in Algorithm~\ref{algo:proposed}.
\input{algorithm/algo_proposed.tex}
\begin{comment}
The algorithm takes as input the sub-query utilities ($\bm{U}$), storage footprints ($\bm{b}$), incremental view maintenance costs ($\bm{m}$), interactions between sub-queries ($X$), and maximum storage capacity ($B_{\text{max}})$.
It outputs the set of sub-queries to be materialized ($Z$) and the set of sub-queries to be used when evaluating each query ($Y$). %Here, we present the basic version of the algorithm.
\end{comment}
Initially, an initial candidate sub-queries (expressed using $\bm{z}$) is generated using one of the metrics topk-$F$, topk-$U$, or topk-$E$, subject to the storage budget (Line 1-2) as detailed in Section~\ref{sec:initial}.
Then, we conduct a local search and ILP until no further improvement can be achieved (Lines 3-14).
Line 5 corresponds to neighbor-based sub-query exploration (detailed in Section~\ref{sssec:neighbor}),
and Line 6 involves applying Integer Linear Programming (ILP) optimization for the current candidate sub-queries ($E$).
%The ILP optimization problem receives the candidate set, storage constraint, inclusion conditions, and incremental view maintenance costs as input, and
ILP updates the decision variables ($\bm{z}, Y$) that maximize the objective function in Definition~\ref{def:loss}.
Lines 9-11 determine termination based on convergence criteria, and Line 15 outputs the final decision variables ($\bm{z}$, $Y$).
%We note that sub-query $s_j$ cannot be used to evaluate a query $q_i$ (i.e. $y_{ij} = 0$) when it has no utility for $q_i$ (i.e. $u_{ij} = 0$) or when it is not materialized (i.e. $z = 0$).
%\begin{comment}
\section{View Materialization and Query Rewriting}
\label{sec:mv_create_query_rewrite}
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 ($\bm{z}, Y$) where $y_{ij}$ indicates whether query $q_i$ uses the materialized view $s_j$, and $z_j$ indicates whether $s_j$ is selected for materialization.
\smallskip
\noindent
{\bf 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 $s_j$ and construct a \texttt{CREATE MATERIALIZED VIEW} statement.
\begin{itemize}
\item \textbf{CREATE MATERIALIZED VIEW as [view_name]}
\item \textbf{SELECT clause}: All columns from the referenced tables are included. The duplicate column names between different tables are resolved via renaming.
\item \textbf{FROM clause}: All tables from the referenced tables.
\item \textbf{WHERE clause}: All conditions from the referenced tables.
\end{itemize}
When materialized views are defined, they are subsequently materialized within the database.
\smallskip
\noindent
{\bf Query rewrite using views}.
Each query is rewritten as follows based on $y_{ij}$: query $q_i$ uses the materialized view $s_j$:
\begin{itemize}
\item \textbf{SELECT clause}: Replace the columns of the base tables in $q_i$ with the corresponding columns in $s_j$.
\item \textbf{FROM clause}: Replace the corresponding base tables in $q_i$ with $s_j$.
\item \textbf{WHERE clause}: Remove the join and filter conditions in $q_i$ if they are included in $s_j$.
\end{itemize}
%Finally, the rewritten queries are executed in the database.
%\end{comment}
\begin{algorithm}[t!]
\caption{View selection optimization}
\label{algo:proposed}
\begin{flushleft}
\textbf{Input:} $\bm{Q}$ (queries), $\bm{S}$ (sub-queries), $\bm{U}$, $\bm{b}$, $\bm{m}$, $X$, and $B_{\text{max}}$ (storage budget)\\
\textbf{Output:} $\bm{z}$, $Y$
\end{flushleft}
\begin{algorithmic}[1]
\STATE $\bm{z} = \textbf{initial\_solution}_{B_{\text{max}}}(\bm{S}, \bm{U},\bm{b})$ \hfill \textcolor{black}{// initial solution selection}
% \STATE $C = \textbf{candidate\_views}(\bm{z})$
%\STATE $iter\_flag \gets 0$
%\STATE $y_{ij} \gets 0, \; \forall i \in [1,|\bmQ|], \forall j \in [1,|\bmS|]$ \hfill \textcolor{black}{// initialization}
% \STATE $U_{\text{max}} = \sum_{q_i\in \bm{Q}} \sum_{s_j\in S} u_{ij}$, $U_{previous} = 0, M_i=M'_i=\phi$
\STATE $U_{previous} = 0 $
\WHILE{$true$}
% \STATE $M_i=\phi$
\STATE $C = \textbf{candidate\_views}(\bm{z})$ \hfill \textcolor{black}{// $\bm{z}$ to $C$ transformation}
\STATE $E = \bigcup_{s \in C} s\cup \textbf{neighbors}(s)$ \hfill \textcolor{black}{// sub-query exploration}
% \STATE $U_{current} \gets 0$
% \FOR{$q_i\in \bm{Q}$}
% \STATE $M_i = M_i \cup \{ j |j \in [1,|\bm{S}|], u_{ij} > 0 \}$
% \STATE $M'_i = M'_i \cup \{ j |j \in [1,|\bm{S}|], z_j > 0 \}$
% \ENDFOR
% \STATE $M = M_i \cap M'_i$
\STATE $\bm{z}, Y = \textbf{ILP}_{ B_{max}, X,\bm{m}, \bm{U}}(\bm{Q}, E)$ \hfill \textcolor{black}{// applying ILP}
\STATE $U_{current} = \sum_{q_i\in \bm{Q}} \sum_{s_j\in \bm{S}} u_{ij} \cdot y_{ij} - \sum_{s_j\in \bm{S}} m_j \cdot z_j$
\STATE \hfill\textcolor{black}{// iteration termination check}
\IF{$U_{previous} >= U_{current}$}
% \STATE $iter\_flag \gets 1$
\STATE \textbf{break}
\ENDIF
\STATE \hfill\textcolor{black}{// preparing for the next iteration}
% \STATE $C = \textbf{candidate\_views}(\bm{z})$
\STATE $U_{previous} = U_{current}$
% \STATE $B_{current} \gets 0$
% \FOR{$z_j \in \bm{z}$}
% \IF{$z_j == 1$}
% \STATE $B_{current} \; += b_j \cdot z_j$
% \ENDIF
% \ENDFOR
\ENDWHILE
\STATE \textbf{return} $\bm{z}$, $Y$
\end{algorithmic}
\end{algorithm}
\section{Experiments}
\label{sec:exp}
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 \cite{bigsubs}.
%and 2) to measure workload performance when materialized views are utilized.
In detail, we conducted four experiments based on the following objectives:
\begin{description}
\item [\textbf{Workload performance improvement}:] We evaluate the workload performance of the proposed method compared to the baselines.
\item [\textbf{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.
\item [\textbf{Ablation study: Effectiveness of initial solution selections}:]
\kaina{We compare the workload performance of the three initial solution selections: topk-$F$, topk-$U$, and topk-$E$}
\item [\textbf{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.
\item [\textbf{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.
\end{description}
\input{table/table_ex_environment_localpc}
\subsection{Setup}
%\input{table/table_benchmark}
%We describe benchmark and environment on our experimental study.
\subsubsection{Benchmark}
\label{benchmark}
We conduct the experiments using the Redbench\footnote{https://github.com/utndatasystems/redbench}, which is a recently published benchmark extended from the Join Ordering Benchmark (JOB)~\cite{job} % and the Cardinality Estimation Benchmark (CEB)~\cite{ceb}
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 Redset\footnote{https://github.com/amazon-science/redset}, a dataset of query metadata published by Amazon Redshift, so Redbench is suitable for workload evaluations.
%Table~\ref{table:benchmark} summarizes the statistics of the Redbench.
Redbench contains 1073 JOB-based queries. % and 6,245 CEB-based queries.
Among these, there are 83 unique JOB queries\footnote{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. }, % and 918 unique CEB queries,
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.
%We randomly choose a base table in IMDb database for generating the insert operations.
% We utilize the subset of the Join Order Benchmark (JOB) included in the Redbench workload.
% 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.
%This workload results in a large number of decision variables for the integer programming solver. Since the computational cost of solving an integer program increases with the number of variables, this setup enables us to evaluate our method under realistic and demanding conditions.
% We use the Join Order Benchmark (JOB) \cite{job}, a synthetic workload inspired by the Internet Movie Database (IMDB). This benchmark consists of 113 queries and serves as an experimental workload. The underlying data model includes 21 tables, and the number of joins per query ranges from 3 to 16, with an average of 8 joins. Consequently, the number of decision variables processed by the integer programming solver is large. Since solving an integer programming problem becomes more costly as the number of variables increases, we selected this workload to test our method under realistic, challenging conditions.
\input{table/table_ex_11_include_12}
% \input{table/table_ex_12}
\subsubsection{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~(\ref{U_j}).
%\kaina{Table~\ref{table:ex_11} presents a comprehensive performance comparison of each method. It details the total workload rerun time (defined as the sum of view selection optimization, materialized view generation, and workload execution times). To provide a comprehensive total time comparison for different query types, we calculated an amortized total time for each query repetition bucket as our primary performance metric. This metric is the sum of the execution time for the queries within the bucket and a pro-rated portion of the total overhead from view selection and generation. The overhead was amortized across each bucket in proportion to its contribution to the total workload execution time.}
We also report the total time for each 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 ($cost_{read}(\cdot)$) in the view utility and the update operation cost ($cost_{write}(\cdot)$).
\subsubsection{Environment}
Table~\ref{table_environment} describes the environment we used in the experiments.
%The workload's predicted execution cost is the sum of the estimated execution costs of all queries in the workload, obtained using the EXPLAIN clause in PostgreSQL.
%Note that this cost is a gain-based metric and does not correspond to any specific physical unit.
%Since BIGSUBS relies on probabilistic techniques for view selection, the solutions obtained at the final iteration may not be the best ones. Therefore, we report the results using the best-performing solutions obtained during the iterations.
In operating PostgreSQL, we have adopted the default configuration settings.
%\kaina{We employ the \texttt{EXPLAIN} command to compute the utility of sub-queries, which serves as one of our performance metrics.}
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.
\subsection{Experimental Results}
\subsubsection{Workload Performance Improvement}
\label{exp:workload_improve}
\kaina{
This experiment demonstrates that our proposed method (topk-$F$) 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-$F$ initial solution selection as the representative configuration for our proposed method.}
%A detailed analysis comparing topk-$F$ with other selections (topk-$U$ and topk-$E$) is provided in Section \ref{ex_prop-comparison}.
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.
%Specifically, we measure
%1) the optimization time for view selection,
%2) the time taken to materialize the selected views,
%3) the workload execution time using the selected materialized views, and 4) the total view utility of the selected materialized views.
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.
\kaina{First, Table~\ref{table:ex_11} shows the total time results for each query repetition bucket.
%presents an total time comparison of each method.
%To provide a comprehensive total time comparison for different query types, we calculated an amortized total time for each query repetition bucket. This metric consists of the execution time for the queries within that bucket, augmented by a pro-rated portion of the total overhead from view selection and generation. The overhead was allocated to each bucket in proportion to its contribution to the total workload execution time.
The results indicate that our proposed method is not only fast overall, but also consistently effective across diverse query repetition buckets. In the lower-repetition buckets (0\%–50\%), Proposed (topk-$F$) 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 high-quality solutions without exhaustive exploration.
}
\kaina{Second, Table~\ref{table:ex_12_improved} 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).}
% \input{table/table_ex_11_include_12}
\input{table/table_ex_12}
% \kaina{Additionally, we investigated the relationship between the estimated MV utility and the actual improvement in workload execution time, finding a strong Pearson correlation coefficient of 0.920. This confirms that the utility model integrated into our objective function serves as a reliable predictor of real-world performance gains. In summary, these results validate that our proposed method provides the most effective and practical solution for workload acceleration, delivering near-optimal query performance with minimal optimization overhead.}
% The proposed methods, particularly topk-$F$ and topk-$U$, demonstrate a good balance between the effectiveness and efficiency of the view selection. They achieve near-optimal view utility with substantially lower overhead than ILP, making them promising candidates for practical use cases.
% In contrast, while the naive ILP can identify the materialized views with the highest utility, its significant optimization overhead makes it less practical for minimizing the workload rerun time. BIGSUBS, despite its efficient MV selection, suffers from lower view utility, which compromises its overall effectiveness in accelerating the workload.
% We also investigate the Pearson correlation coefficient between the actual improvement in workload execution time using materialized views and the estimated total utility of the selected views. We find a strong correlation coefficient of 0.920.
% This confirms that the estimated utility of the selected materialized views effectively contributes to actual improvements in workload execution time.
%Overall, the experimental results indicate a general positive correlation between the total utility of the selected materialized views and the workload rerun time (WR Time). For example, the Naive ILP method achieved a utility(K) of 3374 with a rerun time of 440.68 seconds, which is faster than BIGSUBS, which achieved a lower utility(K) of 1855 and a longer rerun time of 508.18 seconds.
% Overall, a general positive correlation can be observed between the total utility of selected materialized views and the reduction in workload execution time: higher utility tends to correspond to faster execution. However, this relationship is not strictly monotonic. Even if individual views exhibit high utility, their combined effect may fail to provide optimal coverage or synergy across the entire workload, thereby highlighting the importance of holistic view set evaluation.
% original
% From Table~\ref{table:ex_12}, we observe that the proposed method (topk-F) achieves the fastest workload execution when including the time for selecting and creating materialized views, assuming that the Redbench workload is run. In perspective of utility of selected matrialized views, Naive ILP gained the highest utility and proposed three method leads to that, while bigsubs has the lowest utility.BIGSUBS uses probabilities to select candidate subqueries for materialization. However, in the JOB workload, this probabilistic selection is biased, which may result in fewer iterations and shorter runtime, but also leads to lower solution accuracy.
\subsubsection{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.
% by applying the integer programming solver only to the initial solution with that obtained by iteratively applying local search and integer programming.
The number of insert queries was set to 1000.
Table~\ref{table:ex3_50M} shows that local search provides a substantial benefit in increasing total utility for topk-$F$ and topk-$E$, with only a marginal additional time.
As an example of topk-$F$, 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~\ref{table:ex_11}).
%Moreover, the improvement in total gain over the corresponding top-$k$ variants without local search demonstrates the effectiveness of incorporating local search into the proposed method.
Note, in the case of topk-$U$, 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.
% optimal solution was already included among the candidates selected during the initial solution phase.
\input{table/table_ex_3_50M}
\subsubsection{Ablation study: Effectiveness of initial solution selections}
\label{ex_prop-comparison}
\input{table/table_ex_3_WR}
\input{table/table_ex_3_WR-overhead}
\kaina{
Having established the overall superiority of our proposed method, this experiment delves into a comparative analysis of the three distinct initial solution selections: topk-$F$, topk-$U$, and topk-$E$. The objective is to understand the trade-offs inherent in each heuristic and to justify the selection of topk-$F$ as our representative method. The experimental setup remains consistent with that in Section~\ref{exp:workload_improve}.
First, Table~\ref{table:ex_32} shows the total time results for each query repetition
bucket.
This reveals the distinct strengths of each selection. topk-$F$ 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-$U$ demonstrates its strength in the mid-range repetition buckets (30\%-80\%), where it often outperforms topk-$F$. This suggests that topk-$U$ 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~\ref{table:ex_12_improved} provides the breakdown of the total time.
The results indicate that topk-$F$ achieves the best total time, with a total time of 370.98 seconds. Although topk-$U$ identifies a set of views with the highest estimated utility (3344 K), this does not necessarily lead to faster workload execution. Instead, topk-$F$ 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-$F$ (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-$E$ 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-$F$ 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.
}
\subsubsection{Parameter sensitivity: Impact of the number of insert operations}
\kaina{
%The results from this experiment affirm that our proposed method is robust to changes in the update workload, consistently selecting a set of views with near-optimal utility even as maintenance costs increase.
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.
\begin{figure}[t!]
\centering
\includegraphics[width=\hsize]{img/insert_query_utility.png}
\caption{Total view utility with respect to the number of insert operations. }
\label{fig:ex_21}
\end{figure}
\kaina{Figure~\ref{fig:ex_21} illustrates the total utility using the selected views as a function of the number of insert operations for our proposed method (topk-$F$), Naive ILP, and BIGSUBS. The utility of both topk-$F$ 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-$F$ 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~\cite{bigsubs}.
}
\subsubsection{Parameter sensitivity: Impact of storage budget}
%\kaina{The results demonstrate that our proposed method (topk-$F$) consistently achieves near-optimal total utility while maintaining a significantly shorter optimization time compared with the Naive ILP baseline, effectively balancing solution quality and computational efficiency across different storage budgets.}
This experiment analyzes how the storage budget size ($B_{\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 $B_{max}$ was changed across 5 MB, 50 MB, and 500 MB.
\begin{figure}[h]
\centering
\begin{subfigure}[b]{0.48\textwidth}
\centering
\includegraphics[width=\linewidth]{img/utility_comparison.png}
\caption{View utility}
\label{fig:ex_22_u_sub}
\end{subfigure}
\hfill
\begin{subfigure}[b]{0.48\textwidth}
\centering
\includegraphics[width=\linewidth]{img/optimization_time_comparison_semilog.png}
\caption{Optimization time}
\label{fig:ex_22_t_sub}
\end{subfigure}
\caption{Comparison of total view utility and optimization time with respect to $B_{max}$ (5 MB, 50 MB, and 500 MB).}
\label{fig:ex_22_combined}
\end{figure}
\kaina{Figure~\ref{fig:ex_22_u_sub} illustrates the total utility of the materialized views selected by each method. Our proposed method topk-$F$ consistently identifies the views with a utility nearly comparable to that of the 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 $B_{max}$, as a greater budget allows for the materialization of more effective views.
Regarding optimization overhead, Figure~\ref{fig:ex_22_t_sub} 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.}
% They achieve comparable total utility to the naive ILP and high efficiency for the optimization time.
% Regarding the total utility, Figure~\ref{fig:ex_22_u_sub} shows the proposed methods consistently yield the higher total utility across all values of $B_{max}$ compared to BIGSUBS.
% This observation verifies the lower accuracy inherent in the BIGSUBS approach.
% In addition, we can observe a natural phenomenon that the total utility increases as the storage budget size increases and the total utility tends to converge. That is, the selected views are getting close to materializing all queries in the workload.
% Regarding the optimization time, Figure~\ref{fig:ex_22_t_sub} shows that both BIGSUBS and the proposed methods significantly outperform the naive ILP, achieving substantial reductions in optimization time. Among the proposed methods, topk-$F$ and topk-$E$ exhibit relatively longer optimization times. We conjecture that their initial solutions are largely different from the optimal ones, which require more iterations.
% In contrast, topk-$U$ is presumed to select initial solutions closer to the optimal ones, thereby reducing the number of iterations and overall optimization time.
% Also, BIGSUBS is efficient in optimization time, but the total utility is worse than the proposed methods.
\subsubsection{Validation of the Utility Metric}
\kaina{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.
% \begin{figure}[t!]
% \centering
% \includegraphics[width=\hsize]{img/mv_before_correlation.png}
% \caption{Correlation between estimated materialized view utility and workload execution time.}
% \label{fig:ex_before_correl}
% \end{figure}
}
\begin{table}[t!]
\centering
\caption{Experimental environment}
\label{table_environment}
\begin{tabular}{|l|l|} \hline
Machine & MacBook Pro (16-inch, 2024) \\ \hline
OS & macOS Sequoia 15.4.1(24E263)\\ \hline
CPU & \makecell[l]{Apple M4 Pro \\ (14-core CPU, 20-core GPU)}\\ \hline
Memory & 48GB \\ \hline
ILP solver & Gurobi 12.0 \\ \hline
Database system & PostgreSQL 17.4 \\ \hline
Database & IMDb \\ \hline
Database size & 7085MB \\ \hline
% workload & Redbench \\ \hline
% \makecell{workload's predicted \\ execution cost} & 4.47M \\ \hline
\end{tabular}
\end{table}
\begin{table}[t!]
\centering
\small
\caption{
Amortized total time (sec) between proposed method and baselines, including overhead across methods for different query repetition buckets indicating repetition ratio. The storage budget ($B_{\text{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.
}
\label{table:ex_11}
\resizebox{\columnwidth}{!}{%
\begin{tabular}{@{}l r r r r@{}}
\toprule
\begin{tabular}{@{}c@{}}\textbf{Query repetition} \\ \textbf{buckets}\end{tabular} &
\multicolumn{1}{c}{\textbf{w/o MV}} &
\multicolumn{1}{c}{\textbf{Naive ILP}} &
\multicolumn{1}{c}{\textbf{BIGSUBS}} &
\multicolumn{1}{c}{\textbf{Proposed (topk-$F$)}} \\
\cmidrule(lr){2-2} \cmidrule(lr){3-3} \cmidrule(lr){4-4} \cmidrule(lr){5-5}
\hspace{0.5em}0\%--10\% & 55.75 & 25.55 & \underline{20.67} & \textbf{14.96} \\
10\%--20\% & 21.43 & \underline{3.85} & 7.55 & \textbf{2.00} \\
20\%--30\% & 89.60 & \underline{41.02} & 51.64 & \textbf{22.47} \\
30\%--40\% & 71.19 & 18.56 & \underline{17.18} & \textbf{12.78} \\
40\%--50\% & 144.91 & \underline{57.29} & 75.68 & \textbf{51.17} \\
50\%--60\% & 4.32 & 1.45 & \underline{0.60} & \textbf{0.60} \\
60\%--70\% & 85.12 & \underline{49.79} & \textbf{47.36} & 66.17 \\
70\%--80\% & 107.16 & \underline{32.39} & 53.83 & \textbf{30.99} \\
80\%--90\% & \textbf{70.19} & 94.83 & 90.21 & \underline{77.22} \\
90\%--100\% & 226.08 & \underline{115.94} & 143.46 & \textbf{92.62} \\
\midrule
\textbf{Total Time (s)} & 875.75 & \underline{440.68} & 508.18 & \textbf{370.98} \\
\bottomrule
\end{tabular}
}
\end{table}
\begin{table}[t!]
\centering
\small
\caption{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..}
\label{table:ex_12_improved}
\begin{tabular}{@{}l r r r r r@{}}
\toprule
\textbf{Method}
& \thead{Total Time \\ (s)}
& \thead{Opt. \\ Time (s)}
& \thead{MV Gen. \\ Time (s)}
& \thead{Workload \\ Exec. time (s)}
& \thead{MV \\ Utility (K)} \\
\midrule
w/o MV & 875.75 & 0 & 0 & 875.75 & 0 \\
Naive ILP & 440.68 & 79.75 & 194 & 166.93 & 3374 \\
BIGSUBS & 508.18 & 0.73 & 76 & 431.45 & 1855 \\
Prop. (topk-$F$) & \textbf{370.98} & 1.29 & 179 & 190.71 & 3272 \\
\bottomrule
\end{tabular}
\end{table}
\begin{table}[tb!]
\centering
\caption{
Comparison of total view utility and optimization time with and without local search ($B_{\text{max}}$: 50MB).
$\mathit{diff}$ indicates the difference between the proposed method with and without local search (proposed - w/o local search).
}
\label{table:ex3_50M}
\begin{tabular}{lrr}
\toprule
\textbf{Method} & \textbf{Utility (K)} & \textbf{Optimization} \\
& & \textbf{Time (sec)} \\
\midrule
proposed (topk-$F$) & 3270 & 1.29 \\
w/o local search (topk-$F$) & 1570 & 0.11 \\
$\mathit{diff}$ & +1700 & +1.18 \\
\hline
proposed (topk-$U$) & 3340 & 0.76 \\
w/o local search (topk-$U$) & 3340 & 0.28 \\
$\mathit{diff}$ & +0 & +0.48 \\
\hline
proposed (topk-$E$) & 2710 & 0.75 \\
w/o local search (topk-$E$) & 2280 & 0.02 \\
$\mathit{diff}$ & +430 & +0.73 \\
\bottomrule
\end{tabular}
\end{table}
\begin{table}[t!]
\centering
\small
\caption{
Amortized total time (sec) for proposed methods, including overhead across all query repetition buckets indicating repetition ratio. The storage budget ($B_{\text{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.
}
\label{table:ex_32}
\begin{tabular}{@{}l r r r@{}}
\toprule
\multirow{2}{*}{\begin{tabular}{@{}c@{}}\textbf{Query repetition} \\ \textbf{buckets}\end{tabular}} & \multicolumn{3}{c}{\textbf{Proposed}} \\
\cmidrule(lr){2-4}
& \multicolumn{1}{c}{\textbf{topk-$F$}} & \multicolumn{1}{c}{\textbf{topk-$U$}} & \multicolumn{1}{c}{\textbf{topk-$E$}} \\
\midrule
\hspace{0.5em}0\%--10\% & \textbf{14.96} & \underline{16.79} & 26.78 \\
10\%--20\% & \textbf{2.00} & \underline{9.59} & 14.66 \\
20\%--30\% & \textbf{22.47} & 64.46 & \underline{63.42} \\
30\%--40\% & \underline{12.78} & \textbf{7.29} & 21.88 \\
40\%--50\% & \underline{51.17} & \textbf{28.06} & 69.98 \\
50\%--60\% & \underline{0.60} & \textbf{0.39} & 4.93 \\
60\%--70\% & 66.17 & \textbf{38.40} & \underline{41.76} \\
70\%--80\% & \underline{30.99} & \textbf{20.43} & 68.87 \\
80\%--90\% & 77.22 & \textbf{41.54} & \underline{59.09} \\
90\%--100\% & \textbf{92.62} & 261.94 & \underline{230.21} \\
\midrule
\textbf{TOTAL} & \textbf{370.98} & \underline{488.90} & 601.89 \\
\bottomrule
\end{tabular}
\end{table}
\begin{table}[t!]
\centering
\small
\caption{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.}
\label{table:ex_3}
\begin{tabular}{@{}l r r r r r@{}}
\toprule
\textbf{Method}
& \thead{Total Time \\ (s)}
& \thead{Opt. \\ Time (s)}
& \thead{MV Gen. \\ Time (s)}
& \thead{Workload \\ Exec. time (s)}
& \thead{MV \\ Utility (K)} \\
\midrule
Prop. (topk-$F$) & \textbf{370.98} & 1.29 & 179 & 190.71 & 3272 \\
Prop. (topk-$U$) & 488.90 & 0.76 & 110 & 497.59 & 3344 \\
Prop. (topk-$E$) & 601.59 & 0.75 & 113 & 387.89 & 2707 \\
\bottomrule
\end{tabular}
\end{table}
\section{Related Work}
\label{sec:rela}
The selection of materialized views has been extensively studied, with comprehensive surveys~\cite{rela:MV_survey, halevy2001answering} providing overviews of a variety of strategies and their trade-offs, including view selection, query rewriting, and answering queries using views.
A recent survey~\cite{rela:ML_survey} 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 heuristic-based approaches~\cite{rela:MV_cube, harinarayan1996implementing, gupta1997andor, theodoratos1997configuration, gupta2005view, rela:MV_heuristic} or optimization-based approaches that formulate the problem using integer linear programming (ILP)~\cite{papadomanolakis2007ilp, camacho2016pigreuse, bigsubs, wide-deep}.
\subsection{Heuristic-based approaches}
%View selection is closely related to the problem of subexpression selection, which involves selecting a set of views to materialize for a given query workload to minimize an objective function under constraints such as space or maintenance budgets~\cite{rela:MV_survey}.
Classical techniques for view selection in data warehouses~\cite{gupta1997andor, gupta2005view, theodoratos1997configuration} 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.
\subsection{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~\cite{papadomanolakis2007ilp} proposed one of the first ILP-based frameworks for database physical design, originally targeting index selection but extensible to materialized view selection. PigReuse~\cite{camacho2016pigreuse} applies ILP-based selection over AND/OR DAGs for Pig Latin scripts, enabling the reuse of common subplans. A prominent ILP-based system, BIGSUBS~\cite{bigsubs}, 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~\cite{wide-deep} 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.
\subsection{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.~\cite{roy2000efficient} extend the Volcano optimizer with AND/OR DAGs to identify and reuse common subplans across queries, achieving significant cost savings.
Koch and Schlegel~\cite{koch2004algebraic} 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.~\cite{anneser2023autosteer} 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.
%These approaches demonstrate the evolution of MQO from rule-based methods to algebraic and learning-based strategies, improving efficiency for large-scale SQL workloads.
\begin{comment}
\noindent
{\bf 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. Early MQO methods used AND/OR DAGs and Volcano-style optimizers~\cite{roy2000efficient}, without considering storage or maintenance constraints. Later approximation-based methods, such as those based on genetic algorithms~\cite{bayir2007genetic} and provable heuristics~\cite{kathuria2017efficient}, offer better scalability but typically lack parallelizability and assume transient materialization. These differences make MQO techniques less suitable for persistent view selection under long-term workloads.
%\noindent In summary, classical heuristic-based approaches offer efficiency and simplicity but often suffer from limited scalability and suboptimality. ILP-based methods, such as BIGSUBS and Wide-deep, offer a principled and extensible framework for scalable materialized view selection in large-scale OLAP workloads. Meanwhile, MQO techniques remain complementary for short-term workload sharing but are limited in persistent optimization settings.
%%%%%%%%%%%%
A wide range of research has been conducted on the selection of materialized views to accelerate query processing in database systems. These studies span heuristic-based approaches~\cite{rela:MV_cube, rela:MV_heuristic} as well as optimization-based methods that, like our proposed method, formulate the problem using integer linear programming (ILP)~\cite{bigsubs, wide-deep}.
BIGSUBS~\cite{bigsubs} reformulates the view selection problem as a labeling task on a bipartite graph, enabling the decomposition of a large ILP with numerous variables into multiple smaller ILPs. The algorithm alternates between two main steps. In the first step, it employs a probabilistic labeling strategy to expand the candidate space of materialized subqueries. Each subquery is assigned a label of 1 (to be materialized) or 0 (not materialized). The probability of labeling a subquery as 1 increases under two conditions: (1) when the total storage cost of the selected subqueries is small, and (2) when materializing the subquery results in a substantial reduction in query processing cost. In the second step, for each query in the workload, the method solves an ILP to determine which of the subqueries labeled with 1 should be used.
By iteratively alternating between probabilistic labeling and ILP-based selection until no further improvement in cost reduction is observed, BIGSUBS identifies a final set of materialized views. This strategy effectively reduces the number of variables in the ILP, leading to significantly shorter solving times. Consequently, the method is particularly well-suited for large-scale workloads, and the original study demonstrates its applicability to workload sizes that surpass those addressed in prior research.
Wide-deep~\cite{wide-deep} builds upon BIGSUBS and introduces enhancements to address two core challenges in materialized view selection: (1) estimating the benefit of using a materialized view for a given query, and (2) selecting the optimal set of subqueries to materialize. To address the first challenge, the authors propose a neural network-based model that estimates the benefit of a materialized view for a query. This model extracts relevant features from the query—such as operators and variables—and encodes them into a latent representation through a carefully designed neural architecture.
To address the second challenge, Wide-deep employs a combination of ILP and probabilistic labeling similar to BIGSUBS. However, because this approach does not guarantee convergence, the authors incorporate a deep reinforcement learning framework based on a Markov Decision Process (MDP). Within this framework, an agent maintains the current set of materialized views and selects which binary variable $z_j$ to flip (i.e., switch between 0 and 1) among the candidate subqueries. After updating $z_j$, the corresponding $y_{ij}$ variables are determined by solving an ILP. The benefit derived from this new configuration is used as a reward signal to iteratively train the agent, thereby guiding the search toward higher-quality solutions over time.
\end{comment}
\section{Conclusion} \label{sec:conc}
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.
% The proposed method reduces the candidate solutions by considering the gain, storage size, and frequency of subqueries within the workload, and repeatedly solves an integer programming problem through local search.
%This approach achieved higher gains compared to the state-of-the-art method BIGSUBS.
We confirmed the practical effectiveness of our view selection method using a recent benchmark, RedBench.
Future work includes the following directions: \begin{enumerate} \item Evaluating the effectiveness of materialized views on more generalized workloads \item Utilizing PostgreSQL’s incremental view maintenance functionality\footnote{https://wiki.postgresql.org/wiki/Incremental_View_Maintenance} \item Applying the wvlet framework for extracting logical plans, generating view definitions, and rewriting queries\footnote{https://github.com/wvlet/wvlet} \end{enumerate}
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.
\section{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:
\begin{itemize}
\item 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.
\item 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.
\item 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.
\end{itemize}
The authors take full responsibility for the final content and integrity of this research.