Materialized View Construction Method, Device, Equipment and Medium in a Database System

Abstract

Embodiments of the present disclosure relate to a method, apparatus, device, and medium for constructing a materialized view in a database system. The method includes: receiving a materialized view construction request for a target base table, determining whether the materialized view construction request meets a preset construction condition after determining that the target base table belongs to a target type, and constructing a materialized view of the target base table based on a target query statement when it is determined that the materialized view construction request is executable. It can be seen that, for a base table belonging to the target type, embodiments of the present disclosure determine whether the materialized view construction request meets the preset construction condition by determining the situation of aggregation operations included in the query statement of the materialized view and the situation of the base tables referenced by the sub-statements of the query statement, and support constructing a materialized view for the base table when the preset construction condition is met, so as to reduce the probability of data inconsistency between the data of the base table and the data in the materialized view during data update.

Description

Technical Field

[0001] The present disclosure relates to the field of data processing, and in particular, to a method, apparatus, device, and medium for constructing a materialized view in a database system. Background Art

[0002] A materialized view is a special view type in a database. The materialized view pre-computes the query result and stores the result in the materialized view. When the data of the base table changes, the materialized view also needs to be updated to ensure data accuracy. During the query process, the database system optimizes the query based on the content stored in the materialized view to improve query efficiency.

[0003] However, for some base tables, data updates may cause the data in the base table to 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] To solve the above technical problem or at least partially solve the above technical problem, the present disclosure provides a method, apparatus, device, and storage medium for constructing a materialized view in a database system.

[0005] In a first aspect, an embodiment of the present disclosure provides a method for constructing a materialized view in a database system, the method including:

[0006] Receiving a materialized view construction request for a target base table; the materialized view construction request carries a target query statement for constructing the materialized view of the target base table;

[0007] After determining that the target base table belongs to a target type, determining whether the materialized view construction request meets a preset construction condition; the preset construction condition is used to determine the feasibility of the materialized view construction request according to the aggregation operation situation included in the query statement of the materialized view and / or the situation where the sub-statement of the query statement of the materialized view references the 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 storing aggregated data;

[0008] When determining that the materialized view construction request is executable, constructing the materialized view of the target base table based on the target query statement.

[0009] In an optional implementation manner, the determining whether the materialized view construction request meets a preset construction condition after determining that the target base table belongs to a target type includes:

[0010] After determining that the target base table belongs to the first type, determine whether the target query statement contains 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 conditions; the first 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 of the columns in the base table referenced by the sub-statement.

[0012] In an optional implementation manner, after determining that the target base table belongs to the target type, determining whether the materialized view construction request meets the preset construction conditions includes:

[0013] After determining that the target base table belongs to the second type, determine whether the target query statement contains an aggregation operation;

[0014] After determining that the target query statement contains an aggregation operation, determine whether the target query statement meets the second condition matching item in the preset construction conditions; 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 query statement of the materialized view, the situation of the columns in the base table referenced by the sub-statement, and the aggregation operation satisfaction rule situation;

[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 conditions; 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 of the columns in the base table referenced by the sub-statement.

[0016] In an optional implementation manner, before determining whether the target query statement meets the first condition matching item in the preset construction conditions, it further includes:

[0017] After determining that the target query statement does not contain an aggregation operation, determine whether the target base table contains 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 the update of the target base table;

[0018] Correspondingly, determining whether the target query statement meets the first condition matching item in the preset construction conditions includes:

[0019] After determining that the target base table contains a target monotonically increasing column, determine whether the query list sub-statement of the target query statement references the target monotonically increasing column.

[0020] In an alternative embodiment, the first condition match is further configured to determine the feasibility of the materialized view construction request according to the calculation participation of columns in the query statement of the materialized view. After determining that the target query statement does not contain an aggregation operation, determining whether the target query statement meets the first condition match in the preset construction conditions includes:

[0021] After determining that the target query statement does not contain an aggregation operation, determining whether all the key columns of the target base table 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 base table participate in the calculation in the target query statement;

[0022] Determining whether the conditional sub-statement in the target query statement references the value columns of the target base table;

[0023] Correspondingly, when determining that the materialized view construction request is executable, constructing the materialized view of the target base table based on the target query statement includes:

[0024] When the following conditions are met simultaneously, constructing the materialized view of the target base table based on the target query statement;

[0025] The conditions include:

[0026] Determining that the target query statement does not contain an aggregation operation;

[0027] 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 sub-statement of the target query statement references the target monotonically increasing column;

[0028] All the key columns of the target base table belong to the columns referenced by the query list sub-statement in the target query statement and all the key columns of the target base 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 columns of the target base table.

[0030] In an alternative embodiment, the method further includes:

[0031] After determining that the target base table belongs to the second type, determining whether the target base table contains 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 the update of the target base table.

[0032] In an alternative embodiment, after determining that the target query statement does not contain an aggregation operation, determining whether the target query statement meets the third condition match in the preset construction conditions includes:

[0033] After determining that the target query statement does not contain an aggregation operation and the target base table contains a target monotonically increasing column, determine whether the query list sub-statement in the target query statement references the target monotonically increasing column.

[0034] In an alternative embodiment, the third condition match item is further used to determine the feasibility of the materialized view construction request according to the calculation situation of the columns in the query statement of the materialized view. Determining whether the target query statement meets the third condition match item in the preset construction conditions includes:

[0035] Determine whether all the aggregation key columns of the materialized view in the target query statement belong to the aggregation key columns in the target base table;

[0036] Determine whether the conditional sub-statement in the target query statement references the value columns of the target base table;

[0037] Determine whether the value columns of the target base table participate in the calculation in the target query statement;

[0038] Correspondingly, when determining that the materialized view construction request is executable, constructing the materialized view of the target base table based on the target query statement includes:

[0039] When the following conditions are met simultaneously, construct the materialized view of the target base table based on the target query statement;

[0040] The conditions include:

[0041] The target query statement does not contain an aggregation operation;

[0042] 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 sub-statement of the target query statement references the target monotonically increasing column;

[0043] All the aggregation key columns of the materialized view in the target query statement belong to the aggregation key columns in the target base table;

[0044] The conditional sub-statement in the target query statement does not reference the value columns of the target base table;

[0045] The value columns of the target base table do not participate in the calculation in the target query statement.

[0046] In an alternative embodiment, after determining that the target query statement contains an aggregation operation, determining whether the target query statement meets the second condition match item in the preset construction conditions includes:

[0047] After determining that the target query statement contains an aggregation operation and the target base table contains a target monotonically increasing column, determine whether the query list sub-statement in the target query statement references the target monotonically increasing column.

[0048] In an alternative implementation, the second condition match item is further used to determine the feasibility of the materialized view construction request according to the calculation situation of column parameters in the query statement of the materialized view. Determining whether the target query statement meets the second condition match item in the preset construction conditions includes:

[0049] Determine whether all the aggregation key columns of the materialized view in the target query statement belong to the aggregation key columns in the target base table;

[0050] Determine whether the conditional sub-statement in the target query statement references the value columns of the target base table;

[0051] Determine whether the key columns referenced by the grouping sub-statement in the target query statement belong to the key columns of the target base table;

[0052] Determine whether the parameters of the aggregation operation in the target query statement come from the value columns of the target base table, and the value columns do not participate in the calculation in the parameters of the aggregation operation;

[0053] Determine whether the aggregation operation in the target query statement meets the target calculation rules; the target calculation rules include 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 base table.

[0055] In an alternative implementation, the method further includes:

[0056] When it is determined that the materialized view construction request is not executable, modify the target query statement based on the preset construction conditions so that the modified target query statement meets the preset construction conditions;

[0057] Display the modified target query statement;

[0058] In response to the materialized view construction request triggered for the modified target query statement, construct the materialized view of the target base table based on the modified target query statement.

[0059] In an alternative implementation, before modifying the target query statement based on the preset construction conditions so that the modified target query statement meets the preset construction conditions, it further includes:

[0060] Determine the condition matching items that the target query statement does not conform to in the preset construction conditions, and determine whether the target query statement conforms to the preset modification conditions based on the condition matching items;

[0061] Correspondingly, modifying the target query statement based on the preset construction conditions so that the modified target query statement meets the preset construction conditions includes:

[0062] If the target query statement conforms to the preset modification conditions, modify the target query statement based on the condition matching items so that the modified target query statement meets the preset construction conditions.

[0063] In a second aspect, the present disclosure provides a materialized view construction device in a database system, and the device includes:

[0064] A first receiving module, configured to receive a materialized view construction request for a target base table; the target query statement for constructing the materialized view of the target base table is carried in the materialized view construction request;

[0065] A first determination module, configured to determine whether the materialized view construction request meets the preset construction conditions after determining that the target base table belongs to the target type; the preset construction conditions are used to determine the feasibility of the materialized view construction request according to the situation of the query statement of the materialized view including aggregation operations and / or the situation of the sub-statements of the query statement of the materialized view referring to the 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 aggregated data;

[0066] A first construction module, configured to construct a materialized view of the target base table based on the target query statement when determining that the materialized view construction request is executable.

[0067] In a third aspect, an embodiment of the present disclosure further provides an electronic device, and the electronic device includes: a processor; a memory for storing executable instructions of the processor; the processor is configured to read the executable instructions from the memory and execute the instructions to implement the materialized view construction method provided by the embodiment of the present disclosure.

[0068] In a fourth aspect, an embodiment of the present disclosure further provides a computer-readable storage medium, and 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, and the computer program product includes computer programs / instructions, and when the computer programs / instructions are executed by a processor, the above-mentioned method is implemented.

[0070] The technical solutions provided by the embodiments of the present disclosure have the following advantages compared with the prior art:

[0071] In the method for constructing a materialized view in the database system provided by the embodiments of the present disclosure, first, a materialized view construction request for a target base table is received. The materialized view construction request carries a target query statement for constructing the materialized view of the target base table. Then, after determining that the target base table belongs to a target type, it is determined whether the materialized view construction request meets a preset construction condition. The preset construction condition is used to determine the feasibility of the materialized view construction request according to the situation of the query statement of the materialized view including aggregation operations and / or the situation of the sub-statements of the query statement of the materialized view referring to the 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 storing aggregated data. When it is determined that the materialized view construction request is executable, a materialized view of the target base table is constructed based on the target query statement.

[0072] It can be seen that for the base table belonging to the target type in the embodiments of the present disclosure, by determining the situation of the query statement of the materialized view including aggregation operations and the situation of the base table referred to by the sub-statements of the query statement, it is determined whether the materialized view construction request for the above base table meets the preset construction condition, and when the preset construction condition is met, it is supported to construct a materialized view for the base table to reduce the probability of data inconsistency between the data of the base table and the data in the materialized view during data update. BRIEF DESCRIPTION OF THE DRAWINGS

[0073] In combination with the accompanying drawings and with reference to the following specific embodiments, the above and other features, advantages and aspects of the embodiments of the present disclosure will become more obvious. 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 original elements and elements are not necessarily drawn to scale.

[0074] Figure 1 It is a schematic flowchart of a method for constructing a materialized view in a database system provided by the embodiments of the present disclosure;

[0075] Figure 2 It is a schematic diagram of a process for constructing a materialized view in a database system provided by the embodiments of the present disclosure;

[0076] Figure 3 It is a schematic structural diagram of a device for constructing a materialized view in a database system provided by the embodiments of the present disclosure;

[0077] Figure 4 It is a schematic structural diagram of a device for constructing a materialized view in a database system provided by the embodiments of the present disclosure. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0078] Embodiments of the present disclosure will be described in more detail below with reference to the accompanying drawings. Although some embodiments of the present disclosure are shown in the drawings, it should be understood that the present disclosure can be implemented in various forms and should not be construed as limited to the embodiments set forth herein. On the contrary, these embodiments are provided to more thoroughly and completely understand the present disclosure. It should be understood that the drawings and embodiments of the present disclosure are only for exemplary purposes and are not used to limit the protection scope of the present disclosure.

[0079] It should be understood that the various steps recited in the method embodiments of the present disclosure can be executed in a different order 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 regard.

[0080] As used herein, the term "including" and its variations are open-ended, that is, "including but not limited to". The term "based on" is "at least partially based 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", "second", etc. mentioned in the present disclosure are only used to distinguish different devices, modules or units, and are not used to limit the order of the functions executed by these devices, modules or units or their interdependent relationships.

[0082] It should be noted that the modifications of "one" and "plural" mentioned in the present disclosure are illustrative rather than restrictive. Those skilled in the art should understand that, unless otherwise clearly specified 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 for illustrative purposes and are not used to limit the scope of these messages or information.

[0084] Currently, users can, according to their needs, define the query statement and data storage method of the materialized view through the base table. The database system pre-computes the query result according to the definition of the materialized view and stores the result in the materialized view. When the data in the base table changes, the materialized view also needs to be updated to ensure data accuracy. During the query process, the database system will perform query optimization according to the content stored in the materialized view to improve query efficiency.

[0085] However, when creating a materialized view for a base table with a table model of unique and aggregate, data inconsistency may occur between the base table and the materialized view when updating data.

[0086] For example, assume 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 and c is a value column. There are two records (0, 0, 0) and (0, 1, 1) in the base_tbl table.

[0087] If the table model of the materialized view is also unique, the definition of the materialized view is as follows:

[0088] create mv as select a,c+1as c1from base_tbl;

[0089] Its meaning is to create a materialized view named mv. The data of this materialized view comes from the table base_tbl. Select column a from the table base_tbl as column a of the materialized view, and get column c1 of the materialized view by adding 1 to c in the table base_tbl. It can be seen that a of this materialized view is the key column and c1 is the value column.

[0090] According to the data of 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 and the a column is used as the key column, these two records cannot exist in the materialized view at the same time. Therefore, the data in the materialized view is inconsistent with the data in the base table.

[0091] Therefore, for the tables with the above table models of unique and aggregate, how to create a materialized view to ensure the consistency of the data between the base table and the materialized view when the data is updated is a technical problem that needs to be solved by the target.

[0092] To this end, the embodiments of the present disclosure provide a method for constructing a materialized view in a database system. Specifically, first, receive a materialized view construction request for a target base table. The materialized view construction request carries a target query statement for constructing the materialized view of the target base table. Then, after determining that the target base table belongs to the target type, determine whether the materialized view construction request meets the preset construction conditions. The preset construction conditions are used to determine the feasibility of the materialized view construction request according to the situation of the aggregate operation included in the query statement of the materialized view and / or the situation of the sub-statement of the query statement of the materialized view referring to the base table. The target type includes the first type or the 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, then construct the materialized view of the target base table based on the target query statement.

[0093] It can be seen that in the embodiments of the present disclosure, for a base table belonging to a target type, by determining the situation of aggregation operations included in the query statement of the materialized view and the situation of the base tables referenced by the sub-statements of the query statement, it is determined whether the materialized view construction request for the above base table meets the preset construction conditions, and when the preset construction conditions are met, support is provided for constructing a materialized view for the base table to reduce the probability of data inconsistency between the data of the base table and the data in the materialized view during data update.

[0094] Based on this, the embodiments of the present disclosure provide a method for constructing a materialized view in a database system. This method for constructing a materialized view in a database system can be applied in a database system. The following introduces this method in combination with specific embodiments.

[0095] Figure 1 FIG. is a schematic flowchart of a method for constructing a materialized view in a database system provided by an embodiment of the present disclosure. This method can be executed by a materialized view construction device in a database system, where the device can be implemented by software and / or hardware and is generally integrated in an electronic device. As Figure 1 shown, the method includes:

[0096] S101: Receive a materialized view construction request for a target base table.

[0097] The materialized view construction request carries a target query statement for constructing the materialized view of the target base table.

[0098] The target base table in the embodiments of the present disclosure is a base table of a database system, also known as a base table or a basic table. This target base table is the most basic storage unit in the database and is used to store actual data.

[0099] The materialized view construction request in the embodiments of the present disclosure is an instruction to create a materialized view, and the target query statement carried in the materialized view construction request is a creation statement of the materialized view.

[0100] S102: After determining that the target base table belongs to the target type, determine whether the materialized view construction request meets the preset construction conditions.

[0101] The preset construction conditions are used to determine the feasibility of the materialized view construction request according to the situation of aggregation operations included in the query statement of the materialized view and / or the situation of the base tables referenced by the sub-statements of the query statement of the materialized view. 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 aggregated data.

[0102] In the embodiments of the present disclosure, if the target base table belongs to the first type, it indicates that the value of a certain column or a combination of multiple columns in the target base table is unique. Among them, the target base table may include a table with a table model of unique. That is to say, if the target base table belongs to the first type, the table model of the target base table is the unique model;

[0103] If the target base table belongs to the second type, it indicates that the target base table is a table with an aggregation function. That is to say, a table belonging to the second type has the function of aggregating the data in the table. Among them, a table belonging to the second type may include a table with a table model of aggregate. That is to say, if the target base table belongs to the first type, the table model of the target base table is the aggregate model;

[0104] In the embodiments of the present disclosure, after determining the target type of the target base table, it is determined whether the materialized view construction request of the target base table meets the preset construction conditions.

[0105] Among them, the preset construction conditions are used to determine whether a materialized view can be created for the target base table.

[0106] In an optional implementation manner, the preset construction conditions can be used to determine the feasibility of the materialized view construction request according to the situation where the query statement of the materialized view contains an aggregation operation, that is, to determine the situation where the query statement carried in the materialized view construction request contains an aggregation operation, and determine the feasibility of the materialized view construction request. That is to say, by determining the situation where 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 conditions.

[0107] The aggregation operation may include aggregations such as data statistics and calculations. Specifically, the aggregation operation may include statements with aggregation functions such as the grouping (group by) statement. In addition, the aggregation operation can also be implemented through aggregation functions, and the aggregation functions may include functions such as the count function and sum function in the database system.

[0108] In another alternative 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 tables referred to by the sub-statements of the query statement of the materialized view carried in the materialized view construction request. The query statement of the materialized view may include a query (select) sub-statement, a condition (where) sub-statement, etc. That is to say, by judging the reference situation of each sub-statement in the query statement of the materialized view 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 the content referred to by each sub-statement in the query statement to the base table.

[0109] In yet another alternative implementation, the preset construction condition can also be used to determine the feasibility of the materialized view construction request according to the situation that the query statement of the materialized view contains an aggregation operation and the situation of the sub-statements of the query statement referring to 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 construction request is executable, then construct the materialized view of the target base table based on the target query statement.

[0111] Among them, the table model of the materialized view is the same as that of the target base table. That is to say, if the target base table belongs to the first type, the materialized view of the target base table belongs to the first type; if the target base table belongs to the second type, the materialized view of the target base table belongs to the second type.

[0112] In the embodiments 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 condition, it is then possible to construct the materialized view of the target base table based on the target query statement of the materialized view of the target base table carried in the materialized construction request for pre-computing and storing data.

[0113] The method for constructing a materialized view in the database system provided by the embodiments of the present disclosure. Specifically, first, a materialized view construction request for a target base table is received. The materialized view construction request carries a target query statement for constructing the materialized view of the target base table. Then, after determining that the target base table belongs to the target type, it is determined whether the materialized view construction request meets the preset construction conditions. The preset construction conditions are used to determine the feasibility of the materialized view construction request according to the situation of the query statement of the materialized view including aggregation operations and / or the situation of the sub-statements of the query statement of the materialized view referring to the base table. The target type includes the first type or the 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 aggregated data. When it is determined that the materialized view construction request is executable, the materialized view of the target base table is constructed based on the target query statement.

[0114] It can be seen that for the base table belonging to the target type in the embodiments of the present disclosure, by determining the situation of the aggregation operation included in the query statement of the materialized view and the situation of the base table referred to by the sub-statements of the query statement, it is determined whether the materialized view construction request for the above base table meets the preset construction conditions, and when the preset construction conditions are met, it is supported to construct a materialized view for the base table to reduce the probability of data inconsistency between the data of the base table and the data in the materialized view during data update.

[0115] In an optional implementation manner, when receiving the materialized construction request for the target base table, based on the table model of the target base table, it is determined whether the target base table belongs to the target type. 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 of 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 of the target base table is not feasible, that is, the materialized view construction request of the target base table does not meet the preset construction conditions, that is, it is impossible to construct a materialized view for the target base table based on the target query statement.

[0116] In another alternative implementation, if it is determined that the target query statement of the materialized view of the target base 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 conditions, where 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 of the columns in the base table referred to by the sub-statement. Among them, the sub-statements of the query statement of the materialized view can include query sub-statements, conditional sub-statements, etc. That is to say, by determining whether there is a reference between the query list sub-statement, conditional sub-statement and other sub-statements in the query statement of the materialized view and the columns in the base table, it is determined whether the target query statement meets the first condition matching item in the preset construction conditions, that is, the first condition matching item includes the condition of determining whether there is a reference between the query list sub-statement, conditional sub-statement and other sub-statements in the query statement of the materialized view and the columns in the base table.

[0117] In another alternative implementation, the first condition matching item can also be used to determine the feasibility of the materialized view construction request according to the situation of column participation in calculation in the query statement of the materialized view. That is, the first condition matching item also includes the condition of determining whether the key columns of the target base table participate in 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 all the key columns of the target base table belong to the columns referred to by the query list sub-statement in the target query statement, and determine whether the key columns of the target base table participate in calculation in the target query statement, and determine whether the conditional sub-statement in the target query statement refers to the value columns of the target base table.

[0119] Among them, whether all the key columns of the target base table belong to the columns referred to by the query list sub-statement in the target query statement is used to determine whether each key column of the target base table belongs to the columns of the materialized view in the target query statement of the materialized view of the target base table. Among them, the key column of the base table is also called the key column, which is used to store the unique identifier of the data, and the value column of the base 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 base table does not contain an aggregation operation, it is also necessary to first determine whether the target base table contains a target monotonically increasing column. Among them, the target monotonically increasing column belongs to the value column of the target base table and is used to trigger the update of the target base table, and then determine whether the target query statement meets the first condition matching item in the preset construction conditions.

[0121] In an alternative embodiment, after determining that the target query statement of the materialized view of the target base table does not include an aggregation operation, if it is determined that the target base table does not include a target monotonically increasing column, it may be determined whether the target query statement meets the first condition matching item in the preset construction conditions.

[0122] In another alternative embodiment, after determining that the target query statement of the materialized view of the target base table does not include an aggregation operation, if it is determined that the target base table includes a target monotonically increasing column, it is determined whether the query list sub-statement of the target query statement references the target monotonically increasing column. If it is determined that the query list sub-statement of the target query statement does not reference the target monotonically increasing column, it may 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 conditions.

[0124] Among them, the query list sub-statement of the target query statement references the target monotonically increasing column, indicating that the monotonically increasing column included in the target base table belongs to the query list sub-statement in the query statement of the materialized view of the target base table, and it may also indicate that the materialized view in the target base table includes 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 embodiments, if it is determined that the target query statement of the target base table does not include an aggregation operation, and the target base table does not include a target monotonically increasing column or the target base table includes a target monotonically increasing column and the query list sub-statement of the target query statement references the target monotonically increasing column, and all the key columns of the target base table belong to the columns referenced by the query list sub-statement in the target query statement and all the key columns of the target base table do not participate in the calculation in the target query statement, and the conditional sub-statement in the target query statement does not reference the value column of the target base table, it may 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] Among them, the conditional sub-statement in the target query statement does not reference the value column of the target base table, which may mean that the conditional sub-statement in the target query statement can only reference the key column of the target base table.

[0127] That is to say, when the target base table belongs to the first type, when the above conditions are met at the same time, it can be determined that the materialized view construction request for the target base table is executable.

[0128] For example, assume that the key column of the target base table base_tbl is column b and the value column is column c.

[0129] 1. If the target query statement of the materialized view of the target base table is:

[0130] create mv as select a,c+1 as c1 from base_tbl; Its meaning is to create a materialized view named mv, and the data in this materialized view is obtained by executing a query on the base_tbl table. The query selects column a from the table and calculates c+1 to name the result as a new column c1.

[0131] According to the condition in the first condition match item that determines whether each key column of the target base table belongs to the columns 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 columns defined by the materialized view of the target query statement. That is, the materialized view construction request of the target base table does not meet the first condition match 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+1 as c1 from base_tbl;

[0134] According to the condition in the first condition match item that determines whether each key column of the target base table belongs to the columns referenced by the target query statement of the materialized view of the target base table and the key columns of the target base table are not involved in calculations in the target query statement, it can be known that the key column (column b) of the target base table belongs to the columns referenced by the target query statement and the key column is not involved in calculations in the target query statement. Then, other conditions in the first condition match 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+1 as b1 from base_tbl; Its meaning is to create a materialized view named mv, and the data source of the materialized view is the query operation on the base_tbl table. It selects the column obtained by calculating b+1 and naming the result as b1.

[0137] According to the condition of determining whether each key column of the target base table included in the first condition match item belongs to the columns referenced by the target query statement of the materialized view of the target base table and the key columns of the target base table are not involved in the calculation in the target query statement, it can be known that the key column (column b) in the target base table is involved 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 match 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; Its meaning is to create a materialized view named mv, and the data in the materialized view is the value of column b selected from the base_tbl table that meets the condition c>0.

[0140] According to the condition in the first condition match item of 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 match 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; Its meaning is to create a materialized view named mv, and the data in the materialized view is the value of column b selected from the base_tbl table that meets the condition a>0.

[0143] According to the condition in the first condition match item of 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 conditional sub-statement does not reference the value column of the target base table, and it can be known that the materialized view construction request of the target base table meets this condition, then it can continue to determine whether the target query statement meets other conditions in the first condition match item;

[0144] In practical applications, when receiving a materialized construction request for a target base table, after determining that the table model of the target base table belongs to the second type based on the table model of the target base table, it is also necessary to determine whether the target query statement of the materialized view 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 does not contain an aggregation operation, then it is further determined whether the target query statement meets the third condition matching item in the preset construction conditions. Among them, 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 of the columns in the base table referenced by the sub-statement.

[0145] Specifically, the third condition matching item may include the condition of determining whether the aggregation key columns in the materialized view in the target query statement all belong to the aggregation key columns in the target base table, the condition of determining whether the conditional sub-statement in the target query statement references the value columns of the target base table, and the condition of determining whether the value columns of the target base table participate in the calculation in the target query statement.

[0146] In practical applications, 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 can be first determined whether the target base table contains a target monotonically increasing column. If it is determined that the target base table contains the target monotonically increasing column, then it is 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 the columns 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, then it is further determined whether the target query statement meets the third condition matching item in the preset construction conditions. Among them, the query list sub-statement is used to define the columns in the materialized view.

[0147] In another alternative implementation manner, if it is determined that the target base table does not contain the target monotonically increasing column, then it can be determined whether the target query statement meets the third condition matching item in the preset construction conditions 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 situation of the columns in the query statement of the materialized view. Specifically, the third condition matching item also includes the condition of determining whether the value columns of the target base table participate in the calculation in the target query statement.

[0149] Based on the conditions included in the above-mentioned third condition match item, when the target query statement of the target base table does not include an aggregation operation, and the target base table does not include a target monotonically increasing column or the target base table includes a target monotonically increasing column and the query list sub-statement of the target query statement references the target monotonically increasing column, and the aggregation key columns of the materialized view in the target query statement all belong to the aggregation key columns in the target base table, and the conditional sub-statement in the target query statement does not reference the value columns of the target base table, and the value columns of the target base table do 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 alternative implementation, when receiving a materialized construction request for a target base table, based on the table model of the target base table, it is determined whether the target base table belongs to the target type. 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 includes an aggregation operation. If it is determined that the target query statement of the materialized view of the target base table includes an aggregation operation, then it is determined whether the target query statement meets the second condition match item in the preset construction conditions.

[0151] Among them, the second condition match 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 query statement of the materialized view, the situation of the columns in the referenced base table by the sub-statement, and the rule satisfaction situation of the aggregation operation. The rule satisfaction for the aggregation operation is the rule set for judging the aggregation operation.

[0152] Specifically, the second condition match item may include the condition that whether the aggregation key columns of the materialized view in the query statement all belong to the aggregation key columns in the target base table, and the condition that whether the conditional sub-statement in the query statement references the value columns of the base table, and the condition that whether the key columns referenced by the grouping (group by) sub-statement in the query statement belong to the key columns of the base table, and the condition that whether the parameters of the aggregation operation in the query statement come from the value columns of the base table and the value columns do not participate in the calculation in the parameters of the aggregation operation, and the condition that whether the aggregation operation in the query statement meets the target calculation rules, and the condition that whether the aggregation operation in the query statement is consistent with the aggregation operation of the corresponding column in the base table. Among them, the target calculation rules include the commutative law and the associative law.

[0153] Among them, determining whether the aggregation key columns of the materialized view of the target query statement all belong to the aggregation key columns in the target base table means judging whether the aggregation key columns of the materialized view are a subset of the aggregation key columns of the target base table. Determining whether the key columns referenced by the grouping sub-statement in the target query statement belong to the key columns of the target base table means judging whether the key columns referenced by the grouping sub-statement in the target query statement of the materialized view are a subset of the key columns of the target base table.

[0154] In an alternative embodiment, 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 columns of the target base table, and the key columns referenced by the grouping sub-statement in the target query statement belong to the key columns of the target base table, and the parameters of the aggregation operation in the target query statement come from the value columns of the target base table and the value columns do not participate in the calculation in the parameters, and the aggregation operation in the target query statement satisfies the predefined calculation rules, and the aggregation operation in the target query statement is consistent with the aggregation operation of the corresponding column in the target base table, then it can be determined that the target query statement meets the second condition matching item in the predefined construction conditions.

[0155] For example, assume that the base table base_tbl has 3 columns, a and b are key columns, and c is a value column. This base table belongs to the second type, and the aggregation function of column c is the sum function. Taking the example of determining whether the query statement of this base table meets the second condition matching item in the predefined construction conditions,

[0156] The query statement of the materialized view is:

[0157] create mv as(select a,sum(c)s from base_tbl group by a)aggregate key(a);

[0158] Its meaning is to create a materialized view named mv, whose data source is the query of the base_tbl table, select column a and sum column c to get s, group by column a, and at the same time specify a as the aggregate key.

[0159] According to the second condition matching item, it can be determined that the key columns of the materialized view defined by the query statement belong to the key columns of the base table, the key columns referenced by the group by sub-statement in the query statement belong to the key columns of the base table, and the parameters of the aggregation operation in the query statement come from the value columns of the base table, and the value columns do not participate in the calculation in the parameters, and the aggregation operation in the query statement satisfies the target calculation rules, and the aggregation operation in the query statement is consistent with the aggregation operation of the corresponding column in the base table. Therefore, it can be determined that the target query statement meets the second condition matching item in the predefined construction conditions.

[0160] In practical applications, on the basis that the target query statement of the materialized view of the target base table contains an aggregation operation, it is also possible to first determine whether the target base table contains a target monotonically increasing column. Specifically, on the basis that it is determined that the target query statement contains an aggregation operation, it is determined whether the target base table contains a target monotonically increasing column. If it is determined that the target base table does not contain the target monotonically increasing column, then it is determined whether the target query statement of the materialized view of the target base table conforms to the second condition matching item in the preset construction conditions. Among them, the target monotonically increasing column belongs to the value column of the target base table and is used to trigger the update of the target base table.

[0161] In an alternative implementation manner, on the basis that the target query statement of the materialized view of the target base table contains an aggregation operation, if the target base 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 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 columns referenced by the grouping sub-statement in the target query statement belong to the key columns 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 parameters, 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 corresponding column in the target base table, then it can be determined that the target query statement conforms to the second condition matching item in the preset construction conditions.

[0162] In practical applications, when receiving a materialized view construction request for a base table, according to the table model of the target base table, it is determined whether the materialized view construction request meets the preset construction conditions. If it is determined that the materialized view construction request of the target base table is not executable, that is, the materialized view construction request does not meet the preset construction conditions, to help the user create the materialized view of the target base table, the embodiments of the present disclosure can also support the modification function of the target query statement.

[0163] In an alternative implementation manner, when it is determined that the materialized view construction request of the target base table is not executable, the target query statement is modified based on the preset construction conditions so that the modified target query statement meets the preset construction conditions. Then, the modified target query statement is displayed. If a materialized view construction request triggered by the modified target query statement is received, then the materialized view of the target base table is constructed based on the modified target query statement.

[0164] Among them, the target query statement is modified based on preset construction conditions. Specifically, the condition matching items that the target query statement does not meet can be determined in the preset construction conditions, and based on these condition matching items, it is determined whether the target query statement meets the preset modification conditions. Here, the preset modification conditions are 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 conditions, that is, the materialized view construction request of the target base table is executable.

[0165] In an alternative implementation, if the target query statement of the target base table meets the preset modification conditions, then the target query statement is modified based on these condition matching items, so that the modified target query statement meets the preset construction conditions.

[0166] In another alternative implementation, if the target query statement of the target base table does not meet the preset modification conditions, then 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 3 columns, where a and b are key columns and c is a value column.

[0168] 1. Taking the base table base_tbl 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 base table does not belong to the columns referenced by the above query statement, that is, the key column (column b) of the base table does not meet the first condition matching item, indicating that the materialized view construction request does not meet the preset construction conditions. Then, the above query statement is modified based on the preset modification conditions and modified to:

[0171] create mv as select a,b from base_tbl where a>0;

[0172] 2. Taking the base table belonging to the second type as an example, the aggregate key in this 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] It means to create a materialized view named mv. The data of this materialized view comes from the query of the base_tbl table. It selects column a in the base_tbl table, and sums column c in the base_tbl table to get column s, groups by column a, and designates a and s as the aggregation keys.

[0175] According to the second condition match item in the preset construction conditions, it can be known that the aggregation key column of the materialized view in this query statement does not belong to the aggregation key column of the base table. That is, column s in the aggregation key of the query statement of this materialized view is not the aggregation key in the base table, which means it does not meet the second condition match item. Then modify this query statement. 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. Assume that the base table belongs to the second type, and the parameter in the aggregation function of the query statement of the materialized view of this base table is the key column in the base table. According to the second condition match item in the preset construction conditions, it can be known that this query statement does not meet the preset construction conditions. Since it is impossible to determine which value column in the base table the parameter key column in the aggregation function of this query statement should be replaced with based on the preset modification conditions, therefore, this query statement does not meet the preset modification conditions.

[0178] To facilitate understanding of the method for constructing a materialized view in the database system provided by the present disclosure, the embodiments of the present disclosure also provide a schematic diagram of the process for constructing a materialized view in a database system. Refer to Figure 2 .

[0179] First, when a request for constructing a materialized view for a target base table is received, based on the target query statement carried in this request for constructing a materialized view, and based on the target type of the target base table and the preset construction conditions, determine whether the target query statement of this materialized view meets the preset construction conditions.

[0180] In an alternative implementation, if it is determined that the target query statement meets the preset construction conditions, then construct the materialized view of the target base table based on the target query statement.

[0181] In another alternative implementation, if it is determined that the target query statement does not meet the preset construction conditions, then modify the target query statement based on the preset modification conditions, display the modified target query statement. When a request for constructing a materialized view triggered by the modified target query statement is received, construct the materialized view of the target base table based on the modified target query statement.

[0182] To implement the above embodiments, the present disclosure also provides a materialized view construction device in a database system. Figure 3 As shown in the structural schematic diagram 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 is generally integrated in an electronic device. Figure 3 As shown, the device includes:

[0183] A first receiving module 301, configured to receive a materialized view construction request for a target base table; the target query statement for constructing the materialized view of the target base table is carried in the materialized view construction request;

[0184] A first determination module 302, configured to determine whether the materialized view construction request meets a preset construction condition after determining that the target base table belongs to a target type; the preset construction condition is used to determine the feasibility of the materialized view construction request according to the aggregation operation situation included in the query statement of the materialized view and / or the situation where the sub-statement of the query statement of the materialized view references the 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 storing aggregated data;

[0185] A first construction module 303, configured to construct the materialized view of the target base table based on the target query statement when determining that the materialized view construction request is executable.

[0186] In an alternative embodiment, the first determination module includes:

[0187] A first determination sub-module, configured to determine whether the target query statement includes an aggregation operation after determining that the target base table belongs to the first type;

[0188] A second determination sub-module, configured 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 include an aggregation operation; the first 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 of the columns in the base table referenced by the sub-statement.

[0189] In an alternative embodiment, the first determination module includes:

[0190] A third determination sub-module, configured to determine whether the target query statement includes an aggregation operation after determining that the target base table belongs to the second type;

[0191] A fourth determination sub-module, configured to determine whether the target query statement meets a second condition matching item in a 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 a materialized view construction request according to at least two of the type of a sub-statement of the query statement of the materialized view, the situation of columns in the base table referenced by the sub-statement, and the rule satisfaction situation of the aggregation operation;

[0192] A fifth determination sub-module, configured to determine whether the target query statement meets a third condition matching item in a preset construction condition after determining that the target query statement does not include an aggregation operation; the third condition matching item is used to determine the feasibility of a materialized view construction request according to the type of a sub-statement of the query statement of the materialized view and the situation of columns in the base table referenced by the sub-statement.

[0193] In an optional implementation manner, the device includes:

[0194] A second determination module, configured to determine whether the target base table includes a target monotonically increasing column after determining that the target query statement does not include an aggregation operation; the target monotonically increasing column belongs to the value columns of the target base table and is used to trigger the update of the target base table;

[0195] Correspondingly, the second determination sub-module is specifically configured to:

[0196] After determining that the target base table includes a target monotonically increasing column, determine whether the 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 a materialized view construction request according to the calculation situation of columns in the query statement of the materialized view. The second determination sub-module includes:

[0198] A sixth determination sub-module, configured to determine whether all key columns of the target base table belong to the columns referenced by the query list sub-statement in the target query statement and determine whether the key columns of the target base table participate in calculations in the target query statement after determining that the target query statement does not include an aggregation operation;

[0199] A seventh determination sub-module, configured to determine whether the condition sub-statement in the target query statement references the value columns of the target base table;

[0200] Correspondingly, the first construction module is specifically configured to:

[0201] When the following conditions are simultaneously met, construct a materialized view of the target base table based on the target query statement;

[0202] The conditions include:

[0203] Determine that the target query statement does not contain an aggregation operation;

[0204] 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 sub-statement of the target query statement references the target monotonically increasing column;

[0205] All key columns of the target base table belong to the columns referenced by the query list sub-statement in the target query statement and none of the key columns of the target base table participate in calculations in the target query statement;

[0206] The conditional sub-statement in the target query statement does not reference the value columns of the target base table.

[0207] In an alternative implementation, the apparatus further includes:

[0208] A third determination module, configured to determine whether the target base table contains a target monotonically increasing column after determining that the target base table belongs to the second type; the target monotonically increasing column belongs to the value columns of the target base table and is used to trigger the update of the target base table.

[0209] In an alternative implementation, the fifth determination sub-module is specifically configured to:

[0210] After determining that the target query statement does not contain an aggregation operation and the target base table contains a target monotonically increasing column, determine whether the query list sub-statement in the target query statement references the target monotonically increasing column.

[0211] In an alternative implementation, the third condition matching item is further configured to determine the feasibility of the materialized view construction request according to the calculation situation of the columns in the query statement of the materialized view. The fifth determination sub-module includes:

[0212] An eighth determination sub-module, configured to determine whether all the aggregation key columns of the materialized view in the target query statement belong to the aggregation key columns in the target base table;

[0213] A ninth determination sub-module, configured to determine whether the conditional sub-statement in the target query statement references the value columns of the target base table;

[0214] A tenth determination sub-module, configured to determine whether the value columns of the target base table participate in calculations in the target query statement;

[0215] Correspondingly, the first construction module is specifically configured to:

[0216] When the following conditions are simultaneously satisfied, construct a materialized view of the target base table based on the target query statement;

[0217] The conditions include:

[0218] The target query statement does not contain an aggregation operation;

[0219] 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 sub-statement of the target query statement references the target monotonically increasing column;

[0220] The aggregation key columns of the materialized view in the target query statement all belong to the aggregation key columns in the target base table;

[0221] The conditional sub-statement in the target query statement does not reference the value columns of the target base table;

[0222] The value columns of the target base table do not participate in calculations in the target query statement.

[0223] In an alternative implementation, the fourth determination sub-module is specifically configured to:

[0224] After determining that the target query statement contains an aggregation operation and the target base table contains a target monotonically increasing column, determine whether the query list sub-statement in the target query statement references the target monotonically increasing column.

[0225] In an alternative implementation, the second condition matching item is further configured to determine the feasibility of the materialized view construction request according to the calculation situation of column parameters in the query statement of the materialized view. The fourth determination sub-module includes:

[0226] The eleventh determination sub-module is configured to determine whether the aggregation key columns of the materialized view in the target query statement all belong to the aggregation key columns in the target base table;

[0227] The twelfth determination sub-module is configured to determine whether the conditional sub-statement in the target query statement references the value columns of the target base table;

[0228] The thirteenth determination sub-module is configured to determine whether the key columns referenced by the grouping sub-statement in the target query statement belong to the key columns of the target base table;

[0229] The fourteenth determination sub-module is configured to determine whether the parameters of the aggregation operation in the target query statement come from the value columns of the target base table, and the value columns do not participate in calculations in the parameters of the aggregation operation;

[0230] The fifteenth determination sub-module is configured to determine whether the aggregation operation in the target query statement satisfies the target calculation rules; the target calculation rules include the commutative law and the associative law;

[0231] A sixteenth determination sub-module, configured to determine whether the aggregation operation in the target query statement is consistent with the aggregation operation corresponding to the column in the target base table for the aggregation operation.

[0232] In an alternative implementation, the apparatus further includes:

[0233] A fourth determination module, configured to, when determining that the materialized view construction request is not executable, modify the target query statement based on the preset construction conditions, so that the modified target query statement meets the preset construction conditions;

[0234] A display module, configured to display the modified target query statement;

[0235] A second construction module, configured to, in response to a materialized view construction request triggered for the modified target query statement, construct a materialized view of the target base table based on the modified target query statement.

[0236] In an alternative implementation, the apparatus further includes:

[0237] A fifth determination module, configured to determine a condition matching item in the preset construction conditions that the target query statement does not meet, and determine whether the target query statement meets the preset modification conditions based on the condition matching item;

[0238] Correspondingly, the second construction module is specifically configured to:

[0239] If the target query statement meets the preset modification conditions, modify the target query statement based on the condition matching item, so that the modified target query statement meets the preset construction conditions.

[0240] In the materialized view construction apparatus in the database system provided by the embodiments of the present disclosure, first, a materialized view construction request for a target base table is received, and the target query statement for constructing the materialized view of the target base table is carried in the materialized view construction request. Then, after determining that the target base table belongs to the target type, it is determined whether the materialized view construction request meets the preset construction conditions. The preset construction conditions are used to determine the feasibility of the materialized view construction request according to the situation of the query statement of the materialized view including aggregation operations and / or the situation of the sub-statement of the query statement of the materialized view referring to the 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 storing aggregated data. When it is determined that the materialized view construction request is executable, a materialized view of the target base table is constructed based on the target query statement.

[0241] It can be seen that, for the base table belonging to the target type in the embodiments of the present disclosure, by determining the situation of aggregate operations included in the query statement of the materialized view and the situation of the base tables referenced by the sub-statements of the query statement, it is determined whether the materialized view construction request for the above base table meets the preset construction conditions, and when the preset construction conditions are met, it supports constructing a materialized view for the base table to reduce the probability of data inconsistency between the data of the base table and the data in the materialized view during data update.

[0242] The materialized view construction device in the database system provided by the embodiments 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 corresponding functional modules and beneficial effects for executing the method.

[0243] To implement the above embodiments, the present disclosure also proposes a computer program product, including computer programs / instructions, which, when executed by a processor, implement the materialized view construction method in the database system in the above embodiments.

[0244] In addition to the above methods and devices, the embodiments of the present disclosure also provide a computer-readable storage medium, in which instructions are stored. When the instructions run on a terminal device, the terminal device is enabled to implement the materialized view construction method in the embodiments of the present disclosure.

[0245] In addition, the embodiments of the present disclosure also provide a materialized view construction device in a database system. Refer to Figure 4 as shown, it may include:

[0246] A processor 401, a memory 402, an input device 403, and an output device 404. The number of processors 401 in the materialized view construction device in the database system may be one or more, Figure 4 Taking one processor as an example. 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 by a bus or other means. Among them, Figure 4 taking the connection by a bus as an example.

[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 mainly includes a program storage area and a data storage area. Among them, the program storage area can store an operating system, application programs required for at least one function, etc. In addition, the memory 402 can include high-speed random access memory, and can also include non-volatile memory, such as at least one magnetic disk storage device, 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 inputs related to the user settings and function controls of the materialized view construction device in the database system.

[0248] Specifically, in this embodiment, the processor 401 loads the executable files corresponding to the processes of one or more application programs into the memory 402 according to the following instructions, and the processor 401 runs the application programs stored in the memory 402, so as to implement various functions of the materialized view construction device in the above 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 term "comprising", "including" or any other variant thereof is intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements not only includes those elements, but also includes other elements not expressly listed, or further includes elements inherent to such process, method, article or device. Without further limitation, an element defined by the statement "including a..." does not exclude the existence of additional identical elements in the process, method, article or device including the element.

[0250] The above are only specific embodiments of the present disclosure, enabling those skilled in the art to understand or implement the present disclosure. Various modifications to these embodiments will be obvious to those skilled in the art, and the general principles defined herein can 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 these 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 the 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 unique constraint condition represents that the value of a certain column or a combination of multiple columns in the table is unique. The second type is used to identify a table storing aggregated data, and the table storing aggregated data has the function of aggregating data in the table. 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, the unique constraint represents that the value of a certain column or a combination of multiple columns in the table is unique, and the second type is used to identify a table storing aggregated data, the table storing aggregated data has the function of aggregating data in the table; 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.

Images