A method for synchronizing a database table and a method for optimizing a database statement

Abstract

The present specification provides a database table synchronization method and a database statement optimization method, the database table comprising a source table and a target table, the method comprising: obtaining a database statement, the database statement being used to perform a merge operation on the target table and the source table according to an outer join result of the target table and the source table; in the case that the merge operation only comprises an insert operation on the target table according to the source table, performing an anti-join query on the target table and the source table to obtain a data row existing in the source table but not existing in the target table; and inserting the obtained data row into the target table.

Description

Technical Field

[0001] This specification relates to the field of database technology, and in particular to a method for synchronizing database tables and a method for optimizing database statements. Background Technology

[0002] A relational database is a database that uses a relational model to organize data. It stores data in rows and columns for user understanding; these rows and columns are called database tables. Users can use Structured Query Language (SQL) to query, update, and manage database tables in a relational database system. SQL can be further divided into five categories: Data Query Language (DQL), Data Manipulation Language (DML), Data Denifition Language (DDL), Transaction Control Language (TCL), and Data Control Language (DCL). Outer join queries are a common type of SQL statement. They join two or more database tables according to certain join conditions, returning records that meet the join conditions as well as some records that do not. Its operating principle is to join each row of data in the source table with the target table. If a match is found, the matching data is output; otherwise, null is output. For example, the `MERGE INTO` statement is a database operation statement (DML) based on outer joins. When the source and target tables do not match: if the data exists in the source table but not in the target table, an insert operation is performed on the target table; if the data does not exist in the source table but exists in the target table, an update or deletion operation is performed on the target table; when the source and target tables match: an update or deletion operation is performed on the target table.

[0003] However, in related technologies, when two or more database tables are joined together using an outer join and a merge operation is performed, even if the merge operation only involves inserting data into the target table, it is still necessary to match each row of data in the source table with the target table. This results in a large amount of data being joined and a long execution time for the SQL statement, leading to low efficiency. Summary of the Invention

[0004] To overcome the problems existing in related technologies, this specification provides a method for synchronizing database tables and an optimization method for database statements.

[0005] According to a first aspect of one or more embodiments of this specification, a method for synchronizing database tables is provided, the database tables including a source table and a target table, the method comprising:

[0006] Obtain a database statement, which is used to perform a merge operation on the target table and the source table based on the outer join result of the source table and the target table;

[0007] If the merge operation only includes performing an insert operation on the target table based on the source table, a reverse join query is performed on the source table and the target table to obtain data rows that exist in the source table but not in the target table;

[0008] The obtained data rows are inserted into the target table.

[0009] According to a second aspect of one or more embodiments of this specification, a method for optimizing a database statement is provided, the database statement being used to perform a merge operation on a target table and a source table based on the outer join result of a source table and a target table, the method comprising:

[0010] If the merge operation only includes performing an insert operation on the target table based on the source table, the database statement is rewritten as an anti-join query statement. The anti-join query statement is used to perform an anti-join query on the source table and the target table to obtain data rows that exist in the source table but do not exist in the target table.

[0011] The reverse join query statement is assembled into a corresponding insert statement to insert the obtained data rows into the target table.

[0012] According to a third aspect of one or more embodiments of this specification, a database table synchronization apparatus is provided, the database table including a source table and a target table, the apparatus comprising:

[0013] The acquisition unit is used to acquire a database statement, which is used to perform a merge operation on the target table and the source table based on the outer join result of the source table and the target table;

[0014] The anti-join unit is used to perform an anti-join query on the source table and the target table when the merge operation only includes performing an insert operation on the target table based on the source table, so as to obtain data rows that exist in the source table but do not exist in the target table;

[0015] An insertion unit is used to insert the obtained data rows into the target table.

[0016] According to a fourth aspect of one or more embodiments of this specification, a database statement optimization apparatus is provided, the database statement being used to perform a merge operation on a target table and a source table based on the outer join result of a source table and a target table, the apparatus comprising:

[0017] The rewriting unit is used to rewrite the database statement into an anti-join query statement when the merging operation only includes performing an insert operation on the target table based on the source table. The anti-join query statement is used to perform an anti-join query on the source table and the target table to obtain data rows that exist in the source table but do not exist in the target table.

[0018] An assembly unit is used to assemble the reverse join query statement into a corresponding insert statement to insert the obtained data rows into the target table.

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

[0020] A processor; a memory for storing processor-executable instructions; wherein the processor is configured to implement the steps of the method described in the first or second aspect above.

[0021] According to a sixth aspect of one or more embodiments of this specification, a computer-readable storage medium is provided that stores executable instructions thereon; wherein, when executed by a processor, the instructions implement the steps of the method described in the first or second aspect above.

[0022] In the embodiments of this specification, when the merging operation only includes performing an insert operation on the target table based on the source table, the outer join method in the original database statement is replaced by a reverse join. The reverse join can directly return data rows from one table that are not in another data source, that is, it can directly obtain data rows that exist in the source table but not in the target table, eliminating the process of matching and comparing each data row in the source table with the target table. This can reduce the amount of data joined during the execution of the SQL statement, shorten the execution time of the statement, and improve efficiency.

[0023] It should be understood that the above general description and the following detailed description are exemplary and explanatory only, and are not intended to limit this specification. Attached Figure Description

[0024] The accompanying drawings, which are incorporated in and form part of this specification, illustrate embodiments consistent with this specification and, together with the description, serve to explain the principles of this specification.

[0025] Figure 1 This is a schematic diagram of the architecture of a database system provided in an exemplary embodiment of this specification;

[0026] Figure 2 This is a flowchart illustrating a database table synchronization method provided in an exemplary embodiment of this specification;

[0027] Figure 3 This is an exemplary embodiment of the present specification, which provides a flowchart for rewriting a raw statement.

[0028] Figure 4 This is a flowchart illustrating an exemplary embodiment of a database statement optimization method provided in this specification.

[0029] Figure 5 This is a schematic structural diagram of an electronic device provided in an exemplary embodiment of this specification;

[0030] Figure 6 This is a block diagram of a database table synchronization device provided in an exemplary embodiment of this specification;

[0031] Figure 7 This is a block diagram of a database statement optimization apparatus provided in an exemplary embodiment of this specification. Detailed Implementation

[0032] 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.

[0033] It should be noted that in other embodiments, the steps of the corresponding methods are not necessarily performed in the order shown and described in this specification. In some other embodiments, the methods may include more or fewer steps than those 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. It should be understood that although the terms first, second, third, etc., may be used in this specification to describe various information, this information should not be limited to these terms. These terms are only used to distinguish information of the same type from each other. For example, without departing from the scope of this specification, first information may also be referred to as second information, 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," "when," or "in response to a determination."

[0034] 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.

[0035] A relational database is a database that uses a relational model to organize data. It stores data in rows and columns for user understanding; these rows and columns are called database tables. Users can use Structured Query Language (SCL) to query, update, and manage database tables in a relational database system. Outer joins are a common SQL statement that connects two or more database tables with certain join conditions, returning records that meet the join conditions as well as some records that do not. Outer joins can be divided into left joins, right joins, and full outer joins. The following section, using Tables 1, 2, and 3 as examples, will specifically introduce left joins.

[0036] Table 1: student

[0037] ID Name 1 Zhang San 2 Li Si 3 Wang Wu

[0038] Table 2: Course

[0039] ID C_Name(Course Name) 1 football 2 music 4 art

[0040] The following SQL statement demonstrates a left outer join query between Table 1 and Table 2:

[0041] Select*from student left join course on student.ID=course.ID

[0042] The left join condition following the preposition "on" matches the IDs of Table 1 and Table 2. After executing the left join statement, the execution result corresponding to Table 3 can be obtained.

[0043] Table 3

[0044] ID Name ID C_Name 1 Zhang San 1 football 2 Li Si 2 music 3 Wang Wu Null Null

[0045] Therefore, the execution result of a left join can include all rows from the left table, and if a row in the left table does not have a match in the right table, the corresponding row in the result will have all rows from the right table as null.

[0046] The right join is similar to the left join, and its SQL statement can be expressed as follows:

[0047] Select*from student right join course on student.ID=course.ID

[0048] The execution results of the right join can be seen in Table 4.

[0049] Table 4

[0050] ID Name ID C_Name 1 Zhang San 1 football 2 Li Si 2 music Null Null 4 art

[0051] A full outer join can be logically considered as a combination of a left join and a right join, and its specific SQL statement can be shown below:

[0052] Select*from student full join course on student.ID=course.ID

[0053] The execution results of a full outer join can be found in Table 5.

[0054] Table 5

[0055] ID Name ID C_Name 1 Zhang San 1 football 2 Li Si 2 music 3 Wang Wu Null Null Null Null 4 art

[0056] Based on the above introduction to outer joins, it's easy to see that outer join queries can connect two or more database tables with certain join conditions, returning not only records that meet the join conditions but also some records that do not. Correspondingly, SQL statements based on outer joins have emerged, such as the `merge into` statement. As a DML statement in SQL, `merge into` can perform basic database operations. Specifically, it inserts mismatched data into the target table, meaning it no longer simply returns empty data but inserts actual data. The `merge` keyword can combine operations like `INSERT`, `UPDATE`, and `DELETE` into a single statement, performing insert, update, or delete operations on the target table based on the result of the outer join with the source table. Its standard syntax structure is as follows:

[0057] MERGE INTO target_table(target_table)

[0058] USING source_table (source table)

[0059] ON condition (judgment condition)

[0060] WHEN MATCHED THEN

[0061] UPDATE SET (When the judgment condition is satisfied, perform an update operation on the target table) ①

[0062] WHEN NOT MATCHED THEN

[0063] INSERT VALUES (When the judgment condition is not satisfied, perform an insert operation on the target table) ②;

[0064] However, in the related art, when two or more database tables are joined and a merge operation is performed using an outer join, even if the merge operation only includes an insert operation on the target table. For example, taking the merge keyword as an example, assuming that in the corresponding database statement, only the statement corresponding to "② When the judgment condition is not satisfied, perform an insert operation on the target table" is written, and the statement corresponding to "① When the judgment condition is satisfied, perform an update operation on the target table" is not written, it still needs to match each row of data in the source table with the target table. As a result, the merge into statement has a large amount of connected data, a long statement execution time, and low efficiency during the execution process.

[0065] In view of this, this specification provides an improved synchronization solution for database tables and an optimization solution for database statements to solve the problems existing in the related art. The following is a detailed description with reference to the accompanying drawings.

[0066] Figure 1 It is a schematic diagram of the architecture of a database system provided by an exemplary embodiment of this specification. The system architecture may include a database 10 and a terminal 11.

[0067] Database 10 can be deployed on a physical server containing a standalone host, or it can be deployed on a virtual server (such as a cloud server) hosted in a host cluster. Database 10 can be deployed in a distributed manner, allowing data to be stored on different physical nodes. The physical node providing service can be flexibly determined based on the user's geographical location, improving response speed and providing a certain degree of disaster recovery capability. Database 10 can obtain the database statement input by the user and determine whether the statement only includes an insert operation on the target table. If the determination result indicates only an insert operation, it can automatically perform a reverse join query on the source table and the target table, and insert the obtained data rows into the target table. This replaces the original outer join in the user-input database statement with a reverse join. A reverse join can directly return data rows from one table that are not in another data source, i.e., it can directly obtain data rows that exist in the source table but not in the target table, eliminating the process of matching and comparing each data row in the source table with the target table. This reduces the amount of data joined during the execution of the SQL statement, shortens the execution time, and improves efficiency.

[0068] Terminal 11 can be a mobile phone, PC (personal computer), or wearable device (such as a smartwatch or smart glasses), etc., and this specification does not impose any limitations on this. Users can access the database 10 through terminal 11, and users can edit database statements on terminal 11. Terminal 11 can then send the edited database statements to database 10, which will execute the query operation. Furthermore, database 10 can optimize the database statements using the rewriting scheme described in this specification during the execution of the operation, thereby improving efficiency. However, it is not excluded that the database statements can be optimized directly on terminal 11 using the rewriting scheme described in this specification before being sent to database 10 for execution.

[0069] Figure 2 This is a flowchart illustrating a database table synchronization method provided in an exemplary embodiment of this specification. The database tables include a source table and a target table. The method may include the following steps:

[0070] Step 202: Obtain the database statement, which is used to perform a merge operation on the target table and the source table based on the outer join result of the source table and the target table.

[0071] As mentioned above, the database language described in this specification can refer to Structured Query Language, i.e., SQL. Specifically, the obtained database statement can be an SQL statement with the merge keyword as described above. After the SQL statement is executed, it can perform a merge operation on the target table and the source table based on the outer join result of the source table and the target table. The specific merge operation can refer to performing an update operation, an insert operation, or a delete operation on the target table, etc.

[0072] It should be noted that this manual does not limit SQL statements to those using the MERGE keyword. In fact, any database statement that uses the outer join method described above to connect the source and target tables can be considered a database statement applicable to this manual. This manual does not restrict which specific keyword to use in the SQL statement.

[0073] Step 204: If the merge operation only includes performing an insert operation on the target table based on the source table, perform a reverse join query on the source table and the target table to obtain data rows that exist in the source table but not in the target table.

[0074] In one embodiment, the database statement can be generated using an outer join query statement. The standard syntax of this outer join query statement includes a condition and branch statements corresponding to the conditions being met and the conditions not being met. The branch statement meeting the condition can be used to perform an update operation on the target table based on the source table, and the branch statement not meeting the condition can be used to perform an insert operation on the target table based on the source table. Therefore, if the database statement only includes branch statements not meeting the condition, it can be determined that the merge operation only includes performing an insert operation on the target table based on the source table. Taking the SQL statement with the `merge` keyword as an example, the `merge into` statement can be generated using an outer join query statement, and the standard syntax of the `merge into` statement has been described above. Below, using table A (C1, C2, C3) as the target table and table B (C1, C2, C3) as the source table, filling in the standard syntax structure yields the following specific `merge into` statement.

[0075] MERGE INTO A

[0076] USING B

[0077] ON(A.C1=B.C1)# can be considered as a condition, that is, A.C1 is equal to B.C1.

[0078] WHEN NOT MATCHED THEN# can be considered a branch statement that does not meet the above judgment condition.

[0079] INSERT(A.C1,A.C2,A.C3)VALUES

[0080] (B.C1, B.C2, (SELECT SUM(B.C1) FROM A WHERE A.C3=B.C3))# can be considered as the data range described below.

[0081] WHERE

[0082] B.C2>10; #This can be considered as the execution condition described below.

[0083] As can be seen from the example statement above, the `merge into` statement does not have a branch statement configured for "when the condition is met," but only a branch statement configured for "when the condition is not met." In other words, the `merge into` statement only has a branch statement corresponding to "WHEN NOT MATCHED," and not a branch statement corresponding to "WHEN MATCHED THEN." Therefore, it can be determined that the merge operation only includes performing insert operations on the target table based on the source table. The `merge into` statement shown above is only an example; its specific condition and the data inserted can vary according to the actual content and needs of the source and target tables, and this specification does not impose any restrictions on this.

[0084] As mentioned earlier, taking the `merge into` statement as an example, this SQL statement performs insert or update operations on the target table by forming an outer join between the source and target tables. In other words, even if the `merge into` statement only includes the aforementioned insert operation, it still compares and matches data row by row, resulting in low efficiency. Therefore, this specification proposes converting the outer join method into a reverse join, thereby performing a reverse join query on the source and target tables.

[0085] Anti-join queries can be implemented as follows: The data ranges representing the outer join queries of the source and target tables in the database statement can be obtained; the outer join statement representing the relationship between the source and target tables can be rewritten as an anti-join statement; the data ranges and the rewritten anti-join statement can be encapsulated into an anti-join query statement, and then the source and target tables can be anti-joined by executing the anti-join query statement. Taking table A as the target table and table B as the source table as an example, the data range for the outer join query described in this specification can refer to the above-mentioned "B.C1, B.C2, (SELECT SUM(B.C1) FROM A WHERE A.C3=B.C3)". That is, this specification does not modify the data range; it can still use the original data range defined in the MERGE INTO statement. Of course, this data range can be flexibly defined according to the actual situation.

[0086] However, the outer join statement in the `merge into` clause needs to be rewritten as an antijoin statement, as shown below:

[0087] FROM B ANTI JOIN A ON(A.C1=B.C1)

[0088] Therefore, after obtaining the data range and the inverse join statement, it can be encapsulated into a corresponding inverse join query statement, or in other words, into a subquery. As shown below:

[0089] SELECT

[0090] B.C1, B.C2, (SELECT SUM(B.C1) FROM A WHERE A.C3=B.C3) # The data range can remain unchanged.

[0091] FROM B ANTI JOIN A ON (A.C1=B.C1) # Rewrite the outer join statement as an antijoin statement.

[0092] WHERE

[0093] B.C2>10;

[0094] Therefore, by executing the above reverse join query statement, a reverse join query can be performed on the source table and the target table. That is, a reverse join query is performed on table A and table B. Instead of matching data rows one by one, data rows that exist in the source table but not in the target table can be directly returned.

[0095] It should be noted that "WHERE B.C2>10" in the above `merge into` statement can be considered an execution condition, meaning that the insert operation will only be performed if B.C2 is greater than 10. Therefore, when rewriting the statement, this execution condition can be left unprocessed; that is, the reverse join query statement can directly retain this execution condition. In other words, if the database statement includes an execution condition, the encapsulated reverse join query statement can include the execution condition from the database statement. Of course, in some embodiments, the `merge into` statement may not be configured with an execution condition (i.e., "WHERE B.C2>10" is not written), in which case the corresponding reverse join query statement may also not be configured with this execution condition. Specifically, whether the `merge into` statement is configured with an execution condition can be flexibly determined according to actual needs, and this specification does not impose any restrictions on this.

[0096] Step 206: Insert the obtained data rows into the target table.

[0097] As mentioned earlier, after executing the reverse join query, data rows that exist in the source table but not in the target table can be obtained. Since the `merge into` statement will eventually insert these data rows into the target table, it is necessary to rewrite the query to obtain these data rows and then insert them into the target table to ensure consistency of the final result. Specifically, the encapsulated reverse join query statement can be assembled into a corresponding insert statement; and the insert statement can be executed to insert the query result of the reverse join query into the target table.

[0098] As mentioned earlier, an inverse join query statement can be expressed as follows:

[0099] SELECT

[0100] B.C1,B.C2,(SELECT SUM(B.C1)FROM A WHERE A.C3=B.C3)

[0101] FROM B ANTI JOIN A ON(A.C1=B.C1)

[0102] WHERE

[0103] B.C2>10;

[0104] Therefore, the above reverse join query needs to be assembled into a corresponding insert statement so that the query result of the reverse join can be inserted into the target table. The assembled insert statement can be shown below:

[0105] INSERT INTO # Insert statement

[0106] A(C1,C2,C3)

[0107] SELECT

[0108] B.C1,B.C2,(SELECT SUM(B.C1)FROM A WHERE A.C3=B.C3)

[0109] FROM B ANTI JOIN A ON(A.C1=B.C1)

[0110] WHERE

[0111] B.C2>10;

[0112] Once the assembled insert statement is obtained, it can be executed to replace the original merge into statement. This avoids matching line by line, reduces the amount of data joined, speeds up statement execution, and shortens the time to obtain the execution result while ensuring consistency. It should be noted that the database can be configured with a corresponding optimizer that can automatically perform the above statement rewriting process, avoiding the inefficiency of manual execution.

[0113] In one embodiment, the database statement can be a `merge into` statement. However, it is not limited to `merge into`. In fact, any database statement that uses the outer join method described above to link the source and target tables can be considered a database statement applicable to this specification. If the database statement only involves performing insert operations on the target table based on the source table, the corresponding rewriting methods described in this specification can be used to improve efficiency.

[0114] Of course, when the merge operation includes performing an update operation on the target table based on the source table, the update operation on the source table can be performed based on the outer join result of the source table and the target table. That is, taking the `mergeinto` statement as an example, if it has a corresponding "WHEN MATCHED THEN UPDATA" branch statement, an update or delete operation cannot be achieved through an anti-join. Therefore, in this case, the `mergeinto` statement does not need to be rewritten; its original `mergeinto` statement can be used to achieve the update or delete operation.

[0115] To better understand the above rewriting process, this manual also provides a flowchart of the original statement rewriting process. Please refer to... Figure 3 The flowchart may specifically include the following steps:

[0116] Step 302: Obtain the original statement.

[0117] Taking the `merge into` statement as an example, we can retrieve the original statement. For instance, the original statement might look like this:

[0118] MERGE INTO A USING B ON(A.C1=B.C1)

[0119] WHEN NOT MATCHED THEN

[0120] INSERT(A.C1,A.C2,A.C3)VALUES

[0121] (B.C1,B.C2,(SELECT SUM(B.C1)FROM A WHERE A.C3=B.C3))

[0122] WHERE

[0123] B.C2>10;

[0124] Step 304: Determine whether the original statement contains only an insert statement. If the result is yes, proceed to step 306; if the result is no, proceed to step 310.

[0125] Taking the above merge into statement as an example, since the merge into statement only has a branch statement corresponding to "WHEN NOT MATCHEDTHEN", it can be determined that the merge into statement only contains an insert statement, and step 306 can be executed. Of course, let's assume that the original statement obtained is as follows:

[0126] MERGE INTO A USING B ON(A.C1=B.C1)

[0127] WHEN MATCHED THEN

[0128] UPDATA SET A.C2=B.C2

[0129] WHEN NOT MATCHED THEN

[0130] INSERT(A.C1,A.C2,A.C3)VALUES

[0131] (B.C1,B.C2,(SELECT SUM(B.C1)FROM A WHERE A.C3=B.C3))

[0132] WHERE

[0133] B.C2>10;

[0134] Since the above merge into statement contains a branch statement that satisfies the judgment condition (WHEN MATCHEDTHEN), that is, it contains an update statement, it can execute step 310, that is, treat the original statement as the final statement and not rewrite it.

[0135] Step 306: If the judgment result is yes, change the original statement to an anti-join and encapsulate it as a subquery.

[0136] Taking the merge into statement, which only includes insert statements, as an example, as mentioned above, it can be changed into an anti-join and encapsulated as a subquery, specifically resulting in the subquery shown below.

[0137] SELECT

[0138] B.C1,B.C2,(SELECT SUM(B.C1)FROM A WHERE A.C3=B.C3)

[0139] FROM B ANTI JOIN A ON(A.C1=B.C1)

[0140] WHERE

[0141] B.C2>10;

[0142] Step 308: Assemble the encapsulated subquery into the corresponding INSERT INTO statement.

[0143] After obtaining the encapsulated subquery based on step 306, it can be further assembled to obtain the following insert statement.

[0144] INSERT INTO

[0145] A(C1,C2,C3)

[0146] SELECT

[0147] B.C1,B.C2,(SELECT SUM(B.C1)FROM A WHERE A.C3=B.C3)

[0148] FROM B ANTI JOIN A ON(A.C1=B.C1)

[0149] WHERE

[0150] B.C2>10;

[0151] Step 310: Use the assembled insert statement or original statement as the final statement.

[0152] Therefore, the original merge into statement can be rewritten as follows:

[0153] INSERT INTO

[0154] A(C1,C2,C3)

[0155] SELECT

[0156] B.C1,B.C2,(SELECT SUM(B.C1)FROM A WHERE A.C3=B.C3)

[0157] FROM B ANTI JOIN A ON(A.C1=B.C1)

[0158] WHERE

[0159] B.C2>10;

[0160] This insert statement replaces the original merge into statement. Furthermore, because this insert statement uses a reverse join to link the source and target tables, and a reverse join can directly return data rows from one table that are not present in the other data source, it directly obtains data rows that exist in the source table but not in the target table. This eliminates the process of the merge into statement matching and comparing each row in the source table with the target table, reducing the amount of data joined during the execution of the SQL statement, shortening the execution time, and improving efficiency.

[0161] This manual also provides a method for optimizing database statements, such as... Figure 4 As shown, Figure 4 This is a flowchart illustrating an exemplary embodiment of a database statement optimization method provided in this specification. The database statement is used to perform a merge operation on the target table and the source table based on the outer join result of the source table and the target table. The method may include the following steps:

[0162] Step 402: If the merge operation only includes performing an insert operation on the target table based on the source table, the database statement is rewritten as an anti-join query statement. The anti-join query statement is used to perform an anti-join query on the source table and the target table to obtain data rows that exist in the source table but do not exist in the target table.

[0163] Database statements can refer to structured query statements, i.e., SQL, as described above. In one embodiment, the database statement can be generated using an outer join query statement. The standard syntax structure of the outer join query statement includes a condition and branch statements corresponding to the conditions being met and the conditions not being met. The branch statement when the condition is met is used to perform an update operation on the target table based on the source table. Therefore, if the database statement only includes branch statements when the condition is not met, it can be determined that the merge operation only includes performing an insert operation on the target table based on the source table.

[0164] In one embodiment, the data range in the database statement representing the outer join query of the source table and the target table can be obtained; the outer join statement representing the outer join relationship between the source table and the target table in the database statement can be rewritten as an anti-join relationship statement; the data range and the rewritten anti-join relationship statement can be encapsulated into an anti-join query statement, thereby rewriting the database statement as an anti-join query statement.

[0165] In one embodiment, when the database statement includes an execution condition, the encapsulated reverse join query statement may include the execution condition from the database statement.

[0166] In one embodiment, the database statement may include a merge into statement.

[0167] Step 404: Assemble the reverse join query statement into a corresponding insert statement to insert the obtained data rows into the target table.

[0168] Taking the `merge into` statement as an example, if the `merge into` statement only includes an insert statement, it can be rewritten as an inverse join query statement and assembled into the corresponding insert statement. The execution result of the insert statement can be consistent with the execution result of the original `merge into` statement, but the execution efficiency is greatly improved.

[0169] Corresponding to the embodiments of the foregoing methods, this specification also provides embodiments of apparatus, electronic devices, and storage media.

[0170] Figure 5 This is a schematic structural diagram of an electronic device provided in an exemplary embodiment. Please refer to... Figure 5At the hardware level, the device includes a processor 501, a network interface 502, memory 503, non-volatile memory 504, and an internal bus 505, and may also include other hardware required for services. One or more embodiments of this specification can be implemented in software, such as the processor 501 reading the corresponding computer program from the non-volatile memory 504 into memory 503 and then running 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.

[0171] Figure 6 This is a block diagram of a database table synchronization device provided in an exemplary embodiment. Please refer to... Figure 6 The database tables include a source table and a target table. This device can be applied to a client. The device includes:

[0172] The acquisition unit 602 is used to acquire a database statement, which is used to perform a merge operation on the target table and the source table based on the outer join result of the source table and the target table;

[0173] The anti-join unit 604 is used to perform an anti-join query on the source table and the target table when the merge operation only includes performing an insert operation on the target table based on the source table, so as to obtain data rows that exist in the source table but do not exist in the target table;

[0174] Insertion unit 606 is used to insert the obtained data rows into the target table.

[0175] Optionally, the database statement is generated using an outer join query statement. The standard syntax structure of the outer join query statement includes a condition and branch statements corresponding to when the condition is met and when the condition is not met. The branch statement when the condition is met is used to perform an update operation on the target table based on the source table, and the branch statement when the condition is not met is used to perform an insert operation on the target table based on the source table.

[0176] The device further includes:

[0177] The first determination unit 608 is used to determine, when the database statement only includes branch statements that do not meet the determination conditions, that the merge operation only includes performing an insert operation on the target table based on the source table.

[0178] Optionally, the anti-connection unit 604 is specifically used for:

[0179] Obtain the data range in the database statement that represents the outer join query involving the source table and the target table respectively;

[0180] Rewrite the outer join statement representing the outer join relationship between the source table and the target table in the database statement into an anti-join statement;

[0181] The data range and the rewritten reverse join statement are encapsulated into a reverse join query statement, so that the source table and the target table can be reverse joined by executing the reverse join query statement.

[0182] Optionally, if the database statement includes execution conditions, the encapsulated reverse join query statement includes the execution conditions from the database statement.

[0183] Optionally, the insertion unit 606 is specifically used for:

[0184] Assemble the encapsulated reverse join query statement into the corresponding insert statement;

[0185] Execute the insert statement to insert the query result of the reverse join query into the target table.

[0186] Optionally, the database statement includes a merge into statement.

[0187] Optionally, the device further includes:

[0188] The update unit 610 is configured to perform an update operation on the source table based on the outer join result of the source table and the target table, when the merge operation includes performing an update operation on the target table based on the source table.

[0189] Figure 7 This is a block diagram of a database statement optimization apparatus provided in an exemplary embodiment. Please refer to... Figure 7 The database statement is used to perform a merge operation on the target table and the source table based on the outer join result of the source table and the target table. The apparatus includes:

[0190] The rewriting unit 702 is used to rewrite the database statement into an anti-join query statement when the merging operation only includes performing an insert operation on the target table based on the source table. The anti-join query statement is used to perform an anti-join query on the source table and the target table to obtain data rows that exist in the source table but do not exist in the target table.

[0191] Assembly unit 704 is used to assemble the reverse join query statement into a corresponding insert statement to insert the obtained data rows into the target table.

[0192] Optionally, the database statement is generated using an outer join query statement. The standard syntax structure of the outer join query statement includes a condition and branch statements corresponding to when the condition is met and when the condition is not met. The branch statement when the condition is met is used to perform an update operation on the target table based on the source table, and the branch statement when the condition is not met is used to perform an insert operation on the target table based on the source table.

[0193] The device further includes:

[0194] The second determination unit 706 is used to determine, when the database statement only includes branch statements that do not meet the determination conditions, that the merge operation only includes performing an insert operation on the target table based on the source table.

[0195] Optionally, the rewriting unit 702 is specifically used for:

[0196] Obtain the data range in the database statement that represents the outer join query involving the source table and the target table respectively;

[0197] Rewrite the outer join statement representing the outer join relationship between the source table and the target table in the database statement into an anti-join statement;

[0198] The data range and the rewritten reverse join statement are encapsulated into a reverse join query statement.

[0199] Optionally, if the database statement includes execution conditions, the encapsulated reverse join query statement includes the execution conditions from the database statement.

[0200] Optionally, the database statement includes a merge into statement.

[0201] For the device embodiments, since they basically correspond to the method embodiments, the relevant parts can be referred to in the description of the method embodiments. The device embodiments described above are merely illustrative. The modules described as separate components may or may not be physically separate, and the components shown as modules may or may not be physical modules, that is, they may be located in one place or distributed across multiple network modules. Some or all of the modules can be selected to achieve the purpose of the solution in this specification according to actual needs. Those skilled in the art can understand and implement this without creative effort.

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

[0203] 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.

[0204] Computer-readable media includes both permanent and non-permanent, removable and non-removable media that 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, magnetic magnetic disk storage 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.

[0205] The computer-readable medium (or computer-readable storage medium) described above or in any other form may store computer instructions that, when executed by a processor, implement one or more of the embodiments described above, thereby realizing the technical solutions of this specification.

[0206] This specification also provides a computer program that, when executed by a processor, implements one or more of the embodiments described above, thereby achieving the technical solutions of this specification. This computer program may be specifically recorded on the computer-readable medium described above or in any other form, and this specification does not impose any limitations on this.

[0207] 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.

[0208] 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 this specification. Rather, they are merely examples of apparatuses and methods consistent with some aspects of this specification as detailed in the appended claims.

[0209] The terminology used in this specification is for the purpose of describing particular embodiments only and is not intended to be limiting of this specification. The singular forms “a,” “the,” and “the” as used in this specification and 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 and all possible combinations of one or more of the associated listed items.

[0210] It should be understood that although the terms first, second, third, etc., may be used in this specification to describe various information, this 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, without departing from the scope of this specification, first information may also be referred to as second information, 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," "when," or "in response to determination."

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

Claims

1. A method for synchronizing database tables, the database tables including a source table and a target table, the method comprising: A database statement is obtained, which is used to perform a merge operation on the target table and the source table based on the outer join result of the source table and the target table; wherein, the database statement is generated by using an outer join query statement, and the standard syntax structure of the outer join query statement includes at least a condition and a branch statement corresponding to the condition not being met; the branch statement when the condition is not met is used to perform an insert operation on the target table based on the source table; If the merge operation only includes performing an insert operation on the target table based on the source table, a reverse join query is performed on the source table and the target table to obtain data rows that exist in the source table but not in the target table; The obtained data rows are inserted into the target table.

2. The method according to claim 1, wherein the standard syntax structure of the outer join query statement further includes a branch statement corresponding to the condition being met; the branch statement when the condition is met is used to perform an update operation on the target table based on the source table, and the method further includes: If the database statement only includes branch statements that do not meet the judgment conditions, it is determined that the merge operation only includes performing an insert operation on the target table based on the source table.

3. The method according to claim 1, wherein performing a reverse join query on the source table and the target table includes: Obtain the data range in the database statement that represents the outer join query involving the source table and the target table respectively; Rewrite the outer join statement representing the outer join relationship between the source table and the target table in the database statement into an anti-join statement; The data range and the rewritten reverse join statement are encapsulated into a reverse join query statement, so that the source table and the target table can be reverse joined by executing the reverse join query statement.

4. The method according to claim 3, wherein when the database statement includes execution conditions, the encapsulated reverse join query statement includes the execution conditions in the database statement.

5. The method according to claim 3, wherein inserting the obtained data row into the target table comprises: Assemble the encapsulated reverse join query statement into the corresponding insert statement; Execute the insert statement to insert the query result of the reverse join query into the target table.

6. The method according to claim 1, wherein the database statement includes a merge into statement.

7. The method according to claim 1, further comprising: In the case where the merge operation includes performing an update operation on the target table based on the source table, the update operation is performed on the source table based on the outer join result of the source table and the target table.

8. A method for optimizing a database statement, wherein the database statement is used to perform a merge operation on the target table and the source table based on the outer join result of the source table and the target table, wherein, The database statement is generated using an outer join query statement, and the standard syntax structure of the outer join query statement includes at least a condition and a branch statement corresponding to the condition not being met. The branch statement when the judgment condition is not met is used to perform an insert operation on the target table based on the source table, and the method includes: If the merge operation only includes performing an insert operation on the target table based on the source table, the database statement is rewritten as an anti-join query statement. The anti-join query statement is used to perform an anti-join query on the source table and the target table to obtain data rows that exist in the source table but do not exist in the target table. The reverse join query statement is assembled into a corresponding insert statement to insert the obtained data rows into the target table.

9. The method according to claim 8, wherein the standard syntax structure of the outer join query statement further includes a branch statement corresponding to the condition being met; the branch statement when the condition is met is used to perform an update operation on the target table based on the source table, and the method further includes: If the database statement only includes branch statements that do not meet the judgment conditions, it is determined that the merge operation only includes performing an insert operation on the target table based on the source table.

10. The method according to claim 8, wherein rewriting the database statement into an anti-join query statement comprises: Obtain the data range in the database statement that represents the outer join query involving the source table and the target table respectively; Rewrite the outer join statement representing the outer join relationship between the source table and the target table in the database statement into an anti-join statement; The data range and the rewritten reverse join statement are encapsulated into a reverse join query statement.

11. The method according to claim 10, wherein when the database statement includes an execution condition, the encapsulated reverse join query statement includes the execution condition in the database statement.

12. The method according to claim 8, wherein the database statement includes a merge into statement.

13. A synchronization device for a database table, the database table including a source table and a target table, the device comprising: The acquisition unit is used to acquire a database statement, which is used to perform a merge operation on the target table and the source table based on the outer join result of the source table and the target table; wherein, the database statement is generated by using an outer join query statement, and the standard syntax structure of the outer join query statement includes at least a judgment condition and a branch statement corresponding to the condition not being met; the branch statement when the judgment condition is not met is used to perform an insert operation on the target table based on the source table; The anti-join unit is used to perform an anti-join query on the source table and the target table when the merge operation only includes performing an insert operation on the target table based on the source table, so as to obtain data rows that exist in the source table but do not exist in the target table; An insertion unit is used to insert the obtained data rows into the target table.

14. A database statement optimization apparatus, wherein the database statement is used to perform a merge operation on a target table and a source table based on the outer join result of a source table and a target table, wherein, The database statement is generated using an outer join query statement, and the standard syntax structure of the outer join query statement includes at least a condition and a branch statement corresponding to the condition not being met. The branch statement when the judgment condition is not met is used to perform an insertion operation on the target table based on the source table, and the device includes: The rewriting unit is used to rewrite the database statement into an anti-join query statement when the merging operation only includes performing an insert operation on the target table based on the source table. The anti-join query statement is used to perform an anti-join query on the source table and the target table to obtain data rows that exist in the source table but do not exist in the target table. An assembly unit is used to assemble the reverse join query statement into a corresponding insert statement to insert the obtained data rows into the target table.

15. A computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps of the method as claimed in any one of claims 1 to 7 or 8 to 12.

16. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor, when executing the program, implements the steps of the method as claimed in any one of claims 1 to 7 or 8 to 12.

Images