Automatic generation system, automatic generation method, and computer program for automatically generating candidate materialized views

Abstract

To provide a set of candidate MVs for a query workload, and consider balancing the number of rewritten queries and pre-computation operations for each candidate to realize speed-up in workload execution time.SOLUTION: This automatic generation system generates candidate materialized views for a set of queries in a workload. The system acquires a workload of one or more queries input by a user, divides the acquired one or more queries into multiple single select-from-where query blocks, selects pairs of query blocks with higher coupling operation similarity from the query blocks, generates candidate materialized views, and iteratively attempts to add new query blocks to existing candidate materialized views already generated.SELECTED DRAWING: Figure 1A

Description

[Technical Field]

[0001] The present invention relates to the field of database management, and more particularly to an automatic generation system, an automatic generation method, and a computer program for automatically generating materialized views (hereinafter also referred to as MVs). [Background technology]

[0002] In database management systems, materialized views are used to improve database performance by caching the results of repeatedly executed queries (referred to herein as workload queries or simply workloads), whereby some or all of the query operations of the workload queries are pre-computed.

[0003] Defining the set of MVs for a particular workload requires specialized knowledge of the database system and careful consideration of the workload queries and system constraints, e.g., storage constraints.

[0004] In particular, identifying the optimal set of materialized views requires careful consideration of the balance between the number of workload queries rewritten by each materialized view in the set and the number of operations precomputed in each materialized view.

[0005] Techniques for automatically generating a candidate MV set have been disclosed. For example, Patent Document 1 below describes a method for extracting a combined set from a workload query and creating candidate MVs from the combined set by sequentially applying set operations such as equality, superset, and subset.

[0006] However, such methods sequentially extend existing candidate MVs by applying set operations, thereby increasing the number of queries rewritten with each candidate MV, and ignoring the balance between precomputation operations and rewritten queries.

[0007] The following Patent Document 2 describes a method for extracting candidate MVs from a workload query plan to create a candidate MV set, and considers bottom-up and top-down approaches to balance the number of queries rewritten by each candidate MV and the number of precomputation operations.

[0008] However, extracting candidate MVs from a query plan is limited to candidate MVs that follow the join order suggested by the query optimizer, and candidate MVs with different join orders are not considered.

[0009] Patent Document 3 identifies the most suitable candidates by removing syntax from queries that are not suitable for rewriting, uses frequently used multi-joins to generate candidates for rewriting multiple workload queries, and then groups the candidates to create a candidate MV set for the workload.

[0010] The methods described in Patent Documents 1 to 3 generate each candidate MV independently of other candidate MVs, so the same query may be included in multiple candidate MVs. However, at runtime, only one (or a combination of several) MVs will be used to speed up the query. Including a query in a candidate MV that is not used for that query results in inefficient use of storage space due to high storage requirements, and reduces the gain for other queries in the candidate MVs.

[0011] Another aspect to consider in the automatic generation of candidate MVs for a workload is the explosion of the search space as the workload size increases. Patent Documents 1 to 3 consider a fixed search space of candidate MVs to limit the computation time of the candidate MVs. However, such methods do not consider that the requirements of the search space may differ based on the use case.

[0012] Furthermore, another aspect to consider is that there may be system or user-specified constraints that limit the number of candidate MVs that can be generated.

[0013] Patent Document 1 takes into account possible storage constraints and provides a method for generating a set of candidate MVs taking into account specific storage constraints.

[0014] However, while that method orders candidate MVs based on their estimated gain for a specified workload, it does not consider that the estimated gain of each candidate MV should be iteratively modified each time it is materialized to remove estimated gains associated with queries rewritten by the candidate MVs already selected to be materialized. [Prior art documents] [Patent documents]

[0015] [Patent Document 1] U.S. Patent Application Publication No. 2021 / 26847 [Patent Document 2] Japanese Patent Publication No. 2022-29863 [Patent Document 3] US Patent Application Publication No. 2011 / 196857 Summary of the Invention [Problem to be solved by the invention]

[0016] One objective of the present invention is to provide a set of candidate MVs for a query workload and speed up the execution time of the workload by considering a balance between the number of rewritten queries and precomputation operations on each candidate MV.

[0017] Furthermore, conventional approaches do not consider that the independent generation of candidate MVs may lead to inefficient subsumption of the same query in multiple candidate MVs that are not used at runtime, resulting in increased storage space utilized by such candidate MVs and reduced benefits of other rewritten queries due to increased MV scanning costs at runtime.

[0018] Therefore, another object of the present invention is to provide a method for generating each candidate MV taking into account other candidate MVs in the set to prevent the same query from being included in multiple candidates.

[0019] Conventional approaches generate candidate MVs using a fixed search space due to the problem of search space explosion as the workload increases. However, the search space should be adaptive to the use case, allowing for faster results by setting a smaller search space, or more accurate but slower results by setting a larger search space.

[0020] It is yet another object of the present invention to allow the user to configure the size of the search space depending on the use case.

[0021] Previous methods propose ways to select candidates by considering system and user constraints such as storage constraints, but do not consider that the estimated profit gain of a candidate MV should be recalculated at each iteration to reflect only the benefits of queries that have not been rewritten by any already selected candidates.

[0022] It is yet another object of the present invention to provide a method for selecting candidate MVs that returns a list of candidate MVs to be materialized taking into account certain constraints and recalculates the estimated gain of each candidate after each iteration. [Means for solving the problem]

[0023] An automatic materialized view generation system according to one embodiment performs a candidate materialized view generation process, which includes the steps of: acquiring a workload of one or more queries; dividing the acquired one or more queries into multiple query blocks; selecting at least one pair of the query blocks from the multiple divided query blocks based on similarity of join operations; generating at least one candidate materialized view from the selected pair; and iteratively attempting to add a new query block to one or more existing candidate materialized views that have already been generated. In each iteration, the automatic materialized view generation system evaluates whether to add the new query block to the existing candidate materialized views and determines whether to add the new query block based on the result of the evaluation.

[0024] The automatic generation system according to one embodiment may further perform a candidate materialized view elimination process, which includes eliminating unhelpful candidate materialized views from among the existing candidate materialized views using one or more criteria.

[0025] In one embodiment, the automatic generation system may further perform a candidate materialized view selection process, which includes selecting a set of candidate materialized views to materialize from among the existing candidate materialized views, taking into account one or more given constraints. [Brief explanation of the drawings]

[0026] [Figure 1A] FIG. 1 illustrates a functional block diagram of a portion of an automatic generation system according to one embodiment. [Figure 1B] FIG. 10 shows a functional block diagram of another portion of the automatic generation system according to an embodiment. [Figure 2] FIG. 1 illustrates a component diagram according to one embodiment. [Figure 3] 1 is a flowchart illustrating an overview of an embodiment. [Figure 4]1 is a flowchart detailing the candidate generation process. [Figure 5] 10 is a flowchart illustrating the deletion process applied to a candidate MV. [Figure 6] 10 is a flowchart illustrating a process for materializing a set of candidate MVs. [Figure 7] An example of a materialized view generation screen that allows you to upload workload queries and generate candidate MVs is shown below. [Figure 8] An example of a search space setting screen is shown. [Figure 9] 10 shows an example of a user constraint setting screen. [Figure 10] 10 shows an example of a screen displaying selected candidate MVs under user-specified constraints. [Figure 11] 1 illustrates an example of a database table that stores information about joins in workload queries. [Figure 12] 1 illustrates an example system architecture for one embodiment. DETAILED DESCRIPTION OF THE INVENTION

[0027] Hereinafter, embodiments will be described with reference to the drawings. These embodiments are exemplary for explaining the present invention, and some omissions and simplifications have been made as appropriate for clarity of explanation. Furthermore, the present invention is not limited to these embodiments, and all applications consistent with the concept of the present invention are included within the technical scope of the present invention.

[0028] In the figures and the following description, identical parts or parts having similar functions may be given the same number, or may be described with the same number but with different subscripts, or the subscript may be omitted. Unless otherwise limited, each part may be plural or singular.

[0029] To facilitate understanding of the present invention, the function, operation, position, size, shape, range, etc. of each element shown in the drawings may not represent the actual function, operation, position, size, shape, range, etc. Therefore, the present invention is not necessarily limited to the function, operation, position, size, shape, range, etc. disclosed in the drawings.

[0030] In the following description, various types of information may be described using terms such as tables, lists, and columns, although this information may also be represented in other data structures.

[0031] In the following description, candidate materialized views may be referred to using different terms such as "candidate materialized view," "candidate MV," or "candidate."

[0032] In this embodiment, a system for iteratively expanding candidates to generate a candidate set of materialized views is described. The system can be employed, for example, to generate a materialized view set to accelerate workloads in applications that require rapid periodic retrieval of data for purposes such as analysis or monitoring. For example, the system can be used to support data analysis and decision-making processes in supply chains, where rapid data retrieval is required for timely detection of issues that may slow the entire production process and affect the interests of multiple related parties.

[0033] 1A and 1B, taken together, illustrate a functional block diagram of an automatic generation system according to one embodiment. A workload query 101 is provided as input to a single query block extraction 102. The single query block extraction 102 extracts a single SELECT-FROM-WHERE query block from the workload query 101. Hereinafter, a query block may be simply referred to as a "query." In one or more embodiments, a join cost extraction 103 extracts the cost of each join in the workload query 101 from each query plan obtained from the database 104. In one or more embodiments, the join cost extraction 103 may be skipped. For example, if access to the database 104 is unavailable, all query joins are considered to have the same cost.

[0034] In the first iteration, the similarity calculation 105 calculates the similarity between join operations in the query blocks, which can be calculated, for example, by considering the ratio of common joins between two query blocks to the total number of joins of the two query blocks.

[0035] Element 106 is used to extract the pair of candidate query blocks with the most similar join operations. In the first iteration, element 107 returns Yes and all candidates selected in element 106 are created in candidate generation 108.

[0036] From the second iteration onwards, element 105 calculates the similarity of join operations between the original query block and the candidates created in element 108. Element 105 iteratively tries to extend the existing candidates generated in the previous iteration by adding new query blocks, and element 106 then selects the pair of existing candidates and query blocks with the highest similarity.

[0037] In one or more embodiments, the user is prompted to specify a similarity threshold α, and in element 109, only candidates with a join operation similarity higher than the specified threshold are considered. This allows the user to tailor the search space of candidate MVs to the use case, and consequently, the time required to compute the candidate MVs. For example, by setting a high threshold α, the user can reduce the number of candidates considered, thereby reducing the time required to compute the candidates. This is desirable in applications where candidate MVs are needed quickly. Conversely, the user can set a lower value for threshold α to increase the number of candidates considered in the candidate generation process, thereby increasing the likelihood of finding an optimal candidate MV. This may involve a higher time requirement, but may also increase the likelihood of finding an optimal candidate MV.

[0038] For all existing candidate query block pairs with a similarity higher than threshold α, query addition evaluation 110 evaluates whether adding a query block to the existing candidate would be beneficial. If element 111 determines that the evaluation is beneficial, a new candidate is created in element 108 by extending the existing candidate to include the query block. In one or more embodiments, instead of creating a new candidate from an existing candidate and the considered query block, the existing candidate can be directly modified to include the considered query block.

[0039] After all candidates are generated, in one or more embodiments, element 112 is used to remove candidate MVs from the generated candidate MVs that are deemed unprofitable according to defined criteria. The criteria relate to, but are not limited to, the size of the candidate MVs, the cost of the joins they cover, the overlap of the join operations they cover, and the query that will be rewritten with different generated candidate MVs. For example, a candidate MV that is larger than the tables involved may be removed if the cost of scanning the large candidate MV would negate the benefit of the candidate's precomputed join operations. Similarly, a candidate MV that covers a low-cost join operation may be removed if the candidate's estimated benefit does not justify the storage space used. Additionally, candidates that are subsets of other candidates in terms of the rewritten query and precomputed join operations may be removed.

[0040] In one or more embodiments, the user can decide to manually select candidate MVs or request element 113. In the case of manual candidate selection, element 114 presents candidate MVs from which to choose in the form of a graphical user interface (GUI).

[0041] In one or more embodiments, automatic selection of candidate MVs may require a user to specify a set of constraints 118 that are to be considered in selecting candidate MVs. These constraints 118 may include, but are not limited to, storage constraints such as maximum storage capacity for storing MVs.

[0042] This automatic selection method checks whether all workload queries have been rewritten with already materialized MVs (element 115), whether all candidate MVs have been materialized (or whether all candidate MVs have already been selected for materialization) (element 116), and whether constraints 118 are reached (i.e., no more candidate MVs can be materialized within the constraints) (element 117). If none of these conditions are met, candidate gain calculation 119 calculates the estimated gain of the candidate MVs by considering each query's individual contribution (join cost covered by the candidates 120) to the candidate MVs that have not already been selected for materialization. In one or more embodiments, in each iteration, only contributions associated with queries that have not been rewritten by candidates already selected in previous iterations are considered (i.e., contributions associated with queries that will be rewritten by already selected candidates are not considered), facilitating the selection of candidate MVs to rewrite such unrewritten queries and increasing the number of queries that can be rewritten with the selected set of candidate MVs. This may aim to maximize the number of queries that can be rewritten with the minimum number of MVs.

[0043] Element 121 selects a suitable (e.g., best) candidate for materialization from among the candidates that have not been materialized (or not selected for materialization) according to the gain calculated by element 119. In one or more embodiments, user-specified or system constraints are also taken into consideration. If the suitable (e.g., best) candidate does not satisfy the specified constraints, a next suitable (e.g., next best) candidate is selected for materialization. For example, if the size of the selected suitable (e.g., best) candidate MV exceeds the remaining storage space for storing the MV, a next suitable (e.g., next best) candidate MV is searched for materialization. The search for the next candidate continues until a suitable candidate that satisfies the constraints and can be materialized is identified or no more candidates that satisfy the constraints and can be materialized are found. The selected candidate for materialization is saved (element 122). An iteration is then initiated to materialize the next candidate. The iteration ends when any of conditions 115 to 117 is satisfied. The candidates selected to be materialized are presented to the user by element 123 in the form of a graphical user interface (GUI).

[0044] Figure 2 shows a component diagram of one embodiment. The main components are a central processing unit (CPU) 201, short-term memory 202, storage 203, and network and I / O 204. A user can load a workload query 101 into memory 202 using a workload input screen 205. The user can configure the search space in a search space configuration screen 206. Candidate MVs are then generated by similarity calculation 105, query additional evaluation 110, and candidate generation 108. Undeleted candidates 208 are deleted (element 112), and deleted candidates 209 are presented to the user on a generated candidate display screen 114. For automatic candidate selection, the user is prompted to enter constraints via a user constraint setting screen 210. Candidate payoffs and the best available candidate are iteratively calculated and selected (elements 119, 121). Finally, the selected candidate 122 is displayed to the user using a selected candidate display screen 123.

[0045] 3 is a flowchart outlining one embodiment. In a candidate MV generation process 301, candidate MVs are generated by a candidate generation method. In a candidate removal process 302, a candidate pruning method prunes unpromising candidate MVs. In a candidate selection process 303, the candidate selection method returns the best candidates to generate in order, taking into account storage constraints.

[0046] 4 is a flowchart showing the candidate generation process 301 in detail. First, the similarity of join operations between query blocks extracted from the workload query 101 is calculated (step 401). An initial candidate MV is created from the pair of queries with the highest similarity of join operations (step 402). Next, the similarity between the initial candidate MV and the query block is calculated to add a new query to the candidates (step 403). If a similarity higher than a user-specified similarity threshold α is found between an existing candidate MV and a query block (step 404), the candidate query pair with the highest similarity is selected (step 405), and the query is evaluated for incorporation into the candidate MV (step 406).

[0047] The evaluation of the gain of adding a query block to an existing candidate MV (step 406) is performed by considering how the estimated gain of the existing candidate MV changes if the query is added. In one or more embodiments, incorporating a query into an existing candidate MV is considered only if there are no other existing candidate MVs that rewrite the query and no other existing candidate MVs with higher gains for the query (step 4061). This reduces the number of candidate MVs to which each query is rewritten and avoids inefficiently including queries in many candidate MVs that are not used at runtime. Furthermore, in one or more embodiments, incorporating a query is considered beneficial only if incorporating the query increases, or at least does not decrease, the gain of the total candidate MVs (step 4062). The gain of a query in a candidate MV can be calculated, for example, but not limited to, as the portion of the join cost covered by the candidate MV relative to the overall join cost for the query, or as the difference in the query execution cost with and without the candidate MV. Furthermore, the gain of a candidate MV can be calculated, for example, but not limited to, as the sum of all gains of queries rewritten with such candidate MVs.

[0048] Any candidate MVs deemed useful in step 406 are created (step 407). In one or more embodiments, the original candidate MVs are also retained along with the updated candidate MVs, allowing them to be used to create additional candidate MVs. Steps 403-407 are repeated until no candidate MVs have a similarity higher than a user-specified threshold for evaluation.

[0049] 5 illustrates a elimination process 302 applied to the candidate MVs generated in step 301, which eliminates candidates that are not deemed useful. First, in one or more embodiments, the size of the candidate MVs (e.g., the size of the tables involved) is estimated, and candidates with a size exceeding a specified size are eliminated (step 501). Next, in one or more embodiments, the cost of precomputed join operations is considered as an elimination criterion (step 502). For example, candidate MVs that cover a total join cost lower than the median join cost of the query blocks are eliminated because they may not be particularly useful. Furthermore, in one or more embodiments, candidate MVs that are subsets of other candidate MVs in terms of precomputed join operations and rewritten queries are eliminated (step 503).

[0050] Figure 6 shows a process for selecting a set of candidate MVs for materialization, given a set of constraints, including system and user-specified constraints. First, the process checks whether all query blocks have been rewritten (step 601), whether all candidate MVs have been selected for materialization (step 602), and whether constraint limits, such as maximum storage usage, are touched (step 603). If any of the conditions are true, the process ends. In the first iteration, these conditions are not expected to be true, so the process continues, computing the estimated gains of all candidate MVs (step 604). The candidate with the highest estimated gain is selected for materialization (step 605). Next, a list containing all queries rewritten by the selected candidate is generated, and this list is used to track queries rewritten by the selected candidate in future iterations. A list containing the selected candidate is also created (step 606).

[0051] FIG. 7 shows an example of a materialized view generation screen 700 that allows a user to upload workload queries and generate candidate MVs. Workload queries can be uploaded using button 701. Each query 7041 uploaded by the user can be viewed in the query workload box 704. More queries can be viewed by sliding bar 7042. The user can set restrictions on the input search space by pressing button 702. After uploading the workload and configuring the search space, candidate MVs are generated with button 703. Each generated candidate MV 7051 can be viewed in the candidate materialized view box 705. More candidates can be viewed by sliding bar 7052. The user can materialize all candidates using button 706, manually select candidates to materialize via button 707, or enter constraints for automatic candidate selection with button 708.

[0052] FIG. 8 shows an example of the search space configuration screen 206 that appears after pressing button 702 on the materialized view generation screen 700. The user can change the search space using a slide bar 801 that adjusts the similarity threshold. The user can decrease the similarity threshold to broaden the space and consider values with low similarity. To set a narrower space, the similarity threshold should be set to a higher value. After selecting the desired similarity threshold, the user can set the threshold using button 802 and return to the materialized view generation screen 700.

[0053] 9 shows an example of the Set User Constraints screen 210 that is displayed when button 708 on the Generate Materialized View screen 700 is pressed. In one or more embodiments, the Set User Constraints screen 210 allows the user to set storage space constraints for the materialized view. The specified value for the storage constraint 901 can be changed with arrow 902. The Set Constraint button 903 sets the value of the storage constraint to the specified value of the storage constraint 901 and navigates to screen 123 that displays the selected candidate MVs under the specified constraint.

[0054] Figure 10 shows an example of a screen 123 displaying selected candidate MVs under user-specified constraints 1001. Each selected candidate MV 10021 is displayed in element 1002. All candidates can be reviewed using the slide bar 10022. Selected candidates can be materialized with the select candidate button 1003.

[0055] Figure 11 shows an example of a database table that stores information about joins in workload queries. Column 1101 contains the workload query identifier. Column 1102 stores the join condition. Column 1103 stores information about the join type, such as hash join or nested join. Column 1104 stores information about the join cost, which can be used to calculate the estimated gain of the candidate MV.

[0056] 12 shows an example of the system architecture of an automatic generation system 100 according to one embodiment. This system architecture 1200 includes a processor (CPU) 1201, an input device 1202, an output device 1203, a short-term memory 1204, a communication interface 1205, a storage device 1207, and a bus 1206 connecting these devices.

[0057] The CPU 1202 reads data and programs stored in the storage device 1207, loads them into the short-term memory 1204, and executes the programs. The short-term memory 1204 includes a volatile storage element such as a RAM, and stores programs to be executed by the processor 1201.

[0058] The storage device 1207 is a non-volatile storage device such as an HDD (hard disk drive) or an SSD (solid state drive) for storing programs, data, etc. The storage device 1207 includes a candidate generation program 1208, a candidate pruning program 1209, and a candidate selection program 1210 for causing the CPU 1202 to execute the candidate generation process 301, the candidate deletion process 302, and the candidate selection process 301, respectively, shown in FIG. 3 . The storage device 1207 also includes a database 1212. The storage device 1207 also includes a join cost 1213 and a candidate MV 1214, which are described in FIG. 11 . The communication interface 1205 allows a client (user) 1230 to connect to a network 1220, which allows the client (user) 1230 to access the system described in this invention.

[0059] According to one embodiment, by iteratively creating candidate MVs, it is possible to balance the number of queries rewritten by each candidate MV with the number of join operations precomputed by the candidate MV. This avoids creating candidates that maximize the number of rewritten queries and therefore have low query gain due to a small number of precomputed join operations. Alternatively, it is possible to avoid creating candidates that maximize the number of precomputed join operations and therefore reduce the number of queries per candidate MV, thereby increasing storage space usage and maintenance costs due to an increased number of materialized views.

[0060] Furthermore, according to one embodiment, by creating each candidate MV depending on other candidate MVs, a query is included in the candidate MV only if it is determined that incorporating the query is beneficial, and the size of the MV is avoided from increasing if it is not necessary, thereby improving the problem that the increase in the scanning cost of the MV reduces the gain of other rewritten queries.

[0061] According to one embodiment, the size of the search space for candidate MVs can be adjusted to suit specific use cases. For example, in some scenarios, fast generation of candidate MVs is required. In such cases, setting a narrow search space allows the user to obtain quick results. On the other hand, if the user needs to consider optimal candidate MVs, setting a wider search space allows more candidate MVs to be considered, reducing the possibility of missing good candidate MVs.

[0062] A method for selecting candidate MVs according to one embodiment allows a user to be provided with a list of candidate MVs to be materialized, taking into account system or user constraints, such as storage space constraints. By recalculating the estimated gain of each candidate MV at each iteration, only gains from queries that have not been rewritten by previously selected candidate MVs can be considered, maximizing the number of queries that can be rewritten by the set of selected MVs under the specified constraints.

[0063] According to one embodiment, in the process of repeatedly selecting candidate MVs to be materialized, the estimated gains of the candidate MVs are recalculated in each iteration, taking into account only the gains associated with queries that have not been rewritten by the candidate MVs already selected in the previous iteration, and the candidate MVs to be materialized are selected based on the recalculated estimated gains. This allows the candidate MVs to be materialized to be selected in a way that aims to maximize the number of queries rewritten with the minimum number of candidate MVs.

[0064] The present invention is not limited to the above-described embodiments and includes various modifications. For example, the above-described embodiments have been described in detail to clearly explain the present invention, and the present invention is not necessarily limited to those including all of the described configurations. Furthermore, it is possible to replace part of the configuration of one embodiment with the configuration of another embodiment, or to add the configuration of another embodiment to the configuration of one embodiment. Furthermore, it is possible to add, delete, or replace part of the configuration of each embodiment with other configurations. [Explanation of symbols]

[0065] 100: Automatic generation system, 101: Workload query, 102: Single query block extraction, 104: Database, 105: Similarity calculation, 108: Candidate generation, 110: Additional query evaluation, 114: Candidate display screen, 119: Gain calculation, 120: Join cost, 123: Candidate display screen, 202: Short-term memory, 203: Storage, 204: I / O, 205: Workload input screen, 206: Search space setting screen, 210: User constraint setting screen

Claims

1. 1. An automated generation system for generating candidate materialized views for a set of queries in a workload, comprising: a memory device storing at least one program; a processor that executes the program stored in the memory device; an input device for receiving input from a user; Equipped with By executing the program, the processor executes a candidate materialized view generation process; The candidate materialized view generation process: acquiring a workload of one or more queries input by a user via the input device; Dividing the one or more acquired queries into a plurality of query blocks; selecting at least one pair of the query blocks from the divided plurality of query blocks in consideration of similarity of join operations, and generating at least one candidate materialized view from the selected pair; repeatedly attempting to add new query blocks to one or more existing candidate materialized views that have already been generated; In each iteration, evaluating whether to add the new query block to the existing candidate materialized view, and deciding whether to add the new query block depending on the result of the evaluation. Including, Auto-generation system.

2. The automatic generation system according to claim 1, the evaluating performed by the processor includes checking whether the new query block is already included in the existing candidate materialized view that has a higher estimated gain for the new query block; Auto-generation system.

3. The automatic generation system according to claim 1 or 2, the evaluating performed by the processor includes checking whether adding the new query block to the existing candidate materialized view reduces an overall estimated gain of the existing candidate materialized view; Auto-generation system.

4. The automatic generation system according to claim 1, the processor: allowing the user to specify conditions regarding the similarity; using the user-specified criteria for similarity to control a process of selecting the pairs and generating the candidate materialized views. Auto-generation system.

5. The automatic generation system according to claim 1, The processor further comprises: Eliminating unhelpful candidate materialized views from among the existing candidate materialized views using one or more criteria. Execute the candidate materialized view deletion process, including Auto-generation system.

6. The automatic generation system according to claim 5, the criteria include a criterion regarding the size of the candidate materialized view; Auto-generation system.

7. 7. The automatic generation system according to claim 5, the criteria include a criterion related to the total cost of the join operation; Auto-generation system.

8. 7. The automatic generation system according to claim 5, the criteria include criteria for the candidate materialized view to be a subset of another candidate materialized view; Auto-generation system.

9. The automatic generation system according to claim 1, The processor further comprises: performing a candidate materialized view selection process that selects a set of candidate materialized views for materialization from among the existing candidate materialized views, taking into account one or more given constraints; Auto-generation system.

10. The automatic generation system according to claim 9, the candidate materialized view selection process includes iteratively selecting at least one candidate materialized view suitable for materialization from among the unselected candidate materialized views; in each iteration of selection, recalculating estimated gains of the unselected candidate materialized views by considering only gains associated with the query blocks that have not been rewritten by the candidate materialized views already selected in a previous iteration of selection, and selecting at least one suitable candidate materialized view based on the recalculated estimated gains; Auto-generation system.

11. 11. The automatic generation system according to claim 9, One or more of the constraints may include: Constraints defined by the automated generation system or by the user are included; The user-defined constraints include constraints on storage space that the user can specify. Auto-generation system.

12. The automatic generation system according to claim 10, wherein the processor, in each iteration of selection, checking whether any of said one or more constraints has been reached and deciding whether to continue or terminate said iteration of selection depending on the result of the checking; Auto-generation system.

13. 13. The automatic generation system according to claim 10 or 12, wherein the processor, in each iteration of selection, after selecting the suitable candidate materialized views, determining whether the selected candidate materialized views can be materialized under the constraints; If the result of the check is that the materialized view cannot be materialized, select the next best candidate materialized view that can be materialized under the constraints from among the unselected candidate materialized views. Auto-generation system.

14. 1. A method for automatically generating candidate materialized views for a set of queries in a workload, comprising: Obtain a workload of one or more queries; Dividing the one or more acquired queries into a plurality of query blocks; selecting at least one pair of the query blocks from the divided plurality of query blocks in consideration of similarity of join operations, and generating at least one candidate materialized view from the selected pair; repeatedly attempting to add new query blocks to one or more existing candidate materialized views that have already been generated; At each iteration, adding the new query block to the existing candidate materialized view is evaluated, and depending on the result of the evaluation, a decision is made as to whether to add the new query block. Processes include: Automatic generation method.

15. 1. A machine-readable computer program for causing a computer to execute an automated method for generating candidate materialized views for a set of queries of a workload, the method comprising: The automatic generation method includes: Obtain a workload of one or more queries; Dividing the one or more acquired queries into a plurality of query blocks; selecting at least one pair of the query blocks from the divided plurality of query blocks in consideration of similarity of join operations, and generating at least one candidate materialized view from the selected pair; repeatedly attempting to add new query blocks to one or more existing candidate materialized views that have already been generated; At each iteration, adding the new query block to the existing candidate materialized view is evaluated, and depending on the result of the evaluation, a decision is made as to whether to add the new query block. Including, Computer program.

Images