Method, device, equipment, medium and product for merging statement optimization in database

Abstract

The application discloses a database merging statement optimization method, device, equipment, medium and product. The method comprises the following steps: obtaining to-be-processed data, a target table and a hash table; dividing the to-be-processed data into matched data and first unmatched data; traversing each row of data in the first unmatched data, searching the hash table according to the first primary key of the first current row of data, if there is an entry consistent with the first primary key in the hash table, adding the first current row of data to the matched data, if there is no entry consistent with the first primary key in the hash table, inserting the first primary key of the first current row of data into the hash table, and adding the first current row of data to the second unmatched data; inserting the second unmatched data into the target table, and determining the row identifier of the second unmatched data in the target table after insertion; and updating the target table according to the matched data, the hash table and the row identifier. When there are repeated primary keys in the to-be-processed data, the merging statement can still be executed in batches with high performance.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to methods, apparatus, devices, media, and products for optimizing merge statements in databases. Background Technology

[0002] The MERGE INTO statement can conditionally perform UPDATE, DELETE, and INSERT operations in a single atomic operation, based on the matching conditions between the target and source tables.

[0003] When dealing with massive amounts of data, MERGE INTO can be integrated with batch parameter binding technology, allowing the client to bind parameter values ​​from multiple rows of data to a single Structured Query Language (SQL) statement with a pre-generated execution plan, and then send it to the database server for execution all at once.

[0004] However, when duplicate primary key values ​​exist in the source table (i.e., the data to be processed), inserting them into the target table will violate the uniqueness constraint of the target table. Therefore, the batch execution of this SQL will fail and can only degenerate into a non-batch method to ensure the correctness of the results. When there is a massive amount of data, the performance will become extremely poor. Summary of the Invention

[0005] This invention provides a method, apparatus, device, medium, and product for optimizing merge statements in a database, in order to solve the problem that duplicate primary key values ​​exist in the data to be processed, causing batch execution of MERGE INTO statements to fail and be forced to degenerate into non-batch execution.

[0006] According to one aspect of the present invention, a method for optimizing merge statements in a database is provided, comprising: Obtain the data to be processed, the target table, and the hash table. The data to be processed includes data from the source table, and the data to be processed includes a primary key column for matching the data in the target table. The target table includes a base table for receiving the data to be processed. The data to be processed is divided into matching data and first non-matching data. The matching data includes the data to be processed that matches the primary key of the data in the target table, and the first non-matching data includes the data to be processed that does not match the primary key of the data in the target table. Traverse each row of data in the first mismatched data, look up the hash table based on the first primary key of the first current row of data. If there is an entry in the hash table that matches the first primary key, add the first current row of data to the matching data. If there is no entry in the hash table that matches the first primary key, insert the first primary key of the first current row of data into the hash table and add the first current row of data to the second mismatched data. The first primary key is the unprocessed primary key column of the first current row of data. Insert the second mismatched data into the target table, and determine the row identifier of the second mismatched data in the target table after insertion; The target table is updated based on the matching data, the hash table, and the row identifier.

[0007] According to another aspect of the present invention, a database merge statement optimization apparatus is provided, comprising: The acquisition module is used to acquire data to be processed, a target table, and a hash table. The data to be processed includes data in the source table, and the data to be processed includes a primary key column to be processed for matching data in the target table. The target table includes a base table for receiving the data to be processed. A partitioning module is used to partition the data to be processed into matching data and first non-matching data. The matching data includes the data to be processed that matches the primary key of the data in the target table, and the first non-matching data includes the data to be processed that does not match the primary key of the data in the target table. The traversal module is used to traverse each row of data in the first mismatched data, search the hash table according to the first primary key of the first current row of data, if there is an entry in the hash table that matches the first primary key, then add the first current row of data to the matching data; if there is no entry in the hash table that matches the first primary key, then insert the first primary key of the first current row of data into the hash table, and add the first current row of data to the second mismatched data, where the first primary key is the unprocessed primary key column of the first current row of data; An insertion module is used to insert the second mismatched data into the target table and determine the row identifier of the second mismatched data in the target table after insertion; An update module is used to update the target table based on the matching data, the hash table, and the row identifier.

[0008] According to another aspect of the present invention, an electronic device is provided, the electronic device comprising: At least one processor; and A memory communicatively connected to the at least one processor; wherein, The memory stores a computer program that can be executed by the at least one processor, the computer program being executed by the at least one processor to enable the at least one processor to perform the method described in any embodiment of the present invention.

[0009] According to another aspect of the present invention, a computer-readable storage medium is provided, the computer-readable storage medium storing computer instructions for causing a processor to execute and implement the method described in any embodiment of the present invention.

[0010] According to another aspect of the present invention, a computer program product is provided, the computer program product comprising a computer program that, when executed by a processor, implements the method described in any embodiment of the present invention.

[0011] The technical solution of this invention involves acquiring data to be processed, a target table, and a hash table. The data to be processed includes data from a source table and a primary key column for matching data in the target table. The target table includes a base table for receiving the data to be processed. The data to be processed is divided into matching data and first non-matching data. The matching data includes data to be processed that matches the primary key of the data in the target table, and the first non-matching data includes data to be processed that does not match the primary key of the data in the target table. Each row of data in the first non-matching data is traversed, and a hash table is retrieved based on the first primary key of the current row. The process involves: searching the hash table; if an entry matching the first primary key exists in the hash table, adding the first current row data to the matching data; if no entry matching the first primary key exists in the hash table, inserting the first primary key of the first current row data into the hash table; adding the first current row data to the second mismatched data, where the first primary key is the pending primary key column of the first current row data; inserting the second mismatched data into the target table and determining the row identifier of the second mismatched data in the target table after insertion; and updating the target table based on the matching data, the hash table, and the row identifier. The technical solution of this invention first divides the data to be processed into matching data and first non-matching data to perform an initial screening, filtering out the first non-matching data that does not match the primary key of the data in the target table. Then, the first non-matching data is further divided to obtain the second non-matching data that is the first occurrence of the primary key value in the primary key column to be processed, and the matching data that is repeated in the primary key column to be processed. By inserting the second non-matching data into the target table and updating the target table with the matching data, and by performing the insertion first and then the update, the problem of the merge statement having to degenerate into non-batch execution parameter binding due to the duplication of the primary key in the data to be processed can be avoided. While maintaining the performance of the merge statement by batch execution parameter binding, the final execution result is completely consistent with the execution result obtained by the non-batch method.

[0012] It should be understood that the description in this section is not intended to identify key or essential features of the embodiments of the present invention, nor is it intended to limit the scope of the invention. Other features of the invention will become readily apparent from the following description. Attached Figure Description

[0013] To more clearly illustrate the technical solutions in the embodiments of the present invention, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0014] Figure 1 This invention provides a flowchart of a database merge statement optimization method according to Embodiment 1. Figure 2 This is a flowchart of a database merge statement optimization method provided in Embodiment 2 of the present invention; Figure 3 This is a schematic diagram of the structure of a database merge statement optimization device provided in Embodiment 3 of the present invention; Figure 4 A schematic diagram of an electronic device that can be used to implement embodiments of the present invention is shown. Detailed Implementation

[0015] To enable those skilled in the art to better understand the present invention, the technical solutions of the present invention will be clearly and completely described below with reference to the accompanying drawings of the embodiments of the present invention. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of the present invention.

[0016] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this invention are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of the invention described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover a non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.

[0017] MERGE INTO is an SQL statement that conditionally performs update (UPDATE), delete (DELETE), and insert (INSERT) operations within a single atomic operation, based on the matching conditions between the target and source tables. Batch parameter binding is a communication optimization technique between database drivers and database servers. When handling massive amounts of data, MERGE INTO can be integrated with batch parameter binding, allowing clients to bind parameter values ​​from multiple rows of data to a single SQL statement with a pre-generated execution plan, which is then sent to the database server for execution all at once. This approach significantly reduces network round trips and improves batch data processing performance, and is now widely used in incremental updates and data synchronization scenarios.

[0018] When duplicate key data (i.e., primary key values ​​in the primary key column to be processed) exists in the parameter table (i.e., the source table), the execution plan of the merge statement will encounter problems during execution.

[0019] For example, the data to be processed in the source table includes: row 1: c1 = 1, c2 = 1, c3 = 1, c4 = 1; row 2: c1 = 1, c2 = 1, c3 = 2, c4 = 2; row 3: c1 = 1, c2 = 1, c3 = 3, c4 = 3; row 4: c1 = 2, c2 = 2, c3 = 4, c4 = 4; row 5: c1 = 2, c2 = 2, c3 = 5, c4 = 5; row 6: c1 = 2, c2 = 2, c3 = 6, c4 = 6; row 7: c1 = 3, c2 = 3, c3 = 7, c4 = 7; row 8: c1 = 3, c2 = 3, c3 = 8, c4 = 8; row 9: c1 = 3, c2 = 3, c3 = 9, c4 = 9; row 10: c1 = 4, c2 = 4, c3 = 10, c4 = 10.

[0020] Columns c1 and c2 constitute the primary key columns to be processed in table T1. There is a duplicate key every three rows; for example, rows 1-3 have duplicate c1=1 and c2=1, and rows 4-6 have duplicate c1=2 and c2=2. When executed in batch mode, assuming table T1 is currently empty, all 10 rows will not match, thus an insert operation will be performed. The first row will be successfully inserted, but when inserting the second row, since a record with c1=1 and c2=1 already exists, violating the uniqueness constraint of table T1, the batch execution of this SQL statement will fail, and the server will throw an error. If the driver is configured with batch fault tolerance, this SQL statement will be executed again in non-batch mode after the batch execution fails.

[0021] In summary, the success of a batch execution plan using MERGE INTO depends on the specific data. When duplicate keys exist in the parameters, it can only degenerate into a non-batch method to ensure the correctness of the results. When dealing with massive amounts of data, performance becomes extremely poor. Unless the data is deduplicated by key in advance on the application or server, batch execution is impossible, and global data deduplication adds additional execution costs.

[0022] Based on this, the present invention provides a method for optimizing merge statements in a database. The method divides the data by primary key. When the primary key appears for the first time, the corresponding row is inserted. When the primary key does not appear for the first time, the corresponding row is updated. Insertion is performed first, followed by update.

[0023] An exemplary method for processing data (i.e., data to be processed) in the re-partitioned source table includes: Data to be inserted: row 1: c1 = 1, c2 = 1, c3 = 1, c4 = 1; row 4: c1 = 2, c2 = 2, c3 = 4, c4 = 4; row 7: c1 = 3, c2 = 3, c3 = 7, c4 = 7; row 10: c1 = 4, c2 = 4, c3 = 10, c4 = 10.

[0024] Data to be updated: row 2: c1 = 1, c2 = 1, c3 = 2, c4 = 2; row 3: c1 = 1, c2 = 1, c3 = 3, c4 = 3; row 5: c1 = 2, c2 = 2, c3 = 5, c4 = 5; row 6: c1 = 2, c2 = 2, c3 = 6, c4 = 6; row 8: c1 = 3, c2 = 3, c3 = 8, c4 = 8; row 9: c1 = 3, c2 = 3, c3 = 9, c4 = 9.

[0025] Example 1 Figure 1This invention provides a flowchart of a database merge statement optimization method according to Embodiment 1. This embodiment is applicable to situations where the primary key values ​​in the primary key column of the data to be processed are duplicated. The method can be executed by a database merge statement optimization device, which can be implemented in hardware and / or software and can be configured in an electronic device. For example, the electronic device can be a computer, server, or workstation. Figure 1 As shown, the method includes: S110. Obtain the data to be processed, the target table, and the hash table. The data to be processed includes data from the source table, and the data to be processed includes a primary key column for matching the data in the target table. The target table includes a base table for receiving the data to be processed.

[0026] In this embodiment, the data to be processed can be data from a source table. The data to be processed can serve as input data in a merge statement. The data to be processed can be preprocessed data from a source table. The source table can be a table storing parameters to be executed in batches. The source table does not have to be a base table; external batch binding parameters can be used as the source table. The target table can be a base table used to store data. The target table can serve as the receiving side in the merge statement. The target table includes a primary key that can be used to match the data to be processed. The hash table can be an initialized hash table. The hash table can be a structure that stores the primary key column of the data to be processed and the identifier of the row in the target table. The primary key column can be the primary key used by the data to be processed to match the target table. The position of the primary key column in the data to be processed can be specified by the merge statement.

[0027] Specifically, the merge statement is parsed syntactically and semantically to generate a plan for executing the merge statement. The plan's topology can be tree-like, consisting of multiple nodes. The target table is determined according to the plan, and data from the source table is received. After preprocessing, the data to be processed is obtained, and a hash table is initialized.

[0028] For example, a merge statement may include: comparing the data in C1 and C2 (i.e., the primary key columns to be processed) in the parameter table (i.e., the data to be processed in the source table) with the data in the base table T1 (i.e., the target table); if a match is found, C3 and C4 are updated in the target table; otherwise, the rows containing the mismatched C1 and C2 are inserted. Here, the parameter table is the source table, the base table T1 is the target table, and the primary key columns to be processed are C1 and C2.

[0029] S120. Divide the data to be processed into matching data and first non-matching data. The matching data includes the data to be processed that matches the primary key of the data in the target table, and the first non-matching data includes the data to be processed that does not match the primary key of the data in the target table.

[0030] In this embodiment, the matching data can be the data to be processed where the primary key column to be processed matches the primary key of the data in the target table. The matching data can be the data to be processed by the match (MATCHED) branch in the merge statement. The first non-matching data can be the data to be processed where the primary key column to be processed does not match the primary key of the data in the target table. The first non-matching data can be the data to be processed by the non-match (NOT MATCHED) branch in the merge statement. The match branch and the non-matching branch can be planned branches, each with corresponding clauses in the merge statement. The first non-matching data may include data where the primary key value in the primary key column is duplicated. Therefore, the first non-matching data needs to be further divided to extract the data where the primary key value in the primary key column first appears, avoiding duplicate primary key values ​​in the primary key column when the data to be processed is inserted into the target table, thus satisfying the uniqueness constraint requirement of the primary key. The primary key can be the primary key of the data in the target table. The position of the primary key in the column of the target table can be indicated by the merge statement.

[0031] Specifically, based on whether the primary key column to be processed has a consistent primary key in the target table, the data to be processed is divided into matching data and first non-matching data. The matching data is input into the MATCHED branch for processing, and the first non-matching data is further divided.

[0032] For example, when the MERGE INTO statement contains a clause indicating the fields to be inserted (a clause indicating that the rows containing the mismatched C1 and C2 will be inserted if there is no match), if the first mismatched data is not empty, then the first mismatched data will be partitioned; otherwise, no partitioning is required, and the target table will be updated directly with the matching data as indicated by the MERGE INTO statement.

[0033] S130. Traverse each row of data in the mismatched data, and search the hash table according to the first primary key of the first current row data. If there is an entry in the hash table that matches the first primary key, add the first current row data to the matching data. If there is no entry in the hash table that matches the first primary key, insert the first primary key of the first current row data into the hash table and add the first current row data to the second mismatched data. The first primary key is the unprocessed primary key column of the first current row data.

[0034] In this embodiment, the first current row of data can be a row from the currently traversed non-matching data. The first primary key can be the primary key of the first current row of data. The hash table can be searched to see if the first primary key appears for the first time in the non-matching data. The second non-matching data can be the data where the first primary key first appears in the hash table. Since the second non-matching data satisfies the uniqueness constraint of the primary key, batch insertion into the target table can be performed.

[0035] Specifically, the process iterates through the mismatched data row by row. The currently visited row is taken as the first current row. The primary key of the first current row is converted into a hash value. A quick search is performed in the hash table to see if an entry matching the hash value exists. If an entry does exist, it means the primary key is repeated in the first mismatched data and cannot be directly inserted into the target table, failing the uniqueness constraint. Therefore, the corresponding first current row is appended to the matching data. If no entry matching the primary key exists in the hash table, the corresponding first current row is added to the second mismatched data, and the corresponding primary key is inserted into the hash table. This process is repeated row by row until all mismatched data has been traversed.

[0036] S140. Insert the second mismatched data into the target table, and determine the row identifier of the second mismatched data in the target table after insertion.

[0037] In this embodiment, the row identifier can be used to identify the position of the row in the target table where the second mismatched data is inserted. The row identifier can be the identifier of the row where the primary key first appears in the data to be processed is inserted into the target table. For the data to be processed corresponding to the primary key column that appears repeatedly, it can be updated according to the row identifier when the primary key column first appears.

[0038] Specifically, the second mismatch data is inserted into the target table row by row, the row identifier of the second mismatch data in the target table is recorded, and the row identifier is stored in the corresponding position in the hash table.

[0039] S150. Update the target table according to the matching data, the hash table, and the row identifier.

[0040] Specifically, the primary key values ​​in the primary key column of the matching data are converted into hash values ​​and retrieved from the hash table. If a corresponding entry exists in the hash table, it means that the corresponding matching data is derived from the first non-matching data. For data in the data to be processed where the primary key value in the primary key column is not the first occurrence, the row identifier of the first occurrence of the primary key value in the primary key column is assigned to the matching data where the primary key value in the primary key column is repeated. According to the instructions of the merge statement, the fields in the data to be processed are updated to the corresponding positions in the target table. If no corresponding entry exists in the hash table, the primary key in the target table is directly matched with the primary key in the primary key column to be processed, and the fields in the data to be processed are updated to the corresponding positions obtained after the matching in the target table.

[0041] The technical solution of this invention involves acquiring data to be processed, a target table, and a hash table. The data to be processed includes data from a source table and a primary key column for matching data in the target table. The target table includes a base table for receiving the data to be processed. The data to be processed is divided into matching data and first non-matching data. The matching data includes data to be processed that matches the primary key of the data in the target table, and the first non-matching data includes data to be processed that does not match the primary key of the data in the target table. Each row of data in the first non-matching data is traversed, and a hash table is retrieved based on the first primary key of the current row. The process involves: searching the hash table; if an entry matching the first primary key exists in the hash table, adding the first current row data to the matching data; if no entry matching the first primary key exists in the hash table, inserting the first primary key of the first current row data into the hash table; adding the first current row data to the second mismatched data, where the first primary key is the pending primary key column of the first current row data; inserting the second mismatched data into the target table and determining the row identifier of the second mismatched data in the target table after insertion; and updating the target table based on the matching data, the hash table, and the row identifier. The technical solution of this invention first divides the data to be processed into matching data and first non-matching data to perform an initial screening, filtering out the first non-matching data that does not match the primary key of the data in the target table. Then, the first non-matching data is further divided to obtain the second non-matching data that is the first occurrence of the primary key value in the primary key column to be processed, and the matching data that is repeated in the primary key column to be processed. By inserting the second non-matching data into the target table and updating the target table with the matching data, and by performing the insertion first and then the update, the problem of the merge statement having to degenerate into non-batch execution parameter binding due to the duplication of the primary key in the data to be processed can be avoided. While maintaining the performance of the merge statement by batch execution parameter binding, the final execution result is completely consistent with the execution result obtained by the non-batch method.

[0042] In one embodiment, the step of looking up the hash table based on the first primary key of the first current row of data includes: S1301. Determine the hash value of the first primary key of the first current row of data based on the primary key identifier array, wherein the primary key identifier array includes an array storing column identifiers of the primary key column to be processed in the data to be processed.

[0043] In this embodiment, the primary key identifier array can be an array used to indicate the position of the primary key column to be processed in the data to be processed. The primary key identifier array can be used to store the identifier of the primary key column in the data to be processed. The hash value can be the result of hashing the primary key of the data to be processed. Converting the first primary key to a hash value can improve the lookup speed in the hash table.

[0044] Specifically, based on the primary key identifier array, find the first primary key in the corresponding field of the data to be processed, and convert the first primary key into a hash value.

[0045] For example, after obtaining the first primary key, the first primary key is converted into a binary sequence. If the first primary key is a composite primary key (i.e., it contains multiple primary key columns to be processed), the binary sequence is concatenated and compressed into a fixed-length integer through bitwise operations to obtain the hash value.

[0046] S1302. Search for the corresponding structure in the hash table according to the hash value. The structure includes data members that are consistent with the first primary key.

[0047] In this embodiment, the structure can be used to store information about the data to be processed corresponding to the first occurrence of the primary key value in the primary key column. The structure can include multiple members; for example, a member can be used to store the first occurrence of the primary key value in the primary key column, and / or a member can be used to store row identifiers. A data member can be used to store the first occurrence of the primary key value in the primary key column. Each structure uniquely corresponds to one data member.

[0048] Specifically, the data member that matches the hash value is searched in the structure of the hash table to determine the structure corresponding to the data member.

[0049] In one embodiment, inserting the first primary key of the first current row data into the hash table includes: Create a new structure, insert the first primary key of the first current row data into the data members of the structure, and save the structure in the hash table.

[0050] Specifically, if there is no entry in the hash table that matches the first primary key, a new structure is created, the hash value of the first primary key is assigned to the data members of the structure, and the structure is stored in the hash table.

[0051] In one embodiment, the row identifier is stored in a row identifier member, which includes a row identifier member in a structure. Updating the target table based on the matching data, the hash table, and the row identifier includes: S1501. Determine the hash value of the second primary key of the second current row of data based on the primary key identifier array.

[0052] In this embodiment, the second current row data can be data traversed from the matching data. Data is extracted from the matching data row by row for processing, and the extracted data can be used as the second current row data. The second primary key can be the primary key of the second current row data.

[0053] Specifically, the position of the second primary key in the second current row of the matched data is determined based on the primary key identifier array, the second primary key at the corresponding position is retrieved, and the hash value corresponding to the second primary key is determined.

[0054] S1502. Based on the hash value, query whether a corresponding target structure exists in all the structures of the hash table, wherein the data members of the target structure are consistent with the second primary key.

[0055] In this embodiment, the target structure can be a structure that matches the second primary key of the second current row data.

[0056] Specifically, based on the hash value of the second primary key, a search is performed in all structures of the hash table to find if there is a data member that matches the second primary key and its corresponding target structure.

[0057] S1503. If it exists, then the row identifier member in the target structure is assigned to the row identifier corresponding to the second current row data.

[0058] Specifically, if a target structure exists, it indicates that the second current row data comes from the first mismatched data. Since the second primary key of the second current row data appears repeatedly, the row identifier in the target table where the second primary key value first appears in the target structure is assigned to the second current row data. This is manifested by assigning the row identifier member of the target structure to the corresponding row identifier of the second current row data. If the target structure does not exist, the row identifier member of the target structure is not assigned to the second current row data.

[0059] S1504. Update the target table based on the second current row data and the corresponding row identifier.

[0060] Specifically, if a target structure exists, the position in the target table is located based on the row identifier, and the corresponding fields indicated by the merge statement in the second current row of data are updated to the corresponding positions in the target table. If a target structure does not exist, the corresponding primary key is directly matched according to the second primary key, and the corresponding fields indicated by the merge statement in the second current row of data are updated to the corresponding positions in the target table.

[0061] Example 2 Figure 2This is a flowchart illustrating a database merge statement optimization method according to Embodiment 2 of the present invention. This embodiment is an optimization based on any of the above embodiments, and mainly includes a detailed description of the process of obtaining the data to be processed. It should be noted that technical details not described in detail in this embodiment can be found in any of the above embodiments. Figure 2 As shown, the method includes: S210. Obtain the execution plan tree, target table, and hash table. The execution plan tree includes the execution plan tree of the merge statement, and the execution plan tree includes the batch binding parameter scan operator and the merge operator.

[0062] In this embodiment, the execution plan tree can be the execution plan tree obtained after parsing the merged statements syntactically and semantically. The topology of the execution plan tree can be tree-shaped. The execution plan tree includes multiple nodes. Nodes can be operators. The batch binding parameter scan operator can be an operator node that represents scanning the batch parameters as a table. The batch parameters can be parameters in the source table, i.e., the unprocessed data to be processed. The batch binding parameter scan operator can be denoted as the PSCN operator. The batch binding parameter scan operator can be used to preprocess the batch parameters to obtain the data to be processed. The merge operator can be an operator used to coordinate the matching, updating, and insertion logic between the data to be processed and the target table. The merge operator can be used to perform operations such as dividing the data to be processed into matching data and first non-matching data, dividing the first non-matching data into second non-matching data and matching data, inserting the second non-matching data, and updating the matching data.

[0063] Specifically, the merge statement is parsed syntactically and semantically to obtain the execution plan tree corresponding to the merge statement. The target table is obtained, and the hash table is initialized.

[0064] S220, Receive the data to be processed from the left child operator of the merge operator.

[0065] In this embodiment, the left child operator can be the left child of the merge operator on the topology. This invention does not limit the type of the left child operator.

[0066] Specifically, based on the topology of the execution plan tree, the left child operator of the merge operator is determined, and the data to be processed from the left child operator is received.

[0067] S230. Divide the data to be processed into matching data and first non-matching data. The matching data includes the data to be processed that matches the primary key of the data in the target table, and the first non-matching data includes the data to be processed that does not match the primary key of the data in the target table.

[0068] S240. Traverse each row of data in the first mismatched data, and search the hash table according to the first primary key of the first current row data. If there is an entry in the hash table that matches the first primary key, add the first current row data to the matching data. If there is no entry in the hash table that matches the first primary key, insert the first primary key of the first current row data into the hash table and add the first current row data to the second mismatched data. The first primary key is the unprocessed primary key column of the first current row data.

[0069] S250. Insert the second mismatched data into the target table, and determine the row identifier of the second mismatched data in the target table after insertion.

[0070] S260. Update the target table according to the matching data, the hash table, and the row identifier.

[0071] The technical solution of this invention involves obtaining an execution plan tree, a target table, and a hash table. The execution plan tree includes an execution plan tree for merge statements, and includes a batch binding parameter scan operator and a merge operator. It receives data to be processed from the left child operator of the merge operator. The data to be processed is divided into matching data and first non-matching data. The matching data includes data to be processed that matches the primary key of the data in the target table, and the first non-matching data includes data to be processed that does not match the primary key of the data in the target table. The system iterates through each row of data in the first non-matching data, and determines the hash table based on the first primary key of the current row. The process involves: 1) searching the hash table using a key; 2) if an entry matching the first primary key exists in the hash table, adding the first current row data to the matching data; 3) inserting the first primary key of the first current row data into the hash table, adding the first current row data to the second mismatched data, where the first primary key is the pending primary key column of the first current row data; 4) inserting the second mismatched data into the target table and determining the row identifier of the second mismatched data in the target table after insertion; and 5) updating the target table based on the matching data, the hash table, and the row identifier. This embodiment of the invention preprocesses the pending data using a batch binding parameter scan operator before sending it to a merge operator, supporting the processing of batch parameters as a table, achieving batch data binding processing, and improving the efficiency of batch data binding.

[0072] In one embodiment, prior to obtaining the execution plan tree, the method further includes: Check whether the merge statement meets the optimization conditions; The optimization conditions include at least one of the following: The target table is the base table; the merge statement includes a clause indicating that the data source of the data to be processed is a predefined data source; the join condition in the merge statement includes only equality matching of all primary key columns and parameters; the matching clause in the merge statement only includes update statements and has no additional filtering conditions; the insert statement in the non-matching clause of the merge statement can only be in value insert form.

[0073] In this embodiment, the optimization condition can be a precondition used to determine whether the merge statement can be optimized. The data source can be the source of the data to be processed. For example, the data source can be a source table. The data source can be specified through a specific clause in the merge statement. For example, the data source can be specified through the USING clause in the merge statement. A predefined data source can be a table with a specific data structure and data type. For example, a predefined data source can be a dummy table (DUAL table) and / or a system dummy table (denoted as SYSDUAL table). Predefined data sources can be used to perform calculations that do not require the participation of an actual data table; that is, the source table does not have to be an actual data table, the merge statement can still obtain data from the source table, and after preprocessing, treat the data to be processed as a table. The join condition can be a join condition between the target table and a specific field in the data to be processed. The join condition can include equality matching between the primary key column of the data to be processed and the primary key of the target table. Equality matching can be a matching method that matches primary keys with the same key value with the primary key in the primary key column of the data to be processed. The matching clause can be a clause in the merge statement used to instruct the corresponding operation to be performed on the matched fields. A matching clause can be written as a MATCHED clause. An update statement is a clause that instructs the execution of an update operation on matched fields. An update statement is written as an UPDATE statement. A filter condition is a condition that filters matched fields. Filter conditions can be indicated by specific keywords in the MATCHED clause, such as by the WHERE clause. A non-match clause is a clause in a merge statement that instructs the execution of corresponding operations on non-matched fields. A non-match clause is written as a NOT MATCHED clause. An insert statement is a statement that instructs the insertion of non-matched fields into the corresponding positions in the target table. Value insertion form can be a form that inserts the numerical values ​​from non-matched fields into the corresponding positions in the target table.

[0074] Specifically, when performing batch parameter binding optimization on merge statements, the merge statements must meet the optimization conditions before optimization can be performed.

[0075] The present invention will be described by way of example below: This invention modifies the execution flow of the MERGE INTO operator (i.e., the merge operator). For scenarios with duplicate primary keys, the inserted data (i.e., data to be processed) can be divided according to the primary key (i.e., the primary key column to be processed). The first row is used for insertion, and the remaining rows are used for updates. Furthermore, during execution, INSERT (insertion) is performed first, followed by UPDATE (update). This achieves the effect of non-batch execution, ensuring the correctness of the result set, while still allowing for batch execution and improving performance. By dividing the INSERT data and adjusting the execution order of INSERT and UPDATE, the MERGE INTO statement can be executed in batches while ensuring correct results.

[0076] In one example, an optimization method for performing batch binding parameters in a MERGE INTO statement includes: To check if MERGE INTO can be optimized for batch parameter binding, the following conditions must be met: the target table must be a base table; the USING clause must specify DUAL or SYSDUAL; the join condition must contain only equality matches between all primary key columns and parameters; the MATCHED clause must contain only UPDATE statements without any additional WHERE conditions; the NOT MATCHED clause must contain only value-based insert statements (i.e., the target table is a base table; the merge statement must include a clause indicating that the data source for the data to be processed is a predefined data source; the join condition in the merge statement must contain only equality matches between all primary key columns and parameters; the matching clause in the merge statement must contain only update statements without any additional filtering conditions; and the non-matching clause in the merge statement must contain only value-based insert statements). If these conditions (i.e., optimization conditions) are met, batch parameter binding optimization can be performed. When constructing the source table columns used during MERGE INTO execution, the parameter columns from the join condition are added for deduplication based on the primary key during execution. Otherwise, the optimization ends.

[0077] When the MERGE INTO operator begins execution, it searches the leaf layer of the execution plan tree for the existence of the PSCN operator (i.e., the batch binding parameter scan operator), indicating that the data originates from the batch binding parameter table (i.e., the source table). If it exists, the following initialization operations are performed: a hash table is created for subsequent deduplication, and a key_id_arr array is created to record the parameter columns in the join condition, i.e., the primary key columns (i.e., the primary key columns to be processed).

[0078] The MERGE INTO operator receives data from its left child operator and divides the data into two categories: matching data (denoted as `match_data`), which contains records that match in the target table and corresponds to the data to be processed in the MATCHED branch (number of rows denoted as `n_match`), and non-matching data (i.e., the first non-matching data) (denoted as `unmatch_data`), which contains records that do not match in the target table and corresponds to the data to be processed in the NOT MATCHED branch (number of rows denoted as `n_unmatch`). If MERGE INTO contains an INSERT clause and `n_unmatch > 0`, then `unmatch_data` needs to be further divided; otherwise, the normal INSERT / UPDATE operation is executed directly.

[0079] Define a structure (denoted as nmrg_item). `nmrg_item->data` (the data member) records the key value of the first row of data with the same key. `nmrg_item->rowid` (the row identifier member) records the rowid (row identifier) ​​returned after inserting the first row of data corresponding to `nmrg_item->data` into the target table. Iterate through `unmatch_data`, calculating a hash slot and generating a hash value for each row based on `key_id_arr` to improve search efficiency in the hash table. Check if an `nmrg_item` exists in the hash table whose `data` is equal to the key value (i.e., the first primary key) of `unmatch_data[i]` (the first current row of data). If it does not exist, it means the key value in that row of data appears for the first time. Perform the following operations: create a new structure `nmrg_item->data` to store the key value of that row of data, insert `nmrg_item` into the hash table, add that row of data to `unmatch_data2` (the second unmatched data), and increment `n_unmatch2`. If it exists, it means that the key value of the data in that row has appeared repeatedly. The data in that row is appended to match_data, and n_match is incremented by one.

[0080] After the above steps, the original unmatched data (i.e., the original first unmatched data) has been divided according to key_id_arr. The first row of data where each key appears, i.e., the first row of duplicate data, is stored in unmatch_data2, with n_unmatch2 rows. This is the data to be inserted in subsequent INSERT operations. The remaining duplicate data is appended to match_data in sequence. The total number of rows of matched data and duplicate data is n_match, which is the data to be updated in subsequent UPDATE operations.

[0081] After partitioning, since some data in match_data comes from unmatch_data, these data need the rowid (row identifier) ​​of the target table when performing UPDATE (update) operations. However, the rowid has not yet been generated at this time. Therefore, when the MERGEINTO operator performs insertion, it needs to record the rowid returned after unmatch_data2 is inserted. After the insertion is completed, it is registered in the corresponding nmrg_item->rowid (row identifier member).

[0082] Before the MERGE INTO operator performs the update, the rowid needs to be filled into the data partitioned from unmatch_data in match_data. The operation is as follows: Iterate through match_data, calculate the hash slot and generate a hash value for each row of data (i.e., the second current row of data) based on key_id_arr, and check in the hash table whether there exists an nmrg_item whose data is equal to the key value (i.e., the second primary key) of match_data[i] (i.e., the second current row of data). If it exists, it means that the row of data is duplicate data partitioned from unmatch_data, and the rowid of the row of data is set to nmrg_item->rowid; otherwise, no processing is performed.

[0083] Continue processing the next batch of data until all data has been processed.

[0084] Example 3 Figure 3 This is a schematic diagram of a database merge statement optimization device provided in Embodiment 3 of the present invention. Figure 3 As shown, the device includes: The acquisition module 310 is used to acquire data to be processed, a target table, and a hash table. The data to be processed includes data in the source table, and the data to be processed includes a primary key column to be processed for matching with data in the target table. The target table includes a base table for receiving the data to be processed. The partitioning module 320 is used to partition the data to be processed into matching data and first non-matching data. The matching data includes the data to be processed that matches the primary key of the data in the target table, and the first non-matching data includes the data to be processed that does not match the primary key of the data in the target table. The traversal module 330 is used to traverse each row of data in the first mismatched data, search the hash table according to the first primary key of the first current row of data, if there is an entry in the hash table that matches the first primary key, then add the first current row of data to the matching data; if there is no entry in the hash table that matches the first primary key, then insert the first primary key of the first current row of data into the hash table, and add the first current row of data to the second mismatched data, where the first primary key is the unprocessed primary key column of the first current row of data; Insertion module 340 is used to insert the second mismatched data into the target table and determine the row identifier of the second mismatched data in the target table after insertion; The update module 350 is used to update the target table based on the matching data, the hash table, and the row identifier.

[0085] The technical solution of this invention involves an acquisition module that acquires data to be processed, a target table, and a hash table. The data to be processed includes data from a source table and a primary key column for matching data in the target table. The target table includes a base table for receiving the data to be processed. A partitioning module divides the data to be processed into matching data and first non-matching data. The matching data includes data to be processed that matches the primary key of the data in the target table, and the first non-matching data includes data to be processed that does not match the primary key of the data in the target table. A traversal module traverses each row of data in the first non-matching data and, based on the first primary key of the current row, divides the data into matching data and first non-matching data. The system searches the hash table using a key. If an entry matching the first primary key exists in the hash table, the first current row data is added to the matching data. If no entry matching the first primary key exists in the hash table, the first primary key of the first current row data is inserted into the hash table, and the first current row data is added to the second mismatched data. The first primary key is the unprocessed primary key column of the first current row data. The insertion module inserts the second mismatched data into the target table and determines the row identifier of the second mismatched data in the target table after insertion. The update module updates the target table based on the matching data, the hash table, and the row identifier. The technical solution of this invention first divides the data to be processed into matching data and first non-matching data to perform an initial screening, filtering out the first non-matching data that does not match the primary key of the data in the target table. Then, the first non-matching data is further divided to obtain the second non-matching data that is the first occurrence of the primary key value in the primary key column to be processed, and the matching data that is repeated in the primary key column to be processed. By inserting the second non-matching data into the target table and updating the target table with the matching data, and by performing the insertion first and then the update, the problem of the merge statement having to degenerate into non-batch execution parameter binding due to the duplication of the primary key in the data to be processed can be avoided. While maintaining the performance of the merge statement by batch execution parameter binding, the final execution result is completely consistent with the execution result obtained by the non-batch method.

[0086] In one embodiment, the acquisition module 310 is specifically used for: Obtain the execution plan tree, which includes the execution plan tree for merging statements, and the execution plan tree includes the batch binding parameter scan operator and the merge operator; Receive the data to be processed from the left child operator of the merge operator.

[0087] In one embodiment, before obtaining module 310, the following is also included: The checking module is used to check whether the merge statement meets the optimization conditions; The optimization conditions include at least one of the following: The target table is the base table; the merge statement includes a clause indicating that the data source of the data to be processed is a predefined data source; the join condition in the merge statement includes only equality matching of all primary key columns and parameters; the matching clause in the merge statement only includes update statements and has no additional filtering conditions; the insert statement in the non-matching clause of the merge statement can only be in value insert form.

[0088] In one embodiment, traversing module 330 is specifically used for: The hash value of the first primary key of the first current row of data is determined based on the primary key identifier array, wherein the primary key identifier array includes an array storing the column identifiers of the primary key column to be processed in the data to be processed; The hash value is used to look up the corresponding structure in the hash table, and the structure includes data members that are consistent with the first primary key.

[0089] In one embodiment, traversing module 330 is specifically used for: Create a new structure, insert the first primary key of the first current row data into the data members of the structure, and save the structure in the hash table.

[0090] In one embodiment, the row identifier is stored in a row identifier member, which includes a row identifier member in a structure. The update module 350 is specifically used for: The hash value of the second primary key of the second current row of data is determined based on the primary key identifier array; Based on the hash value, query whether a corresponding target structure exists in all the structures of the hash table, wherein the data members of the target structure are consistent with the second primary key; If it exists, then the row identifier member in the target structure is assigned to the row identifier corresponding to the second current row data; The target table is updated based on the second current row data and the corresponding row identifier.

[0091] The database merge statement optimization device provided in this embodiment of the invention can execute the database merge statement optimization method provided in any embodiment of the invention, and has the corresponding functional modules and beneficial effects of the method.

[0092] Example 4 Figure 4A schematic diagram of an electronic device that can be used to implement embodiments of the present invention is shown. The electronic device is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as personal digital processors, cellular phones, smartphones, wearable devices (such as helmets, glasses, watches, etc.), and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely illustrative and are not intended to limit the implementation of the invention described and / or claimed herein.

[0093] like Figure 4 As shown, the electronic device 10 includes at least one processor 11 and a memory, such as a read-only memory (ROM) 12 or a random access memory (RAM) 13, communicatively connected to the at least one processor 11. The memory stores computer programs executable by the at least one processor. The processor 11 can perform various appropriate actions and processes based on the computer program stored in the ROM 12 or loaded from storage unit 18 into the RAM 13. The RAM 13 can also store various programs and data required for the operation of the electronic device 10. The processor 11, ROM 12, and RAM 13 are interconnected via a bus 14. An input / output (I / O) interface 15 is also connected to the bus 14.

[0094] Multiple components in electronic device 10 are connected to I / O interface 15, including: input unit 16, such as keyboard, mouse, etc.; output unit 17, such as various types of displays, speakers, etc.; storage unit 18, such as disk, optical disk, etc.; and communication unit 19, such as network card, modem, wireless transceiver, etc. Communication unit 19 allows electronic device 10 to exchange information / data with other devices through computer networks such as the Internet and / or various telecommunications networks.

[0095] Processor 11 can be a variety of general-purpose and / or special-purpose processing components with processing and computing capabilities. Some examples of processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various special-purpose artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, a digital signal processor (DSP), and any suitable processor, controller, microcontroller, etc. Processor 11 performs the various methods and processes described above, such as the methods proposed in this invention.

[0096] In some embodiments, the method proposed in this invention can be implemented as a computer program tangibly contained in a computer-readable storage medium, such as storage unit 18. In some embodiments, part or all of the computer program can be loaded and / or installed on electronic device 10 via ROM 12 and / or communication unit 19. When the computer program is loaded into RAM 13 and executed by processor 11, one or more steps of the method described above can be performed. Alternatively, in other embodiments, processor 11 can be configured to perform the method proposed in this invention by any other suitable means (e.g., by means of firmware).

[0097] Various embodiments of the systems and techniques described above herein can be implemented in digital electronic circuit systems, integrated circuit systems, field programmable gate arrays (FPGAs), application-specific integrated circuits (ASICs), application-specific standard parts (ASSPs), systems-on-chip (SoCs), complex programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various embodiments may include implementations in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which may be a dedicated or general-purpose programmable processor, capable of receiving data and instructions from a storage system, at least one input device, and at least one output device, and transmitting data and instructions to the storage system, the at least one input device, and the at least one output device.

[0098] Computer programs used to implement the methods of the present invention may be written in any combination of one or more programming languages. These computer programs may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, such that when executed by the processor, the computer programs cause the functions / operations specified in the flowcharts and / or block diagrams to be performed. The computer programs may be executed entirely on a machine, partially on a machine, or as a standalone software package, partially on a machine and partially on a remote machine, or entirely on a remote machine or server.

[0099] In the context of this invention, a computer-readable storage medium stores computer instructions that are used to cause a processor to execute and implement the method provided by this invention.

[0100] The present invention also provides a computer program product comprising a computer program that, when executed by a processor, implements the method provided according to embodiments of the present invention.

[0101] Computer-readable storage media can be tangible media that may contain or store computer programs for use by or in conjunction with an instruction execution system, apparatus, or device. Computer-readable storage media can be, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatus, or devices, or any suitable combination thereof. Alternatively, computer-readable storage media can be machine-readable signal media. More specific examples of machine-readable storage media include electrical connections based on one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fibers, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof.

[0102] To provide interaction with a user, the systems and techniques described herein can be implemented on an electronic device having: a display device for displaying information to the user, such as a cathode ray tube (CRT) or a liquid crystal display (LCD); and a keyboard and pointing device (e.g., a mouse or trackball) through which the user provides input to the electronic device. Other types of devices can also be used to provide interaction with the user; for example, feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including sound input, voice input, or tactile input).

[0103] The systems and technologies described herein can be implemented in computing systems that include backend components (e.g., as data servers), or middleware components (e.g., application servers), or frontend components (e.g., user computers with graphical user interfaces or web browsers through which users can interact with implementations of the systems and technologies described herein), or any combination of such backend, middleware, or frontend components. The components of the system can be interconnected via digital data communication of any form or medium (e.g., communication networks). Examples of communication networks include local area networks (LANs), wide area networks (WANs), blockchain networks, and the Internet.

[0104] A computing system can include clients and servers. Clients and servers are generally located far apart and typically interact through communication networks. The client-server relationship is created by computer programs running on the respective computers and having a client-server relationship with each other. The server can be a cloud server, also known as a cloud computing server or cloud host, which is a hosting product within the cloud computing service system. It addresses the shortcomings of traditional physical hosts and Virtual Private Server (VPS) services, such as high management difficulty and weak business scalability.

[0105] It should be understood that the various forms of processes shown above can be used, with steps reordered, added, or deleted. For example, the steps described in this invention can be executed in parallel, sequentially, or in different orders, as long as the desired result of the technical solution of this invention can be achieved, and this is not limited herein.

[0106] The specific embodiments described above do not constitute a limitation on the scope of protection of this invention. Those skilled in the art should understand that various modifications, combinations, sub-combinations, and substitutions can be made according to design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of this invention should be included within the scope of protection of this invention.

Claims

1. A method for optimizing merge statements in a database, characterized in that, include: Obtain the data to be processed, the target table, and the hash table. The data to be processed includes data from the source table, and the data to be processed includes a primary key column for matching the data in the target table. The target table includes a base table for receiving the data to be processed. The data to be processed is divided into matching data and first non-matching data. The matching data includes the data to be processed that matches the primary key of the data in the target table, and the first non-matching data includes the data to be processed that does not match the primary key of the data in the target table. Traverse each row of data in the first mismatched data, look up the hash table based on the first primary key of the first current row of data. If there is an entry in the hash table that matches the first primary key, add the first current row of data to the matching data. If there is no entry in the hash table that matches the first primary key, insert the first primary key of the first current row of data into the hash table and add the first current row of data to the second mismatched data. The first primary key is the unprocessed primary key column of the first current row of data. Insert the second mismatched data into the target table, and determine the row identifier of the second mismatched data in the target table after insertion; The target table is updated based on the matching data, the hash table, and the row identifier.

2. The method according to claim 1, characterized in that, The acquisition of data to be processed includes: Obtain the execution plan tree, which includes the execution plan tree for merging statements, and the execution plan tree includes a batch binding parameter scan operator and a merge operator; Receive the data to be processed from the left child operator of the merge operator.

3. The method according to claim 2, characterized in that, Before obtaining the execution plan tree, the process also includes: Check whether the merge statement meets the optimization conditions; The optimization conditions include at least one of the following: The target table is the base table; the merge statement includes a clause indicating that the data source of the data to be processed is a predefined data source; the join condition in the merge statement includes only equality matching of all primary key columns and parameters; the matching clause in the merge statement only includes update statements and has no additional filtering conditions; the insert statement in the non-matching clause of the merge statement can only be in value insert form.

4. The method according to claim 1, characterized in that, The step of looking up the hash table based on the first primary key of the first current row of data includes: The hash value of the first primary key of the first current row of data is determined based on the primary key identifier array, wherein the primary key identifier array includes an array storing the column identifiers of the primary key column to be processed in the data to be processed; The hash value is used to look up the corresponding structure in the hash table, and the structure includes data members that are consistent with the first primary key.

5. The method according to claim 1, characterized in that, The step of inserting the first primary key of the first current row data into the hash table includes: Create a new structure, insert the first primary key of the first current row data into the data members of the structure, and save the structure in the hash table.

6. The method according to claim 1, characterized in that, The row identifier is stored in a row identifier member, which includes a row identifier member in a structure. Updating the target table based on the matching data, the hash table, and the row identifier includes: The hash value of the second primary key of the second current row of data is determined based on the primary key identifier array; Based on the hash value, query whether a corresponding target structure exists in all the structures of the hash table, wherein the data members of the target structure are consistent with the second primary key; If it exists, then the row identifier member in the target structure is assigned to the row identifier corresponding to the second current row data; The target table is updated based on the second current row data and the corresponding row identifier.

7. A database merge statement optimization device, characterized in that, include: The acquisition module is used to acquire data to be processed, a target table, and a hash table. The data to be processed includes data in the source table, and the data to be processed includes a primary key column to be processed for matching data in the target table. The target table includes a base table for receiving the data to be processed. A partitioning module is used to partition the data to be processed into matching data and first non-matching data. The matching data includes the data to be processed that matches the primary key of the data in the target table, and the first non-matching data includes the data to be processed that does not match the primary key of the data in the target table. The traversal module is used to traverse each row of data in the first mismatched data, search the hash table according to the first primary key of the first current row of data, if there is an entry in the hash table that matches the first primary key, then add the first current row of data to the matching data; if there is no entry in the hash table that matches the first primary key, then insert the first primary key of the first current row of data into the hash table, and add the first current row of data to the second mismatched data, where the first primary key is the unprocessed primary key column of the first current row of data; An insertion module is used to insert the second mismatched data into the target table and determine the row identifier of the second mismatched data in the target table after insertion; An update module is used to update the target table based on the matching data, the hash table, and the row identifier.

8. An electronic device, characterized in that, The electronic device includes: At least one processor; and A memory communicatively connected to the at least one processor; wherein, The memory stores a computer program that can be executed by the at least one processor to enable the at least one processor to perform the method of any one of claims 1-6.

9. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer instructions that cause a processor to execute the method of any one of claims 1-6.

10. A computer program product, characterized in that, The computer program product includes a computer program that, when executed by a processor, implements the method according to any one of claims 1-6.

Images