Database operation method and apparatus, electronic device, and storage medium

Abstract

A database operation method and apparatus, an electronic device, and a storage medium. The method comprises: determining a database operation statement; and if a weak partition execution condition is satisfied, generating an execution plan that uses a first weak partition execution logic, wherein the weak partition execution condition comprises: an operation of an outer join causes a null value to be generated at a partition key corresponding to a second partition table in a result set; and the first weak partition execution logic is used for: instructing each partition node to separately perform a target operation on a local first-type data row in the result set, and send a generated partition execution result and a local second-type data row to a coordination node; and instructing the coordination node to uniformly perform the target operation on the received second-type data row, and merge the generated uniform execution result and the received partition execution result into a final execution result of the database operation statement.

Description

Database operation methods and apparatus, electronic devices and storage media Technical Field

[0001] This specification relates to the field of database technology, and more particularly to a database operation method and apparatus, electronic device and storage medium. Background Technology

[0002] In distributed database systems, partitioned tables are a crucial mechanism for improving query performance and data management efficiency. By dividing a data table into multiple partition nodes according to a partition key, the system can achieve parallel computing and localized processing.

[0003] When generating the execution plan, if the optimizer determines that the partition key of the partitioned table will produce data that cannot meet the partitioning rules (e.g., null values ​​due to outer joins), and this affects subsequent operations such as partition-wise join, partition-wise group by, and partition-wise distinct, which require the use of partitioning features, the optimizer will repartition the data involved in these operations (e.g., using the EXCHANGE operator). This will impact the database's processing efficiency. Summary of the Invention

[0004] In view of the above, this specification provides a database operation method and apparatus, electronic device and storage medium through one or more embodiments.

[0005] To achieve the above objectives, one or more embodiments of this specification provide the following technical solutions:

[0006] According to a first aspect of one or more embodiments of this specification, a database operation method is provided, the method comprising:

[0007] Determine the database operation statement, which is used to perform an outer join on the first partition table and the second partition table, and perform the target operation on the result set of the outer join based on the partition key of the second partition table;

[0008] If the weak partitioning execution condition is met, an execution plan using the first weak partitioning execution logic is generated; the weak partitioning execution condition includes: the outer join operation causes the partition key in the result set corresponding to the second partition table to produce a null value; and the first weak partitioning execution logic is used to:

[0009] Each partition node is instructed to perform the target operation on the first type of data rows in the result set that are located locally, and to send the generated partition execution result and the second type of data rows located locally to the coordinating node; the partition key value corresponding to the second partition table in the first type of data row is not null, and the partition key value corresponding to the second partition table in the second type of data row is null;

[0010] The coordinating node is instructed to uniformly perform the target operation on the received second type of data rows, and to merge the generated unified execution result and the received partition execution result into the final execution result of the database operation statement.

[0011] According to a second aspect of one or more embodiments of this specification, a database operation method is provided, the method comprising:

[0012] Obtain the execution plan for the database operation statement, which performs an outer join on a first partition table and a second partition table, and performs a target operation on the result set of the outer join based on the partition key of the second partition table; wherein, if the weak partition execution condition is met, the execution plan adopts the first weak partition execution logic; the weak partition execution condition includes: the outer join operation causes the partition key of the second partition table in the result set to produce a null value;

[0013] Based on the first weak partition execution logic, the target operation is executed on the first type of data rows in the result set that are located locally, and the generated partition execution result and the second type of data rows located locally are sent to the coordination node. The coordination node then uniformly executes the target operation on the received second type of data rows, and merges the generated unified execution result and the received partition execution result into the final execution result of the database operation statement. In this case, the partition key value corresponding to the second partition table in the first type of data rows is not null, and the partition key value corresponding to the second partition table in the second type of data rows is null.

[0014] According to a third aspect of one or more embodiments of this specification, a database operation method is provided, the method comprising:

[0015] Obtain the execution plan for the database operation statement, which performs an outer join on a first partition table and a second partition table, and performs a target operation on the result set of the outer join based on the partition key of the second partition table; wherein, if the weak partition execution condition is met, the execution plan adopts the first weak partition execution logic; the weak partition execution condition includes: the outer join operation causes the partition key of the second partition table in the result set to produce a null value;

[0016] The system receives reported data from each partition node. The reported data includes the second type of data row locally on the corresponding partition node and the partition execution result generated by performing the target operation on the first type of data row in the result set locally. The partition key in the first type of data row corresponding to the second partition table has a non-null value, and the partition key in the second type of data row corresponding to the second partition table has a null value.

[0017] Based on the first weak partition execution logic, the target operation is uniformly executed on the received second type of data rows, and the generated unified execution result and the received partition execution result are merged into the final execution result of the database operation statement. According to a fourth aspect of one or more embodiments of this specification, a computer program product is proposed, including a computer program / instructions that, when executed by a processor, implement the steps of the methods described in the first, second, and / or third aspects.

[0018] According to a fifth aspect of one or more embodiments of this specification, an electronic device is provided, comprising:

[0019] processor;

[0020] Memory used to store processor-executable instructions;

[0021] The processor implements the methods described in the first, second, and / or third aspects by running the executable instructions.

[0022] According to a sixth aspect of one or more embodiments of this specification, a computer-readable storage medium is provided having computer instructions stored thereon that, when executed by a processor, implement the steps of the methods as described in the first, second, and / or third aspects.

[0023] The technical solutions provided in the embodiments of this specification may include the following beneficial effects:

[0024] The database operation method provided in the embodiments of this specification, when the database operation statement is used to perform an outer join on the first partition table and the second partition table, and performs a target operation on the result set of the outer join based on the partition key of the second partition table, if it is determined that the weak partition execution condition is met, then by generating an execution plan that adopts the first weak partition execution logic, there is no need to repartition the result set. Each partition node can directly perform the target operation on the first type of data rows locally, while the second type of data rows in the result set are gathered at the coordinating node for unified execution of the target operation. Thus, while avoiding the negative impact of repartitioning, the partition characteristics can be fully utilized to speed up query efficiency, which helps to improve the processing performance of the distributed database. Attached Figure Description

[0025] Figure 1 is a schematic diagram of a distributed database provided in an exemplary embodiment.

[0026] Figure 2 is a flowchart of a database operation method provided in an exemplary embodiment.

[0027] Figure 3 is a schematic diagram of the interaction between partition nodes in an exemplary SQL query method.

[0028] Figure 4 is a flowchart of another database operation method provided by an exemplary embodiment.

[0029] Figure 5 is a flowchart of another database operation method provided in an exemplary embodiment.

[0030] Figure 6 is a schematic diagram of the structure of a device provided in an exemplary embodiment.

[0031] Figure 7 is a block diagram of a database operation apparatus provided in an exemplary embodiment.

[0032] Figure 8 is a block diagram of another database operation apparatus provided in an exemplary embodiment.

[0033] Figure 9 is a block diagram of another database operation apparatus provided in an exemplary embodiment. Detailed Implementation

[0034] Exemplary embodiments will now be described in detail, examples of which are illustrated in the accompanying drawings. When the following description relates to the drawings, unless otherwise indicated, the same numerals in different drawings denote the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with one or more embodiments of this specification. Rather, they are merely examples of apparatuses and methods consistent with some aspects of one or more embodiments of this specification as detailed in the appended claims.

[0035] It should be noted that the steps of the corresponding methods are not necessarily performed in the order shown and described in this specification in other embodiments. In some other embodiments, the methods may include more or fewer steps than described in this specification. Furthermore, a single step described in this specification may be broken down into multiple steps in other embodiments; and multiple steps described in this specification may be combined into a single step in other embodiments.

[0036] Figure 1 is a schematic diagram of a distributed database provided in an exemplary embodiment. As shown in Figure 1, the distributed database includes database nodes of various types, such as Coordinator Node and Worker Node, for example, Partition Node 1, Partition Node 2, ..., Partition Node n shown in Figure 1. A database node is a logical functional unit hosted on certain physical hardware, used to implement corresponding logical functions. The physical hardware can include independent physical servers or virtual servers (such as virtual machines or containers) constructed based on physical server hardware resources.

[0037] Partition nodes are used to maintain partitioned tables. When a data table contains too many rows, it increases the difficulty of maintaining the table and reduces query efficiency. In this case, the data table can be divided into multiple partitions (subsets), and each partition is maintained at a different partition node, thus transforming the data table from an independent data table into a partitioned table. For example, partitioned table T0 contains 100,000 rows of data, divided into 10 partitions T00, T01 to T09, each containing 10,000 rows of data, and each maintained at a different partition node.

[0038] The coordinating node receives database operation statements and coordinates the corresponding partition nodes to complete the operations. For example, the coordinating node generates an execution plan based on the received database operation statement and sends the execution plan, or some of its operators, to the partition nodes involved in the operation for execution. The coordinating node then returns the operation result to the querying party.

[0039] Among related technologies, a query optimization mechanism utilizing partitioning characteristics has been proposed.

[0040] create table t1(a int,b int,c int)partition by hash(a)partitions 4;

[0041] Q1:explain select a,sum(b)from t1 group by a;

[0042] Referring to the partitioned table t1 and aggregate query Q1 listed above, partitioned table t1 contains columns a, b, and c, with partition key 'a'. Q1 groups data using 'a' as the operation key, then sums the values ​​of column b for each group, finally outputting the value of 'a' and the sum for each group. Since the grouping operation key is 'a', which matches the partition key of partitioned table t1, a partition-wise groupby approach can be used. Each partition node groups its own partitions, and then each partition node aggregates its partition execution results to the coordinating node, which merges them to obtain the final execution result. This process fully utilizes the partitioning feature to perform distributed query execution, reducing the cost of transmitting data rows between nodes. This optimization mechanism can be called a query optimization mechanism utilizing partitioning features.

[0043] In the example above, the grouping operation targets the partitioned table t1 itself. The partitioning rule for partitioned table t1 is: partitioning based on the hash value of 'a', specifically dividing it into 4 partitions. All data rows in partitioned table t1 must conform to this partitioning rule, ensuring that these data rows have been assigned to their corresponding partition nodes based on this rule. Therefore, when the operation key of the grouping operation is the same as the partition key of partitioned table t1, the efficient distributed query described above can be achieved directly using the partition-wise-groupby approach.

[0044] However, some query statements involve pre-processing operations that may generate new data. Consider the following examples of partitioned tables t1 and t2, and aggregate queries Q2 and Q3. Partitioned table t1 contains columns a, b, and c, with partition key a. Partitioned table t2 contains columns a, b, and c, with partition key a.

[0045] Both queries Q2 and Q3 first perform an outer join on partition tables t1 and t2 based on the values ​​in column 'a', forming a result set (let's call it t1-2). However, query Q2 then groups this result set t1-2 using t1.a as the operation key, while query Q3 groups it using t2.a as the operation key.

[0046] create table t1(a int,b int,c int)partition by hash(a)partitions 4;

[0047] create table t2(a int,b int,c int)partition by hash(a)partitions 4;

[0048] Q2: explain select t1.a,sum(t2.b)from t1 left join t2 on t1.a=t2.a group by t1.a;

[0049] Q3: explain select t2.a,sum(t1.b)from t1 left join t2 on t1.a=t2.a group by t2.a;

[0050] Because the outer join t1 left join t2 retains all rows of partition table t1, and some values ​​of t1.a do not exist in t2.a, the value of column t2.a in these rows will be recorded as null. For example, if the values ​​of t1.a include (1, 2, 3) and the values ​​of t2.a include (1, 2), then the result set t1-2 formed by the outer join will necessarily contain the data row corresponding to t1.a = 3, and the value of column t2.a in this data row will be null, because t2.a = 3 does not exist in partition table t2 to begin with. The partitioning rule of partition table t2 is: partitioning based on the hash value of t2.a, specifically dividing it into 4 partitions. However, the original partition table t2.a only has values ​​of 1 and 2. The partitioning rule can only accommodate data rows with values ​​of 1 and 2. But the value null may be newly generated in any partition, instead of being allocated to the corresponding partition node according to "hash(null)partitions 4". Therefore, the value null is a new data generated in the result set that does not conform to the partitioning rule corresponding to t2.a (that is, the partitioning rule of partition table t2). Since the outer join mentioned above does not cause t1.a to generate new data, all data in t1.a conforms to the partitioning rule corresponding to t1.a (that is, the partitioning rule of partition table t1: partitioned based on the hash value of t1.a, specifically divided into 4 partitions).

[0051] Therefore, when query Q2 groups data using t1.a as the operation key, the query optimization mechanism utilizing partitioning can be directly applied. However, when query Q3 groups data using t2.a as the operation key, due to the condition t2.a = null, the corresponding data rows cannot be directly grouped using the query optimization mechanism utilizing partitioning.

[0052] In related technologies, for the scenario described in query Q3, it is necessary to repartition the data rows in the result set, and then group the repartitioned partitions using a query optimization mechanism that leverages partitioning characteristics. However, repartitioning incurs additional processing costs and impacts database query performance.

[0053] To address this, this specification proposes a new database query scheme that, without requiring data repartitioning, can still perform distributed operations on each partition node by utilizing a query optimization mechanism based on partition characteristics, thereby improving the query performance of the distributed database.

[0054] Please refer to Figure 2, which exemplarily illustrates a flowchart of a database operation method, including steps S201 to S202.

[0055] In step S201, a database operation statement is determined. The database operation statement is used to perform an outer join on the first partition table and the second partition table, and to perform a target operation on the result set of the outer join based on the partition key of the second partition table.

[0056] Database operation statements may include SQL (Structured Query Language) statements, but this specification does not limit this. Taking SQL statements as an example, when an SQL statement is described as suitable for partitioned execution, it can be understood that the SQL statement can be executed independently on the data rows within each partition node, and the final result can be obtained simply by aggregating the execution results of each partition node. In other words, SQL statements suitable for partitioned execution do not require repartitioning of the data rows within different partition nodes during execution.

[0057] Specifically, if an SQL statement can be executed independently for a single value or range of values ​​within a field, and the query result of the SQL statement can be obtained by merging the results of the independent executions of different values ​​or ranges within that field, then that field is suitable for partitioned execution of the SQL statement. It should be understood that the field targeted for partitioned execution of the SQL statement mentioned in this step refers to one or more fields in the data table targeted by the SQL statement.

[0058] Optionally, if the SQL statement contains at least one of the following: a Left join clause, a Group by clause, a Distinct clause, a Join clause, and a window function clause, then the SQL statement is deemed suitable for partitioned execution.

[0059] For example, if an SQL statement contains a Left join clause, it can be determined that the SQL statement is suitable for partitioned execution, and the fields targeted by the Left join clause are used as the fields targeted by the SQL statement for partitioned execution. For example, the following SQL statement Q1 is a suitable SQL statement for partitioned execution, and the fields targeted by the SQL statement for partitioned execution are t1.a and t2.a.

[0060] create table t1(a int,b int,c int)partition by hash(a)partitions 4;

[0061] create table t2(a int,b int,c int)partition by hash(a)partitions 4;

[0062] Q4: explain select t1.a,t1.b,t2.a,t2.b from t1 left join t2 on t1.a=t2.a

[0063] For another example, if an SQL statement contains a GROUP BY clause, it can be determined that the SQL statement is suitable for partitioned execution, and the field targeted by the GROUP BY clause is used as the field targeted by the SQL statement for partitioned execution. For instance, as mentioned above, queries Q2 and Q3 are SQL statements suitable for partitioned execution. The field targeted by the SQL statement for partitioned execution corresponding to query Q2 is t1.a, and the field targeted by the SQL statement for partitioned execution corresponding to query Q3 is t2.a.

[0064] For example, if an SQL statement contains a Distinct clause, it can be determined that the SQL statement is suitable for partitioned execution, and the field targeted by the Distinct clause can be used as the field targeted by the SQL statement for partitioned execution.

[0065] For example, if the SQL statement contains a Join clause, it can be determined that the SQL statement is suitable for partitioned execution, and the field targeted by the Join clause is used as the field targeted by the SQL statement for partitioned execution.

[0066] For example, if the SQL statement contains a window function clause, it can be determined that the SQL statement is suitable for partitioned execution, and the field targeted by the window function clause is used as the field targeted by the SQL statement for partitioned execution.

[0067] The partitioning characteristics include strong partitioning and weak partitioning. Strong partitioning indicates that all data rows within a field satisfy the corresponding partitioning rules. For example, in queries Q2 and Q3 listed above, the partition key t1.a in the result set meets the characteristics of strong partitioning. Weak partitioning indicates that some data rows within a field satisfy the corresponding partitioning rules, while other data rows do not. For example, in queries Q2 and Q3 listed above, the partition key t2.a in the result set meets the characteristics of weak partitioning, where data rows where t2.a ≠ null satisfy the partitioning rules, and data rows where t2.a = null do not satisfy the partitioning rules.

[0068] In some embodiments, weak partitions can be further differentiated. For example, the proportion of data rows in a corresponding field that conform to the partitioning rules within all data rows of that field can be calculated, and differentiated into two cases based on whether the proportion is less than or not less than a proportion threshold. This will be described in detail below.

[0069] Partitioning rules refer to the rules used in a distributed database to determine the partition node corresponding to each data row. For example, the hash value of the key can be used as a parameter for partitioning. Each partition node corresponds to one or more hash values. If the hash value of the key of a data row corresponds to a partition node, then that partition node is determined as the partition node corresponding to that data row, and the data row is stored in the corresponding partition node. Such a partitioning rule can be called hash partitioning. In other embodiments, other partitioning rules can also be used, such as: range partitioning – partitioning based on a continuous range of column values ​​(such as dates, numerical ranges); list partitioning – suitable for cases where column values ​​are discrete sets (such as status codes, regions); and composite partitioning – combining multiple partitioning methods, such as first partitioning by range and then by hash or list sub-partitioning, etc. This specification does not limit these methods.

[0070] In step S202, if it is determined that the weak partition execution condition is met, an execution plan using the first weak partition execution logic is generated; the weak partition execution condition includes: the outer join operation causes the partition key corresponding to the second partition table in the result set to produce a null value.

[0071] The first weak partition execution logic is used for:

[0072] Each partition node is instructed to perform the target operation on the first type of data rows in the result set that are located locally, and to send the generated partition execution result and the second type of data rows located locally to the coordinating node; the partition key value corresponding to the second partition table in the first type of data row is not null, and the partition key value corresponding to the second partition table in the second type of data row is null;

[0073] The coordinating node is instructed to uniformly perform the target operation on the received second type of data rows, and to merge the generated unified execution result and the received partition execution result into the final execution result of the database operation statement.

[0074] For the aforementioned outer join operation and target operation, since the result set generated by the outer join operation contains the partition key of the second partition table, and the target operation is the result set, and the operation key of the target operation is the same as the partition key mentioned above, the target operation has the prerequisite of using the "query optimization mechanism that utilizes partition characteristics".

[0075] However, if the weak partition execution conditions described in step S202 are met, then the technical solution in this specification needs to be improved to ensure that each partition node can directly execute the target operation by utilizing the query optimization mechanism of partition characteristics without needing to repartition the data.

[0076] It should be understood that each partition node mentioned in this step refers to the partition node where each partition of the data table targeted by the SQL statement is located.

[0077] Taking Q3 in the above example: `explain select t2.a, sum(t1.b) from t1 left join t2 on t1.a = t2.a group by t2.a`, t2.a has a weak partitioning characteristic. Before performing an outer join on partitioned tables t1 and t2, both t1.a and t2.a have strong partitioning characteristics because all data contained in t1.a and t2.a satisfies the corresponding partitioning rules. After performing the outer join on partitioned tables t1 and t2, the data contained in t1.a remains unchanged, maintaining strong partitioning, while t2.a adds null values ​​distributed across different partition nodes, changing its partitioning characteristic to weak partitioning. That is, all data rows in t2.a except for null values ​​conform to the partitioning rules; only the data rows containing null values ​​do not conform to the partitioning rules.

[0078] For example, some data rows from tables t1 and t2 in the above example are shown below:

[0079] Table t1 Table t2

[0080] In the partitioning rules, the hash values ​​of keys 1, 2, and 3 correspond to partition node 1. Therefore, the data rows in table t1 with a value of 1, 2, and 3 are in partition node 1, and the data rows in table t2 with a value of 1 and 2 are in partition node 1. In the partitioning rules, the hash values ​​of keys 4, 5, and 6 correspond to partition node 2. Therefore, the data rows in table t1 with a value of 4 are in partition node 2, and the data rows in table t2 with a value of 5 are in partition node 2.

[0081] When performing an outer join on tables t1 and t2 (t1 left join t2 on t1.a = t2.a), both t1.a and t2.a have strong partitioning properties. Therefore, the outer join can be performed partitioned. Specifically, the outer join of the t1 table partition with t1.a values ​​of 1, 2, and 3 and the t2 table partition with t2.a values ​​of 1 and 2 is performed within partition node 1, and the outer join of the t1 table partition with t1.a value of 4 and the t2 table partition with t2.a value of 5 is performed within partition node 2.

[0082] For example, the outer join operation in the generated execution plan may contain operators such as:

[0083] 4 HASH OUTER JOIN

[0084]

[0085]

[0086] Operators 5 and 6 are full table scan operators, used to instruct each partition node to scan the partitions corresponding to t1 and t2 maintained locally. Operator 4 is a hash outer join operator, used to instruct each partition node to perform an outer join on the scanned data rows. Specifically: a hash table is built from t2 scanned by operator 6, then each row of t1 scanned by operator 5 is traversed, and a matching t2 row is searched in the hash table. If a match is found, the t1 row is joined with the matching t2 row; otherwise, the t1 row is joined with the t2 row where each column has a null value.

[0087] The result of the outer join of tables t1 and t2 is as follows:

[0088] Table t1 left join t2 on t1.a=t2.a

[0089] As can be seen, the t1.a field also conforms to the partitioning rules, that is, the data rows containing 1, 2, and 3 are in partition node 1, and the data row containing 4 is in partition node 2. Therefore, the partitioning characteristic of the t1.a field is strong partitioning. The data rows containing 1 and 2 in the t2.a field also conform to the partitioning rules and are in partition node 1. The data row containing null in the t2.a field does not conform to the partitioning rules. The two data rows are in partition node 1 and partition node 2 respectively, and the hash value of null does not correspond to partition node 1 or partition node 2.

[0090] Therefore, the remaining target operations in Q3 (i.e., grouping based on t2.a, summing the values ​​of t1.b, and outputting t2.a and the summation result) can be executed in the manner provided in this step. Please refer to Figure 3. Partition node 1 executes the target operations on the data rows where the t2.a field is 1 and 2, and sends the partition execution result and the data rows where the t2.a field is null (i.e., the data rows where the t1.a field is 3) to the coordinating node. Partition node 2 sends the data rows where the t2.a field is null (i.e., the data rows where the t1.a field is 4) to the coordinating node (if there are data rows on partition node 2 where the t2.a field value is not null, then the target operations are also involved, and the partition execution result is sent to the coordinating node). The coordinating node executes the target operations uniformly on the two data rows where the t2.a field is null (i.e., the two data rows where the t1.a field is 3 and 4), and merges the generated unified execution result with the partition execution results sent by each partition node to generate the final execution result of Q3.

[0091] For example, the target operation mentioned above could include the following operator in the generated execution plan:

[0092]

[0093]

[0094] Operator #3 is a hash grouping and aggregation operator, used to instruct each partition node to group and aggregate the first-type data rows in the result set (generated by outer joining tables t1 and t2) locally. Of course, in this specification, the coordinating node also needs to execute operator #3 or an operator that performs a similar function to uniformly execute the target operation (no grouping and aggregation operation in this example) on each received second-type data row, thereby generating the corresponding unified execution result.

[0095] Optionally, operator 2 is a parallel partitioning iteration operator, used to start a worker thread (or task) for each partition node to execute operators 3-6, ensuring that these operators can be executed in parallel on each partition node to improve efficiency.

[0096] For example, the generated execution plan uses the following operators to achieve data interaction and result aggregation between partition nodes and coordinating nodes:

[0097] 0 PX COORDINATOR

[0098]

[0099] Among them, operator 1 is the distributed data exchange output operator, which instructs each partition node to send its own generated partition execution result and the second type of data row located locally to the coordinating node. Operator 0 is the parallel coordinator operator, which instructs the coordinating node to merge its own generated unified execution result and the received partition execution result into the final execution result.

[0100] Therefore, the execution plan generated by the optimizer for query Q3 above can generally include operators 0 to 6 as follows, and it can be seen that it does not include the repartition operator:

[0101] 0 PX COORDINATOR

[0102]

[0103]

[0104]

[0105] 4 HASH OUTER JOIN

[0106]

[0107]

[0108] Continuing with the previous example, partition node 1 needs to perform the target operation on the first type of data rows located locally. Specifically, it needs to group and aggregate the data rows where t1.a has values ​​of 1 and 2, and then sum the values ​​of t1.b in each group. Accordingly, the execution result obtained by partition node 1 is as follows:

[0109] By aggregating data rows that do not conform to the partitioning rules (i.e., the second type of data rows) to the coordinating node, the target operation can be uniformly executed on these data rows within the coordinating node. This is equivalent to aggregating these data rows that do not conform to the partitioning rules and are scattered across different partitioning nodes, and then uniformly executing the target operation, thereby improving efficiency. Although this requires more inter-node transmission of these data rows that do not conform to the partitioning rules compared to the case of strong partitioning (i.e., each partitioning node needs to send these data rows to the coordinating node), the communication volume is far less than that of the repartitioning operation steps.

[0110] To further reduce communication overhead, the optimizer can determine the operation type of the target operation when generating the execution plan and add corresponding preprocessing operators to the first weak partition execution logic. Different operation types correspond to different preprocessing operators. The first weak partition execution logic is further used to instruct each partition node to preprocess the second type of data rows located locally according to the preprocessing operators, so as to send the preprocessed data rows to the coordinating node. In this way, transmitting the preprocessed data rows further reduces network transmission costs compared to directly transmitting the second type of data rows. For example, if the target operation's operation type is grouping, such as partition-wise-groupby, then the preprocessing operators include aggregation operators, i.e., pre-aggregation for the second type of data rows; if the target operation's operation type is deduplication, such as partition-wise-distinct, then the preprocessing operators include deduplication operators, i.e., pre-deduplication for the second type of data rows.

[0111] Please refer to Figure 3. The coordinating node performs the target operation on the two data rows where the t2.a field is null (i.e., the two data rows where the t1.a field is 3 and 4). The unified execution result is as follows:

[0112] After merging the partition execution results uploaded by partition node 1 with the unified execution results mentioned above, the final execution result of Q3 is as follows:

[0113] As mentioned earlier, the optimizer needs to generate execution plans corresponding to database operation statements, enabling partition nodes and coordinating nodes to execute the operators contained in the execution plan, thereby achieving the technical solution described in this specification. The optimizer can be deployed on the coordinating node or on other nodes in the distributed database system; this specification does not impose any restrictions on this. The first operation and target operation corresponding to the database operation statement are specifically represented as one or more operators in the execution plan. Because the operations executed by partition nodes and coordinating nodes differ, all operators in the complete execution plan are usually not sent to all nodes. Instead, only the relevant operators are sent to the corresponding nodes for execution, based on the actual operations required by each node. Alternatively, annotation information can be added to each operator to indicate which nodes need to execute that operator. This allows the complete execution plan to be sent to all nodes, and each node can determine the operators it needs to execute based on the annotation information.

[0114] In the technical solution of this specification, the execution process of the target operation differs from related technologies. It requires mutual cooperation between partitioning nodes and coordinating nodes, and the target operation needs to be executed on both partitioning nodes and coordinating nodes. Therefore, the operator corresponding to this target operation should be distinguished from the operators used in related technologies to implement similar operations.

[0115] For example, the operator corresponding to the target operation can be a general operator, meaning that the operator itself is the same as the related technology. For instance, when the target operation is a grouping and aggregation operation, the corresponding operator can be the HASH GROUP BY operator as described above. However, this general operator can be supplemented with a weak partitioning identifier to indicate the processing logic of the partitioning node and the coordinating node. Therefore, when the partitioning node and the coordinating node scan for this weak partitioning identifier: the partitioning node will distinguish between the first type of data rows and the second type of data rows in the result set located locally, and will perform the target operation on the first type of data rows; and the coordinating node will uniformly perform the target operation on the received second type of data rows.

[0116] For example, the operator corresponding to the target operation can be a weak partitioning-specific operator, and its operator type is used to indicate the processing logic of the partitioning node and the coordinating node. Then, the coordinating node and the partitioning node can determine, based on the operator type, whether to execute the target operation based on relevant technologies or based on the technical solution of this specification.

[0117] For example, the first weak partition execution logic is further configured to: instruct each partition node to delete the local second type of data row after sending it to the coordinating node; and instruct the coordinating node to determine the partition node corresponding to the second type of data row based on the partitioning rules applicable to the received second type of data row, and to allocate the second type of data row to the corresponding partition node.

[0118] For example, continuing with Q3 in the above example: `explain select t2.a,sum(t1.b)from t1 left join t2 on t1.a=t2.a group by t2.a`, please refer to Figure 3. The coordinating node determines that the hash value of null corresponds to partition node 3. Therefore, the two data rows where the received t2.a field is null (i.e., the two data rows where t1.a fields are 3 and 4) can be sent to partition node 3 for storage. Similarly, partition node 1 sends the data row where the t2.a field is null (i.e., the data row where t1.a field is 3) to the coordinating node and then deletes that data row; control partition node 2 sends the data row where the t2.a field is null (i.e., the data row where t1.a field is 4) to the coordinating node and then deletes that data row.

[0119] In this way, these second-category data rows are partitioned according to the partitioning rules. This ensures that when the same database operation statement is executed again, these data rows belong to the first category of data rows and there are no more second-category data rows. Therefore, each partition node can directly process the data by utilizing the query optimization mechanism of partition characteristics (such as executing the target operation), without needing to transmit the second-category data rows or have the coordinating node uniformly execute the target operation on the second-category data rows. This helps to improve the execution efficiency of the database operation statement.

[0120] As mentioned above, in step S202, the weak partitioning execution conditions include: the first operation causes the result set to generate a second type of data row, where the values ​​belonging to the partition key in the second type of data row do not satisfy the partitioning rule corresponding to the partition key. In other words, in one embodiment, as long as the result set generates a second type of data row that does not satisfy the partitioning rule, each partition node can perform the target operation on its local first type of data row in the manner described in step S202, and the coordination node can aggregate the second type of data rows uploaded by each partition node and uniformly execute the target operation.

[0121] In another embodiment, the weak partitioning execution condition may further include: the proportion of the first type of data rows in the result set is not less than a proportion threshold. This specification does not limit the method, timing, or specific value of setting this proportion threshold. In summary, in this embodiment, only when the proportion of the first type of data rows reaches a certain proportion (i.e., the proportion threshold) is it considered worthwhile for the target operation to be executed in a distributed and parallel manner by each partition node, while only the second type of data rows are transmitted to the coordinating node for processing, to achieve the processing scheme described in step S202.

[0122] If the proportion of the first type of data rows in the result set is less than the proportion threshold, the optimizer can generate an execution plan using the second weak partitioning execution logic. The second weak partitioning execution logic is used to: instruct each partition node to send all data rows in the result set that are local to the coordinating node; and instruct the coordinating node to uniformly execute the target operation on all received data rows to generate the final execution result of the database operation statement.

[0123] In other words, if the proportion of the first type of data rows does not reach a certain percentage (i.e., the percentage threshold), the overhead of creating threads on each partition node for processing may be too high. Therefore, each partition node can be instructed to send all local data rows (including the second type of data rows and the first type of data rows) to the coordinating node, and the coordinating node can execute the target operation uniformly. In this way, the overall overhead of the database system is relatively lower than the distributed execution of the target operation.

[0124] The preceding text primarily describes the technical solution of this specification from the perspective of generating an execution plan. As mentioned earlier, when executing this execution plan, the technical solution also involves partition nodes and coordinating nodes, which cooperate to implement the database query scheme of this specification. Below, embodiments for implementing the technical solution of this specification are provided from the perspectives of partition nodes and coordinating nodes, respectively. It should be noted that since the processes of generating and executing the execution plan are essentially closely related, the processing logic of partition nodes and coordinating nodes in the execution process has already been described above. Therefore, the repeated content will not be elaborated upon below, and the content described above is applicable to the following embodiments.

[0125] Corresponding to a partition node, Figure 4 is a flowchart of another database operation method provided in an exemplary embodiment. As shown in Figure 4, the method includes:

[0126] Step S401: Obtain the execution plan for the database operation statement, which is used to perform an outer join on the first partition table and the second partition table, and to perform a target operation on the result set of the outer join based on the partition key of the second partition table; wherein, if the weak partition execution condition is met, the execution plan adopts the first weak partition execution logic; the weak partition execution condition includes: the outer join operation causes the partition key of the second partition table in the result set to produce a null value.

[0127] Step S402: Based on the first weak partition execution logic, the target operation is executed on the first type of data rows in the result set that are located locally, and the generated partition execution result and the second type of data rows located locally are sent to the coordination node. The coordination node uniformly executes the target operation on the received second type of data rows, and merges the generated unified execution result and the received partition execution result into the final execution result of the database operation statement; wherein, the partition key value corresponding to the second partition table in the first type of data rows is not null, and the partition key value corresponding to the second partition table in the second type of data rows is null.

[0128] As mentioned above, the execution plan employing the first weak partitioning execution logic may include: an operator corresponding to the target operation, wherein:

[0129] In one embodiment, the operator corresponding to the target operation is a general operator, which is marked with a weak partition identifier to indicate the processing logic of the partition node and the coordinating node; or,

[0130] In another embodiment, the operator corresponding to the target operation is a weak partitioning-specific operator, and its operator type is used to indicate the processing logic of the partitioning node and the coordinating node.

[0131] As mentioned above, the method may further include:

[0132] Based on the first weak partition execution logic, the second type of data rows located locally are preprocessed so that the preprocessed data rows can be sent to the coordination node;

[0133] The preprocessing method is matched to the operation type of the target operation.

[0134] As mentioned above,

[0135] If the operation type is grouping, then the preprocessing method includes aggregation;

[0136] If the operation type is deduplication, then the preprocessing method includes deduplication.

[0137] As mentioned above, the method may further include:

[0138] Based on the first weak partition execution logic, after sending the second type of data row located locally to the coordinating node, the second type of data row located locally is deleted.

[0139] The first weak partition execution logic is further used to instruct the coordinating node to determine the partition node corresponding to the second type of data row based on the partitioning rules applicable to the received second type of data row, and to allocate the second type of data row to the corresponding partition node.

[0140] As mentioned above, the weak partitioning execution condition may further include: the proportion of the first type of data rows in the result set is not less than a proportion threshold.

[0141] As mentioned above,

[0142] If the proportion of the first type of data rows in the result set is less than a proportion threshold, the execution plan adopts the second weak partitioning execution logic; the method may further include:

[0143] Send all data rows in the result set that are located locally to the coordinating node;

[0144] The second weak partition execution logic is further used to instruct the coordinating node to uniformly execute the target operation on all received data rows to generate the final execution result of the database operation statement.

[0145] Corresponding to the coordinating node, Figure 5 is a flowchart of another database operation method provided in an exemplary embodiment. As shown in Figure 5, the method includes:

[0146] Step S501: Obtain the execution plan for the database operation statement, which is used to perform an outer join on the first partition table and the second partition table, and perform a target operation on the result set of the outer join based on the partition key of the second partition table; wherein, if the weak partition execution condition is met, the execution plan adopts the first weak partition execution logic; the weak partition execution condition includes: the outer join operation causes the partition key of the second partition table in the result set to generate a null value.

[0147] Step S502: Receive reported data from each partition node. The reported data includes the second type of data row locally on the corresponding partition node and the partition execution result generated by performing the target operation on the first type of data row in the result set locally; wherein, the values ​​of the partition key in the first type of data row satisfy the partitioning rules.

[0148] Step S503: Based on the first weak partition execution logic, the target operation is uniformly executed on the received second type of data rows, and the generated unified execution result and the received partition execution result are merged into the final execution result of the database operation statement.

[0149] As mentioned above,

[0150] The first weak partition execution logic can also be used to: instruct each partition node to delete the local second-type data rows after sending the local second-type data rows to the coordinating node;

[0151] The method may further include: determining the partition node corresponding to the second type of data row based on the partitioning rules applicable to the received second type of data row according to the first weak partition execution logic, and allocating the second type of data row to the corresponding partition node.

[0152] As mentioned above, the weak partitioning execution condition may further include: the proportion of the first type of data rows in the result set is not less than a proportion threshold.

[0153] As mentioned above,

[0154] If the proportion of the first type of data rows in the result set is less than the proportion threshold, the execution plan adopts the second weak partitioning execution logic; the second weak partitioning execution logic is used to: instruct each partition node to send all data rows in the result set that are local;

[0155] The method may further include: performing the target operation on all received data rows uniformly based on the second weak partition execution logic to generate the final execution result of the database operation statement.

[0156] In summary, the database operation method provided in the embodiments of this specification, when the database operation statement is used to perform an outer join on the first partition table and the second partition table, and performs a target operation on the result set of the outer join based on the partition key of the second partition table, if it is determined that the weak partition execution condition is met, then by generating an execution plan that adopts the first weak partition execution logic, there is no need to repartition the result set. Each partition node can directly perform the target operation on the first type of data rows locally, while the second type of data rows in the result set are gathered at the coordinating node for unified execution of the target operation. Thus, while avoiding the negative impact of repartitioning, the partition characteristics can be fully utilized to speed up query efficiency, which helps to improve the processing performance of the distributed database.

[0157] Figure 6 is a schematic structural diagram of a device provided in an exemplary embodiment. Referring to Figure 6, at the hardware level, the device includes a processor 602, an internal bus 604, a network interface 606, a memory 608, and a non-volatile memory 610, and may also include other hardware required for tasks. One or more embodiments of this specification can be implemented in software, for example, the processor 602 reads the corresponding computer program from the non-volatile memory 610 into the memory 608 and then runs it. Of course, in addition to software implementation, one or more embodiments of this specification do not exclude other implementation methods, such as logic devices or a combination of hardware and software, etc. That is to say, the execution subject of the following processing flow is not limited to each logic unit, but can also be hardware or logic devices.

[0158] Please refer to Figure 7. The database operation device can be applied to the device shown in Figure 6 to implement the technical solution of this specification. The database operation device may include:

[0159] Statement determination unit 701 determines a database operation statement, which is used to perform an outer join on a first partition table and a second partition table, and to perform a target operation on the result set of the outer join based on the partition key of the second partition table.

[0160] The first plan generation unit 702, if it determines that the weak partitioning execution conditions are met, generates an execution plan using the first weak partitioning execution logic; the weak partitioning execution conditions include: the outer join operation causes the partition key corresponding to the second partition table in the result set to produce a null value; and the first weak partitioning execution logic is used to:

[0161] Each partition node is instructed to perform the target operation on the first type of data rows in the result set that are located locally, and to send the generated partition execution result and the second type of data rows located locally to the coordinating node; the partition key value corresponding to the second partition table in the first type of data row is not null, and the partition key value corresponding to the second partition table in the second type of data row is null;

[0162] The coordinating node is instructed to uniformly perform the target operation on the received second type of data rows, and to merge the generated unified execution result and the received partition execution result into the final execution result of the database operation statement.

[0163] Optionally, the execution plan employing the first weak partitioning execution logic includes: an operator corresponding to the target operation, wherein:

[0164] The operator corresponding to the target operation is a general operator, which is marked with a weak partition identifier to indicate the processing logic of the partition node and the coordinating node; or,

[0165] The operator corresponding to the target operation is a weak partitioning-specific operator, and its operator type is used to indicate the processing logic of the partitioning node and the coordinating node.

[0166] Optionally, it also includes:

[0167] The type determination unit 703 determines the operation type of the target operation and adds a corresponding preprocessing operator to the first weak partition execution logic; different operation types correspond to different preprocessing operators;

[0168] The first weak partition execution logic is further used for:

[0169] Each partition node is instructed to preprocess the second type of data rows located locally according to the preprocessing operator, so as to send the preprocessed data rows to the coordinating node.

[0170] Optionally,

[0171] If the operation type is grouping, then the preprocessing operator includes an aggregation operator;

[0172] If the operation type is deduplication, then the preprocessing operator includes a deduplication operator.

[0173] Optionally, the first weak partition execution logic is further used to:

[0174] Instruct each partition node to delete the local Category II data rows after sending them to the coordinating node;

[0175] The coordinating node is instructed to determine the partition node corresponding to the second type of data row based on the partitioning rules applicable to the received second type of data row, and to allocate the second type of data row to the corresponding partition node.

[0176] Optionally, the weak partitioning execution condition further includes: the proportion of the first type of data rows in the result set is not less than a proportion threshold.

[0177] Optionally, it also includes:

[0178] The second plan generation unit 704 generates an execution plan using the second weak partitioning execution logic if the proportion of the first type of data rows in the result set is less than a proportion threshold; the second weak partitioning execution logic is used for:

[0179] Instruct each partition node to send all data rows of the result set located locally to the coordinating node;

[0180] The coordinating node is instructed to uniformly execute the target operation on all received data rows to generate the final execution result of the database operation statement.

[0181] Please refer to Figure 8. The database operation device can be applied to the device shown in Figure 6 to implement the technical solution of this specification. The database operation device may include:

[0182] The plan acquisition unit 801 acquires the execution plan for a database operation statement. This database operation statement is used to perform an outer join on a first partition table and a second partition table, and to perform a target operation on the result set of the outer join based on the partition key of the second partition table. If a weak partition execution condition is met, the execution plan adopts the first weak partition execution logic. The weak partition execution condition includes: the outer join operation causes the partition key of the second partition table in the result set to produce a null value.

[0183] The execution unit 802, based on the first weak partition execution logic, performs the target operation on the first type of data rows in the result set that are located locally, and sends the generated partition execution result and the second type of data rows located locally to the coordination node. The coordination node then uniformly performs the target operation on the received second type of data rows, and merges the generated unified execution result and the received partition execution result into the final execution result of the database operation statement. Specifically, the partition key value corresponding to the second partition table in the first type of data rows is not null, and the partition key value corresponding to the second partition table in the second type of data rows is null.

[0184] Optionally, the execution plan employing the first weak partitioning execution logic includes: an operator corresponding to the target operation, wherein:

[0185] The operator corresponding to the target operation is a general operator, which is marked with a weak partition identifier to indicate the processing logic of the partition node and the coordinating node; or,

[0186] The operator corresponding to the target operation is a weak partitioning-specific operator, and its operator type is used to indicate the processing logic of the partitioning node and the coordinating node.

[0187] Optionally, it also includes:

[0188] The preprocessing unit 803 preprocesses the second type of data rows located locally based on the first weak partition execution logic, so as to send the preprocessed data rows to the coordination node;

[0189] The preprocessing method is matched to the operation type of the target operation.

[0190] Optionally,

[0191] If the operation type is grouping, then the preprocessing method includes aggregation;

[0192] If the operation type is deduplication, then the preprocessing method includes deduplication.

[0193] Optionally, it also includes:

[0194] The data row deletion unit 804, based on the first weak partition execution logic, deletes the local second-type data row after sending the local second-type data row to the coordinating node;

[0195] The first weak partition execution logic is further used to instruct the coordinating node to determine the partition node corresponding to the second type of data row based on the partitioning rules applicable to the received second type of data row, and to allocate the second type of data row to the corresponding partition node.

[0196] Optionally, the weak partitioning execution condition further includes: the proportion of the first type of data rows in the result set is not less than a proportion threshold.

[0197] Optionally,

[0198] If the proportion of the first type of data rows in the result set is less than a proportion threshold, the execution plan adopts the second weak partitioning execution logic; the device further includes:

[0199] The data row sending unit 805 sends all data rows that are located locally in the result set to the coordinating node;

[0200] The second weak partition execution logic is further used to instruct the coordinating node to uniformly execute the target operation on all received data rows to generate the final execution result of the database operation statement.

[0201] Please refer to Figure 9. The database operation device can be applied to the device shown in Figure 6 to implement the technical solution of this specification. The database operation device may include:

[0202] The plan acquisition unit 901 acquires the execution plan for a database operation statement. This database operation statement is used to perform an outer join on a first partition table and a second partition table, and to perform a target operation on the result set of the outer join based on the partition key of the second partition table. If a weak partition execution condition is met, the execution plan adopts the first weak partition execution logic. The weak partition execution condition includes: the outer join operation causes the partition key of the second partition table in the result set to produce a null value.

[0203] The data receiving unit 902 receives reported data from each partition node. The reported data includes the second type of data row locally in the corresponding partition node and the partition execution result generated by performing the target operation on the first type of data row in the result set locally. The partition key in the first type of data row corresponding to the second partition table has a non-null value, and the partition key in the second type of data row corresponding to the second partition table has a null value.

[0204] The result aggregation unit 903, based on the first weak partition execution logic, uniformly executes the target operation on the received second type of data rows, and merges the generated unified execution result and the received partition execution result into the final execution result of the database operation statement.

[0205] Optionally,

[0206] The first weak partition execution logic is also used to: instruct each partition node to delete the local second-type data rows after sending the local second-type data rows to the coordinating node;

[0207] The apparatus further includes a data row allocation unit 904, which determines the partition node corresponding to the second type of data row based on the partitioning rules applicable to the received second type of data row according to the first weak partitioning execution logic, and allocates the second type of data row to the corresponding partition node.

[0208] Optionally, the weak partitioning execution condition further includes: the proportion of the first type of data rows in the result set is not less than a proportion threshold.

[0209] Optionally,

[0210] If the proportion of the first type of data rows in the result set is less than the proportion threshold, the execution plan adopts the second weak partitioning execution logic; the second weak partitioning execution logic is used to: instruct each partition node to send all data rows in the result set that are local;

[0211] The device further includes a result generation unit 905, which performs the target operation on all received data rows based on the second weak partition execution logic to generate the final execution result of the database operation statement.

[0212] One or more embodiments of this specification also provide a computer program product including a computer program / instructions that, when executed by a processor, implement the steps of the method provided in the first aspect.

[0213] One or more embodiments of this specification also provide a computer-readable storage medium having computer instructions stored thereon that, when executed by a processor, implement the steps of the method as described in the first aspect.

[0214] The systems, devices, modules, or units described in the above embodiments can be implemented by computer chips or entities, or by products with certain functions. A typical implementation device is a computer, which can take the form of a personal computer, laptop computer, cellular phone, camera phone, smartphone, personal digital assistant, media player, navigation device, email sending and receiving device, game console, tablet computer, wearable device, or any combination of these devices.

[0215] In a typical configuration, a computer includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.

[0216] Memory may include non-persistent storage in computer-readable media, such as random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM. Memory is an example of computer-readable media.

[0217] Computer-readable media, including both permanent and non-permanent, removable and non-removable media, can store information using any method or technology. Information can be computer-readable instructions, data structures, modules of programs, or other data. Examples of computer storage media include, but are not limited to, phase-change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, CD-ROM, digital versatile optical disc (DVD) or other optical storage, magnetic tape, disk storage, quantum memory, graphene-based storage media or other magnetic storage devices, or any other non-transferable medium that can be used to store information accessible by a computing device. As defined herein, computer-readable media does not include transient computer-readable media, such as modulated data signals and carrier waves.

[0218] It should also be noted that the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0219] The foregoing has described specific embodiments of this specification. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps recited in the claims may be performed in a different order than that shown in the embodiments and may still achieve the desired result. Furthermore, the processes depicted in the drawings do not necessarily require the specific or sequential order shown to achieve the desired result. In some embodiments, multitasking and parallel processing are possible or may be advantageous.

[0220] The terminology used in one or more embodiments of this specification is for the purpose of describing particular embodiments only and is not intended to limit the scope of one or more embodiments of this specification. The singular forms “a,” “described,” and “the” used in one or more embodiments of this specification and in the appended claims are also intended to include the plural forms unless the context clearly indicates otherwise. It should also be understood that the term “and / or” as used herein refers to and includes any or all possible combinations of one or more associated listed items.

[0221] The user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, data stored, data displayed, etc.) involved in this manual are all information and data authorized by the user or fully authorized by all parties. Furthermore, the collection, use and processing of related data must comply with the relevant laws, regulations and standards of the relevant countries and regions, and corresponding operation portals are provided for users to choose to authorize or refuse.

[0222] It should be understood that although the terms first, second, third, etc., may be used to describe various information in one or more embodiments of this specification, such information should not be limited to these terms. These terms are only used to distinguish information of the same type from one another. For example, first information may also be referred to as second information without departing from the scope of one or more embodiments of this specification, and similarly, second information may also be referred to as first information. Depending on the context, the word "if" as used herein may be interpreted as "when," "in response to a determination," or "when," or "in the event of a determination."

[0223] The above description is merely a preferred embodiment of one or more embodiments of this specification and is not intended to limit the scope of one or more embodiments of this specification. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of one or more embodiments of this specification should be included within the protection scope of one or more embodiments of this specification.

Claims

1. A database operation method, the method comprising: Determine the database operation statement, which is used to perform an outer join on the first partition table and the second partition table, and perform the target operation on the result set of the outer join based on the partition key of the second partition table; If the weak partition execution conditions are met, an execution plan using the first weak partition execution logic is generated. The weak partitioning execution condition includes: the outer join operation causes the partition key corresponding to the second partition table in the result set to produce a null value; and the first weak partitioning execution logic is used to: Each partition node is instructed to perform the target operation on the first type of data rows in the result set that are located locally, and to send the generated partition execution result and the second type of data rows located locally to the coordinating node; the partition key value corresponding to the second partition table in the first type of data row is not null, and the partition key value corresponding to the second partition table in the second type of data row is null; The coordinating node is instructed to uniformly perform the target operation on the received second type of data rows, and to merge the generated unified execution result and the received partition execution result into the final execution result of the database operation statement.

2. The method according to claim 1, wherein the execution plan employing the first weak partitioning execution logic includes: an operator corresponding to the target operation, wherein: The operator corresponding to the target operation is a general operator, which is marked with a weak partition identifier to indicate the processing logic of the partition node and the coordinating node; or, The operator corresponding to the target operation is a weak partitioning-specific operator, and its operator type is used to indicate the processing logic of the partitioning node and the coordinating node.

3. The method according to claim 1, further comprising: The operation type of the target operation is determined, and a corresponding preprocessing operator is added to the execution logic of the first weak partition; different operation types correspond to different preprocessing operators; The first weak partition execution logic is further used for: Each partition node is instructed to preprocess the second type of data rows located locally according to the preprocessing operator, so as to send the preprocessed data rows to the coordinating node.

4. The method according to claim 3, If the operation type is grouping, then the preprocessing operator includes an aggregation operator; If the operation type is deduplication, then the preprocessing operator includes a deduplication operator.

5. The method according to claim 1, wherein the first weak partition execution logic is further configured to: Instruct each partition node to delete the local Category II data rows after sending them to the coordinating node; The coordinating node is instructed to determine the partition node corresponding to the second type of data row based on the partitioning rules applicable to the received second type of data row, and to allocate the second type of data row to the corresponding partition node.

6. The method of claim 1, the weak partition execution condition further comprising: The proportion of the first type of data rows in the result set is not less than the proportion threshold.

7. The method according to claim 6, further comprising: If the proportion of the first type of data rows in the result set is less than the proportion threshold, an execution plan using the second weak partitioning execution logic is generated. The second weak partition execution logic is used for: Instruct each partition node to send all data rows of the result set located locally to the coordinating node; The coordinating node is instructed to uniformly execute the target operation on all received data rows to generate the final execution result of the database operation statement.

8. A database operation method, the method comprising: Obtain the execution plan for the database operation statement, which performs an outer join on a first partition table and a second partition table, and performs a target operation on the result set of the outer join based on the partition key of the second partition table; wherein, if the weak partition execution condition is met, the execution plan adopts the first weak partition execution logic; the weak partition execution condition includes: the outer join operation causes the partition key of the second partition table in the result set to produce a null value; Based on the first weak partition execution logic, the target operation is executed on the first type of data rows in the result set that are located locally, and the generated partition execution result and the second type of data rows located locally are sent to the coordination node. The coordination node then uniformly executes the target operation on the received second type of data rows, and merges the generated unified execution result and the received partition execution result into the final execution result of the database operation statement. In this case, the partition key value corresponding to the second partition table in the first type of data rows is not null, and the partition key value corresponding to the second partition table in the second type of data rows is null.

9. The method according to claim 8, wherein the execution plan employing the first weak partitioning execution logic includes: an operator corresponding to the target operation, wherein: The operator corresponding to the target operation is a general operator, which is marked with a weak partition identifier to indicate the processing logic of the partition node and the coordinating node; or, The operator corresponding to the target operation is a weak partitioning-specific operator, and its operator type is used to indicate the processing logic of the partitioning node and the coordinating node.

10. The method of claim 8, further comprising: Based on the first weak partition execution logic, the second type of data rows located locally are preprocessed so that the preprocessed data rows can be sent to the coordination node; The preprocessing method is matched to the operation type of the target operation.

11. The method according to claim 10, If the operation type is grouping, then the preprocessing method includes aggregation; If the operation type is deduplication, then the preprocessing method includes deduplication.

12. The method according to claim 8, further comprising: Based on the first weak partition execution logic, after sending the second type of data row located locally to the coordinating node, the second type of data row located locally is deleted. The first weak partition execution logic is further used to instruct the coordinating node to determine the partition node corresponding to the second type of data row based on the partitioning rules applicable to the received second type of data row, and to allocate the second type of data row to the corresponding partition node.

13. The method of claim 8, the weak partition execution condition further comprising: The proportion of the first type of data rows in the result set is not less than the proportion threshold.

14. The method according to claim 13, If the proportion of the first type of data rows in the result set is less than the proportion threshold, the execution plan adopts the second weak partitioning execution logic; the method further includes: Send all data rows in the result set that are located locally to the coordinating node; The second weak partition execution logic is further used to instruct the coordinating node to uniformly execute the target operation on all received data rows to generate the final execution result of the database operation statement.

15. A database operation method, the method comprising: Obtain the execution plan for the database operation statement, which performs an outer join on a first partition table and a second partition table, and performs a target operation on the result set of the outer join based on the partition key of the second partition table; wherein, if the weak partition execution condition is met, the execution plan adopts the first weak partition execution logic; the weak partition execution condition includes: the outer join operation causes the partition key of the second partition table in the result set to produce a null value; The system receives reported data from each partition node. The reported data includes the second type of data row locally on the corresponding partition node and the partition execution result generated by performing the target operation on the first type of data row in the result set locally. The partition key in the first type of data row corresponding to the second partition table has a non-null value, and the partition key in the second type of data row corresponding to the second partition table has a null value. Based on the first weak partition execution logic, the target operation is uniformly executed on the received second type of data rows, and the generated unified execution result and the received partition execution result are merged into the final execution result of the database operation statement.

16. The method according to claim 15, The first weak partition execution logic is also used to: instruct each partition node to delete the local second-type data rows after sending the local second-type data rows to the coordinating node; The method further comprises: According to the first weak partition execution logic, based on the partitioning rules applicable to the received second type of data row, the partition node corresponding to the second type of data row is determined, and the second type of data row is assigned to the corresponding partition node.

17. The method of claim 15, the weak partition execution condition further comprising: The proportion of the first type of data rows in the result set is not less than the proportion threshold.

18. The method according to claim 17, If the proportion of the first type of data rows in the result set is less than the proportion threshold, the execution plan adopts the second weak partitioning execution logic; the second weak partitioning execution logic is used to: instruct each partition node to send all data rows in the result set that are local; The method further comprises: Based on the second weak partition execution logic, the target operation is uniformly executed on all received data rows to generate the final execution result of the database operation statement.

19. A computer program product comprising a computer program / instructions that, when executed by a processor, implement the steps of the method according to any one of claims 1 to 18.

20. An electronic device, comprising: processor; Memory used to store processor-executable instructions; The processor implements the method as described in any one of claims 1 to 18 by executing the executable instructions.

21. A computer readable storage medium having stored thereon computer instructions, the instructions, when executed by a processor, implementing the steps of the method of any one of claims 1 to 18.

Images