Materialized view construction method and device in database system, equipment and medium
By receiving materialized view construction requests in the database system, the feasibility of materialized view is judged based on preset conditions and constructed materialized view, the problem of materialized view inconsistent with the base table data when the base table data is updated is solved, and data consistency and query are improved efficiency.
Patent Information
- Application Number
- CN202411166567.6
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-08-23
- Publication Date
- 2024-12-03
- Estimated Expiration
- 2044-08-23
AI Technical Summary
In a database system, when the base table data is updated, the materialized view is inconsistent with the base table data, especially when creating a materialized view of a table with unique constraints or aggregated data, it is difficult to ensure the consistency of the data.
By receiving materialized view construction requests, determine the type of the target basic table, and judge the feasibility of materialized view construction requests based on preset construction conditions, including the aggregation operation situation and the reference status of sub-statements to the basic table. If the conditions are met, construct materialized view , to reduce the chance of data inconsistency.
It effectively reduces the chance that the base table and the materialized view data are inconsistent when the data is updated, and ensures data consistency and query efficiency.
Smart Images

Figure CN119066090A_ABST
Abstract
Description
Technical Field
[0001] The present disclosure relates to the field of data processing, and in particular to a materialized view construction method, device, equipment and medium in a database system. Background Art
[0002] A materialized view is a special type of view in the database. The materialized view pre-calculates the query results and stores the results in the materialized view. When the base table data changes, the materialized view also needs to be updated to determine the accuracy of the data. During the query process, the database system will optimize the query based on the content stored in the materialized view to improve query efficiency.
[0003] However, for some base tables, when data is updated, the base table data will be inconsistent with the data in the materialized view. Therefore, how to create a materialized view of the base table in the database becomes a technical problem to be solved. Summary of the invention
[0004] In order to solve the above technical problem or at least partially solve the above technical problem, the present disclosure provides a materialized view construction method, apparatus, device and storage medium in a database system.
[0005] In a first aspect, an embodiment of the present disclosure provides a materialized view construction method in a database system, the method comprising:
[0006] Receive a materialized view construction request for a target basic table; the materialized view construction request carries a target query statement for constructing a materialized view of the target basic table;
[0007] After determining that the target basic table belongs to the target type, determining whether the materialized view construction request satisfies a preset construction condition; the preset construction condition is used to determine the feasibility of the materialized view construction request according to whether the query statement of the materialized view contains an aggregation operation and / or whether a sub-statement of the query statement of the materialized view references a basic table, the target type includes a first type or a second type, the first type is used to identify a table with a unique constraint condition, and the second type is used to identify a table storing aggregate data;
[0008] When it is determined that the materialized view building request is executable, a materialized view of the target basic table is built based on the target query statement.
[0009] In an optional implementation, after determining that the target basic table belongs to the target type, determining whether the materialized view construction request satisfies a preset construction condition includes:
[0010] After determining that the target basic table belongs to the first type, determining whether the target query statement includes an aggregation operation;
[0011] After determining that the target query statement does not contain an aggregation operation, determine whether the target query statement meets the first condition matching item in the preset construction condition; the first condition matching item is used to determine the feasibility of the materialized view construction request based on the type of the sub-statement of the materialized view query statement and the situation in which the sub-statement references the column in the basic table.
[0012] In an optional implementation, after determining that the target basic table belongs to the target type, determining whether the materialized view construction request satisfies a preset construction condition includes:
[0013] After determining that the target basic table belongs to the second type, determining whether the target query statement includes an aggregation operation;
[0014] After determining that the target query statement includes an aggregation operation, determining whether the target query statement meets a second condition matching item in a preset construction condition; the second condition matching item is used to determine the feasibility of the materialized view construction request according to at least two of the type of a sub-statement of the materialized view query statement, the situation that the sub-statement references a column in a basic table, and the situation that the aggregation operation satisfies a rule;
[0015] After determining that the target query statement does not contain an aggregation operation, determine whether the target query statement meets the third condition matching item in the preset construction condition; the third condition matching item is used to determine the feasibility of the materialized view construction request based on the type of the sub-statement of the materialized view query statement and the situation in which the sub-statement references the column in the basic table.
[0016] In an optional implementation manner, before determining whether the target query statement meets the first condition matching item in the preset construction condition, the method further includes:
[0017] After determining that the target query statement does not include an aggregation operation, determining whether the target basic table includes a target monotonically increasing column; the target monotonically increasing column belongs to a value column of the target basic table and is used to trigger an update of the target basic table;
[0018] Accordingly, determining whether the target query statement meets the first condition matching item in the preset construction condition includes:
[0019] After determining that the target basic table includes a target monotonically increasing column, it is determined whether a query list sub-statement of the target query statement references the target monotonically increasing column.
[0020] In an optional implementation manner, the first condition matching item is further used to determine the feasibility of the materialized view construction request according to the column participation in the calculation in the materialized view query statement, and after determining that the target query statement does not include an aggregation operation, determining whether the target query statement meets the first condition matching item in the preset construction condition includes:
[0021] After determining that the target query statement does not include an aggregation operation, determining whether the key columns of the target basic table all belong to the columns referenced by the query list sub-statement in the target query statement, and determining whether the key columns of the target basic table participate in calculation in the target query statement;
[0022] Determine whether a conditional sub-statement in the target query statement references a value column of the target basic table;
[0023] Accordingly, when it is determined that the materialized view building request is executable, building the materialized view of the target basic table based on the target query statement includes:
[0024] When the following conditions are met at the same time, a materialized view of the target basic table is constructed based on the target query statement;
[0025] The conditions include:
[0026] Determining that the target query statement does not include an aggregation operation;
[0027] The target basic table does not include a target monotonically increasing column, or the target basic table includes a target monotonically increasing column and a query list sub-statement of the target query statement references the target monotonically increasing column;
[0028] The key columns of the target basic table all belong to the columns referenced by the query list sub-statements in the target query statement and the key columns of the target basic table do not participate in the calculation in the target query statement;
[0029] The conditional sub-statement in the target query statement does not reference the value column of the target basic table.
[0030] In an optional implementation manner, the method further includes:
[0031] After determining that the target base table belongs to the second type, determine whether the target base table includes a target monotonically increasing column; the target monotonically increasing column belongs to the value column of the target base table and is used to trigger an update of the target base table.
[0032] In an optional implementation, after determining that the target query statement does not include an aggregation operation, determining whether the target query statement meets a third condition matching item in a preset construction condition includes:
[0033] After determining that the target query statement does not include an aggregation operation and the target base table includes a target monotonically increasing column, it is determined whether a query list sub-statement in the target query statement references the target monotonically increasing column.
[0034] In an optional implementation, the third condition matching item is further used to determine the feasibility of the materialized view construction request according to the column participation in the calculation in the query statement of the materialized view, and the determining whether the target query statement meets the third condition matching item in the preset construction condition includes:
[0035] Determine whether the aggregate key columns of the materialized view in the target query statement all belong to the aggregate key columns in the target basic table;
[0036] Determine whether a conditional sub-statement in the target query statement references a value column of the target basic table;
[0037] Determine whether the value column of the target basic table participates in the calculation in the target query statement;
[0038] Accordingly, when it is determined that the materialized view building request is executable, building the materialized view of the target basic table based on the target query statement includes:
[0039] When the following conditions are met at the same time, a materialized view of the target basic table is constructed based on the target query statement;
[0040] The conditions include:
[0041] The target query statement does not contain an aggregation operation;
[0042] The target basic table does not include a target monotonically increasing column, or the target basic table includes a target monotonically increasing column and a query list sub-statement of the target query statement references the target monotonically increasing column;
[0043] The aggregate key columns of the materialized view in the target query statement all belong to the aggregate key columns in the target basic table;
[0044] The conditional sub-statement in the target query statement does not reference the value column of the target basic table;
[0045] The value column of the target basic table does not participate in the calculation in the target query statement.
[0046] In an optional implementation, after determining that the target query statement includes an aggregation operation, determining whether the target query statement meets a second condition matching item in a preset construction condition includes:
[0047] After determining that the target query statement includes an aggregation operation and the target base table includes a target monotonically increasing column, it is determined whether a query list sub-statement in the target query statement references the target monotonically increasing column.
[0048] In an optional implementation manner, the second condition matching item is further used to determine the feasibility of the materialized view construction request according to the column parameter calculation status in the materialized view query statement, and the determining whether the target query statement meets the second condition matching item in the preset construction condition includes:
[0049] Determine whether the aggregate key columns of the materialized view in the target query statement all belong to the aggregate key columns in the target basic table;
[0050] Determine whether a conditional sub-statement in the target query statement references a value column of the target basic table;
[0051] Determine whether the key column referenced by the grouping sub-statement in the target query statement belongs to the key column of the target basic table;
[0052] Determine whether a parameter of the aggregation operation in the target query statement comes from a value column of the target basic table, and the value column does not participate in the calculation of the parameter of the aggregation operation;
[0053] Determine whether the aggregation operation in the target query statement satisfies the target calculation rule; the target calculation rule includes the commutative law and the associative law;
[0054] Determine whether the aggregation operation in the target query statement is consistent with the aggregation operation of the column corresponding to the aggregation operation in the target basic table.
[0055] In an optional implementation manner, the method further includes:
[0056] When it is determined that the materialized view construction request is not executable, modifying the target query statement based on the preset construction condition so that the modified target query statement satisfies the preset construction condition;
[0057] Display the modified target query statement;
[0058] In response to a materialized view building request triggered for the modified target query statement, a materialized view of the target basic table is built based on the modified target query statement.
[0059] In an optional implementation manner, before modifying the target query statement based on the preset construction condition so that the modified target query statement satisfies the preset construction condition, the method further includes:
[0060] Determine a condition matching item that the target query statement does not meet in the preset construction condition, and determine whether the target query statement meets the preset modification condition based on the condition matching item;
[0061] Accordingly, the modifying the target query statement based on the preset construction condition so that the modified target query statement satisfies the preset construction condition includes:
[0062] If the target query statement meets the preset modification condition, the target query statement is modified based on the condition matching item so that the modified target query statement meets the preset construction condition.
[0063] In a second aspect, the present disclosure provides a materialized view construction device in a database system, the device comprising:
[0064] A first receiving module is used to receive a materialized view construction request for a target basic table; the materialized view construction request carries a target query statement for constructing a materialized view of the target basic table;
[0065] A first determination module is used to determine whether the materialized view construction request satisfies a preset construction condition after determining that the target basic table belongs to a target type; the preset construction condition is used to determine the feasibility of the materialized view construction request according to whether the query statement of the materialized view contains an aggregation operation and / or whether a sub-statement of the query statement of the materialized view references a basic table, the target type includes a first type or a second type, the first type is used to identify a table with a unique constraint condition, and the second type is used to identify a table storing aggregate data;
[0066] The first building module is used to build the materialized view of the target basic table based on the target query statement when it is determined that the materialized view building request is executable.
[0067] In a third aspect, an embodiment of the present disclosure further provides an electronic device, comprising: a processor; a memory for storing executable instructions of the processor; the processor is used to read the executable instructions from the memory and execute the instructions to implement a materialized view construction method as provided in an embodiment of the present disclosure.
[0068] In a fourth aspect, an embodiment of the present disclosure further provides a computer-readable storage medium, wherein the storage medium stores a computer program, and the computer program is used to execute the materialized view construction method in the database system provided by the embodiment of the present disclosure.
[0069] In a fifth aspect, the present disclosure provides a computer program product, wherein the computer program product comprises a computer program / instructions, and the computer program / instructions implement the above method when executed by a processor.
[0070] Compared with the prior art, the technical solution provided by the embodiments of the present disclosure has the following advantages:
[0071] In a materialized view construction method in a database system provided by an embodiment of the present disclosure, first, a materialized view construction request for a target basic table is received, the materialized view construction request carries a target query statement for constructing a materialized view of the target basic table, and then, after determining that the target basic table belongs to a target type, it is determined whether the materialized view construction request satisfies a preset construction condition, the preset construction condition is used to determine the feasibility of the materialized view construction request according to whether the query statement of the materialized view contains an aggregation operation and / or whether a sub-statement of the query statement of the materialized view refers to a basic table, the target type includes a first type or a second type, the first type is used to identify a table with a unique constraint condition, and the second type is used to identify a table storing aggregate data, and when it is determined that the materialized view construction request is executable, a materialized view of the target basic table is constructed based on the target query statement.
[0072] It can be seen that the embodiment of the present disclosure determines, for a base table belonging to a target type, whether a materialized view construction request for the base table meets a preset construction condition by determining the aggregation operation contained in the query statement of the materialized view and the base table referenced by the sub-statement of the query statement. If the preset construction condition is met, the materialized view is supported to be constructed for the base table, so as to reduce the probability of inconsistency between the data in the base table and the data in the materialized view when the data is updated. BRIEF DESCRIPTION OF THE DRAWINGS
[0073] The above and other features, advantages and aspects of the embodiments of the present disclosure will become more apparent with reference to the following detailed description in conjunction with the accompanying drawings. Throughout the accompanying drawings, the same or similar reference numerals represent the same or similar elements. It should be understood that the drawings are schematic and the originals and elements are not necessarily drawn to scale.
[0074] Figure 1 A schematic diagram of a process for constructing a materialized view in a database system provided by an embodiment of the present disclosure;
[0075] Figure 2 A schematic diagram of a materialized view construction process in a database system provided in an embodiment of the present disclosure;
[0076] Figure 3 A schematic diagram of the structure of a materialized view building device in a database system provided by an embodiment of the present disclosure;
[0077] Figure 4 A schematic diagram of the structure of a materialized view building device in a database system provided in an embodiment of the present disclosure. DETAILED DESCRIPTION
[0078] Embodiments of the present disclosure will be described in more detail below with reference to the accompanying drawings. Although certain embodiments of the present disclosure are shown in the accompanying drawings, it should be understood that the present disclosure can be implemented in various forms and should not be construed as being limited to the embodiments described herein, which are instead provided for a more thorough and complete understanding of the present disclosure. It should be understood that the drawings and embodiments of the present disclosure are only for exemplary purposes and are not intended to limit the scope of protection of the present disclosure.
[0079] It should be understood that the various steps described in the method embodiments of the present disclosure may be performed in different orders and / or in parallel. In addition, the method embodiments may include additional steps and / or omit the steps shown. The scope of the present disclosure is not limited in this respect.
[0080] The term "including" and its variations used herein are open inclusions, i.e., "including but not limited to". The term "based on" means "based at least in part on". The term "one embodiment" means "at least one embodiment"; the term "another embodiment" means "at least one additional embodiment"; the term "some embodiments" means "at least some embodiments". The relevant definitions of other terms will be given in the following description.
[0081] It should be noted that the concepts such as "first" and "second" mentioned in the present disclosure are only used to distinguish different devices, modules or units, and are not used to limit the order or interdependence of the functions performed by these devices, modules or units.
[0082] It should be noted that the modifications of "one" and "plurality" mentioned in the present disclosure are illustrative rather than restrictive, and those skilled in the art should understand that unless otherwise clearly indicated in the context, it should be understood as "one or more".
[0083] The names of the messages or information exchanged between multiple devices in the embodiments of the present disclosure are only used for illustrative purposes and are not used to limit the scope of these messages or information.
[0084] Currently, users can define the query statements and data storage methods of materialized views through base tables according to their needs. The database system pre-calculates the query results based on the definition of the materialized view and stores the results in the materialized view. When the base table data changes, the materialized view also needs to be updated to determine the accuracy of the data. During the query process, the database system will optimize the query based on the content stored in the materialized view to improve query efficiency.
[0085] However, creating a materialized view for a base table whose table model is unique or aggregate will cause inconsistency between the data in the base table and the materialized view when the data is updated.
[0086] For example, assuming that the table model of the base table is unique, the base table is base_tbl, base_tbl has three columns a, b, and c, where a and b are key columns, c is the value column, and there are two records in the base_tbl table: (0, 0, 0) and (0, 1, 1).
[0087] If the table model of the materialized view is also unique, the definition of the materialized view is:
[0088] create mv as select a,c+1as c1from base_tbl;
[0089] This means creating a materialized view named mv. The data of the materialized view comes from the table base_tbl. Column a is selected from the table base_tbl as the column a of the materialized view, and column c1 of the materialized view is obtained by adding c+1 in the table base_tbl. It can be seen that a of the materialized view is the key column and c1 is the value column.
[0090] According to the data in the base table and the definition of the materialized view, there should be two records (0, 1) and (0, 2) in the materialized view. However, since the table model of the materialized view is a unique table with column a as the key column, these two records cannot exist in the materialized view at the same time. Therefore, the data in the materialized view and the base table are inconsistent.
[0091] Therefore, for the tables whose table models are unique and aggregate, how to create materialized views to ensure that the data in the base table and the materialized view are consistent when the data is updated is a technical problem that needs to be solved.
[0092] To this end, an embodiment of the present disclosure provides a materialized view construction method in a database system. Specifically, first, a materialized view construction request for a target basic table is received, and the materialized view construction request carries a target query statement for constructing a materialized view of the target basic table. Then, after determining that the target basic table belongs to a target type, it is determined whether the materialized view construction request satisfies a preset construction condition. The preset construction condition is used to determine the feasibility of the materialized view construction request according to whether the query statement of the materialized view contains an aggregation operation and / or whether a sub-statement of the query statement of the materialized view refers to a basic table. The target type includes a first type or a second type. The first type is used to identify a table with a unique constraint condition, and the second type is used to identify a table storing aggregate data. When it is determined that the materialized view construction request is executable, a materialized view of the target basic table is constructed based on the target query statement.
[0093] It can be seen that the embodiment of the present disclosure determines, for a base table belonging to a target type, whether a materialized view construction request for the base table meets a preset construction condition by determining the aggregation operation contained in the query statement of the materialized view and the base table referenced by the sub-statement of the query statement. If the preset construction condition is met, the materialized view is supported to be constructed for the base table, so as to reduce the probability of inconsistency between the data in the base table and the data in the materialized view when the data is updated.
[0094] Based on this, an embodiment of the present disclosure provides a materialized view construction method in a database system. The materialized view construction method in the database system can be applied in the database system. The method is introduced below in conjunction with a specific embodiment.
[0095] Figure 1 The present invention provides a flow chart of a materialized view construction method in a database system, which can be executed by a materialized view construction device in a database system, wherein the device can be implemented by software and / or hardware, and can generally be integrated in an electronic device. Figure 1 As shown, the method includes:
[0096] S101: receiving a materialized view construction request for a target basic table.
[0097] The materialized view building request carries a target query statement for building a materialized view of the target basic table.
[0098] The target basic table in the embodiment of the present disclosure is a basic table of the database system, also called a base table or a basic table. The target basic table is the most basic storage unit in the database and is used to store actual data.
[0099] The materialized view building request in the embodiment of the present disclosure is an instruction to create a materialized view, and the target query statement carried in the materialized view building request is a statement to create the materialized view.
[0100] S102: After determining that the target basic table belongs to the target type, determining whether the materialized view construction request satisfies a preset construction condition.
[0101] The preset construction condition is used to determine the feasibility of the materialized view construction request based on whether the query statement of the materialized view contains an aggregation operation and / or whether a sub-statement of the query statement of the materialized view references a base table. The target type includes a first type or a second type. The first type is used to identify a table with a unique constraint condition, and the second type is used to identify a table that stores aggregate data.
[0102] In the embodiment of the present disclosure, if the target basic table belongs to the first type, it means that the value of a certain column or a combination of multiple columns in the target basic table is unique, wherein the target basic table may include a table whose table model is unique, that is, if the target basic table belongs to the first type, the table model of the target basic table is a unique model;
[0103] If the target basic table belongs to the second type, it means that the target basic table is a table with aggregation function, that is, the table belonging to the second type has the function of aggregating the data in the table, wherein the table belonging to the second type may include a table whose table model is aggregate, that is, if the target basic table belongs to the first type, the table model of the target basic table is an aggregate model;
[0104] In the embodiment of the present disclosure, after determining the target type of the target basic table, it is determined whether the materialized view construction request of the target basic table meets the preset construction condition.
[0105] The preset construction condition is used to determine whether a materialized view can be created for the target basic table.
[0106] In an optional implementation, the preset construction condition can be used to determine the feasibility of the materialized view construction request according to whether the query statement of the materialized view contains an aggregation operation, that is, to determine the feasibility of the materialized view construction request by judging whether the query statement carried in the materialized view construction request contains an aggregation operation. In other words, by judging whether the query statement of the materialized view contains an aggregation operation, the feasibility of the materialized view construction request is determined, and then it is determined whether the materialized view construction request meets the preset construction condition.
[0107] The aggregation operation may include aggregation operations such as data statistics and calculations. Specifically, the aggregation operation may include statements with aggregation functions such as group by statements. In addition, the aggregation operation may also be implemented through aggregation functions, which may include count functions and sum functions in the database system.
[0108] In another optional implementation, the preset construction condition can be used to determine the feasibility of the materialized view construction request according to the situation of the base table referenced by the sub-statements of the materialized view query statement carried in the materialized view construction request. The materialized view query statement may include a query (select) sub-statement, a condition (where) sub-statement, etc. In other words, by judging the reference situation of each sub-statement in the materialized view query statement to the base table, the feasibility of the materialized view construction request is determined, and then it is determined whether the materialized view construction request meets the preset construction condition. The reference situation of each sub-statement in the query statement to the base table may include the situation of each sub-statement in the query statement referencing the content of the base table.
[0109] In another optional implementation, the preset construction condition can also be used to determine the feasibility of the materialized view construction request based on whether the query statement of the materialized view contains aggregation operations and whether the sub-statements of the query statement reference the base table, and then determine whether the materialized view construction request meets the preset construction condition.
[0110] S103: When it is determined that the materialized view building request is executable, a materialized view of the target basic table is built based on the target query statement.
[0111] The materialized view has the same table model as the target base table, that is, if the target base table is of the first type, the materialized view of the target base table is of the first type, and if the target base table is of the second type, the materialized view of the target base table is of the second type.
[0112] In the embodiment of the present disclosure, by determining that the materialized view construction request is executable and determining that the materialized construction request of the target base table meets the preset construction conditions, the materialized view of the target base table can be constructed based on the target query statement of the materialized view of the target base table carried in the materialized construction request for pre-calculation and storage of data.
[0113] The materialized view construction method in a database system provided by an embodiment of the present disclosure specifically comprises the following steps: first, a materialized view construction request for a target basic table is received, wherein the materialized view construction request carries a target query statement for constructing a materialized view of the target basic table; then, after determining that the target basic table belongs to a target type, determining whether the materialized view construction request satisfies a preset construction condition; the preset construction condition is used to determine the feasibility of the materialized view construction request according to whether the query statement of the materialized view contains an aggregation operation and / or whether a sub-statement of the query statement of the materialized view refers to a basic table; the target type includes a first type or a second type, wherein the first type is used to identify a table with a unique constraint condition, and the second type is used to identify a table storing aggregate data; when it is determined that the materialized view construction request is executable, a materialized view of the target basic table is constructed based on the target query statement.
[0114] It can be seen that the embodiment of the present disclosure determines, for a base table belonging to a target type, whether a materialized view construction request for the base table meets a preset construction condition by determining the aggregation operation contained in the query statement of the materialized view and the base table referenced by the sub-statement of the query statement. If the preset construction condition is met, the materialized view is supported to be constructed for the base table, so as to reduce the probability of inconsistency between the data in the base table and the data in the materialized view when the data is updated.
[0115] In an optional implementation, when a materialized construction request for a target base table is received, it is determined whether the target base table belongs to the target type based on the table model of the target base table. If it is determined that the target base table belongs to the first type, it is determined whether the target query statement carried in the materialized construction request for the target base table includes an aggregation operation. If it is determined that the target query statement includes an aggregation operation, it is determined that the materialized view construction request for the target base table is not feasible, that is, the materialized view construction request for the target base table does not meet the preset construction conditions, that is, it is impossible to build a materialized view for the target base table based on the target query statement.
[0116] In another optional implementation, if it is determined that the target query statement of the materialized view of the target basic table does not contain an aggregation operation, it is further determined whether the target query statement meets the first condition matching item in the preset construction condition, wherein the first condition matching item can be used to determine the feasibility of the materialized view construction request according to the type of the sub-statement of the query statement of the materialized view and the situation that the sub-statement references the column in the basic table, wherein the sub-statement of the query statement of the materialized view may include query sub-statements, condition sub-statements, etc., that is, by judging whether the query list sub-statements, condition sub-statements, etc. in the query statement of the materialized view are referenced to the columns in the basic table, it is determined whether the target query statement meets the first condition matching item in the preset construction condition, that is, the first condition matching item includes the condition for judging whether the query list sub-statements, condition sub-statements, etc. in the query statement of the materialized view are referenced to the columns in the basic table.
[0117] In another optional implementation, the first condition matching item can also be used to determine the feasibility of the materialized view construction request according to whether the columns in the materialized view query statement participate in the calculation. That is, the first condition matching item also includes a condition for determining whether the key column of the target base table participates in the calculation in the target query statement.
[0118] Specifically, on the basis of determining that the target query statement of the materialized view of the target base table does not contain an aggregation operation, it is also necessary to determine whether the key columns of the target base table all belong to the columns referenced by the query list substatements in the target query statement, and whether the key columns of the target base table participate in the calculation in the target query statement, and whether the conditional substatements in the target query statement reference the value columns of the target base table.
[0119] Among them, whether the key columns of the target basic table all belong to the columns referenced by the query list sub-statements in the target query statement is used to determine whether the key columns of the target basic table all belong to the columns of the materialized view in the target query statement of the materialized view of the target basic table. Among them, the key column of the basic table is also called the key column, which is used to store the unique identifier of the data, and the value column of the basic table is also called the value column, which is used to store the actual data value related to the key column.
[0120] In practical applications, after determining that the target query statement of the materialized view of the target basic table does not contain an aggregation operation, it is necessary to first determine whether the target basic table contains a target monotonically increasing column. The target monotonically increasing column belongs to the value column of the target basic table and is used to trigger the update of the target basic table, and then determine whether the target query statement meets the first condition matching item in the preset construction condition.
[0121] In an optional implementation, after determining that the target query statement of the materialized view of the target base table does not contain an aggregation operation, if it is determined that the target base table does not contain a target monotonically increasing column, it can be determined whether the target query statement meets the first condition matching item in the preset construction condition.
[0122] In another optional implementation, after determining that the target query statement of the materialized view of the target base table does not contain an aggregation operation, if it is determined that the target base table contains a target monotonically increasing column, it is determined whether the query list substatement of the target query statement references the target monotonically increasing column; if it is determined that the query list substatement of the target query statement does not reference the target monotonically increasing column, it can be determined that the materialized view construction request for the target base table cannot be executed.
[0123] If it is determined that the query list sub-statement of the target query statement references the target monotonically increasing column, it may be further determined whether the target query statement meets the first condition matching item in the preset construction condition.
[0124] Among them, the query list substatement of the target query statement references the target monotonically increasing column, indicating that the monotonically increasing column contained in the target base table belongs to the query list substatement in the query statement of the materialized view of the target base table, and it can also indicate that the materialized view in the target base table contains the monotonically increasing column, and the monotonically increasing column is used to trigger the update of the materialized view.
[0125] Based on the content of the above embodiment, if it is determined that the target query statement of the target base table does not contain an aggregation operation, and the target base table does not contain a target monotonically increasing column or the target base table contains a target monotonically increasing column and the query list substatement of the target query statement references the target monotonically increasing column, and the key columns of the target base table all belong to the columns referenced by the query list substatements in the target query statement and the key columns of the target base table do not participate in the calculation in the target query statement, and the conditional substatement in the target query statement does not reference the value column of the target base table, then it can be determined that the materialized view construction request is executable, that is, the materialized view of the target base table can be constructed based on the target query statement.
[0126] The conditional sub-statement in the target query statement does not reference the value column of the target basic table, which may mean that the conditional sub-statement in the target query statement can only reference the key column of the target basic table.
[0127] That is, when the target basic table is of the first type, it can be determined that the materialized view construction request of the target basic table is executable only when the above conditions are met at the same time.
[0128] For example, assume that the target base table base_tbl contains column b as the key column and column c as the value column.
[0129] 1. If the target query statement of the materialized view of the target base table is:
[0130] create mv as select a,c+1as c1from base_tbl; it means to create a materialized view named mv. The data in the materialized view is obtained by executing a query on the base_tbl table. The query selects column a in the table and calculates c+1 to name the result as a new column c1.
[0131] According to the condition contained in the first condition matching item for determining whether each key column of the target base table belongs to the column referenced by the target query statement of the materialized view of the target base table, it can be known that the key column (column b) of the target base table does not belong to the column of the materialized view defined by the target query statement, that is, the materialized view construction request of the target base table does not meet the first condition matching item, and the materialized view construction request of the target base table cannot be executed.
[0132] 2. If the target query statement of the materialized view of the target base table is:
[0133] create mv as select b,c+1as c1from base_tbl;
[0134] According to the conditions contained in the first condition matching item for determining whether all key columns of the target basic table belong to the columns referenced by the target query statement of the materialized view of the target basic table and the key columns of the target basic table do not participate in the calculation in the target query statement, it can be known that the key column (column b) of the target basic table belongs to the column referenced by the target query statement and the key column does not participate in the calculation in the target query statement, then other conditions in the first condition matching item can be determined.
[0135] 3. If the target query statement of the materialized view of the target base table is:
[0136] create mv as select b+1as b1from base_tbl; it means to create a materialized view named mv. The data source of the materialized view is the query operation on the base_tbl table. It selects the column that calculates b+1 and names the result as b1.
[0137] According to the condition contained in the first condition matching item for determining whether each key column of the target base table belongs to the column referenced by the target query statement of the materialized view of the target base table and the key columns of the target base table do not participate in the calculation in the target query statement, it can be known that the key column (column b) in the target base table participates in the calculation in the target query statement, that is, the materialized view construction request of the target base table does not meet the first condition matching item, and the materialized view construction request of the target base table cannot be executed.
[0138] 4. If the target query statement of the materialized view of the target base table is:
[0139] create mv as select b from base_tbl where c>0; it means to create a materialized view named mv. The data in the materialized view is the value of column b that satisfies the condition c>0 selected from the base_tbl table.
[0140] According to the condition in the first condition matching item for determining whether the conditional sub-statement in the target query statement references the value column of the target base table, it can be known that the where sub-statement in the target query statement of the target base table references the value column, then the materialized view construction request of the target base table does not meet the first condition matching item, and the materialized view construction request of the target base table cannot be executed.
[0141] 5. If the target query statement of the materialized view is:
[0142] create mv as select b from base_tbl where a>0; it means to create a materialized view named mv. The data in the materialized view is the value of column b that satisfies the condition a>0 selected from the base_tbl table.
[0143] According to the condition in the first condition matching item for determining whether the conditional substatement in the target query statement references the value column of the target basic table, it can be known that the conditional substatement does not reference the value column, and it can be known that the materialized view construction request of the target basic table satisfies the condition, and it can be further determined whether the target query statement satisfies other conditions in the first condition matching item;
[0144] In actual applications, when a materialized construction request for a target basic table is received, after determining that the table model of the target basic table belongs to the second type based on the table model of the target basic table, it is also necessary to determine whether the target query statement of the materialized view of the target basic table contains an aggregation operation. If it is determined that the target query statement of the materialized view of the target basic table does not contain an aggregation operation, it is then determined whether the target query statement meets the third condition matching item in the preset construction condition. The third condition matching item is used to determine the feasibility of the materialized view construction request according to the type of the sub-statement of the query statement of the materialized view and the situation that the sub-statement references the column in the basic table.
[0145] Specifically, the third condition matching items may include a condition for determining whether the aggregate key columns in the materialized view in the target query statement all belong to the aggregate key columns in the target base table, a condition for determining whether the conditional sub-statement in the target query statement references the value column of the target base table, and a condition for determining whether the value column of the target base table participates in the calculation in the target query statement.
[0146] In practical applications, after determining that the target query statement of the materialized view of the target basic table does not contain an aggregation operation, it is possible to first determine whether the target basic table contains a target monotonically increasing column. If it is determined that the target basic table contains the target monotonically increasing column, it is then determined whether the query list sub-statement in the target query statement references the target monotonically increasing column, that is, whether the target monotonically increasing column belongs to a column in the query list sub-statement in the target query statement. If it is determined that the query list sub-statement in the target query statement references the target monotonically increasing column, it is further determined whether the target query statement meets the third condition matching item in the preset construction condition. The query list sub-statement is used to define the columns in the materialized view.
[0147] In another optional implementation, if it is determined that the target basic table does not contain the target monotonically increasing column, it can be determined whether the target query statement meets the third condition matching item in the preset construction condition to determine the feasibility of the materialized view construction request.
[0148] In practical applications, the third condition matching item can also be used to determine the feasibility of the materialized view construction request according to the calculation participation of the columns in the materialized view query statement. Specifically, the third condition matching item also includes a condition for determining whether the value column of the target basic table participates in the calculation in the target query statement.
[0149] Based on the conditions contained in the above-mentioned third condition matching item, when the target query statement of the target base table does not contain an aggregation operation, and the target base table does not contain a target monotonically increasing column or the target base table contains a target monotonically increasing column and the query list substatement of the target query statement references the target monotonically increasing column, and the aggregate key columns of the materialized view in the target query statement all belong to the aggregate key columns in the target base table, and the conditional substatement in the target query statement does not reference the value column of the target base table, and the value column of the target base table does not participate in the calculation in the target query statement, it can be determined that the materialized view construction request is executable.
[0150] In another optional implementation, when a materialized construction request for a target base table is received, it is determined whether the target base table belongs to the target type based on the table model of the target base table. If it is determined that the target base table belongs to the second type, it is determined whether the target query statement in the materialized view construction request of the target base table contains an aggregation operation. If it is determined that the target query statement of the materialized view of the target base table contains an aggregation operation, it is determined whether the target query statement meets the second condition matching item in the preset construction condition.
[0151] Among them, the second condition matching item is used to determine the feasibility of the materialized view construction request based on at least two of the types of sub-statements of the materialized view query statement, the situation of the sub-statement referencing the columns in the basic table, and the aggregation operation satisfying the rule, and the aggregation operation satisfying the rule is a rule set for judging the aggregation operation.
[0152] Specifically, the second condition matching items may include the condition that whether the aggregate key columns of the materialized view in the query statement all belong to the aggregate key columns in the target basic table, the condition that whether the conditional sub-statement in the query statement references the value column of the basic table, the condition that whether the key column referenced by the group by sub-statement in the query statement belongs to the key column of the basic table, the condition that whether the parameter of the aggregate operation in the query statement comes from the value column of the basic table and the value column does not participate in the calculation of the parameter of the aggregate operation, the condition that whether the aggregate operation in the query statement satisfies the condition of the target calculation rule, and the condition that whether the aggregate operation in the query statement is consistent with the aggregate operation of the column corresponding to the aggregate operation in the basic table. Among them, the target calculation rule includes the commutative law and the associative law.
[0153] Among them, it is determined whether the aggregate key columns of the materialized view of the target query statement all belong to the aggregate key columns of the target base table, that is, it is determined whether the aggregate key columns of the materialized view are a subset of the aggregate key columns of the target base table. It is determined whether the key columns referenced by the grouping sub-statements in the target query statement belong to the key columns of the target base table, that is, it is determined whether the key columns referenced by the grouping sub-statements in the target query statement of the materialized view are a subset of the key columns of the target base table.
[0154] In an optional implementation, if it is determined that the aggregate key columns of the materialized view of the target query statement all belong to the aggregate key columns of the target base table, and the conditional sub-statement in the target query statement does not reference the value column of the target base table, and the key column referenced by the grouping sub-statement in the target query statement belongs to the key column of the target base table, and the parameters of the aggregation operation in the target query statement come from the value column of the target base table and the value column does not participate in the calculation in the parameter, and the aggregation operation in the target query statement satisfies the preset calculation rules, and the aggregation operation in the target query statement is consistent with the aggregation operation of the column corresponding to the aggregation operation in the target base table, then it can be determined that the target query statement meets the second condition matching item in the preset construction condition.
[0155] For example, assume that the base table base_tbl has 3 columns, a and b are key columns, and c is the value column. The base table belongs to the second type, and the aggregate function of column c is the sum function. Take the query statement of the base table to determine whether it meets the second condition match item in the preset construction condition as an example.
[0156] The query statement for the materialized view is:
[0157] create mv as(select a,sum(c)s from base_tbl group by a)aggregate key(a);
[0158] This means creating a materialized view named mv, whose data comes from the query of the base_tbl table, selecting column a and summing column c to get s, grouping by column a, and specifying a as the aggregation key.
[0159] According to the second condition matching item, the key column of the materialized view defined by the query statement belongs to the key column of the base table, the key column referenced by the group by sub-statement in the query statement belongs to the key column of the base table, and the parameter of the aggregation operation in the query statement comes from the value column of the base table, and the value column does not participate in the calculation of the parameter, and the aggregation operation in the query statement satisfies the target calculation rule, and the aggregation operation in the query statement is consistent with the aggregation operation of the column corresponding to the aggregation operation in the base table. It can be determined that the target query statement meets the second condition matching item in the preset construction condition.
[0160] In practical applications, on the basis of determining that the target query statement of the materialized view of the target basic table contains an aggregation operation, it is also possible to first determine whether the target basic table contains a target monotonically increasing column. Specifically, on the basis of determining that the target query statement contains an aggregation operation, it is determined whether the target basic table contains a target monotonically increasing column. If it is determined that the target basic table does not contain the target monotonically increasing column, it is determined whether the target query statement of the materialized view of the target basic table meets the second condition matching item in the preset construction condition. Among them, the target monotonically increasing column belongs to the value column of the target basic table and is used to trigger the update of the target basic table.
[0161] In an optional implementation, based on determining that the target query statement of the materialized view of the target basic table includes an aggregation operation, if the target basic table does not contain the target monotonically increasing column, and it is determined that the aggregation key columns of the materialized view of the target query statement all belong to the aggregation key columns of the target basic table, and the conditional sub-statement in the target query statement does not reference the value column of the target basic table, and the key column referenced by the grouping sub-statement in the target query statement belongs to the key column of the target basic table, and the parameter of the aggregation operation in the target query statement comes from the value column of the target basic table and the value column does not participate in the calculation in the parameter, and the aggregation operation in the target query statement satisfies the preset calculation rules, and the aggregation operation in the target query statement is consistent with the aggregation operation of the column corresponding to the aggregation operation in the target basic table, then it can be determined that the target query statement meets the second condition matching item in the preset construction condition.
[0162] In actual applications, when a materialized view construction request for a base table is received, it is determined whether the materialized view construction request meets the preset construction conditions based on the table model of the target base table. If it is determined that the materialized view construction request for the target base table is not executable, that is, the materialized view construction request does not meet the preset construction conditions, in order to help the user create a materialized view of the target base table, the embodiment of the present disclosure can also support the function of modifying the target query statement.
[0163] In an optional implementation, when it is determined that the materialized view building request of the target base table is not executable, the target query statement is modified based on the preset building condition so that the modified target query statement meets the preset building condition, and then the modified target query statement is displayed. If a materialized view building request triggered by the modified target query statement is received, the materialized view of the target base table is built based on the modified target query statement.
[0164] Among them, the target query statement is modified based on the preset construction condition. Specifically, the condition matching items that the target query statement does not meet can be determined in the preset construction condition, and whether the target query statement meets the preset modification condition is determined based on the condition matching items. The preset modification condition is used to determine whether the target query statement can be modified based on the condition matching items that the target query statement does not meet, so that the target query statement meets the preset construction condition, even if the materialized view construction request of the target basic table is executable.
[0165] In an optional implementation, if the target query statement of the target basic table meets the preset modification condition, the target query statement is modified based on the condition matching item so that the modified target query statement meets the preset construction condition.
[0166] In another optional implementation, if the target query statement of the target basic table does not meet the preset modification conditions, a modification prompt message is displayed to prompt the user to manually modify the target query statement.
[0167] For example, assume that the base table base_tbl has three columns, a and b are key columns, and c is the value column.
[0168] 1. Take the base_tbl basic table belonging to the first type as an example. If the query statement of the materialized view is:
[0169] create mv as select a from base_tbl where a>0;
[0170] According to the first condition matching item, it can be known that the key column (column b) of the basic table does not belong to the column referenced by the above query statement, that is, the key column (column b) of the basic table does not meet the first condition matching item, indicating that the materialized view construction request does not meet the preset construction condition. Based on the preset modification condition, the above query statement is modified to:
[0171] create mv as select a,b from base_tbl where a>0;
[0172] 2. Take the case where the base table belongs to the second type, and the aggregate key in the base table is a. If the query statement of the materialized view is:
[0173] create mv as(select a,sum(c)s from base_tbl group by a)aggregate key(a,s);
[0174] This means creating a materialized view named mv. The data of the materialized view comes from the query of the base_tbl table. Column a in the base_tbl table is selected, and column s is obtained by summing column c in the base_tbl table. The data is grouped according to column a, and a and s are specified as aggregation keys.
[0175] According to the second condition matching item in the preset construction condition, it can be known that the aggregate key column of the materialized view in the query statement does not belong to the aggregate key column of the base table, that is, the s column in the aggregate key in the query statement of the materialized view is not the aggregate key in the base table, that is, the second condition matching item is not satisfied, so the query statement is modified, and the modified query statement is:
[0176] create mv as(select a,sum(c)s from base_tbl group by a)aggregate key(a);
[0177] 3. Assuming that the base table belongs to the second type, the parameter in the aggregate function in the query statement of the materialized view of the base table is the key column in the base table. According to the second condition matching item in the preset construction condition, it can be seen that the query statement does not meet the preset construction condition. Since it is impossible to determine which value column in the base table should be replaced by the parameter key column in the aggregate function in the query statement based on the preset modification condition, the query statement does not meet the preset modification condition.
[0178] In order to facilitate understanding of the materialized view construction method in the database system provided by the present disclosure, the present disclosure also provides a schematic diagram of the materialized view construction process in the database system, with reference to Figure 2 .
[0179] First, when a materialized view construction request for a target base table is received, it is determined whether the target query statement of the materialized view meets the preset construction conditions based on the target type of the target base table and the preset construction conditions according to the target query statement carried in the materialized view construction request.
[0180] In an optional implementation, if it is determined that the target query statement meets the preset construction condition, a materialized view of the target basic table is constructed based on the target query statement.
[0181] In another optional implementation, if it is determined that the target query statement does not meet the preset construction conditions, the target query statement is modified based on the preset modification conditions, the modified target query statement is displayed, and when a materialized view construction request triggered for the modified target query statement is received, a materialized view of the target base table is constructed based on the modified target query statement.
[0182] In order to implement the above embodiment, the present disclosure also proposes a materialized view construction device in a database system. Figure 3 This is a schematic diagram of the structure of a materialized view construction device provided by an embodiment of the present disclosure. The device can be implemented by software and / or hardware and can generally be integrated into an electronic device. Figure 3 As shown, the device comprises:
[0183] The first receiving module 301 is used to receive a materialized view construction request for a target basic table; the materialized view construction request carries a target query statement for constructing a materialized view of the target basic table;
[0184] A first determination module 302 is used to determine whether the materialized view construction request satisfies a preset construction condition after determining that the target basic table belongs to a target type; the preset construction condition is used to determine the feasibility of the materialized view construction request according to whether the query statement of the materialized view contains an aggregation operation and / or whether a sub-statement of the query statement of the materialized view references a basic table, the target type includes a first type or a second type, the first type is used to identify a table with a unique constraint condition, and the second type is used to identify a table storing aggregate data;
[0185] The first construction module 303 is configured to construct the materialized view of the target basic table based on the target query statement when it is determined that the materialized view construction request is executable.
[0186] In an optional implementation manner, the first determining module includes:
[0187] A first determination submodule, configured to determine whether the target query statement includes an aggregation operation after determining that the target basic table belongs to the first type;
[0188] The second determination submodule is used to determine whether the target query statement meets the first condition matching item in the preset construction condition after determining that the target query statement does not contain an aggregation operation; the first condition matching item is used to determine the feasibility of the materialized view construction request based on the type of the sub-statement of the materialized view query statement and the situation of the sub-statement referencing the column in the basic table.
[0189] In an optional implementation manner, the first determining module includes:
[0190] A third determination submodule is used to determine whether the target query statement contains an aggregation operation after determining that the target basic table belongs to the second type;
[0191] A fourth determination submodule is used to determine whether the target query statement meets the second condition matching item in the preset construction condition after determining that the target query statement includes an aggregation operation; the second condition matching item is used to determine the feasibility of the materialized view construction request according to at least two of the type of the sub-statement of the materialized view query statement, the situation that the sub-statement references the column in the basic table, and the situation that the aggregation operation satisfies the rule;
[0192] The fifth determination submodule is used to determine whether the target query statement meets the third condition matching item in the preset construction condition after determining that the target query statement does not contain an aggregation operation; the third condition matching item is used to determine the feasibility of the materialized view construction request based on the type of the sub-statement of the materialized view query statement and the situation of the sub-statement referencing the column in the basic table.
[0193] In an optional embodiment, the device comprises:
[0194] A second determination module is used to determine whether the target basic table contains a target monotonically increasing column after determining that the target query statement does not contain an aggregation operation; the target monotonically increasing column belongs to the value column of the target basic table and is used to trigger the update of the target basic table;
[0195] Accordingly, the second determining submodule is specifically used for:
[0196] After determining that the target basic table includes a target monotonically increasing column, it is determined whether a query list sub-statement of the target query statement references the target monotonically increasing column.
[0197] In an optional implementation manner, the first condition matching item is further used to determine the feasibility of the materialized view construction request according to the participation of the columns in the materialized view query statement in the calculation, and the second determination submodule includes:
[0198] A sixth determination submodule is used to determine whether the key columns of the target basic table all belong to the columns referenced by the query list substatement in the target query statement, and whether the key columns of the target basic table participate in the calculation in the target query statement after determining that the target query statement does not contain an aggregation operation;
[0199] A seventh determination submodule, used to determine whether a conditional substatement in the target query statement references a value column of the target basic table;
[0200] Accordingly, the first building module is specifically used for:
[0201] When the following conditions are met at the same time, a materialized view of the target basic table is constructed based on the target query statement;
[0202] The conditions include:
[0203] Determining that the target query statement does not include an aggregation operation;
[0204] The target basic table does not include a target monotonically increasing column, or the target basic table includes a target monotonically increasing column and a query list sub-statement of the target query statement references the target monotonically increasing column;
[0205] The key columns of the target basic table all belong to the columns referenced by the query list sub-statements in the target query statement and the key columns of the target basic table do not participate in the calculation in the target query statement;
[0206] The conditional sub-statement in the target query statement does not reference the value column of the target basic table.
[0207] In an optional implementation, the device further includes:
[0208] The third determination module is used to determine whether the target basic table includes a target monotonically increasing column after determining that the target basic table belongs to the second type; the target monotonically increasing column belongs to the value column of the target basic table and is used to trigger the update of the target basic table.
[0209] In an optional implementation manner, the fifth determining submodule is specifically configured to:
[0210] After determining that the target query statement does not include an aggregation operation and the target base table includes a target monotonically increasing column, it is determined whether a query list sub-statement in the target query statement references the target monotonically increasing column.
[0211] In an optional implementation manner, the third condition matching item is further used to determine the feasibility of the materialized view construction request according to the participation of the columns in the materialized view query statement in the calculation, and the fifth determination submodule includes:
[0212] An eighth determination submodule, used to determine whether the aggregate key columns of the materialized view in the target query statement all belong to the aggregate key columns in the target basic table;
[0213] A ninth determination submodule, used to determine whether a conditional substatement in the target query statement references a value column of the target basic table;
[0214] a tenth determination submodule, used to determine whether the value column of the target basic table participates in the calculation in the target query statement;
[0215] Accordingly, the first building module is specifically used for:
[0216] When the following conditions are met at the same time, a materialized view of the target basic table is constructed based on the target query statement;
[0217] The conditions include:
[0218] The target query statement does not contain an aggregation operation;
[0219] The target basic table does not include a target monotonically increasing column, or the target basic table includes a target monotonically increasing column and a query list sub-statement of the target query statement references the target monotonically increasing column;
[0220] The aggregate key columns of the materialized view in the target query statement all belong to the aggregate key columns in the target basic table;
[0221] The conditional sub-statement in the target query statement does not reference the value column of the target basic table;
[0222] The value column of the target basic table does not participate in the calculation in the target query statement.
[0223] In an optional implementation manner, the fourth determining submodule is specifically configured to:
[0224] After determining that the target query statement includes an aggregation operation and the target base table includes a target monotonically increasing column, it is determined whether a query list sub-statement in the target query statement references the target monotonically increasing column.
[0225] In an optional implementation manner, the second condition matching item is further used to determine the feasibility of the materialized view construction request according to the column parameter calculation status in the materialized view query statement, and the fourth determination submodule includes:
[0226] An eleventh determination submodule is used to determine whether the aggregate key columns of the materialized view in the target query statement all belong to the aggregate key columns in the target basic table;
[0227] A twelfth determination submodule, used to determine whether a conditional substatement in the target query statement references a value column of the target basic table;
[0228] A thirteenth determination submodule is used to determine whether the key column referenced by the grouping substatement in the target query statement belongs to the key column of the target basic table;
[0229] A fourteenth determination submodule is used to determine whether the parameters of the aggregation operation in the target query statement are from the value column of the target basic table, and the value column does not participate in the calculation of the parameters of the aggregation operation;
[0230] A fifteenth determination submodule is used to determine whether the aggregation operation in the target query statement satisfies a target calculation rule; the target calculation rule includes a commutative law and an associative law;
[0231] The sixteenth determination submodule is used to determine whether the aggregation operation in the target query statement is consistent with the aggregation operation of the column corresponding to the aggregation operation in the target basic table.
[0232] In an optional implementation, the device further includes:
[0233] a fourth determining module, configured to modify the target query statement based on the preset construction condition when determining that the materialized view construction request is not executable, so that the modified target query statement satisfies the preset construction condition;
[0234] A display module, used to display the modified target query statement;
[0235] The second construction module is used to respond to the materialized view construction request triggered by the modified target query statement and to construct the materialized view of the target basic table based on the modified target query statement.
[0236] In an optional implementation, the device further includes:
[0237] A fifth determination module, configured to determine a condition matching item that the target query statement does not meet in the preset construction condition, and determine whether the target query statement meets a preset modification condition based on the condition matching item;
[0238] Accordingly, the second building module is specifically used for:
[0239] If the target query statement meets the preset modification condition, the target query statement is modified based on the condition matching item so that the modified target query statement meets the preset construction condition.
[0240] In a materialized view building device in a database system provided by an embodiment of the present disclosure, first, a materialized view building request for a target basic table is received, the materialized view building request carries a target query statement for building a materialized view of the target basic table, and then, after determining that the target basic table belongs to a target type, it is determined whether the materialized view building request satisfies a preset building condition, the preset building condition is used to determine the feasibility of the materialized view building request according to whether the query statement of the materialized view contains an aggregation operation and / or whether a sub-statement of the query statement of the materialized view refers to a basic table, the target type includes a first type or a second type, the first type is used to identify a table with a unique constraint condition, and the second type is used to identify a table storing aggregate data, and when it is determined that the materialized view building request is executable, a materialized view of the target basic table is built based on the target query statement.
[0241] It can be seen that the embodiment of the present disclosure determines, for a base table belonging to a target type, whether a materialized view construction request for the base table meets a preset construction condition by determining the aggregation operation contained in the query statement of the materialized view and the base table referenced by the sub-statement of the query statement. If the preset construction condition is met, the materialized view is supported to be constructed for the base table, so as to reduce the probability of inconsistency between the data in the base table and the data in the materialized view when the data is updated.
[0242] The materialized view construction device in the database system provided by the embodiment of the present disclosure can execute the materialized view construction method in the database system provided by any embodiment of the present disclosure, and has the functional modules and beneficial effects corresponding to the execution method.
[0243] In order to implement the above embodiments, the present disclosure further proposes a computer program product, including a computer program / instruction, which implements the materialized view construction method in the database system in the above embodiments when executed by a processor.
[0244] In addition to the above-mentioned method and apparatus, the embodiment of the present disclosure further provides a computer-readable storage medium, in which instructions are stored. When the instructions are executed on a terminal device, the terminal device implements the materialized view construction method in the database system described in the embodiment of the present disclosure.
[0245] In addition, the present disclosure also provides a materialized view construction device in a database system, see Figure 4 As shown, it may include:
[0246] Processor 401, memory 402, input device 403 and output device 404. The number of processors 401 in the materialized view building device in the database system can be one or more. Figure 4 In some embodiments of the present disclosure, the processor 401, the memory 402, the input device 403 and the output device 404 may be connected via a bus or other means, wherein: Figure 4 The example of connecting through bus is taken in the following.
[0247] The memory 402 can be used to store software programs and modules. The processor 401 executes various functional applications and data processing of the materialized view construction device in the database system by running the software programs and modules stored in the memory 402. The memory 402 can mainly include a program storage area and a data storage area, wherein the program storage area can store an operating system, at least one application required for a function, etc. In addition, the memory 402 can include a high-speed random access memory, and can also include a non-volatile memory, such as at least one disk storage device, a flash memory device, or other volatile solid-state storage devices. The input device 403 can be used to receive input digital or character information, and generate signal input related to user settings and function control of the materialized view construction device in the database system.
[0248] Specifically in this embodiment, the processor 401 will load the executable files corresponding to the processes of one or more applications into the memory 402 according to the following instructions, and the processor 401 will run the applications stored in the memory 402, so as to realize various functions of the materialized view construction device in the above-mentioned database system.
[0249] It should be noted that, in this article, relational terms such as "first" and "second" are only used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Moreover, the terms "include", "comprise" or any other variants thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, the elements defined by the sentence "comprise a ..." do not exclude the existence of other identical elements in the process, method, article or device including the elements.
[0250] The above description is only a specific embodiment of the present disclosure, so that those skilled in the art can understand or implement the present disclosure. Various modifications to these embodiments will be apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the present disclosure. Therefore, the present disclosure will not be limited to the embodiments described herein, but will conform to the widest scope consistent with the principles and novel features disclosed herein.
Claims
1. A materialized view construction method in a database system, characterized in that: include: Receive a materialized view building request for a target base table; The materialized view building request carries a target query statement for building a materialized view of the target basic table; After determining that the target basic table belongs to the target type, determining whether the materialized view construction request satisfies a preset construction condition; The preset construction condition is used to determine the feasibility of the materialized view construction request according to whether the query statement of the materialized view contains an aggregation operation and / or whether a sub-statement of the query statement of the materialized view references a base table, and the target type includes a first type or a second type, the first type is used to identify a table with a unique constraint condition, and the second type is used to identify a table storing aggregate data; When it is determined that the materialized view building request is executable, a materialized view of the target basic table is built based on the target query statement.
2. The method according to claim 1, characterized in that After determining that the target basic table belongs to the target type, determining whether the materialized view construction request satisfies a preset construction condition includes: After determining that the target basic table belongs to the first type, determining whether the target query statement includes an aggregation operation; After determining that the target query statement does not contain an aggregation operation, determine whether the target query statement meets the first condition matching item in the preset construction condition; the first condition matching item is used to determine the feasibility of the materialized view construction request based on the type of the sub-statement of the materialized view query statement and the situation in which the sub-statement references the column in the basic table.
3. The method according to claim 1, characterized in that After determining that the target basic table belongs to the target type, determining whether the materialized view construction request satisfies a preset construction condition includes: After determining that the target basic table belongs to the second type, determining whether the target query statement includes an aggregation operation; After determining that the target query statement includes an aggregation operation, determining whether the target query statement meets a second condition matching item in a preset construction condition; the second condition matching item is used to determine the feasibility of the materialized view construction request according to at least two of the type of a sub-statement of the materialized view query statement, the situation that the sub-statement references a column in a basic table, and the situation that the aggregation operation satisfies a rule; After determining that the target query statement does not contain an aggregation operation, determine whether the target query statement meets the third condition matching item in the preset construction condition; the third condition matching item is used to determine the feasibility of the materialized view construction request based on the type of the sub-statement of the materialized view query statement and the situation in which the sub-statement references the column in the basic table.
4. The method according to claim 2, characterized in that: Before determining whether the target query statement meets the first condition matching item in the preset construction condition, the method further includes: After determining that the target query statement does not include an aggregation operation, determining whether the target basic table includes a target monotonically increasing column; the target monotonically increasing column belongs to a value column of the target basic table and is used to trigger an update of the target basic table; Accordingly, determining whether the target query statement meets the first condition matching item in the preset construction condition includes: After determining that the target basic table includes a target monotonically increasing column, it is determined whether a query list sub-statement of the target query statement references the target monotonically increasing column.
5. The method according to claim 4, characterized in that The first condition matching item is also used to determine the feasibility of the materialized view construction request according to the column participation in the calculation in the query statement of the materialized view. After determining that the target query statement does not include an aggregation operation, determining whether the target query statement meets the first condition matching item in the preset construction condition includes: After determining that the target query statement does not include an aggregation operation, determining whether the key columns of the target basic table all belong to the columns referenced by the query list sub-statement in the target query statement, and determining whether the key columns of the target basic table participate in calculation in the target query statement; Determine whether a conditional sub-statement in the target query statement references a value column of the target basic table; Accordingly, when it is determined that the materialized view building request is executable, building the materialized view of the target basic table based on the target query statement includes: When the following conditions are met at the same time, a materialized view of the target basic table is constructed based on the target query statement; The conditions include: Determining that the target query statement does not include an aggregation operation; The target basic table does not include a target monotonically increasing column, or the target basic table includes a target monotonically increasing column and a query list sub-statement of the target query statement references the target monotonically increasing column; The key columns of the target basic table all belong to the columns referenced by the query list sub-statements in the target query statement and the key columns of the target basic table do not participate in the calculation in the target query statement; The conditional sub-statement in the target query statement does not reference the value column of the target basic table.
6. The method according to claim 3, characterized in that The method further comprises: After determining that the target base table belongs to the second type, determine whether the target base table includes a target monotonically increasing column; the target monotonically increasing column belongs to the value column of the target base table and is used to trigger an update of the target base table.
7. The method according to claim 6, characterized in that After determining that the target query statement does not include an aggregation operation, determining whether the target query statement meets a third condition matching item in a preset construction condition includes: After determining that the target query statement does not include an aggregation operation and the target base table includes a target monotonically increasing column, it is determined whether a query list sub-statement in the target query statement references the target monotonically increasing column.
8. The method according to claim 7, characterized in that The third condition matching item is also used to determine the feasibility of the materialized view construction request according to the column participation in the calculation in the query statement of the materialized view. The determining whether the target query statement meets the third condition matching item in the preset construction condition includes: Determine whether the aggregate key columns of the materialized view in the target query statement all belong to the aggregate key columns in the target basic table; Determine whether a conditional sub-statement in the target query statement references a value column of the target basic table; Determine whether the value column of the target basic table participates in the calculation in the target query statement; Accordingly, when it is determined that the materialized view building request is executable, building the materialized view of the target basic table based on the target query statement includes: When the following conditions are met at the same time, a materialized view of the target basic table is constructed based on the target query statement; The conditions include: The target query statement does not contain an aggregation operation; The target basic table does not include a target monotonically increasing column, or the target basic table includes a target monotonically increasing column and a query list sub-statement of the target query statement references the target monotonically increasing column; The aggregate key columns of the materialized view in the target query statement all belong to the aggregate key columns in the target basic table; The conditional sub-statement in the target query statement does not reference the value column of the target basic table; The value column of the target basic table does not participate in the calculation in the target query statement.
9. The method according to claim 6, characterized in that After determining that the target query statement includes an aggregation operation, determining whether the target query statement meets a second condition matching item in a preset construction condition includes: After determining that the target query statement includes an aggregation operation and the target base table includes a target monotonically increasing column, it is determined whether a query list sub-statement in the target query statement references the target monotonically increasing column.
10. The method according to claim 9, characterized in that The second condition matching item is also used to determine the feasibility of the materialized view construction request according to the column parameter calculation status in the materialized view query statement, and the determining whether the target query statement meets the second condition matching item in the preset construction condition includes: Determine whether the aggregate key columns of the materialized view in the target query statement all belong to the aggregate key columns in the target basic table; Determine whether a conditional sub-statement in the target query statement references a value column of the target basic table; Determine whether the key column referenced by the grouping sub-statement in the target query statement belongs to the key column of the target basic table; Determine whether a parameter of the aggregation operation in the target query statement comes from a value column of the target basic table, and the value column does not participate in the calculation of the parameter of the aggregation operation; Determine whether the aggregation operation in the target query statement satisfies the target calculation rule; the target calculation rule includes the commutative law and the associative law; Determine whether the aggregation operation in the target query statement is consistent with the aggregation operation of the column corresponding to the aggregation operation in the target basic table.
11. The method according to claim 1, characterized in that: The method further comprises: When it is determined that the materialized view construction request is not executable, modifying the target query statement based on the preset construction condition so that the modified target query statement satisfies the preset construction condition; Display the modified target query statement; In response to a materialized view building request triggered for the modified target query statement, a materialized view of the target basic table is built based on the modified target query statement.
12. The method according to claim 11, characterized in that Before modifying the target query statement based on the preset construction condition so that the modified target query statement meets the preset construction condition, the method further includes: Determine a condition matching item that the target query statement does not meet in the preset construction condition, and determine whether the target query statement meets the preset modification condition based on the condition matching item; Accordingly, the modifying the target query statement based on the preset construction condition so that the modified target query statement satisfies the preset construction condition includes: If the target query statement meets the preset modification condition, the target query statement is modified based on the condition matching item so that the modified target query statement meets the preset construction condition.
13. A materialized view construction device in a database system, characterized in that: The device comprises: A first receiving module is used to receive a materialized view construction request for a target basic table; the materialized view construction request carries a target query statement for constructing a materialized view of the target basic table; A first determination module is used to determine whether the materialized view construction request satisfies a preset construction condition after determining that the target basic table belongs to a target type; the preset construction condition is used to determine the feasibility of the materialized view construction request according to whether the query statement of the materialized view contains an aggregation operation and / or whether a sub-statement of the query statement of the materialized view references a basic table, the target type includes a first type or a second type, the first type is used to identify a table with a unique constraint condition, and the second type is used to identify a table storing aggregate data; The first building module is used to build the materialized view of the target basic table based on the target query statement when it is determined that the materialized view building request is executable.
14. An electronic device, characterized in that: The electronic device comprises: processor; a memory for storing instructions executable by the processor; The processor is used to read the executable instructions from the memory and execute the instructions to implement the materialized view construction method in the database system described in any one of claims 1 to 12.
15. A computer-readable storage medium, characterized in that: The storage medium stores a computer program, and the computer program is used to execute the materialized view construction method in the database system described in any one of claims 1 to 12.
16. A computer program product, characterized in that The computer program product comprises a computer program / instruction, and when the computer program / instruction is executed by a processor, the method according to any one of claims 1 to 12 is implemented.
Citation Information
Patent Citations
Database materialized view construction system and method and system creation method
CN111597209A
SQL (Structured Query Language) statement processing method and device
CN114969101A
Cross-data-source database view visualization construction method and system
CN115422167A
Database-based data processing method and device, medium and equipment
CN116821438A
Database data processing method and device, medium and electronic equipment
CN116881285A
Cited By
Data materialization method and device, computer equipment and storage medium
CN120448375A
