A materialized view construction method, data processing system, medium and program product

Abstract

A materialized view construction method, data processing system, medium, and program product relate to the field of electronic digital data processing. The method comprises: receiving text query information, determining the execution order and execution type of query operations in the text query information, and generating a logical plan; extracting scan operations, projection operations, join operations, and aggregation operations from the logical plan to obtain a query sub-plan representing the query semantics; converting the query sub-plan into an initial plan slice based on the operation type of the query sub-plan; normalizing the initial plan slice to generate a standard plan slice in a preset format; performing redundancy elimination and merging on the standard plan slice according to a preset aggregation strategy to obtain a target plan slice; and generating a materialized view table creation statement based on the target plan slice to construct the materialized view. Implementation of this application can optimize materialized view generation and reduce redundancy in materialized views.

Description

Technical Field

[0001] The present application relates to the field of electronic digital data processing, and in particular to a materialized view construction method, a data processing system, a medium, and a program product. Background Art

[0002] In OLAP database systems, multi-table joins and aggregations are a typical query operation. To improve the performance of such queries, database systems often use materialized views. This precomputes the joins and aggregations in the query and stores them in a table. When a user initiates a query, the query engine replaces the query portion that matches the materialized view with a query that accesses the materialized view, reducing computational effort and improving query performance.

[0003] In related technologies, materialized view construction primarily uses a logical plan composed of relational algebra operators to represent query structures. This approach analyzes query histories, converts queries into logical plans, extracts the query structure, and then generates the materialized view creation statements based on this query structure.

[0004] However, as query patterns become increasingly complex and users write queries in more diverse ways, the number of logical plans has increased dramatically. Since each logical plan triggers the generation of materialized views, the system needs to maintain a large number of materialized views, consuming a large amount of storage space and computing resources, resulting in high costs. Summary of the Invention

[0005] The present application provides a materialized view construction method, a data processing system, a medium, and a program product for optimizing materialized view generation and reducing the redundancy of materialized views.

[0006] In a first aspect, the present application provides a materialized view construction method, which is applied to a data processing system, and the method includes: receiving text query information, determining the execution order and execution type of query operations in the text query information, and generating a logical plan; extracting scan operations, projection operations, join operations, and aggregation operations from the logical plan to obtain a query sub-plan that represents the query semantics; converting the query sub-plan into an initial plan slice according to the operation type of the query sub-plan; normalizing the initial plan slice to generate a standard plan slice in a preset format; performing redundancy merging processing on the standard plan slice according to a preset aggregation strategy to obtain a target plan slice; generating a materialized view table creation statement based on the target plan slice to construct the materialized view.

[0007] In the above embodiment, the data processing system receives text query information and generates a logical plan, extracts the query sub-plan of the SPJG pattern and converts it into a plan slice, and then processes it through normalization and aggregation strategy processing to finally generate a materialized view table creation statement; it realizes the standardized representation and equivalence judgment of the query structure, can effectively identify equivalent query structures, eliminate redundant materialized view recommendations, and at the same time maintain the query acceleration capability of the materialized view; through the intermediate representation of the plan slice, it not only ensures the standardization of query processing, but also provides sufficient flexibility to handle various complex scenarios.

[0008] In combination with some embodiments of the first aspect, in some embodiments, the initial plan slice includes a table plan slice, a star join slice and an aggregation plan slice; according to the operation type of the query sub-plan, the step of converting the query sub-plan into the initial plan slice specifically includes: when the query sub-plan is a single-table operation, converting the query sub-plan into a table plan slice; when the query sub-plan is a multi-table join operation, converting the query sub-plan into a star join slice; when the query sub-plan includes an aggregation operation, generating an aggregation plan slice corresponding to the query sub-plan.

[0009] In the above embodiment, the data processing system divides the initial plan slices into three types: table plan slices, star connection slices and aggregation plan slices, and adopts corresponding conversion strategies for different query operation types, so that the system can accurately process single-table operations, multi-table connection operations and aggregation operations; it not only improves the accuracy of query structure conversion, but also facilitates subsequent normalization processing and redundant merging, and also enhances the system's ability to handle complex queries.

[0010] In combination with some embodiments of the first aspect, in some embodiments, the step of normalizing the initial plan slice to generate a standardized plan slice in a preset format specifically includes: extracting the sub-slice corresponding to the wide table in the initial plan slice to obtain the plan slice to be processed; traversing the plan slice to be processed from bottom to top, converting the field data in the plan slice to be processed into a string according to a preset fixed order to obtain a standardized value; generating a standardized plan slice based on the plan slice to be processed and the standardized value.

[0011] In the above embodiment, the data processing system uses a normalization processing method that traverses the plan slices from the bottom up and converts them into strings in a fixed order. The system can convert query structures of different forms but the same semantics into the same normalized values, which can effectively eliminate the impact of differences in query writing methods, so that different expressions with the same query semantics can be identified as equivalent structures, thereby avoiding the generation of redundant materialized views.

[0012] In combination with some embodiments of the first aspect, in some embodiments, the step of performing redundant merging processing on the standard plan slices according to the preset aggregation strategy to obtain the target plan slice specifically includes: grouping the standard plan slices with the same standard values, and performing merging processing of the indicator column and dimension column on each group of slices to obtain a merged plan slice; obtaining a preset aggregation logical expression and determining the preset aggregation strategy; performing roll-up conversion processing on the merged plan slice according to the preset aggregation strategy to obtain the target plan slice.

[0013] In the above embodiment, the data processing system groups plan slices with the same normalized values ​​and merges their indicator columns and dimension columns, and then performs transformation processing according to a preset aggregation strategy. The system achieves effective identification and merging of equivalent query structures; it not only reduces the number of redundant materialized views, but also ensures the query performance of the materialized views through reasonable strategy selection.

[0014] In combination with some embodiments of the first aspect, in some embodiments, the step of rolling up the merged plan slice according to the preset aggregation strategy to obtain the target plan slice specifically includes: extracting the sub-slice corresponding to the wide table in the merged plan slice to obtain the merged sub-slice; identifying the predicate types in the merged sub-slice including atomic predicates, constant predicates and aggregation predicates; determining the corresponding preset aggregation strategy according to the predicate type, rolling up the merged plan slice to obtain the target plan slice.

[0015] In the above embodiment, the data processing system identifies the predicate types in the merged sub-shards and selects an appropriate aggregation strategy accordingly. The system can then take appropriate processing approaches based on the characteristics of different predicate types. This categorized processing approach enables the system to more accurately process various query conditions, ensuring the accuracy of query results while providing room for optimization.

[0016] In combination with some embodiments of the first aspect, in some embodiments, the step of determining the corresponding preset aggregation strategy based on the predicate type, performing roll-up conversion processing on the merged plan slice, and obtaining the target plan slice specifically includes: detecting the aggregation operation type in the merged sub-slice, and determining the set of aggregation operations to be processed; according to the aggregation operation set, obtaining the corresponding basic conversion strategy from the preset strategy library, and obtaining the basic strategy set; determining the strategy combination rules of the basic strategy set based on user settings, and generating a composite conversion strategy; and performing roll-up conversion processing on the merged plan slice according to the composite conversion strategy to obtain the target plan slice.

[0017] In the above embodiment, the data processing system detects the type of aggregation operation and selects the corresponding conversion strategy from the strategy library, and then generates a composite conversion strategy based on user settings. The system realizes flexible processing of aggregation operations; it can not only handle various complex aggregation scenarios, but also dynamically adjust the processing strategy according to actual needs.

[0018] In combination with some embodiments of the first aspect, in some embodiments, after the step of generating a table creation statement for a materialized view based on a target plan slice to construct a materialized view, the method further includes: establishing a mapping relationship between a normalized value and a materialized view to obtain a view mapping table; receiving a user query request, extracting a query structure in the user query request, and obtaining a plan slice to be matched; performing normalization processing on the plan slice to be matched to obtain a normalized value to be matched; and determining a target materialized view based on the normalized value to be matched and the view mapping table.

[0019] In the above embodiment, the data processing system achieves efficient materialized view matching and usage by establishing a mapping relationship between normalized values ​​and materialized views and using this mapping relationship to process user query requests. This processing method not only speeds up query processing but also improves the utilization rate of materialized views.

[0020] In a second aspect, an embodiment of the present application provides a data processing system, which includes: one or more processors and a memory; the memory is coupled to the one or more processors, the memory is used to store computer program code, the computer program code includes computer instructions, and the one or more processors call the computer instructions to enable the data processing system to execute the method described in the first aspect and any possible implementation of the first aspect.

[0021] In a third aspect, an embodiment of the present application provides a computer program product comprising instructions, which, when the computer program product is run on a data processing system, enables the data processing system to execute the method described in the first aspect and any possible implementation of the first aspect.

[0022] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium comprising instructions, which, when executed on a data processing system, causes the data processing system to execute the method described in the first aspect and any possible implementation of the first aspect.

[0023] It is understandable that the data processing system provided in the second aspect, the computer program product provided in the third aspect, and the computer storage medium provided in the fourth aspect are all used to execute the methods provided in the embodiments of the present application. Therefore, the beneficial effects that can be achieved can be referenced to the beneficial effects of the corresponding methods and will not be repeated here.

[0024] One or more technical solutions provided in the embodiments of this application have at least the following technical effects or advantages:

[0025] 1. By utilizing a complete processing flow from text queries to materialized view table creation statements, including logical plan generation, query sub-plan extraction, plan slice conversion, normalization, and aggregation strategy processing, this system can convert semantically identical query structures of varying forms into a standardized representation. This effectively addresses the materialized view redundancy issue inherent in related technologies, resulting from diverse query expressions. This in turn ensures accurate materialized view recommendations and optimizes system resource utilization. Using the intermediate representation of plan slices, the system accurately identifies equivalent query structures, avoiding the creation of redundant materialized views for semantically identical queries.

[0026] 2. Since three different types of plan slice representations, namely table plan slices, star join slices and aggregate plan slices, are used, and corresponding conversion processing is performed according to the operation type of the query sub-plan, it can accurately handle various types of query operations, effectively solving the problem of insufficient flexibility in handling complex query structures in related technologies, thereby achieving improved accuracy in query structure conversion and processing efficiency; through targeted conversion strategies, the system can accurately capture the characteristics of different types of query operations, especially when processing multi-table join scenarios in star models, which can better maintain query semantics and provide optimization space.

[0027] 3. By adopting a mapping mechanism between normalized values ​​and materialized views and determining the appropriate materialized view through normalization and mapping matching during query processing, the system can quickly and accurately find the appropriate materialized view to accelerate the query, effectively solving the problem of inefficient materialized view selection in related technologies and achieving a significant improvement in query processing performance. By establishing a mapping relationship between normalized values ​​and materialized views, the system can quickly locate the appropriate materialized view and avoid repeated matching calculations. BRIEF DESCRIPTION OF THE DRAWINGS

[0028] Figure 1 This is a schematic diagram of an application scenario of the materialized view construction method in an embodiment of the present application;

[0029] Figure 2 This is a flow chart of a method for constructing a materialized view in an embodiment of the present application;

[0030] Figure 3 This is another flowchart of the materialized view construction method in an embodiment of the present application;

[0031] Figure 4 This is a schematic diagram of the structural organization of the plan slice in the embodiment of the present application;

[0032] Figure 5 This is another structural organization diagram of the plan slice in the embodiment of the present application;

[0033] Figure 6This is another schematic diagram of an application scenario of the materialized view construction method in an embodiment of the present application;

[0034] Figure 7 It is a schematic diagram of the structure of a physical device of the data processing system in an embodiment of the present application. DETAILED DESCRIPTION

[0035] The terms used in the following examples of the present application are only for the purpose of describing specific embodiments and are not intended to limit the present application. As used in the specification of the present application, the singular expressions "a", "an", "above", "the", and "this" are intended to include plural expressions as well, unless the context clearly indicates otherwise. It should also be understood that the term "and / or" used in the present application refers to any or all possible combinations of one or more of the listed items.

[0036] In the following, the terms "first" and "second" are used for descriptive purposes only and should not be understood to imply or suggest relative importance or implicitly indicate the number of the technical features indicated. Therefore, the features defined as "first" and "second" may explicitly or implicitly include one or more of the features. In the description of the embodiments of this application, unless otherwise specified, "plurality" means two or more.

[0037] For ease of understanding, the scenarios in the embodiments of the present application are introduced below. First, you need to understand the characteristics of the OLAP (Online Analytical Processing) system. OLAP is an analysis-oriented data processing method, mainly used to process complex multidimensional data analysis queries. In the OLAP system, materialized view (MV) is an important query acceleration method. A materialized view is essentially a pre-calculated and stored database object (snapshot), which saves the query results in the form of a physical table. When users query, they can directly obtain data from these pre-calculated results, thereby significantly improving query performance. During the query processing process, the database query optimizer will generate a logical plan (Logical Plan), which is a query execution plan composed of relational algebra operators, which describes the specific execution steps and order of the query. Among these queries, the SPJG mode is a typical query mode, which includes four basic operations: table scan (Scan), projection (Project), join (Join) and aggregation (Aggregate). Aggregation operations can be divided into two categories: rollup aggregate functions (such as sum, count, min, and max) and non-rollup aggregate functions (such as countdistinct, avg distinct, and stddev). The results of rollup aggregate functions can be used for further aggregation calculations, while the results of non-rollup aggregate functions cannot be directly aggregated again.

[0038] To better represent and process query structures, this application proposes a PlanPiece system. A PlanPiece is an abstract base class that contains basic information such as the output column set and the filter predicate set. Based on PlanPieces, this application designs three specific subclasses: TablePiece, which represents single-table operations; StarJoinPiece, which describes multi-table join relationships and is particularly suitable for handling scenarios where a fact table joins multiple dimension tables in a star schema; and AggregatePiece, which describes aggregation operations. It not only contains information such as regular metrics, deduplication metrics, dimension columns, and rolled-up dimension columns, but also introduces the concept of a virtual wide table (FlatTable). A virtual wide table (FlatTable) is an important data structure used to describe the data source for aggregation operations. It categorizes predicates into two types: fixed-form rigid predicate sets (stiffConjuncts) and flexible predicate sets (flexibleConjuncts). This classification helps the system better handle different types of filtering conditions.

[0039] To enable flexible aggregation operations, the present invention also proposes an aggregation operator (AggregatePolicy). This is a policy system based on logic programming principles and includes a variety of basic policies, such as IdentityPolicy (maintaining original values), SumPolicy (summing), AvgPolicy (average), and AndPolicy (conditional combination), including AndPolicy, OrPolicy, NotPolicy, and SeqPolicy. By combining these policies, the system can flexibly handle various complex aggregation scenarios. In practical applications, the present invention provides multiple matching modes: PerfectMatch retains all predicate conditions and is suitable for scenarios requiring precise matching; RollupMatch provides stronger generalization capabilities by removing flexible predicates and adding rollup dimensions; PartiallyRollupMatch retains time-related predicates while rolling up some dimensions, achieving a balance between accuracy and generalization capabilities.

[0040] The organic combination of these concepts and mechanisms enables the present invention to effectively identify equivalent query structures, eliminate redundancy, and flexibly generate appropriate materialized view table creation statements based on actual needs. By moving filtering conditions to a higher-level predicate lifting technique and converting query structures into standard normalization, the system can better discover and process equivalent query structures, thereby improving the accuracy and efficiency of materialized view recommendations.

[0041] The following describes the application scenarios of the embodiments of the present application.

[0042] In related technologies, multi-table joins and aggregations in OLAP database systems are a typical query operation. To improve the performance of such queries, database systems often use materialized views, which pre-calculate the join and aggregation operations in the query and store them in table form. Specifically, the materialized view pre-calculates the results of the query's join and aggregation operators by accessing the query's associated base tables. When a user initiates a query, the query engine replaces the query portion that matches the materialized view with an access to the materialized view. This reduces or even eliminates the computational effort required to retrieve data from the base tables for joins and aggregations, improving query performance.

[0043] In related technologies, query performance can be optimized by using a materialized view recommendation method based on query frequency. The following describes a scenario in which the materialized view construction method in related technologies is used.

[0044] When using the materialized view acceleration technology, users need to consider the following when designing the materialized view table creation statement:

[0045] 1. Extract query substructures that can be accelerated by MV. Query substructures refer to the portion of a query plan that satisfies a certain pattern (for example, the SPJG pattern, which includes the query portion of the Scan, Project, Join, and Aggregate operators).

[0046] 2. Preprocess query substructures to eliminate redundant ones and avoid recommending redundant MVs. Redundant query substructures are groups of query substructures that can be accelerated by the same MV. Due to differences in text writing, filter conditions, and expression calculation, the query engine optimizer generates unequal logical plans.

[0047] 3. Choose the correct MV creation statement. When designing an MV for the same query substructure, you have multiple options for MV creation statements. This is especially true when the query substructure contains different predicates or when the aggregation operator includes non-rollup aggregate functions (such as count distinct). Consider various factors, including MV acceleration, MV generalization, and MV maintenance costs.

[0048] The materialized view construction method described in the embodiments of this application converts queries into a standard plan slice structure, performs normalization processing, and intelligently merges them, enabling efficient reuse of materialized views. This not only improves query performance but also reduces system maintenance costs. The following describes scenarios in which the materialized view construction method described in this application is used.

[0049] When designing the materialized view construction method described in the embodiments of this application, the following aspects must be comprehensively considered: First, query substructures that can be accelerated using MVs must be extracted. A query substructure here refers to the portion of a query plan that satisfies a certain pattern (e.g., the SPJG pattern, which includes Scan, Project, Join, and Aggregate operators). Second, the query substructures must be preprocessed to eliminate redundancy. Due to the diverse ways users write queries, the same query may differ in terms of textual writing, filter conditions, and expression calculation. This can result in unequal logical plans after processing by the query engine's optimizer. These query substructures can, in fact, be accelerated using the same MV. Finally, the correct MV table creation statement must be selected. For the same query substructure, multiple table creation statement options may exist when designing the MV. This is especially true when the query substructure contains different predicates or when the aggregate operator contains non-rollup aggregate functions (e.g., countdistinct). Comprehensive consideration must be given to multiple dimensions, including the MV's acceleration capability, generalization capabilities, and maintenance costs.

[0050] Based on this, see Figure 1 , Figure 1The following is a schematic diagram of an application scenario for the materialized view construction method in an embodiment of this application. The entire process begins with textual SQL query information (textual SQL). These queries are first processed and optimized by the query engine's optimizer (RboOptimizer) to generate an initial logical plan. The generated logical plan then enters the plan slice processing phase. During this phase, the system first uses the plan slice builder (PlanPieceBuilder) to convert the logical plan into an initial plan slice format. These plan slices are then normalized by the plan slice normalizer (PlanPieceNormalizer). These normalized plan slices can be merged to eliminate redundant query structures. Next, these processed plan slices are further transformed and optimized using the aggregation policy (AggregatePolicy) to adjust them to a form more suitable for materialized view generation. Finally, in the generation phase, the system uses the query generator (QueryGenerator) to generate the query portion of the materialized view, and the aggregate materialized view generator (AggregateMVGenerator) constructs the final materialized view table creation statement. The entire process forms a complete closed loop, ensuring efficient conversion from query to materialized view.

[0051] It can be seen that the materialized view construction method in the embodiment of the present application can not only achieve query optimization, but also effectively solve the problem of difficult reuse of materialized views in traditional methods, thereby achieving an optimal balance between resource utilization and query efficiency.

[0052] For ease of understanding, the following describes the process of the method provided by this implementation in combination with the above scenario. Figure 2 , which is a flow chart of the materialized view construction method in an embodiment of the present application.

[0053] S201: Receive text query information, determine the execution order and execution type of query operations in the text query information, and generate a logical plan.

[0054] Among them, text query information represents the SQL statement written by the user, including information such as query target, data source, filtering conditions and calculation logic; query operation refers to various operation operators in the SQL statement, such as SELECT, FROM, WHERE, GROUP BY and other statement blocks; execution order is used to represent the dependency relationship and sequence between various query operations; execution type refers to the specific processing method of the query operation, including table scan, data filtering, association calculation, aggregate statistics and other types; logical plan represents the tree-like execution structure after converting the text query, which is used to describe the complete processing flow of the query.

[0055] After receiving a user's query request, the data processing system needs to convert the textual SQL query into an executable solution. Specifically, the data processing system first parses the SQL text to identify the various query operations contained therein; then, it analyzes the dependencies between these operations to determine their execution order; then, based on the semantic characteristics of each operation, it determines its execution type; and finally, it organizes this information into a tree-like logical plan structure, where each node corresponds to a query operation, and the connections between nodes represent the data flow.

[0056] In some embodiments, the conversion of SQL queries into logical plans can be achieved through various methods: Optionally, the SQL text can be first converted into an abstract syntax tree (AST), the query structure can be identified through lexical and syntactic analysis, the AST can then be converted into a logical operator tree, and the operator order can be optimized and adjusted to obtain the final logical plan. Alternatively, keywords and expressions in the SQL can be directly identified using rule matching, the corresponding logical operators can be constructed, and the logical plan can be organized based on the dependencies between the operators. It is understood that other parsing and conversion methods can also be used to process SQL queries, and are not limited here.

[0057] In real-world applications, SQL queries may contain complex subqueries or non-standard syntax, which complicates logical plan generation. Data processing systems can employ query rewriting techniques to convert complex queries into equivalent standard forms. Specifically, they establish a query rewriting rule base containing transformation rules such as subquery expansion, predicate pushdown, and expression simplification. Then, they perform pattern matching on the input query to identify applicable rewriting rules. Finally, they repeatedly apply these rules until further optimization is impossible. This ensures that the generated logical plan is well-structured and optimizable.

[0058] S202: Extract scan operations, projection operations, join operations, and aggregation operations from the logical plan to obtain a query sub-plan representing query semantics.

[0059] Among them, the scan operation refers to the process of reading raw data from the data source, including full table scan and index scan; the projection operation refers to the process of selecting the required column fields; the join operation is used to represent the relationship between multiple tables, including inner join, outer join and other types; the aggregation operation represents the calculation of grouped statistics on data, such as sum, count, average, etc.; the query sub-plan refers to the partial execution plan extracted from the complete logical plan that meets the specific pattern.

[0060] After obtaining the logical plan, the data processing system needs to identify query components that can be optimized using materialized views. Specifically, the system first locates the subtree structure containing the SPJG pattern in the logical plan, which in turn contains the execution paths for the four operations: scan, projection, join, and aggregation. It then analyzes the specific properties of these operations, including the scanned table objects, the projected column sets, the join conditions, and the aggregation grouping and calculation logic. Finally, it reorganizes this information into independent query subplans, preserving the complete query semantics.

[0061] In some embodiments, query subplan extraction can be achieved through a variety of methods: optionally, a bottom-up traversal approach can be used, starting from the leaf node and examining each execution path. When a complete path that satisfies the SPJG pattern is found, it is extracted as a query subplan. Alternatively, a pattern matching approach can be used, pre-defining the characteristic description of the SPJG pattern, and then searching for a matching subtree structure in the logical plan to extract the matching portion. It is understood that other traversal and matching strategies can also be used to extract query subplans, which are not limited here.

[0062] In some cases, the same logical plan may contain multiple execution paths that satisfy the SPJG pattern, and these paths may overlap or contain each other. To address this situation, the data processing system needs to perform path selection and merging. Specifically, the system first calculates the costs and benefits of each SPJG path, taking into account factors such as the amount of data involved and computational complexity. It then examines the relationships between the paths and identifies sets of paths that can be merged. Finally, it selects the optimal path combination and generates the corresponding query sub-plan. This avoids extracting redundant query structures and improves the efficiency of subsequent processing.

[0063] S203: Convert the query sub-plan into an initial plan slice according to the operation type of the query sub-plan.

[0064] Among them, the query sub-plan represents the SPJG mode execution path extracted from the logical plan; the operation type refers to the specific type of operation contained in the sub-plan, including single-table operation, multi-table connection operation and aggregation operation; the initial plan slice represents the standardized data structure obtained after the preliminary conversion of the query sub-plan, including three types: table plan slice, star connection slice and aggregation plan slice; plan slice conversion represents the process of mapping the query operation to the corresponding plan slice structure.

[0065] The data processing system needs to convert query subplans into plan slices for subsequent processing. Specifically, the system first analyzes the query subplan's structural characteristics to determine its operation type. For single-table operations, it generates a table plan slice containing table metadata and filter conditions. For multi-table join operations, it generates a star join slice, recording the connection between the central table and dimension tables. For operations involving aggregation, it generates an aggregate plan slice, storing information about indicator columns, dimension columns, and aggregate functions. Each plan slice contains two basic fields: columns (the set of output columns) and conjuncts (the set of filter predicates).

[0066] In some embodiments, the conversion of query sub-plans to plan slices can be achieved in a variety of ways: optionally, a recursive conversion method can be used, starting from the root node of the sub-plan, creating a corresponding plan slice structure according to the node type, then recursively processing the sub-nodes, and finally connecting all plan slices into a complete structure; optionally, a template matching method can be used to predefine plan slice templates corresponding to different operation types, and then fill the specific parameters in the sub-plan into the template to generate the final plan slice. It is understandable that other conversion strategies can also be used to generate plan slices, which are not limited here.

[0067] During the conversion process, complex join relationships may be difficult to directly map to a standard star structure. Data processing systems can employ join reorganization techniques. Specifically, the following steps are performed: First, analyze the join conditions between tables and construct a join diagram. Then, identify possible star schemas within the diagram, including the central table and related dimension tables. For any parts that do not conform to the star schema, attempt to reorganize them into a star structure by adjusting the join order or splitting the joins. Finally, generate the corresponding star join slices. For cases where conversion to a star structure is truly impossible, maintain the original join relationships.

[0068] S204: normalize the initial plan slice to generate a standard plan slice in a preset format.

[0069] Among them, the initial plan slice represents the original data structure converted from the query sub-plan; normalization processing refers to the process of converting the plan slice into a unified standard format; the preset format represents the standardized representation pre-defined by the system; the standardized plan slice represents the plan slice structure after standardization processing, which is convenient for equivalence judgment and subsequent processing.

[0070] The data processing system needs to uniformly normalize plan slices from different sources and formats. Specifically, the data processing system first extracts the wide table information from the plan slice, including the table structure, field information, and filter conditions. It then traverses the entire plan slice structure from the bottom up, converting the information of each node into a string format according to a preset fixed order. It then processes various special structures, such as standardizing the different orders of multi-table joins and equivalent filter conditions. Finally, it generates a canonical plan slice based on the converted string and the original plan slice, ensuring that queries with the same semantics receive the same canonical form.

[0071] In some embodiments, plan slices can be normalized in a variety of ways: Alternatively, lexicographic normalization can be employed, sorting and organizing all information in the plan slice (including table names, column names, filter conditions, etc.) according to predefined lexicographic order rules to generate a unique normalized form. Alternatively, semantic normalization can be employed, first analyzing the query semantics of the plan slice and then converting structures with different expressions but the same semantics into a unified standard form. It is understood that other normalization strategies can also be employed to process plan slices, and these are not limited here.

[0072] During normalization, a key issue is how to handle predicate conditions in a plan slice, as the same filtering semantics can be expressed in multiple different ways. Data processing systems can employ predicate normalization techniques. Specifically, complex predicate expressions are first decomposed into atomic predicates. Each atomic predicate is then simplified and normalized, such as by unifying the direction of comparison operators and merging overlapping range conditions. The normalized atomic predicates are then recombined according to predefined rules. Finally, a unified predicate expression is generated. This ensures that semantically equivalent predicate conditions are given the same normalized form.

[0073] S205 , performing redundancy elimination and merging processing on the standard plan slices according to a preset aggregation strategy to obtain a target plan slice.

[0074] Among them, the preset aggregation strategy represents a set of transformation rules predefined by the system, which are used to guide the optimization and merging of plan slices; redundant merging refers to the process of eliminating redundancy and merging equivalent structures; standardized plan slice refers to the standard format plan slice that has been normalized; and the target plan slice refers to the optimized plan slice structure that is ultimately used to generate the materialized view.

[0075] The data processing system needs to further optimize the normalized plan slices. Specifically, the data processing system first groups plan slices with the same standard form based on the normative values. It then merges the indicator and dimension columns of each plan slice within each group, including merging rollup-capable aggregation functions and associated dimension fields. It then obtains the preset aggregation logic expression and determines the applicable aggregation strategy. Finally, based on the selected strategy, the merged plan slices are rolled up and transformed, adjusting the aggregation level and calculation logic to obtain the final target plan slice. This process takes into account multiple factors, including query performance, storage cost, and usage frequency.

[0076] In some embodiments, redundant merging of plan slices can be achieved through a variety of methods: Optionally, a rule-based merging strategy can be employed, predefining a set of conversion rules (such as IdentityPolicy, AvgPolicy, and AndPolicy), selecting and applying appropriate rules based on the characteristics of the plan slices for conversion; Optionally, a cost-based merging strategy can be employed, calculating the comprehensive cost (including computational complexity, storage overhead, etc.) for each possible merging solution and selecting the solution with the optimal cost for conversion. It is understood that other strategies can also be employed to achieve optimized merging of plan slices, which are not limited here.

[0077] During the merge process, a typical issue is how to handle plan slices containing non-rollup aggregate functions (such as countdistinct). Data processing systems can employ specialized aggregate function processing techniques. Specifically, the approach involves first identifying non-rollup aggregate functions in the plan slices; then analyzing their computational characteristics and usage scenarios; for countdistinct, retaining the underlying deduplicated data or using approximate computation methods; and converting avg distinct to a combination of sum distinct and count distinct. Finally, selecting an appropriate processing strategy based on actual needs to achieve a balance between accuracy and performance.

[0078] S206: Generate a table creation statement for the materialized view based on the target plan slice to construct the materialized view.

[0079] Among them, the target plan slice represents the final plan slice structure after optimization; the table creation statement refers to the SQL statement used to create the materialized view; the materialized view represents the database object that pre-calculates the query results and stores them in the form of a table; the construction process includes steps such as view creation, data calculation and storage.

[0080] The data processing system needs to convert the optimized plan slice into an executable materialized view definition. Specifically, the data processing system first analyzes the structural characteristics of the target plan slice, including the data source, calculation logic, and output specifications. Based on this information, the system then generates a standardized SELECT statement, including the select clause, FROM clause, WHERE clause, and GROUP BY clause. It then adds necessary view management options, such as refresh policies and storage parameters, and finally assembles the complete CREATEMATERIALIZED VIEW statement. This entire process ensures that the generated table creation statement accurately expresses the query semantics of the plan slice and is easy for the database system to execute and maintain.

[0081] In some embodiments, the generation of materialized view table creation statements can be achieved through various methods: Alternatively, a templated generation method can be used, pre-defining SQL templates corresponding to different types of plan slices, then filling the templates with specific information from the plan slices to generate the final table creation statement; Alternatively, a progressive generation method can be used, first generating a basic query structure, then gradually adding filtering conditions, aggregate calculations, optimization tips, and other content, and finally assembling a complete table creation statement. It is understood that other methods can also be used to generate materialized view definition statements, which are not limited here.

[0082] When creating materialized views, choosing the appropriate refresh strategy and storage parameters can be challenging. Data processing systems can employ adaptive configuration technology. Specifically, this involves first analyzing the usage characteristics of the materialized view, including query frequency, data update patterns, and storage capacity requirements. Based on these characteristics, the most appropriate refresh method (such as full refresh or incremental refresh) and storage parameters (such as partitioning strategy and compression method) are dynamically selected for each materialized view. Finally, this configuration information is incorporated into the table creation statement. This ensures optimal performance for the materialized view in real-world operations.

[0083] In the above embodiment, although the query substructure in the query engine adopts the same representation as the query (i.e., the logical plan composed of relational algebra operators), this representation can effectively solve the query optimization problem. However, in the MV recommendation scenario, the intermediate representation of the query substructure also needs to focus on the following two aspects: On the one hand, the equivalence determination of the query substructure. By eliminating the branches and leaves in the query substructure and retaining the stem part, the query substructure is converted into a standard form, so that query structures with the same standard form are equivalent, thereby avoiding the creation of redundant MVs for equivalent query substructures. On the other hand, it is the flexibility of the form. When a query substructure has multiple candidate MVs, the intermediate representation needs to be able to be flexibly converted into all possible MV table creation statements.

[0084] The following is a more detailed description of the process of the method provided by this implementation. Figure 3 , is another flowchart of the materialized view construction method in an embodiment of the present application.

[0085] S301: Receive text query information, determine the execution order and execution type of query operations in the text query information, and generate a logical plan.

[0086] Referring to step S201 , the data processing system generates a logical plan.

[0087] S302: Extract scan operations, projection operations, join operations, and aggregation operations from the logical plan to obtain a query sub-plan representing query semantics.

[0088] Referring to step S202 , the data processing system determines a query sub-plan.

[0089] S303: Convert the query sub-plan into an initial plan slice according to the operation type of the query sub-plan.

[0090] Referring to step S203 , the data processing system generates an initial planning slice.

[0091] In some embodiments, the initial plan slice includes a table plan slice, a star join slice, and an aggregate plan slice; the data processing system will classify the processing, that is, when the query sub-plan is a single-table operation, the data processing system will convert the query sub-plan into a table plan slice; when the query sub-plan is a multi-table join operation, the query sub-plan will be converted into a star join slice; when the query sub-plan includes an aggregate operation, an aggregate plan slice corresponding to the query sub-plan will be generated.

[0092] See also Figure 4 , Figure 4 A schematic diagram of the structural organization of the plan slice in the embodiment of this application; Figure 4This paper details the structural design and relationships of the three core plan slice types in this invention. First, the basic plan slice (PlanPiece), an abstract base class, contains two basic fields: a column set (columns) for storing output column information and a predicate set (conjuncts) for storing filter conditions. The table plan slice (TablePiece) extends the functionality of the basic plan slice through inheritance. It additionally includes table fields for storing complete metadata about database tables, directly corresponding to physical tables in the database. The star join slice (StarJoinPiece) provides a more complex structure specifically designed to describe joins between multiple tables. In a star join slice, the center table field points to the fact table, while the corner array describes the join information with each dimension table. Each corner contains information such as the join type (joinType), equal join conditions (equalConjuncts), and non-equal join conditions (otherConjuncts). The bottom portion of the star-connected slice diagram uses a specific example to show the connection relationship between the partsupp, part, and supplier tables in TPC-H, intuitively illustrating the actual application scenario of the star-connected slice.

[0093] Also, see Figure 5 , Figure 5 This is another structural organization diagram of the plan slice in the embodiment of this application; Figure 5This article provides an in-depth look at the complete structural design of an AggregatePiece. As a structure dedicated to handling aggregation operations, an AggregatePiece consists of four core field sets: metrics, which stores aggregate functions that can be rolled up, such as sum and count; distinctMetrics, which stores aggregate functions that cannot be directly rolled up, typically count distinct; dimensions, which stores dimension columns for aggregation operations; and rollupDimensions, which stores dimension columns that can be rolled up. Furthermore, an AggregatePiece includes a key wide table structure (flatTable), which is further divided into three parts: stiffConjuncts, which stores fixed filter conditions; flexibleConjuncts, which stores dynamic filter conditions that may change over time; and a PlanPiece object, which points to the underlying data structure, which may be a TablePiece or StarJoinPiece. This design ensures flexible data processing while maintaining structural clarity.

[0094] Specifically, the data processing system establishes an abstract PlanPiece class and its three subclasses: TablePiece, StarJoinPiece, and AggregatePiece. Query substructures that satisfy the SPJG pattern can be converted into a tree structure composed of these three types. PlanPiece is an abstract base class containing two main fields: columns and conjuncts, which represent all columns output by the PlanPiece and the predicates used for data filtering, respectively. TablePiece corresponds to the Scan operator in the logical plan, and its table field stores metadata about the accessed table, such as the table name, column information, and partition information. StarJoinPiece is used to represent multi-table join relationships and is particularly suitable for describing the association between a fact table and multiple dimension tables in a star schema. Its center field points to the fact table, and the corners field is an array of the StarJoinCorner type. Each array element describes the join information for a dimension table, including the join type, equality conditions, and non-equality conditions. AggregatePiece corresponds to aggregation operations and includes fields such as metrics (conventional metrics), distinctMetrics (deduplication metrics), dimensions (dimensions), and rollupDimensions (rollup dimensions). This design makes it more suitable for materialized view recommendation scenarios than the common Aggregate operator.

[0095] Correspondingly, a single-table operation refers to query processing involving only one data table; a table plan slice refers to a standardized execution plan generated for a single-table query; a multi-table join operation refers to an associated query involving multiple data tables at the same time; a star join slice is used to represent a standardized execution plan centered on a core table and connecting multiple dimension tables; an aggregation operation refers to processing logic that requires grouped statistical calculations; an aggregation plan slice refers to a standardized execution plan that includes aggregation calculations; and a query sub-plan refers to a local execution plan extracted from the original query.

[0096] The data processing system needs to convert query subplans into corresponding standard plan slice structures based on their different characteristics. Specifically, the data processing system first analyzes the structural characteristics of the query subplan to determine whether it is a single-table operation, a multi-table join operation, or contains aggregation operations. For single-table operations, the data processing system extracts information such as the table access method, filter conditions, and output fields to construct the corresponding table plan slice. For multi-table join operations, the data processing system analyzes the connection relationships between tables, identifies core tables and dimension tables, and constructs a star-shaped join slice structure. For cases involving aggregation operations, the data processing system needs to extract information such as grouping fields, aggregate functions, and calculation expressions to generate an aggregate plan slice. During the conversion process, the data processing system also needs to retain the original query's filter conditions, field mappings, and calculation logic to ensure that the converted plan slice can fully express the query semantics.

[0097] In some embodiments, the conversion of query sub-plans to plan slices can be achieved in a variety of ways: optionally, a template matching conversion method can be used, first defining a standard plan slice template for each type of query operation, then identifying the operation type of the query sub-plan, then filling the specific parameters in the query into the corresponding template, and finally generating a complete plan slice structure; optionally, a recursive construction conversion method can be used, first decomposing the query sub-plan into basic operation units, then processing each operation unit from the bottom up, then creating corresponding plan slice nodes according to the operation type, and finally connecting these nodes into a complete plan slice according to the execution order of the query. It is understandable that other methods can also be used to achieve the conversion operation of query sub-plans to plan slices, which are not limited here.

[0098] S304: Extract the sub-slice corresponding to the wide table in the initial plan slice to obtain the plan slice to be processed.

[0099] Among them, the initial plan slice represents the original plan slice structure converted from the query sub-plan; the wide table represents the logical data view formed after the table connection operation, which contains the field information of all related tables; the sub-slice refers to the local structural unit in the plan slice, which contains specific operation logic; the pending plan slice represents the plan slice sub-structure that needs to be normalized.

[0100] Before normalization, the data processing system must locate and extract key data structures. Specifically, the system first analyzes the overall structure of the initial plan slice and identifies the table joins it contains. It then locates the wide table structure formed by these joins, which contains the complete data access path. It then extracts the operational logic associated with this wide table, including field mappings, filter conditions, and calculation expressions. Finally, it organizes these extracted structures into a plan slice to be processed, preparing for subsequent normalization.

[0101] In some embodiments, the extraction of sub-slices related to the wide table can be achieved through various methods: Alternatively, a structure traversal approach can be employed, starting from the root node of the initial plan slice and traversing downward along the data flow to identify and collect all operation nodes related to the wide table; alternatively, a mark propagation approach can be employed, first marking the base table nodes involved in the wide table, then propagating the marks upward along the plan slice structure, and finally extracting all marked nodes and their associated information. It is understood that other methods can also be employed to extract relevant sub-slices, which are not limited here.

[0102] During the extraction process, it is necessary to determine how to handle nested table join structures. Data processing systems can adopt a hierarchical extraction strategy. Specifically, the following approach is required: first, a dependency graph of table joins is constructed to identify direct and indirect join relationships; then, starting from the innermost layer of joins, the relevant operational logic is extracted layer by layer outward; for each layer of joins, its unique join conditions and filter predicates need to be retained; finally, this hierarchical information is organized into structured plan slices to be processed. This ensures that complex table join structures can be completely and accurately extracted, facilitating subsequent normalization processing. In addition, for join relationships with circular dependencies, appropriate split points are required to break the loop and ensure the termination of the extraction process.

[0103] S305: traverse the pending plan slice from bottom to top, convert the field data in the pending plan slice into character strings according to a preset fixed order, and obtain a standard value.

[0104] Among them, bottom-up traversal means starting from the leaf node of the plan slice and processing upward step by step according to the hierarchical structure; field data represents the structured information in the plan slice, including table name, field name, filter conditions, calculation expressions, etc.; preset fixed order refers to the system's predefined standardized sorting rules, which are used to ensure that structures with the same semantics can be represented by the same string; standard value represents the string form after standardized conversion.

[0105] The data processing system needs to convert plan slices with different formats but the same semantics into a unified string representation. Specifically, the data processing system first traverses the lowest-level nodes of the plan slice to be processed, which are usually basic table access operations. It then processes the field data in each node according to a preset fixed order, including lexicographic sorting of table names, normalization of column names, standardization of filter conditions, and unified representation of calculation expressions. The processed information is then concatenated into a normalized string format. Finally, these strings are combined according to the dependencies between nodes to generate a complete normalized value.

[0106] In some embodiments, the conversion from a plan slice to a canonical value can be achieved in a variety of ways: optionally, a recursive conversion method can be used to normalize the information of each node, then recursively process its child nodes, and finally combine all the results according to a fixed format; optionally, a template filling method can be used to predefine string templates for different types of nodes, and then fill the normalized field information into the corresponding template. It is understood that other conversion methods can also be used to generate canonical values, which are not limited here.

[0107] During the conversion process, a major challenge is how to handle complex expressions and conditions. Data processing systems can employ expression normalization techniques. Specifically, the expression is first parsed to obtain its internal operators and operands. The operands are then normalized, such as by unifying numerical precision and standardizing string formats. The expression structure is then reorganized based on operator precedence and associativity. Finally, a normalized string representation is generated according to a predefined expression template. Complex predicates containing multiple conditions must be broken down into atomic predicates and reassembled in a standard order. For example, the condition "a>1 AND b<2 OR c=3" must first be broken down into basic comparison expressions and then reorganized according to the precedence of the logical operators and the lexicographical order of the operands, ensuring that semantically equivalent conditions receive the same normalized value.

[0108] S306: Generate a standard plan slice based on the plan slice to be processed and the standard value.

[0109] Among them, the pending plan slice represents the original plan slice structure; the standard value represents the string form after standardization; the standard plan slice represents the plan slice with a standard structure, which not only retains the original computational semantics but also has a unified format representation, which facilitates subsequent equivalence judgment and optimization processing.

[0110] The data processing system needs to construct a standard-format plan slice based on the original structure and normalized string. Specifically, the data processing system first parses the normalized value string to extract the structured information contained therein, such as table access paths, field mappings, filter conditions, and calculation expressions. This information is then matched with the original semantics of the plan slice to be processed, ensuring that the conversion process does not change the query's computational logic. The parsed information is then reorganized into a standard tree structure according to a predefined plan slice template. Finally, the necessary attribute tags, such as data type and computation cost, are set for the newly generated plan slice. This entire process ensures that the normalized plan slice maintains the complete semantics of the query while maintaining a unified structural representation.

[0111] In some embodiments, the generation of a standard plan slice can be achieved through a variety of methods: optionally, a structural reconstruction method can be used to first construct basic operation nodes based on standard values, then reconnect these nodes according to the dependencies of the original plan slice, and finally supplement the necessary attribute information; optionally, an in-place conversion method can be used to directly perform structural adjustments and attribute updates based on the pending plan slice to convert it into a form that meets the standard requirements. It is understood that other methods can also be used to generate standard plan slices, which are not limited here.

[0112] In the process of generating standard plan slices, you may encounter the problem of reconstructing complex expressions. The data processing system can use expression reconstruction technology. The specific approach is: first parse the standardized expression string from the standard value; then analyze the operator priority and association relationship in the expression; then build the syntax tree structure of the expression based on this information; finally, convert the syntax tree into an executable calculation node. In this process, special attention must be paid to maintaining the computational semantics of the expression unchanged while ensuring that the generated structure meets the standardization requirements. For example, for complex expressions containing multiple calculation steps, it is necessary to correctly handle the data type and precision requirements of the intermediate results to avoid deviations in the calculation results due to the reconstruction process. For expressions containing custom functions or special operators, it is also necessary to ensure that these special semantics are correctly retained during the standardization process.

[0113] S307 , grouping the standard plan slices with the same standard value into one group, and performing a merging process of the indicator column and the dimension column on each group of slices to obtain a merged plan slice.

[0114] Among them, the same specification value means that the specification plan slices have completely consistent standardized string representations; the indicator column represents the numerical field that needs to be aggregated; the dimension column represents the descriptive field used for grouping; merge processing refers to the process of integrating the calculation logic of multiple plan slices into a unified structure; the merged plan slice represents the optimized plan slice structure after the merge.

[0115] The data processing system needs to merge and optimize plan slices with the same semantics. Specifically, the data processing system first compares the normative values ​​of the normative plan slices and clusters plan slices with the same normative values ​​into the same group. It then analyzes the field attributes of the plan slices within each group and identifies the indicator columns and dimension columns. The indicator columns are then merged, including merging identical aggregation functions and processing duplicate calculation logic. The dimension columns are also merged to ensure that all necessary grouping dimensions are retained. Finally, the merged indicator and dimension columns are organized into a new plan slice structure to form an optimized merged plan slice. This process ensures that the merged plan slice can simultaneously meet all the computational requirements of the original plan slice.

[0116] In some embodiments, the merging of plan slices can be achieved in a variety of ways: Optionally, a two-stage merging strategy can be employed, where the first stage merges plan slices with identical computational logic, and the second stage processes partially overlapping plan slices, achieving merging by appropriately expanding the computational scope. Optionally, a progressive merging strategy can be employed, starting with the simplest indicators and gradually processing increasingly complex computational logic, ensuring at each step that the merged result meets the original requirements. It is understood that other strategies can also be employed to achieve the merging of plan slices, which are not limited here.

[0117] During the merge process, a key issue is how to handle different types of aggregate functions. The data processing system can use aggregate function merging technology. The specific approach is: first identify the various aggregate functions contained in the plan slice, such as SUM, COUNT, AVG, etc.; then analyze the calculation characteristics of these functions to determine whether they can be merged directly or need to be merged after conversion; for simple aggregate functions (such as SUM, MAX), they can be merged directly; for composite aggregate functions (such as AVG), they need to be decomposed into basic aggregate functions (SUM / COUNT) before merging; for special aggregate functions (such as DISTINCTCOUNT), the original calculation logic may need to be retained. For example, when merging plan slices containing AVG and SUM, it is necessary to ensure that enough intermediate calculation results are retained to support all required aggregate calculations. In addition, when merging dimension columns, it is also necessary to consider the granularity level of the dimension to ensure that the merged plan slice can support data analysis needs of different granularities.

[0118] S308: Obtain a preset aggregation logic expression and determine a preset aggregation strategy.

[0119] Among them, the aggregation logic expression represents the formal expression describing the aggregation calculation rules; the preset aggregation strategy represents a set of optimization rules predefined by the system to guide the transformation of plan slices; the roll-up transformation represents the process of converting low-granularity aggregation calculations into high-granularity ones; the target plan slice represents the final plan slice structure after aggregation optimization.

[0120] The data processing system needs to optimize the aggregation of plan slices based on predefined policies. Specifically, the data processing system first retrieves preset aggregation logic expressions from the configuration library. These expressions define the conversion rules for different types of aggregation operations. It then determines the applicable aggregation policy based on the semantics of the expression, including IdentityPolicy (maintaining the original value), SumPolicy (summing), AvgPolicy (average), and AndPolicy (conditional combination). It then analyzes the aggregation operations in the merged plan slice to determine whether they meet the rollup conditions. Finally, it converts the aggregation operations that meet the conditions based on the selected policy to generate the optimized target plan slice. This entire process ensures that the converted calculation results remain consistent with the original query semantics.

[0121] In some embodiments, the aggregation strategy can be implemented in a variety of ways: Alternatively, a rule-matching approach can be used to predefine a set of aggregation transformation rules, and corresponding rules can be selected and applied based on the characteristics of the aggregation operations in the plan slice. Alternatively, a cost evaluation approach can be used to calculate the execution cost and benefit of each possible transformation scheme and select the optimal scheme for transformation. It is understood that other approaches can also be used to implement the aggregation strategy, and these are not limited here.

[0122] During the aggregation optimization process, it is necessary to determine how to handle complex aggregation dependencies. Data processing systems can use aggregation dependency analysis technology. The specific approach is: first, construct a dependency graph between aggregation operations and identify direct and indirect dependencies; then analyze the characteristics of each aggregation operation, including whether it is decomposable and whether it satisfies the associative law; then determine the execution order of the transformation based on the dependency relationship to ensure that the dependent aggregation operation is transformed before the dependent party; finally, apply the transformation rules step by step according to the determined order. For example, when processing a plan slice containing nested aggregations, it is necessary to start from the innermost aggregation and transform it layer by layer outward. For transformations that may cause changes in the calculation results, appropriate compensation calculations are required to ensure the accuracy of the results. In addition, the system needs to maintain the intermediate state during the transformation process to support possible rollback operations.

[0123] Afterwards, the data processing system will roll up and transform the merged plan slices according to the preset aggregation strategy to obtain the target plan slices.

[0124] S309: Extract the sub-slice corresponding to the wide table in the merge plan slice to obtain a merged sub-slice.

[0125] Among them, the merged plan slice represents the plan slice structure after the merged processing; the sub-slice corresponding to the wide table represents the local calculation structure related to the wide table; the merged sub-slice represents the part extracted from the merged plan slice that needs to be further processed.

[0126] The data processing system needs to extract key structures from the optimized plan slices. Specifically, the system first analyzes the overall structure of the merged plan slices and identifies the computational components related to wide tables. It then determines the boundaries of these components, including the tables, fields, and computational logic involved. It then completely extracts these related structures to form independent merged sub-slices. Finally, it ensures that the extracted sub-slices retain the necessary contextual information to facilitate subsequent processing.

[0127] In some embodiments, sub-slices can be extracted using a variety of methods: Alternatively, a label propagation approach can be employed, first marking nodes related to the wide table, then propagating the labels along the data stream, and finally extracting all marked portions; alternatively, a structure matching approach can be employed, pre-defining pattern features related to the wide table and then searching for matching structures in the planned slice. It is understood that other methods can also be used to extract sub-slices, and these are not limited here.

[0128] During the extraction process, a key issue is how to ensure completeness. Data processing systems can employ dependency analysis techniques. Specifically, this involves first constructing a dependency graph for each compute node in the planned slice; then, starting from the target node, tracing its dependencies backwards; and finally, ensuring that all necessary computational logic is included in the extracted sub-slice.

[0129] S310: Identify predicate types in the merged sub-slice, including atomic predicates, constant predicates, and aggregate predicates.

[0130] Among them, atomic predicates represent the most basic comparison expressions; constant predicates represent filter conditions containing fixed values; aggregate predicates represent filter conditions involving aggregate calculations; and predicate types represent different types of filter condition classifications.

[0131] The data processing system needs to classify and analyze the filter conditions in the sub-slices. Specifically, the data processing system first scans all filter conditions in the merged sub-slices. It then categorizes the conditions into three types based on their characteristics: atomic predicates (e.g., a > b), constant predicates (e.g., x = 5), and aggregate predicates (e.g., SUM(y) > 100). Each type of predicate is then further analyzed, including operand types and comparison operators. Finally, each predicate is annotated with its type information to facilitate subsequent optimization.

[0132] In some embodiments, predicate identification and classification can be achieved through various methods: Alternatively, grammatical analysis can be used to construct a grammatical tree for the predicate and determine the predicate type based on the tree's structural characteristics; alternatively, pattern matching can be used to predefine characteristic patterns for different types of predicates and determine the predicate type through matching. It is understood that other methods can also be used to achieve predicate classification, and these are not limited here.

[0133] During the recognition process, a common problem is how to handle compound predicates. Data processing systems can employ predicate decomposition techniques. Specifically, the compound predicate is first decomposed into basic logical expressions; then, the type of each basic expression is determined; and finally, the logical relationships between these basic expressions are recorded.

[0134] S311 . Determine the corresponding preset aggregation strategy according to the predicate type, perform roll-up conversion processing on the merged plan slice, and obtain the target plan slice.

[0135] Among them, the preset aggregation strategy represents the conversion rules predefined by the system for different types of predicates; the roll-up conversion processing represents the process of converting low-level filtering conditions into high-level ones; and the target plan slice represents the final optimized plan slice structure.

[0136] The data processing system needs to select an appropriate optimization strategy based on the predicate type. Specifically, the data processing system first retrieves the corresponding preset aggregation strategy from the configuration library based on the identified predicate type. It then analyzes the specific characteristics of each predicate and determines the applicable transformation rules. It then transforms the predicates according to the strategy requirements, which may include simplification, merging, and rewriting. Finally, it integrates the transformed predicates into the plan slice to generate the optimized target structure.

[0137] In some embodiments, the conversion processing of predicates can be achieved in a variety of ways: optionally, a rule-based conversion method can be adopted to define specific conversion rules for each predicate type and process them according to the rules; optionally, a cost-based conversion method can be adopted to evaluate the execution efficiency of different conversion schemes and select the optimal scheme. It is understandable that other methods can also be used to achieve the conversion of predicates, which are not limited here. During the conversion process, it is necessary to determine how to handle the dependencies between predicates. The data processing system can use dependency protection technology. The specific approach is: first analyze the dependencies between predicates; then determine the execution order of predicate conversion; and finally maintain the necessary dependency information during the conversion process.

[0138] In some embodiments, the data processing system will perform a composite strategy operation, that is, the data processing system will detect the type of aggregation operation in the merged sub-slice and determine the set of aggregation operations to be processed; based on the set of aggregation operations, obtain the corresponding basic conversion strategy from the preset policy library to obtain the basic strategy set; determine the policy combination rules of the basic strategy set based on user settings, and generate a composite conversion strategy; perform roll-up conversion processing on the merged plan slice according to the composite conversion strategy to obtain the target plan slice.

[0139] Among them, the aggregation operation type represents the various aggregation function types contained in the plan slice, such as SUM, COUNT, AVG, etc.; the aggregation operation set refers to the complete set of aggregation functions that need to be processed; the basic conversion strategy represents the optimization rules for a single aggregation function type; the basic strategy set is used to represent the set of all applicable basic optimization rules; the strategy combination rule refers to the combination method and priority between multiple basic strategies; the composite conversion strategy represents the complete optimization plan formed by combining multiple basic strategies; the roll-up conversion processing refers to the process of converting low-level aggregation calculations into high-level ones.

[0140] After obtaining the merged sub-slice, the data processing system needs to analyze and optimize the aggregation operations therein. Specifically, the data processing system first scans all calculation expressions in the merged sub-slice, identifies the types of aggregate functions contained therein, such as SUM, COUNT, AVG, etc., and collects these functions into the aggregate operation set to be processed; then, based on the function type in the aggregate operation set, it searches for matching basic conversion strategies from the pre-configured strategy library. These strategies define the optimization rules for different aggregate functions; then, based on the parameters set by the user in the configuration file or interface, it determines how to combine these basic strategies, including the execution order, priority, and conflict resolution of the strategies; finally, the data processing system systematically converts the aggregation operations in the merged plan slice according to the generated composite conversion strategy, including decomposing complex aggregations, replacing aggregate functions, adjusting the calculation order, and finally generating the optimized target plan slice. During the processing process, the data processing system also needs to ensure that the converted calculation results are consistent with the original query.

[0141] In some embodiments, the optimized conversion of aggregation operations can be achieved in a variety of ways: optionally, a rule-based conversion method can be used, first building an equivalent conversion rule library for the aggregation function, then analyzing the characteristics and context of each aggregation operation, then selecting the applicable conversion rule for processing, then verifying the correctness of the conversion result, and finally generating an optimized calculation structure; optionally, a cost-based conversion method can be used, first building a cost evaluation model for the aggregation operation, then calculating the execution cost for each possible conversion scheme, then selecting the optimal conversion path through a dynamic programming algorithm, then executing the conversion operation according to the selected path, and finally generating a calculation scheme with the optimal cost. It is understandable that other methods can also be used to achieve the optimized conversion of aggregation operations, which are not limited here.

[0142] To handle aggregation operations more flexibly, this application designs the AggregatePolicy mechanism. This mechanism uses logic programming to customize the modification of AggregatePiece by combining different policies. AggregatePolicy includes three types of implementations:

[0143] 1. Basic control class: including IdentityPolicy that always succeeds and NonePolicy that always fails

[0144] 2. Function implementation class: such as AvgPolicy that converts avg to sum and count, which can be expanded according to needs

[0145] 3. Logical combination classes: These include AndPolicy, OrPolicy, NotPolicy, and SeqPolicy, which combine multiple basic policies into composite policies. This design allows the system to flexibly handle various aggregation scenarios, such as handling non-rollup aggregate functions and optimizing query performance. Furthermore, through policy combination, more complex transformation logic can be implemented to meet diverse materialized view optimization requirements.

[0146] See also Figure 6 , Figure 6 This is a schematic diagram of another application scenario of the materialized view construction method in an embodiment of the present application; Figure 6 The actual processing of the entire system is demonstrated through a specific query example based on TPC-H. This query includes the common counting operation count(1) and the deduplication counting operation count(distinct l.l_partkey), and involves join operations on multiple tables, including lineitem, orders, customer, and nation. During processing, the system recognizes the time condition "l_shipdate > '1996-12-01'" as a flexible predicate and processes it, while the region condition "n_name in ('UNITED STATES', 'CHINA')" is processed as a dimension column. The count(1) in the query is placed in the metrics collection as a rollup aggregate function, while count(distinct l.l_partkey) is placed in the distinctMetrics collection as a non-rollup aggregate function. The lower half of the figure details the complete star join relationship, clearly demonstrating the hierarchical relationship and join method between tables such as lineitem, orders, customer, and nation through multiple StarJoinPieces. This example fully demonstrates how the system processes a complex real-world query and how the various components work together.

[0147] S312. Generate a table creation statement for a materialized view based on the target plan slice to construct the materialized view.

[0148] Referring to step S206 , the data processing system constructs a materialized view.

[0149] In some embodiments, the data processing system will perform a quick query based on the user request, that is, the data processing system will establish a mapping relationship between the normalized value and the materialized view to obtain a view mapping table; receive the user query request, extract the query structure in the user query request, and obtain the plan slice to be matched; perform normalization processing on the plan slice to be matched to obtain the normalized value to be matched; determine the target materialized view based on the normalized value to be matched and the view mapping table.

[0150] Among them, the canonical value represents the standardized string representation of the plan slice structure; the materialized view refers to the pre-calculated and stored query results; the view mapping table is used to record the correspondence between the canonical value and the materialized view; the user query request represents the original query statement that needs to be processed; the query structure refers to the various operations in the query and the relationship between them; the to-be-matched plan slice represents the part that needs to be matched extracted from the user query; the to-be-matched canonical value represents the standardized representation of the to-be-matched plan slice; the target materialized view refers to the materialized view that is finally matched and can be used to optimize the query.

[0151] The data processing system needs to establish and maintain an index structure for materialized views to quickly locate available materialized views. Specifically, the data processing system first calculates the normalized value for each materialized view and saves the correspondence between the normalized value and the view definition in the view mapping table. When receiving a user's query request, the data processing system parses the query statement and extracts the operational structure, including information such as table access methods, field selection, filter conditions, and calculation logic, to form a plan slice to be matched. The system then performs the same normalization processing on this plan slice as when the materialized view was created, obtaining a standardized normalized value to be matched. Finally, this normalized value is used to search the view mapping table to determine whether there is a materialized view that can be used to optimize the query. During this process, the data processing system also needs to consider factors such as the freshness and access rights of the materialized view.

[0152] In some embodiments, matching and searching of materialized views can be implemented in a variety of ways: optionally, an exact matching approach can be adopted, where a hash index structure of the canonical value is first constructed, then the hash value of the canonical value to be matched is calculated, then the fully matched item is searched in the index, then the validity of the matching result is verified, and finally the found materialized view is returned; optionally, a pattern matching approach can be adopted, where the canonical value is first converted into a feature vector, then the similarity between the canonical value to be matched and each item in the index is calculated, then several candidate items with the highest similarity are selected, then the best match is determined through detailed semantic analysis, and finally the selected materialized view is returned. It is understood that other approaches can also be adopted to implement matching and searching of materialized views, which are not limited here.

[0153] In the embodiment of the present application, due to the innovative proposal of a query analysis framework based on plan slices and the combination of an intelligent aggregation strategy management mechanism, complex query requests can be converted into a standardized plan slice structure. Through normalized processing and optimized merging, accurate recognition of query semantics and efficient reuse of materialized views can be achieved, effectively solving the problems of inaccurate query pattern recognition, low view reuse rate, and high maintenance cost in traditional materialized view methods, thereby achieving overall performance optimization of the data analysis system; not only significantly improving query performance, but also reducing the complexity of system maintenance.

[0154] The data processing system in the embodiment of the present invention is described below from the perspective of hardware processing. Figure 7 , is a schematic diagram of a physical device structure of a data processing system in an embodiment of the present application.

[0155] It should be noted that Figure 7 The structure of the data processing system shown is only an example and should not limit the functions and scope of use of the embodiments of the present invention.

[0156] like Figure 7 As shown, the data processing system includes a CPU 701, which can perform various appropriate actions and processes according to the programs stored in the ROM 702 or the programs loaded from the storage unit 708 into the RAM 703, such as executing the methods described in the above embodiments. The RAM 703 also stores various programs and data required for system operation. The CPU 701, ROM 702, and RAM 703 are connected to each other via a bus 704. An I / O interface 705 is also connected to the bus 704.

[0157] The following components are connected to the I / O interface 705: an input section 706 including an audio input device, push button switches, and the like; an output section 707 including a liquid crystal display (LCD), an audio output device, indicator lights, and the like; a storage section 708 including a hard disk and the like; and a communication section 709 including a network interface card such as a LAN (Local Area Network) card or a modem. The communication section 709 performs communication processing via a network such as the Internet. A drive 710 is also connected to the I / O interface 705 as needed. Removable media 711, such as a magnetic disk, an optical disk, a magneto-optical disk, or a semiconductor memory, is installed in the drive 710 as needed, so that computer programs read from the removable media can be installed in the storage section 708 as needed.

[0158] In particular, according to an embodiment of the present invention, the processes described above with reference to the flowcharts can be implemented as computer software programs. For example, an embodiment of the present invention includes a computer program product comprising a computer program carried on a computer-readable medium, the computer program including a computer program for executing the method shown in the flowchart. In such an embodiment, the computer program can be downloaded and installed from a network via the communication section 709 and / or installed from the removable medium 711. When the computer program is executed by the CPU 701, the various functions defined in the present invention are performed.

[0159] As described above, the above embodiments are only used to illustrate the technical solutions of the present application, rather than to limit them. Although the present application has been described in detail with reference to the above embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the above embodiments, or make equivalent replacements for some of the technical features therein. However, these modifications or replacements do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present application.

Claims

1. A materialized view construction method, characterized in that: Applied to a data processing system, the method comprises: receiving text query information, determining the execution order and execution type of query operations in the text query information, and generating a logical plan; Extracting scan operations, projection operations, join operations, and aggregation operations from the logical plan to obtain a query sub-plan representing query semantics; According to the operation type of the query sub-plan, the query sub-plan is converted into an initial plan slice; the initial plan slice includes a table plan slice, a star join slice, and an aggregate plan slice; the step of converting the query sub-plan into the initial plan slice according to the operation type of the query sub-plan specifically includes: when the query sub-plan is a single-table operation, converting the query sub-plan into a table plan slice; when the query sub-plan is a multi-table join operation, converting the query sub-plan into a star join slice; when the query sub-plan includes an aggregate operation, generating an aggregate plan slice corresponding to the query sub-plan; Normalizing the initial plan slice to generate a standard plan slice in a preset format; Performing redundancy elimination and merging processing on the standard plan slices according to a preset aggregation strategy to obtain a target plan slice; A table creation statement for a materialized view is generated based on the target plan slice to construct the materialized view.

2. The method according to claim 1, characterized in that The step of normalizing the initial plan slice to generate a standard plan slice in a preset format specifically includes: Extracting the sub-slice corresponding to the wide table in the initial plan slice to obtain a plan slice to be processed; Traversing the pending plan slice from bottom to top, converting the field data in the pending plan slice into character strings according to a preset fixed order to obtain a standard value; A standard plan slice is generated based on the to-be-processed plan slice and the standard value.

3. The method according to claim 1 or 2, characterized in that The step of performing redundancy elimination and merging processing on the standard plan slices according to the preset aggregation strategy to obtain the target plan slice specifically includes: Grouping the standard plan slices with the same standard value into a group, and performing a merging process of the indicator column and the dimension column on each group of slices to obtain a merged plan slice; Get the preset aggregation logic expression and determine the preset aggregation strategy; The merged plan slice is rolled up and converted according to the preset aggregation strategy to obtain the target plan slice.

4. The method according to claim 3, characterized in that The step of performing roll-up conversion processing on the merged plan slice according to the preset aggregation strategy to obtain the target plan slice specifically includes: Extracting the sub-slice corresponding to the wide table in the merged plan slice to obtain a merged sub-slice; Identifying predicate types in the merged sub-slice, including atomic predicates, constant predicates, and aggregate predicates; A corresponding preset aggregation strategy is determined according to the predicate type, and a roll-up conversion process is performed on the merged plan slice to obtain the target plan slice.

5. The method according to claim 4, characterized in that The step of determining a corresponding preset aggregation strategy according to the predicate type, performing rollup conversion processing on the merged plan slice, and obtaining the target plan slice specifically includes: Detecting the type of aggregation operation in the merged sub-slice and determining a set of aggregation operations to be processed; According to the aggregation operation set, the corresponding basic conversion strategy is obtained from the preset strategy library to obtain a basic strategy set; Determine the policy combination rules of the basic policy set based on user settings and generate a composite conversion policy; The merged plan slice is rolled up and converted according to the composite conversion strategy to obtain the target plan slice.

6. The method according to claim 1, characterized in that After the step of generating a table creation statement for a materialized view based on the target plan slice to construct the materialized view, the method further includes: Establish a mapping relationship between the standard value and the materialized view to obtain the view mapping table; Receive a user query request, extract the query structure in the user query request, and obtain a plan slice to be matched; Performing normalization processing on the to-be-matched plan slice to obtain a to-be-matched normalized value; A target materialized view is determined according to the to-be-matched specification value and the view mapping table.

7. A data processing system, characterized in that: The data processing system includes: one or more processors and a memory; the memory is coupled to the one or more processors, the memory is used to store computer program code, the computer program code includes computer instructions, and the one or more processors call the computer instructions to cause the data processing system to execute the method according to any one of claims 1 to 6.

8. A computer-readable storage medium comprising instructions, characterized in that: When the instructions are executed on a data processing system, the data processing system is caused to execute the method according to any one of claims 1 to 6.

9. A computer program product, characterized in that When the computer program product is run on a data processing system, the data processing system is caused to perform the method according to any one of claims 1 to 6.

Images