Database operation statement processing method, medium, product and equipment

Abstract

The invention provides a database operation statement processing method, a medium, a product and equipment. The method comprises the following steps: acquiring a database operation statement; judging whether or not an OR logic keyword exists in a conditional statement of the database operation statement and a judgment keyword exists in a record; if yes, whether the sub-connection query table in the sub-connection corresponding to the judgment keyword is recorded to be an empty table or not is judged; under the condition that the sub-connection query table is not an empty table and the database operation statement meets a preset conversion condition, determining a corresponding self-defined keyword according to the position where the judgment keyword and / or the logic keyword exist in the record; and converting the database operation statement according to the user-defined keyword, and processing the converted database operation statement according to the execution logic corresponding to the user-defined keyword. Through the method, the problem that sub-connection to semi-connection and reverse semi-connection operations cannot be executed due to or conditions can be avoided, so that the execution plan is simplified, the optimization space is expanded, and the performance is improved.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a method, medium, product and device for processing database operation statements. Background Art

[0002] When processing a sub-join, the database often converts it into a semi-join operation to reduce repeated scanning of the sub-query, thereby improving performance. For example:

[0003] Select*from t1 where exists(select 1from t2 where t1.id=t2.id);

[0004] Can be converted into

[0005] Select*from t1 semi join t2 on t1.id=t2.id;

[0006] Optimization point: In the original statement, each piece of data in the outer table needs to be substituted into the sub-join once, which generates a high cost. After optimization, each piece of data in the outer table is scanned for conditional judgment with the data in the inner table. When the first pair of data is selected, the next piece of data in the outer table can be scanned. The cost is much lower than that of the original statement, and the performance is improved. However, when the sub-join condition is connected with another outer table condition using or, this optimization cannot be used, such as:

[0007] Select*from t1 where value<10or exists(select 1from t2 where t1.id=t2.id). Summary of the invention

[0008] In view of the above problems, the present invention proposes a method, medium, product and device for processing database operation statements that overcome the above problems or at least partially solve the above problems.

[0009] One object of the present invention is to enable database operation statements that meet the conditions to also be converted into semi-joins.

[0010] A further object of the present invention is to improve the execution efficiency of database operation statements.

[0011] In particular, the present invention provides a method for processing a database operation statement, comprising:

[0012] Get database operation statements;

[0013] Determine whether there is a logical keyword or a record existence determination keyword in the conditional statement of the database operation statement;

[0014] If so, it is determined whether the sub-connection query table in the sub-connection corresponding to the record determination keyword is an empty table;

[0015] In the case where the sub-join query table is not an empty table and the database operation statement meets the preset conversion condition, the corresponding custom keyword is determined according to the position of the record existence determination keyword and the OR logic keyword, and the preset conversion condition is that the remaining statement after retaining the record existence determination keyword and its sub-join in the database operation statement and removing another independent condition corresponding to the OR logic keyword meets the semi-join conversion condition;

[0016] The database operation statement is converted according to the custom keyword, and the converted database operation statement is processed according to the execution logic corresponding to the custom keyword.

[0017] Optionally, the position of the OR logic keyword includes a first position and a second position, the first position being one of the conditions for the record existence determination keyword to be connected with the OR logic keyword, and the second position being the OR logic keyword being located inside a sub-connection corresponding to the record existence determination keyword.

[0018] Optionally, the record existence determination keywords include: exists;

[0019] The step of determining the corresponding custom keyword according to the position of the record existence determination keyword and the OR logic keyword includes: when the record existence determination keyword is exists and the position of the OR logic keyword is the first position or the second position, determining the custom keyword to be the first custom keyword, and the processing logic corresponding to the first custom keyword is semi-join;

[0020] The step of converting the database operation statement according to the user-defined keyword includes: when the record existence determination keyword is exists and the position of the OR logic keyword is the first position or the second position, storing the independent condition corresponding to the OR logic keyword into the first user-defined keyword, and performing a semi-join conversion on the remaining statements;

[0021] The step of processing the converted database operation statement according to the execution logic corresponding to the custom keyword includes: obtaining the data source in the converted database operation statement; judging one by one whether the tuples in the data source meet the independent conditions stored in the first custom keyword, and obtaining the judgment result; when the judgment result is true, returning the tuple; when the judgment result is false, processing the data of the tuple according to the semi-join logic in the first custom keyword, and returning the processing result; when the judgment result is null, discarding the current tuple and processing the next tuple.

[0022] Optionally, after the step of determining whether there is an OR logical keyword in the conditional statement of the database operation statement and the step of recording the existence of the determination keyword, the following step is further included:

[0023] When there is no record existence determination keyword in the database operation statement, it is determined whether there is an OR logic keyword and a multi-value matching keyword in the conditional statement of the database operation statement. The multi-value matching keywords include: any, some;

[0024] When the database operation statement contains the OR logical keyword and any multi-value matching keyword, the sub-join is equivalently converted to the sub-join corresponding to EXISTS.

[0025] The step of determining whether a sub-connection query table in a sub-connection corresponding to a keyword is an empty table is executed according to the converted database operation statement to determine whether a record exists.

[0026] Optionally, the record existence determination keywords include: not exists;

[0027] The step of determining the corresponding custom keyword according to the position of the record existence determination keyword and the OR logic keyword includes: when the record existence determination keyword is not exists and the position of the OR logic keyword is the first position, determining the custom keyword to be a second custom keyword, and the processing logic corresponding to the second custom keyword is an anti-semi join;

[0028] The step of converting the database operation statement according to the user-defined keyword includes: when the record existence determination keyword is not exists and the position of the OR logic keyword is the first position, storing the independent condition corresponding to the OR logic keyword into the second user-defined keyword, and performing an anti-semi-join conversion on the remaining statements;

[0029] The step of processing the converted database operation statement according to the execution logic corresponding to the custom keyword includes: obtaining the data source in the converted database operation statement; judging one by one whether the tuples in the data source meet the independent conditions stored in the second custom keyword, and obtaining the judgment result; when the judgment result is true, returning the tuple; when the judgment result is false, processing the data of the tuple according to the anti-semi-join processing logic in the second custom keyword, and returning the processing result; when the judgment result is null, skipping the current tuple and processing the next tuple.

[0030] Optionally, the step of determining the corresponding custom keyword according to the position of the record existence determination keyword and the OR logic keyword includes: when the record existence determination keyword is not exists and the position of the OR logic keyword is the second position, determining the custom keyword to be a third custom keyword, and the processing logic corresponding to the third custom keyword is an anti-semi join;

[0031] The step of converting the database operation statement according to the user-defined keyword includes: when the record existence determination keyword is not exists and the position of the OR logic keyword is the second position, storing the independent condition corresponding to the OR logic keyword into a third user-defined keyword, and performing an anti-semi-join conversion on the remaining statements;

[0032] The step of processing the converted database operation statement according to the execution logic corresponding to the custom keyword includes: obtaining the data source in the converted database operation statement; judging one by one whether the tuples in the data source meet the independent conditions stored in the third custom keyword, and obtaining the judgment result; when the judgment result is true, skipping the current tuple and processing the next tuple; when the judgment result is false, processing the data of the tuple according to the anti-semi-join processing logic in the second custom keyword, and returning the processing result; when the judgment result is null, returning the tuple.

[0033] Optionally, after the step of determining whether the sub-connection query table in the sub-connection corresponding to the record determination keyword is an empty table, the method further includes:

[0034] When the sub-connection query table is an empty table, the invalid parts in the record existence determination keywords and the database operation statements are eliminated.

[0035] According to another aspect of the present invention, a computer-readable storage medium is provided, on which a computer program is stored. When the computer program is executed by a processor, the steps of any of the above-mentioned methods for processing database operation statements are implemented.

[0036] According to another aspect of the present invention, a computer program product is provided, which includes a computer program, and when the computer program is executed by a processor, the steps of any one of the above-mentioned methods for processing database operation statements are implemented.

[0037] According to another aspect of the present invention, a computer device is provided, comprising a memory, a processor, and a machine executable program stored in the memory and running on the processor, and the processor implements the steps of any of the above-mentioned database operation statement processing methods when executing the machine executable program.

[0038] The processing method of the database operation statement of the present invention first obtains the database operation statement; determines whether there are OR logic keywords and record existence judgment keywords in the conditional statement of the database operation statement; if so, determines whether the sub-connection query table in the sub-connection corresponding to the record existence judgment keyword is an empty table; when the sub-connection query table is not an empty table and the database operation statement meets the preset conversion condition, determines the corresponding custom keyword according to the position of the record existence judgment keyword and the OR logic keyword, and the preset conversion condition is to retain the record existence judgment keyword and its sub-connection in the database operation statement and remove the remaining statement after another independent condition corresponding to the OR logic keyword meets the semi-connection conversion condition; converts the database operation statement according to the custom keyword, and processes the converted database operation statement according to the execution logic corresponding to the custom keyword. Through this method, the problem that the sub-connection conversion to semi-connection and the anti-semi-connection operation cannot be executed due to the OR condition can be avoided, thereby simplifying the execution plan, expanding the optimization space, and improving performance.

[0039] Furthermore, the method for processing database operation statements of the present invention eliminates invalid parts of record existence determination keywords and database operation statements when the sub-connection query table is an empty table. By this method, the database operation statements can be optimized to the greatest extent when the sub-connection query table is an empty table, thereby greatly improving the execution speed of the database operation statements.

[0040] Based on the following detailed description of specific embodiments of the present invention in conjunction with the accompanying drawings, those skilled in the art will become more aware of the above and other objects, advantages and features of the present invention. BRIEF DESCRIPTION OF THE DRAWINGS

[0041] Hereinafter, some specific embodiments of the present invention will be described in detail in an exemplary and non-limiting manner with reference to the accompanying drawings. The same reference numerals in the accompanying drawings indicate the same or similar components or parts. It should be understood by those skilled in the art that these drawings are not necessarily drawn to scale. In the accompanying drawings:

[0042] Figure 1 is a flowchart of a method for processing database operation statements according to an embodiment of the present invention;

[0043] Figure 2 is a flowchart of a method for processing database operation statements according to another embodiment of the present invention;

[0044] Figure 3 is a schematic diagram of a computer program product according to an embodiment of the present invention;

[0045] Figure 4 is a schematic diagram of a computer-readable storage medium according to an embodiment of the present invention; and

[0046] Figure 5 is a schematic diagram of a computer device according to an embodiment of the present invention. DETAILED DESCRIPTION

[0047] It should be understood by those skilled in the art that the embodiments described below are only some embodiments of the present invention, rather than all embodiments of the present invention, and these embodiments are intended to explain the technical principles of the present invention, rather than to limit the protection scope of the present invention. Based on the embodiments provided by the present invention, all other embodiments obtained by ordinary technicians in this field without creative work should still fall within the protection scope of the present invention.

[0048] It should be noted that the logic and / or steps represented in the flowchart or described in other ways herein, for example, can be considered as a sequenced list of executable instructions for implementing logical functions, and can be specifically implemented in any computer-readable medium for use by an instruction execution system, device or equipment (such as a computer-based system, a system including a processor, or other system that can fetch instructions from an instruction execution system, device or equipment and execute instructions), or used in combination with these instruction execution systems, devices or equipment.

[0049] The present invention provides a method for processing database operation statements. Figure 1 FIG. 1 is a flow chart of a method for processing a database operation statement according to an embodiment of the present invention. Figure 1 As shown, the method for processing the database operation statement at least includes the following steps S101 to S105.

[0050] Step S101, obtaining database operation statements. An optional example is SQL (Structured Query Language). SQL statements have multiple functions such as data manipulation and data definition. This language has interactive characteristics and can provide great convenience for users. In this step, the way to receive database operation statements can generally be active input by the user, or automatically generated by the database program during operation due to business needs. Those skilled in the art can decide the way to receive database operation statements according to actual conditions.

[0051] Step S102, determine whether there are OR logical keywords and record existence judgment keywords in the conditional statement of the database operation statement. OR logical keywords generally include or, and record existence judgment keywords generally include not exists and exists, where exists is used to test whether the subquery returns at least one row of data. If the subquery returns at least one row, the exists condition is true (true), otherwise it is false (false). This is usually used to check whether a row in a table exists in another table. Not exists is the opposite of exists, and it is used to test whether the subquery does not return any rows. If the subquery does not return any rows, the not exists condition is true (true), otherwise it is false (false).

[0052] Step S103: If the result of the determination in step S102 is yes, it is recorded whether the sub-connection query table in the sub-connection corresponding to the judgment keyword is an empty table.

[0053] Step S104, if the judgment in step S103 is yes and the database operation statement meets the preset conversion condition, the corresponding custom keyword is determined according to the position of the record existence determination keyword and the OR logic keyword. The preset conversion condition is that the remaining statement after retaining the record existence determination keyword and its sub-connection in the database operation statement and removing another independent condition corresponding to the OR logic keyword meets the semi-join conversion condition.

[0054] Step S105 , converting the database operation statement according to the custom keyword, and processing the converted database operation statement according to the execution logic corresponding to the custom keyword.

[0055] This method can avoid the problem of sub-join conversion to semi-join and anti-semi-join operation being unable to execute due to the or condition, thereby simplifying the execution plan, expanding the optimization space, and improving performance.

[0056] In some optional embodiments, the position of the OR logic keyword can generally include a first position and a second position, the first position is one of the conditions for the record existence determination keyword to be connected with the OR logic keyword, and the second position is that the OR logic keyword is located inside the sub-connection corresponding to the record existence determination keyword. In order to clearly illustrate the difference between the first position and the second position, two SQL statement examples are proposed as follows:

[0057] Select * from t1 where value<10 or exists(select 1 from t2 wheret1.id=t2.id) Formula 1

[0058] As shown in Formula 1, the two conditions of the or connection in the SQL statement are "value<10" and "exists(select1fromt2 where t1.id=t2.id)". It can be seen that in the SQL statement shown in Formula 1, exists and its sub-connection are one of the two conditions of the or connection, and therefore belong to the first position.

[0059] Select * from t1 where exists(select 1 from t2 where t1.id=t2.id orvalue<10) Formula 2

[0060] As shown in Formula 2, there is a record existence determination keyword exists in the SQL statement, and its corresponding sub-connection is "select 1from t2 where t1.id=t2.id or value<10". It can be seen that the or logic keyword is located inside the sub-connection corresponding to the record existence determination keyword, and therefore belongs to the second position.

[0061] Optionally, when the record existence determination keyword is exists, the step of determining the corresponding custom keyword based on the position of the record existence determination keyword and the OR logical keyword may generally include: when the record existence determination keyword is exists and the position of the OR logical keyword is the first position or the second position, determining that the custom keyword is the first custom keyword, and the processing logic corresponding to the first custom keyword is semi-join.

[0062] Optionally, the step of converting the database operation statement according to the custom keyword may generally include: when the record existence determination keyword is exists and the position of the OR logical keyword is the first position or the second position, the independent condition corresponding to the OR logical keyword is stored in the first custom keyword, and the remaining statements are semi-joined.

[0063] Optionally, the step of processing the converted database operation statement according to the execution logic corresponding to the custom keyword can generally include: obtaining the data source in the converted database operation statement; judging one by one whether the tuples in the data source meet the independent conditions stored in the first custom keyword, and obtaining the judgment result; when the judgment result is true, returning the tuple; when the judgment result is false, processing the tuple data according to the semi-join logic in the first custom keyword, and returning the processing result; when the judgment result is null, discarding the current tuple and processing the next tuple.

[0064] An optional specific processing example of the method of the present invention for exists is as follows:

[0065] When the database operation statement obtained by the database is the SQL statement shown in Formula 1, first determine whether the OR logic keyword and the record existence determination keyword exist. Since there is "or exists(select 1from t2 where t1.id=t2.id)" in Formula 1, it contains or and exists, so it is determined to be yes; then determine whether the sub-connection query table in the sub-connection corresponding to the record existence determination keyword is an empty table. It can be seen from the sub-connection "select 1from t2 where t1.id=t2.id" in Formula 1 that it is necessary to determine whether the t2 table is an empty table.

[0066] When the t2 table is not an empty table, it is necessary to determine whether the database operation statement meets the preset conversion conditions. In the SQL statement shown in Formula 1, the OR logical keyword corresponds to two conditional information, namely "exists(select 1from t2where t1.id=t2.id)" and "value<10". Since the semi-join conversion needs to remove or, and "value<10" is an independent condition, it is necessary to determine whether the SQL statement after removing "value<10or" can meet the semi-join conversion conditions. Since the SQL statement shown in Formula 1 can be semi-joined after removing "value<10or", it is determined to meet the preset conversion conditions.

[0067] Subsequently, the corresponding custom keyword is determined according to the position of the record existence determination keyword and the OR logic keyword. Since the record existence determination keyword is exists and the position of the OR logic keyword is the first position, the custom keyword is determined to be the first custom keyword. An optional example of the first custom keyword is: "semi join or". This example can intuitively represent the role and effect of the first custom keyword. It should be noted that the method of the present invention does not limit the specific style of the custom keyword, but only requires the corresponding execution logic to be consistent with the execution logic corresponding to the first custom keyword. Those skilled in the art can set the specific style of the custom keyword according to actual conditions.

[0068] Then, the database operation statement is converted according to the first custom keyword. First, "value<10" is stored in the first custom keyword, that is, "semi join or (value<10)". Then, the remaining statements are semi-joined. The final conversion result is shown in Formula 3:

[0069] Select * from t1 semi join or(value<10) t2 on t1.id=t2.id Formula 3

[0070] When the database faces the SQL statement shown in Formula 3, it will process the tuple data of the t1 table one by one. The specific processing process is: first determine whether the tuple data meets the "value<10" condition. If so, return the tuple data directly; if the judgment result is false, process the tuple data according to the semi-join logic (i.e., semi join) in the first custom keyword, and return the processing result to find out whether the records in one table have matching items in another table. The result of the semi-join only contains the records in the first table (t1) that have corresponding matching items in the second table (t2) (i.e., t1.id=t2.id). If the tuple data is null, the current tuple can be discarded directly and the next tuple can be processed.

[0071] Another optional implementation example is: when the database operation statement obtained by the database is the SQL statement shown in Formula 2, first determine whether there is a logical keyword and a record existence determination keyword. Since there is "exists(select 1from t2 where t1.id=t2.id or value<10)" in Formula 2, it is determined to be yes; then determine whether the sub-connection query table in the sub-connection corresponding to the record existence determination keyword is an empty table. It can be seen from the sub-connection "select 1fromt2 where t1.id=t2.id" in Formula 2 that it is necessary to determine whether the t2 table is an empty table.

[0072] When the t2 table is not an empty table, it is necessary to determine whether the database operation statement meets the preset conversion conditions. In the SQL statement shown in Formula 2, the or logic keyword corresponds to two conditional information, namely "t1.id=t2.id" and "value<10". Since the semi-join conversion needs to remove or, and "value<10" is an independent condition, it is necessary to determine whether the SQL statement after removing "or value<10" can meet the semi-join conversion conditions. Since the SQL statement shown in Formula 2 can be semi-joined after removing "orvalue<10", it is determined to meet the preset conversion conditions.

[0073] Then, the corresponding custom keyword is determined according to the positions of the record existence determination keyword and the OR logic keyword. Since the record existence determination keyword is exists and the position of the OR logic keyword is the second position, the custom keyword is determined to be the first custom keyword.

[0074] Then, the database operation statement is converted according to the first custom keyword. First, "value<10" is stored in the first custom keyword, that is, "semi join or (value<10)". Then, the remaining statements are semi-joined. The final conversion result is also shown in Formula 3:

[0075] When the database faces the SQL statement shown in Formula 3, it will process the tuple data of the t1 table one by one. The specific processing process is: first determine whether the tuple data meets the "value<10" condition. If so, return the tuple data directly; if the judgment result is false, process the tuple data according to the semi-join logic (i.e., semi join) in the first custom keyword, and return the processing result to find out whether the records in one table have matching items in another table. The result of the semi-join only contains the records in the first table (t1) that have corresponding matching items in the second table (t2) (i.e., t1.id=t2.id). If the tuple data is null, the current tuple can be discarded directly and the next tuple can be processed.

[0076] In some other optional embodiments, the target keyword may also include: any, some. After the step of determining whether the target keyword exists in the conditional statement of the database operation statement, the following steps may generally be included: when the database operation statement contains the OR logical keyword and also contains any or some, converting the sub-connection equivalently to the sub-connection corresponding to exists; and executing the step of determining whether the sub-connection query table in the sub-connection corresponding to the keyword is an empty table according to the converted database operation statement. An optional example is shown in Formula 4:

[0077] Select * from t1 where value<10 or id <> any(select id from t2) Formula 4

[0078] In the SQL statement shown in Formula 4, "id<>any(select id from t2)" can generally be equivalently converted to the exists keyword. Its original meaning is to determine whether t1.id is not equal to any id in the t2 table. After conversion, it is converted to exists(select 1from t2 where t1.id<>t2.id). Finally, the SQL statement shown in Formula 4 is converted to the SQL statement shown in Formula 5:

[0079] Select * from t1 where value<10 or exists(select 1 from t2 wheret1.id<>t2.id) Formula 5

[0080] As the SQL statement shown in Formula 5 uses or to connect two conditional clauses, and there is a sub-connection corresponding to the keyword exists, it meets the conversion condition and can continue to execute step S103.

[0081] It should be noted that the conversion logic of the SQL statements of the comparison operators (such as =, <, >, <>, etc.) that can be used with any and some is the same as Formula 4, and technical personnel in this field can perform equivalent conversion of SQL statements according to actual conditions.

[0082] In some further optional embodiments, when the record existence determination keyword is not exists, the step of determining the corresponding custom keyword based on the position of the record existence determination keyword and the OR logical keyword may generally include: when the record existence determination keyword is not exists and the position of the OR logical keyword is the first position, determining that the custom keyword is the second custom keyword, and the processing logic corresponding to the second custom keyword is an anti-semi-join.

[0083] Optionally, the step of converting the database operation statement according to the user-defined keyword may generally include: when the record existence determination keyword is not exists and the position of the OR logic keyword is the first position, storing the independent condition corresponding to the OR logic keyword into the second user-defined keyword, and performing an anti-semi-join conversion on the remaining statements;

[0084] Optionally, the step of processing the converted database operation statement according to the execution logic corresponding to the custom keyword can generally include: obtaining the data source in the converted database operation statement; judging one by one whether the tuples in the data source meet the independent conditions stored in the second custom keyword, and obtaining the judgment result; when the judgment result is true, returning the tuple; when the judgment result is false, processing the tuple data according to the anti-semi-join processing logic in the second custom keyword, and returning the processing result; when the judgment result is null, skipping the current tuple and processing the next tuple.

[0085] An optional specific processing example of the method of the present invention for not exists is as follows:

[0086] Select*from t1 where value<10or not exists(select 1from t2 wheret1.id=t2.id)Equation 6

[0087] When the database operation statement obtained by the database is the SQL statement shown in Formula 6, first determine whether the or logical keyword and the record existence determination keyword exist. Since there is "or not exists(select1from t2 wheret1.id=t2.id)" in Formula 6, it contains or and not exists, so it is determined to be yes; then determine whether the sub-connection query table in the sub-connection corresponding to the record existence determination keyword is an empty table. It can be seen from the sub-connection "select 1from t2 wheret1.id=t2.id" in Formula 6 that it is necessary to determine whether the t2 table is an empty table.

[0088] When the t2 table is not an empty table, it is necessary to determine whether the database operation statement meets the preset conversion conditions. In the SQL statement shown in Formula 6, the OR logical keyword corresponds to two conditional information, namely "exists(select 1from t2where t1.id=t2.id)" and "value<10". Since the semi-join conversion needs to remove or, and "value<10" is an independent condition, it is necessary to determine whether the SQL statement after removing "value<10or" can meet the semi-join conversion conditions. Since the SQL statement shown in Formula 6 can be semi-joined after removing "value<10or", it is determined to meet the preset conversion conditions.

[0089] Subsequently, the corresponding custom keyword is determined according to the position of the record existence determination keyword and the OR logic keyword. Since the record existence determination keyword is not exists and the position of the OR logic keyword is the first position, the custom keyword is determined to be the second custom keyword. An optional example of the second custom keyword is: "anti_join_outer_or". It should be noted that the method of the present invention does not limit the specific style of the custom keyword, but only requires the corresponding execution logic to be consistent with the execution logic corresponding to the second custom keyword. Technical personnel in this field can set the specific style of the custom keyword according to actual conditions.

[0090] Then, the database operation statement is converted according to the second custom keyword. First, "value<10" is stored in the second custom keyword, that is, "anti join outer or (value<10)", and then the remaining statements are semi-joined. The final conversion result is shown in Formula 7:

[0091] Select * from t1 anti join outer or(value<10) t2 on t1.id=t2.id Equation 7

[0092] When the database faces the SQL statement shown in Formula 7, it will process the tuple data of table t1 one by one. The specific processing process is: first determine whether the tuple data meets "value<10". If so, return the tuple data directly; if the judgment result is false, process the tuple data according to the anti-semi-join logic (also known as anti join logic) in the second custom keyword, and return the processing result. The anti join logic is used to find out the situation where the records in one table have no matching items in the other table. The result of the semi-join only contains the records in the first table (t1) that have no corresponding matching items in the second table (t2). If the tuple data is null, you can directly skip the current tuple and process the next tuple.

[0093] In some further optional embodiments, the step of determining the corresponding custom keyword based on the position of the record existence determination keyword and the OR logical keyword may generally include: when the record existence determination keyword is not exists and the position of the OR logical keyword is the second position, determining that the custom keyword is the third custom keyword, and the processing logic corresponding to the third custom keyword is an anti-semi-join.

[0094] Optionally, the step of converting the database operation statement according to the custom keyword may generally include: when the record existence determination keyword is not exists and the position of the OR logic keyword is the second position, storing the independent condition corresponding to the OR logic keyword into a third custom keyword, and performing an anti-semi-join conversion on the remaining statements;

[0095] Optionally, the step of processing the converted database operation statement according to the execution logic corresponding to the custom keyword can generally include: obtaining the data source in the converted database operation statement; judging one by one whether the tuples in the data source meet the independent conditions stored in the third custom keyword, and obtaining the judgment result; when the judgment result is true, skipping the current tuple and processing the next tuple; when the judgment result is false, processing the tuple data according to the anti-semi-join processing logic in the second custom keyword, and returning the processing result; when the judgment result is null, returning the tuple.

[0096] An optional specific processing example is as follows:

[0097] Select*from t1 where not exists(select 1from t2 where t1.id=t2.id orvalue<10) Formula 8

[0098] When the database operation statement obtained by the database is the SQL statement shown in Formula 8, first determine whether the OR logical keyword and the record existence determination keyword exist. Since there is "not exists(select 1from t2 where t1.id=t2.id or value<10)" in Formula 8, it contains or and not exists, so it is determined to be yes; then determine whether the sub-connection query table in the sub-connection corresponding to the record existence determination keyword is an empty table. It can be seen from the sub-connection "select 1fromt2 where t1.id=t2.id or value<10" in Formula 8 that it is necessary to determine whether the t2 table is an empty table.

[0099] When the t2 table is not an empty table, it is necessary to determine whether the database operation statement meets the preset conversion conditions. In the SQL statement shown in Formula 8, the or logic keyword corresponds to two conditional information, namely "t1.id=t2.id" and "value<10". Since the semi-join conversion needs to remove or, and "value<10" is an independent condition, it is necessary to determine whether the SQL statement after removing "or value<10" can meet the semi-join conversion conditions. Since the SQL statement shown in Formula 8 can be semi-joined after removing "orvalue<10", it is determined to meet the preset conversion conditions.

[0100] Then, the corresponding custom keyword is determined according to the position of the record existence determination keyword and the OR logic keyword. Since the record existence determination keyword is not exists and the position of the OR logic keyword is the second position, the custom keyword is determined to be the third custom keyword. An optional example of the third custom keyword is: "anti_join_inner_or".

[0101] Then, the database operation statement is converted according to the third custom keyword. First, "value<10" is stored in the third custom keyword, that is, "anti join inner or (value<10)", and then the remaining statements are semi-joined. The final conversion result is shown in Formula 9:

[0102] Select * from t1 anti join inner or(value<10) t2 on t1.id=t2.id Formula 9

[0103] When facing the SQL statement shown in Formula 9, the database will process the tuple data of the t1 table one by one. The specific processing process is: first determine whether the tuple data meets the "value<10" condition. If so, skip the current tuple and process the next tuple; if the judgment result is false, process the tuple data according to the anti-semi-join logic (also known as anti join logic) in the third custom keyword, and return the processing result; if the tuple data is null, return the current tuple data.

[0104] Optionally, after the step of determining whether the sub-connection query table in the sub-connection corresponding to the record existence determination keyword is an empty table, the step further includes: if the sub-connection query table is an empty table, eliminating the invalid part in the record existence determination keyword and the database operation statement. Still taking the SQL statement shown in Formula 1 as an example, if the t2 table is an empty table, the result returned by "exists(select1from t2 where t1.id=t2.id)" will always be false, so you can choose to eliminate this invalid part, and the SQL statement after elimination is shown in Formula 10:

[0105] Select * from t1 where value<10 Equation 10

[0106] The execution process of the SQL statement shown in Formula 10 reduces the processing process of exists and its sub-connections compared to the SQL statement shown in Formula 1, thereby greatly improving the processing efficiency of the SQL.

[0107] Similarly, when the t2 table is an empty table, the execution result of the SQL statement shown in Formula 2 only contains tuple data with value<10. Therefore, you can also choose to eliminate this invalid part. After elimination, the SQL statement is still as shown in Formula 10.

[0108] Taking the SQL statement shown in Formula 6 as an example, if the t2 table is an empty table, the result returned by "not exists (select 1 from t2 where t1.id = t2.id)" will always be true. At this time, all the data of the t1 table can be directly returned. Therefore, you can choose to eliminate the invalid part after where, that is, the independent condition corresponding to the OR logical keyword (value < 10or), the record existence judgment keyword (not exists), and the sub-connection corresponding to the record existence judgment keyword (select 1 from t2 where t1.id = t2.id). The SQL statement after elimination is shown in Formula 11:

[0109] Select * from t1 formula 11

[0110] The execution process of the SQL statement shown in Formula 11 reduces the processing process of the conditional statement compared to the SQL statement shown in Formula 6, thereby greatly improving the processing efficiency of the SQL.

[0111] Similarly, when the t2 table is an empty table, the execution result of the SQL statement shown in Formula 8 also includes all the data in the t1 table. Therefore, you can also choose to eliminate the conditional statement. The SQL statement after elimination is still as shown in Formula 11.

[0112] Figure 2 FIG. 1 is a flow chart of a method for processing a database operation statement according to another embodiment of the present invention. Figure 2 As shown, the method for processing the database operation statement at least includes the following steps S201 to S213.

[0113] Step S201, obtaining a database operation statement.

[0114] Step S202, determine whether there are OR logic keywords and record existence determination keywords in the conditional statement.

[0115] Step S203: If the result of step S202 is yes, it is determined whether the sub-connection query table in the sub-connection corresponding to the record existence determination keyword is an empty table.

[0116] Step S204, when the judgment in step S203 is no, it is determined whether the database operation statement meets the preset conversion condition.

[0117] Step S205: When the judgment in step S204 is yes, it is determined whether the record contains the type of the judgment keyword.

[0118] Step S206: When the record existence determination keyword is exists, the user-defined keyword is determined to be the first user-defined keyword.

[0119] Step S207, storing the independent conditions corresponding to the OR logic keyword into the first user-defined keyword, and performing a semi-join conversion on the remaining statements.

[0120] Step S208, when the record existence determination keyword is not exists, determine the position of the determination or logic keyword.

[0121] Step S209: when not exists exists in or, determine that the custom keyword is the second custom keyword.

[0122] Step S210, storing the independent condition corresponding to the OR logic keyword into the second user-defined keyword, and performing a semi-join conversion on the remaining statements.

[0123] Step S211: when there is or in not exists, the custom keyword is determined to be the third custom keyword.

[0124] Step S212, storing the independent condition corresponding to the OR logic keyword into the third user-defined keyword, and performing a semi-join conversion on the remaining statements.

[0125] Step S213, when the judgment in step S203 is yes, delete the invalid part in the record existence determination keyword and the database operation statement.

[0126] This method can avoid the problem of sub-join to semi-join and anti-semi-join operation being unable to execute due to the or condition, and can optimize the database operation statement to the greatest extent when the sub-join query table is an empty table, thereby simplifying the execution plan, expanding the optimization space, and improving the execution speed of the database operation statement.

[0127] The flow chart provided by the present embodiment is not intended to indicate that the operation of the method will be performed in any particular order, or that all operations of the method are included in all every case. In addition, the method may include additional operations. Within the scope of the technical thinking provided by the present embodiment method, additional changes may be made to the above method.

[0128] It should be understood that in some embodiments, each part can be implemented by hardware, software, firmware or a combination thereof. In the above embodiments, multiple steps or methods can be implemented by software or firmware stored in a memory and executed by a suitable instruction execution system.

[0129] This embodiment also provides a computer program product 10 , a computer readable storage medium 20 , and a computer device 30 . Figure 3 is a schematic diagram of a computer program product 10 according to an embodiment of the present invention, Figure 4 is a schematic diagram of a computer-readable storage medium 20 according to an embodiment of the present invention, Figure 5 is a schematic diagram of a computer device 30 according to an embodiment of the present invention. The computer program product 10 includes a computer program 11, which implements the steps of any of the above-mentioned methods for processing database operation statements when executed by a processor 32. The computer-readable storage medium 20 stores the above-mentioned computer program 11, which implements the steps of any of the above-mentioned methods for processing database operation statements when executed by a processor 32. The computer device 30 may include a memory 31, a processor 32, and the computer program 11 stored in the memory 31 and running on the processor 32.

[0130] The computer program 11 for performing the operation of the present invention may be an assembly instruction, an instruction set architecture (ISA) instruction, a machine instruction, a machine-related instruction, a microcode, a firmware instruction, a state setting data, a configuration data of an integrated circuit, or a source code or an object code written in any combination of one or more programming languages ​​and process programming languages. The computer program 11 may be executed entirely on the user's computer, partially on the user's computer, as an independent software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the latter case, the remote computer may be connected to the user's computer via any type of network (including a local area network (LAN) or a wide area network (WAN), or may be connected to an external computer (e.g., using an Internet service provider via the Internet). In some embodiments, in order to perform various aspects of the present invention, an electronic circuit including, for example, a programmable logic circuit, a field programmable gate array (FPGA) or a programmable logic array (PLA) may execute computer-readable program instructions by utilizing the state information of the computer-readable program instructions to personalize the electronic circuit.

[0131] For the purposes of the description of the present embodiment, the computer program product 10 is a related product that includes the computer program 11. For the purposes of the description of the present embodiment, the computer readable storage medium 20 is a tangible device capable of retaining and storing the computer program 11, which may be any device that can contain, store, communicate, propagate or transmit the program 11 for use with or in conjunction with an instruction execution system, device or apparatus. More specific examples (a non-exhaustive list) of the computer readable storage medium 20 include the following: a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), a static random access memory (SRAM), a portable compact disk read-only memory (CD-ROM), a digital versatile disk (DVD), a memory stick, a floppy disk, a mechanical encoding device, and any suitable combination of the foregoing.

[0132] The computer device 30 may be, for example, a server, a desktop computer, a notebook computer, a tablet computer, or a smart phone. In some examples, the computer device 30 may be a cloud computing node. The computer device 30 may be described in the general context of computer system executable instructions (such as program modules) executed by a computer system. Typically, a program module may include routines, programs, target programs, components, logic, data structures, etc. that perform specific tasks or implement specific abstract data types. The computer device 30 may be implemented in a distributed cloud computing environment where remote processing devices linked via a communication network perform tasks. In a distributed cloud computing environment, program modules may be located on a local or remote computing system storage medium including a storage device.

[0133] The computer device 30 may include a processor 32 adapted to execute stored instructions, and a memory 31 providing temporary storage space for the operation of the instructions during operation. The processor 32 may be a single-core processor, a multi-core processor, a computing cluster, or any number of other configurations. The memory 31 may include a random access memory (RAM), a read-only memory, a flash memory, or any other suitable storage system.

[0134] The computer device 30 may also include a network adapter / interface and an input / output (I / O) interface. The I / O interface allows data to be input and output with external devices that may be connected to the computer device. The network adapter / interface may provide communication between the computer device and a network, which is typically shown as a communication network.

[0135] At this point, those skilled in the art should recognize that, although multiple exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications that conform to the principles of the present invention can still be directly determined or derived based on the content disclosed in the present invention without departing from the spirit and scope of the present invention. Therefore, the scope of the present invention should be understood and recognized as covering all these other variations or modifications.

Claims

1. A method for processing a database operation statement, comprising: Obtaining the database operation statement; Determine whether there is an OR logic keyword and a record existence determination keyword in the conditional statement of the database operation statement; If so, determining whether the sub-connection query table in the sub-connection corresponding to the record determination keyword is an empty table; In the case where the sub-connection query table is not an empty table and the database operation statement meets the preset conversion condition, the corresponding custom keyword is determined according to the positions of the record existence determination keyword and the OR logic keyword, and the preset conversion condition is that the remaining statement after retaining the record existence determination keyword and its sub-connection in the database operation statement and removing another independent condition corresponding to the OR logic keyword meets the semi-connection conversion condition; The database operation statement is converted according to the custom keyword, and the converted database operation statement is processed according to the execution logic corresponding to the custom keyword.

2. The method for processing database operation statements according to claim 1, wherein: The position of the OR logical keyword includes a first position and a second position, the first position being that the record existence determination keyword is one of the conditions for connecting the OR logical keyword, and the second position being that the OR logical keyword is located inside the sub-connection corresponding to the record existence determination keyword.

3. The method for processing database operation statements according to claim 2, wherein: The record existence determination keywords include: exists; The step of determining the corresponding custom keyword according to the record existence determination keyword and the position of the OR logic keyword includes: when the record existence determination keyword is exists and the position of the OR logic keyword is the first position or the second position, determining that the custom keyword is a first custom keyword, and the processing logic corresponding to the first custom keyword is semi-join; The step of converting the database operation statement according to the user-defined keyword includes: when the record existence determination keyword is exists and the position of the OR logic keyword is the first position or the second position, storing the independent condition corresponding to the OR logic keyword into the first user-defined keyword, and performing semi-join conversion on the remaining statements; The step of processing the converted database operation statement according to the execution logic corresponding to the custom keyword includes: obtaining the data source in the converted database operation statement; judging one by one whether the tuples in the data source meet the independent conditions stored in the first custom keyword, and obtaining the judgment result; when the judgment result is true, returning the tuple; when the judgment result is false, processing the data of the tuple according to the semi-join logic in the first custom keyword, and returning the processing result; when the judgment result is null, discarding the current tuple and processing the next tuple.

4. The method for processing database operation statements according to claim 3, wherein: After the step of determining whether there is an OR logical keyword and a record existence determination keyword in the conditional statement of the database operation statement, the following step is further followed: In the case that the record existence determination keyword does not exist in the database operation statement, it is determined whether there is an OR logic keyword and a multi-value matching keyword in the conditional statement of the database operation statement, and the multi-value matching keyword includes: any, some; When the database operation statement contains the OR logical keyword and also contains any multi-value matching keyword, the sub-connection is equivalently converted into a sub-connection corresponding to EXISTS; The step of determining whether the sub-connection query table in the sub-connection corresponding to the record existence determination keyword is an empty table is executed according to the converted database operation statement.

5. The method for processing database operation statements according to claim 2, wherein: The record existence determination keywords include: not exists; The step of determining the corresponding custom keyword according to the record existence determination keyword and the position of the OR logic keyword includes: when the record existence determination keyword is not exists and the position of the OR logic keyword is the first position, determining that the custom keyword is a second custom keyword, and the processing logic corresponding to the second custom keyword is an anti-semi-join; The step of converting the database operation statement according to the user-defined keyword includes: when the record existence determination keyword is not exists and the position of the OR logic keyword is the first position, storing the independent condition corresponding to the OR logic keyword into the second user-defined keyword, and performing an anti-semi-join conversion on the remaining statements; The step of processing the converted database operation statement according to the execution logic corresponding to the custom keyword includes: obtaining the data source in the converted database operation statement; judging one by one whether the tuples in the data source meet the independent condition stored in the second custom keyword, and obtaining the judgment result; if the judgment result is true, returning the tuple; if the judgment result is false, processing the data of the tuple according to the anti-semi-join processing logic in the second custom keyword, and returning the processing result; if the judgment result is null, skipping the current tuple and processing the next tuple.

6. The method for processing database operation statements according to claim 5, wherein: The step of determining the corresponding custom keyword according to the position of the record existence determination keyword and the OR logic keyword includes: when the record existence determination keyword is not exists and the position of the OR logic keyword is the second position, determining that the custom keyword is a third custom keyword, and the processing logic corresponding to the third custom keyword is an anti-semi-join; The step of converting the database operation statement according to the user-defined keyword includes: when the record existence determination keyword is not exists and the position of the OR logic keyword is the second position, storing the independent condition corresponding to the OR logic keyword into the third user-defined keyword, and performing an anti-semi-join conversion on the remaining statements; The step of processing the converted database operation statement according to the execution logic corresponding to the custom keyword includes: obtaining the data source in the converted database operation statement; judging one by one whether the tuples in the data source meet the independent condition stored in the third custom keyword, and obtaining the judgment result; if the judgment result is true, skipping the current tuple and processing the next tuple; if the judgment result is false, processing the data of the tuple according to the anti-semi-join processing logic in the second custom keyword, and returning the processing result; if the judgment result is null, returning the tuple.

7. The method for processing database operation statements according to claim 1, wherein: After the step of judging whether the sub-connection query table in the sub-connection corresponding to the record determination keyword is an empty table, the following step is further included: In the case that the sub-connection query table is an empty table, the record existence determination keyword and the invalid part in the database operation statement are eliminated.

8. A computer-readable storage medium having a computer program stored thereon, wherein when the computer program is executed by a processor, the steps of the method for processing a database operation statement according to any one of claims 1 to 7 are implemented.

9. A computer program product, comprising a computer program, which, when executed by a processor, implements the steps of the method for processing a database operation statement according to any one of claims 1 to 7.

10. A computer device comprising a memory, a processor, and a machine executable program stored in the memory and running on the processor, wherein the processor implements the steps of the method for processing database operation statements according to any one of claims 1 to 7 when executing the machine executable program.

Images