Database operation statement optimization method, medium and product

Abstract

The invention provides a database operation statement optimization method, a medium and a product. The method comprises the following steps: acquiring a database operation statement; judging whether the database operation statement contains a target keyword or not, wherein the target keyword is used for processing a result set obtained by the parallel query branch connected with the target keyword; if yes, the database operation statement is optimized according to the corresponding relation between the parallel query branches and the type of the target keyword, and therefore the query branches are reduced. Through the method, the number of parallel query branches can be reduced, so that unnecessary database operation and table scanning are reduced, the query efficiency is improved, the query logic is simplified, the database query performance is improved, and resource consumption is reduced.

Description

Technical Field

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

[0002] In the current general database technology, as the amount of data continues to grow, the performance of database queries has become a key issue. In traditional database queries, there are some keywords that can process the result set of parallel query statements, but in some special cases, two parallel query statements will repeat some of the same operations, thereby increasing unnecessary database resource consumption. Therefore, further optimization can be performed to improve query performance and reduce resource consumption. Summary of the invention

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

[0004] An object of the present invention is to optimize database operation statements that meet the conditions.

[0005] A further object of the present invention is to reduce the number of parallel query branches.

[0006] Another further object of the present invention is to improve the performance of the database and reduce resource consumption.

[0007] In particular, the present invention provides a method for optimizing database operation statements, comprising:

[0008] Get database operation statements;

[0009] Determine whether the database operation statement contains the target keyword, which is used to process the result set obtained by the parallel query branch to which it is connected;

[0010] If so, the database operation statement is optimized according to the correspondence between the parallel query branches and the type of the target keyword, thereby reducing the query branches.

[0011] Optionally, the target keyword includes a union deduplication keyword, and the union deduplication keyword is used to merge the result sets of the parallel query branches and perform deduplication processing;

[0012] The steps of optimizing the database operation statement according to the correspondence between the parallel query branches and the type of the target keyword include:

[0013] Determine whether the target columns and data source tables between the parallel query branches connected by the union deduplication keywords are the same;

[0014] When the target columns and data source tables between parallel query branches are the same, determining whether there is a subset relationship between the parallel query branches;

[0015] When there is a subset relationship between parallel query branches, retain the query branch with a larger result set range among the parallel query branches, and use the retained unique keywords to remove duplicates;

[0016] When there is no subset relationship between parallel query branches, the filter conditions of the parallel query branches are connected by OR logical keywords, and duplicate removal is performed by retaining unique keywords.

[0017] Optionally, the target keyword includes an intersection deduplication keyword, and the intersection deduplication keyword is used to obtain the intersection in the result set of the parallel query branch and perform deduplication processing;

[0018] The steps of optimizing the database operation statement according to the correspondence between the parallel query branches and the type of the target keyword include:

[0019] Determine whether the target columns and data source tables between parallel query branches connected by intersection deduplication keywords are the same;

[0020] When the target columns and data source tables between parallel query branches are the same, determining whether there is a subset relationship between the parallel query branches;

[0021] When there is a subset relationship between parallel query branches, retain the query branch with a smaller result set range in the parallel query branch, and use the unique keyword to remove duplicates;

[0022] When there is no subset relationship between parallel query branches, the filter conditions of the parallel query branches are connected with logical keywords, and duplicates are removed by retaining unique keywords.

[0023] Optionally, after the step of determining whether the target columns and data source tables between parallel query branches connected by intersection deduplication keywords are the same, the following step is further included:

[0024] When the target columns and data source tables between the parallel query branches are different, determining whether the filter conditions corresponding to the target columns between the parallel query branches have an intersection relationship;

[0025] If there is no intersection relationship between the filter conditions, the database operation statement is converted so that the database operation statement does not output any result.

[0026] Optionally, the target keyword includes an intersection non-deduplication keyword, and the intersection non-deduplication keyword is used to obtain an intersection in a result set of a parallel query branch, and no deduplication processing is performed;

[0027] The steps of optimizing the database operation statement according to the correspondence between the parallel query branches and the type of the target keyword include:

[0028] Determine whether the target columns and data source tables between parallel query branches connected by intersection without deduplication of keywords are the same;

[0029] When the target columns and data source tables between the parallel query branches are different, determining whether the filter conditions corresponding to the target columns between the parallel query branches have an intersection relationship;

[0030] If there is no intersection relationship between the filter conditions, the database operation statement is converted so that the database operation statement does not output any result.

[0031] Optionally, the target keyword includes a difference set non-deduplication keyword, and the difference set non-deduplication keyword is used to obtain data that exists in a first query result set corresponding to a first query branch in the parallel query branch but does not exist in a second query result set corresponding to a second query branch, including duplicate data;

[0032] The steps of optimizing the database operation statement according to the correspondence between the parallel query branches and the type of the target keyword include:

[0033] Determine whether the target columns and data source tables between parallel query branches connected by difference sets without deduplication of keywords are the same;

[0034] When the target columns and data source tables between parallel query branches are the same, determining whether there is a subset relationship between the parallel query branches;

[0035] In the case where there is a subset relationship between parallel query branches and a first query branch in the parallel query branches is a subset of a second query branch, the database operation statement is converted so that the database operation statement does not output a result.

[0036] Optionally, the target keyword includes a difference set deduplication keyword, and the difference set deduplication keyword is used to obtain non-repeated data that exists in a first query result set corresponding to a first query branch in a parallel query branch but does not exist in a second query result set corresponding to a second query branch;

[0037] The steps of optimizing the database operation statement according to the correspondence between the parallel query branches and the type of the target keyword include:

[0038] Determine whether the target columns and data source tables between the parallel query branches connected by the difference set deduplication keywords are the same;

[0039] When the target columns and data source tables between parallel query branches are the same, determining whether there is a subset relationship or an intersection relationship between the parallel query branches;

[0040] In the case where there is a subset relationship between parallel query branches and the second query branch in the parallel query branch is a subset of the first query branch, the filtering condition of the second query branch is negated, the filtering condition of the parallel query branch is connected with the logical keyword, and the unique keyword is retained to remove duplicates;

[0041] In the case where there is a subset relationship between parallel query branches and a first query branch in the parallel query branches is a subset of a second query branch, converting the database operation statement so that the database operation statement does not output a result;

[0042] When there is no subset relationship and no intersection relationship between the parallel query branches, the first query branch is retained, the difference set deduplication keyword and the second query branch are deleted, and the retained unique keyword is used for deduplication.

[0043] Optionally, after the step of determining whether the target columns and data source tables between the parallel query branches connected by the difference set deduplication keywords are the same, the following step is further included:

[0044] When the target columns and data source tables between parallel query branches are different, determining whether there is an intersection relationship between the filter conditions corresponding to the parallel query branches;

[0045] When there is no intersection relationship between the filtering conditions, the first query branch is retained, the difference set deduplication keywords and the second query branch are deleted, and the retained unique keywords are used for deduplication.

[0046] Optionally, the step of converting the database operation statement so that the database operation statement does not output a result includes:

[0047] retaining the first query branch in the parallel query branches;

[0048] Add the keyword "limit the number of returned rows" after the first query branch, and limit the number of returned rows to 0 through the keyword "limit the number of returned rows".

[0049] 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 optimizing database operation statements are implemented.

[0050] The method for optimizing database operation statements of the present invention first obtains database operation statements; determines whether the database operation statements contain target keywords, which are used to process the result set obtained by the parallel query branches connected to the database operation statements; if so, optimizes the database operation statements according to the corresponding relationship between the parallel query branches and the type of the target keywords, thereby reducing the query branches. Through this method, the number of parallel query branches can be reduced, thereby reducing unnecessary database operations and table scans, which not only improves query efficiency, but also simplifies query logic, improves database query performance and reduces resource consumption.

[0051] 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

[0052] 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:

[0053] Figure 1 is a flow chart of a method for optimizing database operation statements according to an embodiment of the present invention;

[0054] Figure 2 is a flow chart of a method for optimizing database operation statements according to another embodiment of the present invention;

[0055] Figure 3 is a flow chart of a method for optimizing database operation statements according to yet another embodiment of the present invention;

[0056] Figure 4 is a flow chart of a method for optimizing database operation statements according to yet another embodiment of the present invention;

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

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

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

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

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

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

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

[0064] Step S102: determine whether the database operation statement contains a target keyword, wherein the target keyword is used to process the result set obtained by the parallel query branch to which it is connected.

[0065] Step S103, when the judgment in step S102 is yes, the database operation statement is optimized according to the correspondence between the parallel query branches and the type of the target keyword, so as to reduce the query branches.

[0066] Through this method, the number of parallel query branches can be reduced, thereby reducing unnecessary database operations and table scans, which not only improves query efficiency, but also simplifies query logic, improves database query performance and reduces resource consumption.

[0067] In some optional embodiments, the target keyword may include a union deduplication keyword, which is used to merge the result sets of parallel query branches and perform deduplication processing. In current database technology, the union deduplication keyword is generally UNION, which is used to merge the result sets of two or more SELECT statements. The UNION operator removes duplicate rows in the result set and returns a unique row set.

[0068] Optionally, the step of optimizing the database operation statement according to the correspondence between parallel query branches and the type of target keywords may generally include: determining whether the target columns and data source tables between the parallel query branches connected by the union deduplication keywords are the same; when the target columns and data source tables between the parallel query branches are the same, determining whether there is a subset relationship between the parallel query branches; when there is a subset relationship between the parallel query branches, retaining the query branch with a large result set range in the parallel query branch, and using the retained unique keyword for deduplication; when there is no subset relationship between the parallel query branches, connecting the filtering conditions of the parallel query branches through the OR logical keyword, and using the retained unique keyword for deduplication.

[0069] In the case where the target columns and data source tables between parallel query branches are the same, if there is a subset relationship between the parallel query branches, then after the database operation statement is merged under the action of UNION, only the query results corresponding to the query branch with a large result set range will exist. Therefore, it is possible to consider eliminating the query branch with a relatively small result set range, thereby reducing the execution steps of the database.

[0070] If there is no subset relationship between parallel query branches, but the target columns and data source tables between the parallel query branches are the same, it means that the difference between the two query branches is only the different filtering conditions, so they can be optimized to merge the filtering conditions of the two query branches into one statement. Since the function of UNION is to obtain the union, it is necessary to connect the filtering conditions of the two through the logical keyword (such as or); in addition, considering that UNION also has the function of deduplication, it is necessary to deduplicate by retaining unique keywords (such as distinct). In the end, compared with the database resources consumed by the database executing the original two query branches and executing the UNION operator, it is obvious that executing a SQL statement consumes fewer database resources and the execution speed will also be improved.

[0071] In some other optional embodiments, the target keyword may include an intersection deduplication keyword, which is used to obtain the intersection of the result sets of the parallel query branches and perform deduplication processing. In current database technology, the intersection deduplication keyword is generally intersect, which is used to return the intersection of two query result sets, that is, the rows that appear in both result sets.

[0072] Optionally, the step of optimizing the database operation statement according to the correspondence between parallel query branches and the type of target keywords may generally include: determining whether the target columns and data source tables between the parallel query branches connected by the intersection deduplication keywords are the same; when the target columns and data source tables between the parallel query branches are the same, determining whether there is a subset relationship between the parallel query branches; when there is a subset relationship between the parallel query branches, retaining the query branches with a small result set range in the parallel query branches, and using the retained unique keywords for deduplication; when there is no subset relationship between the parallel query branches, connecting the filtering conditions of the parallel query branches with the logical keywords, and using the retained unique keywords for deduplication.

[0073] When the target columns and data source tables between parallel query branches are the same, if there is a subset relationship between the parallel query branches, after the database operation statement is merged under the action of intersect, only the query results corresponding to the query branch with a small result set range will exist. Therefore, you can consider eliminating the query branch with a relatively large result set range, thereby reducing the execution steps of the database.

[0074] If there is no subset relationship between parallel query branches, but the target columns and data source tables between the parallel query branches are the same, it means that the difference between the two query branches is only the different filtering conditions, so they can be optimized to merge the filtering conditions of the two query branches into one statement. Since intersect is used to obtain the intersection, it is necessary to connect the filtering conditions of the two branches through logical keywords (such as and); in addition, considering that intersect also has the function of deduplication, it is necessary to deduplicate by retaining unique keywords (such as distinct). In the end, compared with the database resources consumed by the database executing the original two query branches and executing the intersect operator, it is obvious that executing a SQL statement consumes fewer database resources and the execution speed will also be improved.

[0075] Optionally, after the step of determining whether the target columns and data source tables between the parallel query branches connected by the intersection deduplication keywords are the same, it can generally also include: when the target columns and data source tables between the parallel query branches are different, determining whether there is an intersection relationship between the filtering conditions corresponding to the target columns between the parallel query branches; if the filtering conditions do not have an intersection relationship, converting the database operation statement so that the database operation statement does not output any results.

[0076] When the target columns and data source tables of the parallel query branches are different, and there is no intersection in the filtering conditions of the two, it indicates that the result sets obtained by the final query of the two query branches are completely different, so there will be no results when the intersection of the two is taken under the action of intersect. If it is known that the database operation statement has no results, it can be optimized so that the database operation statement does not output results directly, avoiding the database from executing too many useless query operations and improving query efficiency. An optional example of making the database operation statement output no results is to retain the first query branch in the parallel query branch; add a keyword to limit the number of returned rows after the first query branch, and limit the number of returned rows to 0 (for example, limit 0) through the keyword to limit the number of returned rows.

[0077] In some other optional embodiments, the target keyword may include an intersection non-deduplication keyword, which is used to obtain the intersection of the result sets of the parallel query branches without deduplication. In current database technology, the intersection non-deduplication keyword is generally intersect all, which is used to return the intersection of two query result sets, including duplicate rows.

[0078] Optionally, the step of optimizing the database operation statement according to the correspondence between parallel query branches and the type of target keywords may generally include: determining whether the target columns and data source tables between parallel query branches connected by intersection non-deduplicated keywords are the same; when the target columns and data source tables between the parallel query branches are different, determining whether there is an intersection relationship between the filtering conditions corresponding to the target columns between the parallel query branches; if the filtering conditions do not have an intersection relationship, converting the database operation statement so that the database operation statement does not output any results.

[0079] When the target columns and data source tables of the parallel query branches are different, and there is no intersection in the filtering conditions of the two, it indicates that the result sets obtained by the final query of the two query branches are completely different. Therefore, the intersection of the two under the effect of intersect all will result in no results. If it is known that the database operation statement has no results, it can be optimized so that the database operation statement does not output results directly, avoiding the database from executing too many useless query operations and improving query efficiency. An optional example of making the database operation statement output no results is to retain the first query branch in the parallel query branch; add a keyword to limit the number of returned rows after the first query branch, and limit the number of returned rows to 0 (for example, limit 0) through the keyword to limit the number of returned rows.

[0080] In some other optional embodiments, the target keyword may include a difference set non-deduplication keyword, which is used to obtain data that exists in the first query result set corresponding to the first query branch in the parallel query branch but does not exist in the second query result set corresponding to the second query branch, including duplicate data. In current database technology, the difference set non-deduplication keyword is generally except all, which is used to return all rows that exist in the first query result set but do not exist in the second query result set, including duplicate rows.

[0081] Optionally, the step of optimizing the database operation statement according to the correspondence between the parallel query branches and the type of the target keyword may generally include: determining whether the target columns and data source tables between the parallel query branches connected by the difference set without deduplicated keywords are the same; when the target columns and data source tables between the parallel query branches are the same, determining whether there is a subset relationship between the parallel query branches; when there is a subset relationship between the parallel query branches and the first query branch in the parallel query branches is a subset of the second query branch, converting the database operation statement so that the database operation statement does not output a result.

[0082] When the target columns and data source tables between parallel query branches are the same, and there is a subset relationship between the parallel query branches and the first query branch in the parallel query branch is a subset of the second query branch, it means that the result set that can be queried by the first query branch is within the result set obtained by the second query branch. Since the difference set does not remove duplicates, the keyword except all is used to obtain data that exists in the first query result set corresponding to the first query branch in the parallel query branch, but does not exist in the second query result set corresponding to the second query branch. Therefore, under the effect of except all, there is no data row in the database operation statement that exists in the first query result set but does not exist in the second query result set, that is, there is no result set. When it is known that the database operation statement has no results, it can be optimized, so that the database operation statement does not output results directly, avoiding the database from executing too many useless query operations and improving query efficiency. An optional example of making the database operation statement output no results is to retain the first query branch in the parallel query branch; add a keyword to limit the number of returned rows after the first query branch, and limit the number of returned rows to 0 (for example, limit 0) by limiting the number of returned rows.

[0083] In some other optional embodiments, the target keyword may include a difference set deduplication keyword, which is used to obtain non-duplicate data that exists in the first query result set corresponding to the first query branch in the parallel query branch, but does not exist in the second query result set corresponding to the second query branch. In current database technology, the difference set non-deduplication keyword is generally except, which is used to return all rows that exist in the first query result set but do not exist in the second query result set, but does not include duplicate rows.

[0084] Optionally, the step of optimizing the database operation statement according to the correspondence between the parallel query branches and the type of the target keyword may generally include: determining whether the target columns and data source tables between the parallel query branches connected by the difference set deduplication keywords are the same; when the target columns and data source tables between the parallel query branches are the same, determining whether there is a subset relationship or an intersection relationship between the parallel query branches; when there is a subset relationship between the parallel query branches and the second query branch in the parallel query branch is a subset of the first query branch, negating the filtering condition of the second query branch, connecting the filtering condition of the parallel query branch with the logical keyword, and using the retained unique keyword for deduplication; when there is a subset relationship between the parallel query branches and the first query branch in the parallel query branch is a subset of the second query branch, converting the database operation statement so that the database operation statement does not output a result; when there is no subset relationship and no intersection relationship between the parallel query branches, retaining the first query branch, deleting the difference set deduplication keyword and the second query branch, and using the retained unique keyword for deduplication.

[0085] In the case where the target columns and data source tables between parallel query branches are the same, if the second query branch in the parallel query branch is a subset of the first query branch, since the difference set deduplication keyword except is used to obtain data that exists in the first query result set corresponding to the first query branch in the parallel query branch but does not exist in the second query result set corresponding to the second query branch, the final execution result of the database operation statement will be the remaining data rows after deleting the second query result set from the first query result set. When this result is known, the database operation statement can be transformed. The specific transformation operation can be to negate the filter condition of the second query branch (for example, add not before the filter condition), connect the filter condition of the parallel query branch through the logical keyword (for example, and), and since the difference set deduplication keyword has the deduplication property, it is also necessary to use the unique retention keyword (for example, distinct) for deduplication.

[0086] In the case where the target columns and data source tables between parallel query branches are the same, if the first query branch in the parallel query branch is a subset of the second query branch, it means that the result set that can be queried by the first query branch is within the result set obtained by the second query branch. Since the function of the difference set deduplication keyword except is to obtain data that exists in the first query result set corresponding to the first query branch in the parallel query branch but does not exist in the second query result set corresponding to the second query branch, under the function of except, there is no data row in the database operation statement that exists in the first query result set but does not exist in the second query result set, that is, there is no result set. In the case where it is known that the database operation statement has no results, it can be optimized so that the database operation statement does not output results directly, avoiding the database from executing too many useless query operations and improving query efficiency. An optional example of making the database operation statement output no results is to retain the first query branch in the parallel query branch; add a keyword to limit the number of returned rows after the first query branch, and limit the number of returned rows to 0 (for example, limit 0) by using the keyword to limit the number of returned rows.

[0087] When the target columns and data source tables between parallel query branches are the same, if there is no subset relationship and no intersection relationship between the parallel query branches, it means that there are no identical data rows in the final result sets of the two branches. Combined with the effect of the difference set deduplication keyword except, it can be determined that the result of the parallel query branch will only be the result set of the first query branch and because there is no subset relationship, the data rows will not be deleted due to the result set of the second query branch. If it is known that the result of the parallel query branch is not affected by the result set of the second query branch, it can be optimized to retain the first query branch, delete the difference set deduplication keyword and the second query branch, and use the unique keyword (such as distinct) for deduplication.

[0088] Optionally, after the step of determining whether the target columns and data source tables between the parallel query branches connected by the difference deduplication keywords are the same, the following may generally be included: when the target columns and data source tables between the parallel query branches are different, determining whether there is an intersection relationship between the filtering conditions corresponding to the parallel query branches; when there is no intersection relationship between the filtering conditions, retaining the first query branch, deleting the difference deduplication keywords and the second query branch, and using the retained unique keywords for deduplication.

[0089] When the target columns and data source tables of parallel query branches are different, if there is no intersection relationship between the parallel query branches, it means that there are no identical data rows in the final result sets of the two branches. Combined with the effect of the difference set deduplication keyword except, it can be determined that the result of the parallel query branch will only be the result set of the first query branch and because there is no intersection relationship, the data rows will not be deleted due to the result set of the second query branch. If it is known that the result of the parallel query branch is not affected by the result set of the second query branch, it can be optimized to retain the first query branch, delete the difference set deduplication keyword and the second query branch, and use the unique keyword (such as distinct) for deduplication.

[0090] Optionally, the step of converting the database operation statement so that the database operation statement does not output results may generally include: retaining the first query branch in the parallel query branch; adding a keyword to limit the number of returned rows after the first query branch, and limiting the number of returned rows to 0 (for example, limit 0) through the keyword to limit the number of returned rows.

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

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

[0093] Step S202, determining whether the database operation statement contains a union deduplication keyword.

[0094] Step S203, when the judgment in step S202 is yes, it is determined whether the target columns and data source tables between the parallel query branches connected by the union deduplication keywords are the same.

[0095] Step S204: If the answer in step S203 is yes, determine whether there is a subset relationship between the parallel query branches.

[0096] Step S205: If the result of step S204 is yes, retain the query branch with a large result set range in the parallel query branch, and use the retained unique keywords to remove duplicates.

[0097] Step S206, when the judgment in step S204 is no, the filter conditions of the parallel query branches are connected by OR logical keywords, and duplicate removal is performed using the retained unique keywords.

[0098] Through this method, the number of parallel query branches corresponding to the union deduplication keywords can be reduced, thereby reducing unnecessary database operations and table scans, which not only improves query efficiency, but also simplifies query logic, improves database query performance and reduces resource consumption.

[0099] In order to clearly illustrate the processing method of the present invention for the database operation statement containing the union deduplication keyword, a specific example is proposed as shown in Formula 1:

[0100] select id from t1 where name='king'

[0101] union

[0102] select id from t1 where age < 4 Formula 1

[0103] As shown in Formula 1, there are two parallel query branches through union connection. The first query branch is "select id from t1 where name = 'king'", and the second query branch is "select id from t1 where age < 4". First, execute step S202. Since it contains the union deduplication keyword union, it is determined to be yes. If step S202 is determined to be yes, execute step S203 to determine whether the target columns and data source tables between the parallel query branches connected by the union deduplication keyword are the same. Since the target column in the first query branch is the id column and the data source table is the t1 table, the target column in the second query branch is also the id column and the data source table is also the t1 table, so it meets the conditions. If step S203 is determined to be yes, execute step S204 to determine whether there is a subset relationship between the parallel query branches. Since the filtering condition of the first query branch is "name='king'" and the filtering condition of the second query branch is "age<4", there is no subset relationship between the two. Select to execute step S206 to connect the filtering conditions of the parallel query branches through the or logical keyword, and use the unique keyword to remove duplicates. The id column of the first query branch and the second query branch is different only in the filtering condition. The union semantics is to select the data of age<4 or name='king'. It can be equivalent to use or to replace the union operation. The final optimized SQL statement is shown in Formula 2:

[0104] select distinct id from t1 where age < 4 or name = 'king' Formula 2

[0105] The SQL statement shown in Formula 2 reduces query branches compared to the SQL statement shown in Formula 1, and the database does not need to repeatedly scan the t1 table, thereby reducing database resource consumption and improving query efficiency.

[0106] Another optional example is shown in Formula 3:

[0107] select t1.id from t1 join t2 on t1.id=t2.id

[0108] union

[0109] select t1.id from t1 join t2 on t1.id = t2.id where t1.age < 4 andt2.id > 1 Equation 3

[0110] As shown in Formula 3, there are two parallel query branches through union connection. The first query branch is "select t1.id from t1 join t2 on t1.id=t2.id", and the second query branch is "select t1.idfrom t1 join t2 on t1.id=t2.id where t1.age<4and t2.id>1". First, step S202 is executed. Since it contains the union deduplication keyword union, it is determined to be yes. If step S202 is determined to be yes, step S203 is executed to determine whether the target columns and data source tables between the parallel query branches connected by the union deduplication keywords are the same. Since the target column in the first query branch is the id column and the data source table is the t1 table, the target column in the second query branch is also the id column and the data source table is also the t1 table, so the condition is met. If step S203 is determined to be yes, step S204 is executed to determine whether there is a subset relationship between the parallel query branches. Since the screening condition of the first query branch is "t1.id=t2.id'" and the screening condition of the second query branch is "t1.id=t2.id where t1.age<4and t2.id>1", there is a subset relationship between the two. Step S205 is selected to retain the query branch with a large result set range in the parallel query branch, and the unique keyword is retained for deduplication. At this time, the second query branch is a subset of the first query branch, and the result of the union is the first query branch. There is no need to perform a union operation (keep the branch with a large value range). And because the union operation will deduplicate, the rewritten statement needs to be deduplicated. The final optimized SQL statement is shown in Formula 4:

[0111] select distinct t1.id from t1 join t2 on t1.id = t2.id Equation 4

[0112] The SQL statement shown in Formula 4 reduces query branches compared to the SQL statement shown in Formula 3, and the database does not need to repeatedly scan the t1 table, thereby reducing database resource consumption and improving query efficiency.

[0113] Figure 3 FIG. 1 is a flow chart of a method for optimizing database operation statements according to another embodiment of the present invention. Figure 3 As shown, the method for optimizing the database operation statement at least includes the following steps S301 to S308.

[0114] Step S301, obtaining a database operation statement.

[0115] Step S302, determining whether the database operation statement contains intersection deduplication keywords.

[0116] Step S303, when the judgment in step S302 is yes, it is determined whether the target columns and data source tables between the parallel query branches connected by the intersection deduplication keywords are the same.

[0117] Step S304: When the result of the determination in step S303 is yes, it is determined whether there is a subset relationship between the parallel query branches.

[0118] Step S305: If the result of step S304 is yes, retain the query branch with a smaller result set range in the parallel query branch, and use the retained unique keywords to perform deduplication.

[0119] Step S306, when the judgment in step S304 is no, the filtering conditions of the query branch are connected in parallel with the logical keywords, and the unique keywords are retained for deduplication.

[0120] Step S307 , when the result of the determination in step S303 is no, it is determined whether there is an intersection relationship between the filter conditions corresponding to the target columns between the parallel query branches.

[0121] Step S308: If the judgment in step S307 is no, convert the database operation statement so that the database operation statement does not output a result.

[0122] Through this method, the number of parallel query branches corresponding to the intersection deduplication keywords can be reduced, thereby reducing unnecessary database operations and table scans, which not only improves query efficiency, but also simplifies query logic, improves database query performance and reduces resource consumption.

[0123] In order to clearly illustrate the processing method of the present invention for the database operation statement containing the intersection deduplication keyword, a specific example is proposed as shown in Formula 5:

[0124] select id from t1 where name='king'

[0125] intersect

[0126] select id from t1 where age < 4 Formula 5

[0127] As shown in the SQL statement of Formula 5, there are two parallel query branches through intersect connection, the first query branch is "select id from t1 where name = 'king'", and the second query branch is "select id from t1 where age < 4".

[0128] First, step S302 is executed. Since it contains the intersection deduplication keyword intersect, it is determined to be yes. If step S302 is determined to be yes, step S303 is executed to determine whether the target columns and data source tables between the parallel query branches connected by the intersection deduplication keywords are the same. Since the target column in the first query branch is the id column and the data source table is the t1 table, the target column in the second query branch is also the id column and the data source table is also the t1 table, so it meets the conditions. If step S303 is determined to be yes, step S304 is executed to determine whether there is a subset relationship between the parallel query branches. Since the filtering condition of the first query branch is "name='king'" and the filtering condition of the second query branch is "age<4", there is no subset relationship between the two. Step S306 is selected to connect the filtering conditions of the parallel query branches through the or logical keyword, and use the unique keyword to remove duplicates. The id column of the first query branch and the second query branch is different only in the filtering condition. The semantics of intersect is to select the data with age<4 and name='king'. The intersect operation can be equivalently replaced by and. The final optimized SQL statement is shown in Formula 6:

[0129] select distinct id from t1 where age < 4 and name = 'king' Formula 6

[0130] The SQL statement shown in Formula 6 reduces query branches compared to the SQL statement shown in Formula 1, and the database does not need to repeatedly scan the t1 table, thereby reducing database resource consumption and improving query efficiency.

[0131] Another optional example is shown in Formula 7:

[0132] select t1.id from t1 join t2 on t1.id=t2.id

[0133] intersect

[0134] select t1.id from t1 join t2 on t1.id = t2.id where t1.age < 4 andt2.id > 1 Equation 7

[0135] As shown in Formula 7, there are two parallel query branches through intersect connection, the first query branch is "select t1.id from t1 join t2 on t1.id=t2.id", and the second query branch is "select t1.idfrom t1 join t2 on t1.id=t2.id where t1.age<4and t2.id>1". First, step S302 is executed. Since it contains the intersection deduplication keyword intersect, it is determined to be yes. If step S302 is determined to be yes, step S303 is executed to determine whether the target columns and data source tables between the parallel query branches connected by the intersection deduplication keywords are the same. Since the target column in the first query branch is the id column and the data source table is the t1 table, the target column in the second query branch is also the id column and the data source table is also the t1 table, so it meets the conditions. If step S303 is determined to be yes, step S304 is executed to determine whether there is a subset relationship between the parallel query branches. Since the screening condition of the first query branch is "t1.id=t2.id'" and the screening condition of the second query branch is "t1.id=t2.id where t1.age<4and t2.id>1", under the premise that the target column and the data source table are the same, the second query branch is a subset of the first query branch, so the two have a subset relationship. When the target column and data source table between parallel query branches are the same, the result of intersect is the second query branch, so there is no need to perform intersect operation (keep the branch with a small value range). Therefore, step S305 is selected to retain the query branch with a small result set range in the parallel query branch, and use the unique keyword to deduplicate. And because the intersect operation will deduplicate, it is necessary to deduplicate the rewritten statement (for example, distinct). The final optimized SQL statement is shown in Formula 8:

[0136] select distinct t1.id from t1 join t2 on t1.id=t2.id where t1.age<4and t2.id>1 Formula 8

[0137] The SQL statement shown in Formula 8 reduces query branches compared to the SQL statement shown in Formula 7, and the database does not need to repeatedly scan the t1 table, thereby reducing database resource consumption and improving query efficiency.

[0138] Another optional example is shown in Formula 9:

[0139] select t1.id from t1 join t3 on t1.id=t3.id and t3.id<4

[0140] intersect

[0141] select t2.id from t2 join t4 on t2.id=t4.id and t4.id=5

[0142] intersect

[0143] select id from t5 formula 9

[0144] As shown in Formula 9, in the SQL statement, there are three parallel query branches connected by intersect, the first query branch is "select t1.id from t1 join t3 on t1.id=t3.id and t3.id<4", the second query branch is "select t2.id from t2 join t4 on t2.id=t4.id and t4.id=5", and the third query branch is "select id from t5".

[0145] First, step S302 is executed. Since it contains the intersection deduplication keyword intersect, it is determined to be yes. If step S302 is determined to be yes, step S303 is executed to determine whether the target columns and data source tables between the parallel query branches connected by the intersection deduplication keywords are the same. Since the target column in the first query branch is t1.id and the data source table is t1, the target column in the second query branch is t2.id and the data source table is t2, and the target column in the third query branch is id and the data source table is t5, the condition is not met. If step S303 is determined to be no, step S307 is executed to determine whether there is an intersection relationship between the filter conditions corresponding to the target columns between the parallel query branches. Since the filtering condition of the first query branch is "t1.id=t3.id and t3.id<4", the filtering condition of the second query branch is "t2.id=t4.id and t4.id=5", and the third query branch has no filtering condition, there is no intersection relationship between the three. When the first query branch and the second query branch filter data from the table, the acquired data have no intersection (the first query branch takes data with t1.id<4, and the second query branch takes data with t2.id=5), and cannot intersect with the third query branch, that is, the entire intersect is not required, and the intersect statement has no result output. Execute step S308 to convert the database operation statement so that the database operation statement does not output results. An optional way of conversion is, for example: retain the first query branch in the parallel query branch; add a keyword to limit the number of returned rows after the first query branch, and limit the number of returned rows to 0 by limiting the number of returned rows. The final optimized SQL statement is shown in Formula 10:

[0146] select t1.id from t1 join t3 on t1.id = t3.id and t3.id < 4 limit 0 Equation 10

[0147] The SQL statement shown in Formula 10 reduces query branches compared to the SQL statement shown in Formula 9, and the database does not need to repeatedly scan the t1 table, thereby reducing database resource consumption and improving query efficiency.

[0148] In addition, when the target keyword is an intersection keyword without duplicate removal, there is also a situation that can be optimized. An optional example is shown in Formula 11:

[0149] select id from t1 where id<4

[0150] Intersect all

[0151] select id from t2 where id > 5 Equation 11

[0152] First, since it contains the intersection non-deduplication keyword intersect, it is determined whether the target columns and data source tables between the parallel query branches connected by the intersection deduplication keyword are the same. Since the target column in the first query branch is id and the data source table is t1, and the target column in the second query branch is id and the data source table is t2, it does not meet the conditions; then it is determined whether the filtering conditions corresponding to the target columns between the parallel query branches have an intersection relationship. Since the filtering condition of the first query branch is "id<4" and the filtering condition of the second query branch is "id>5", there is no intersection relationship between the two. When the first query branch and the second query branch filter data from the table, the acquired data have no intersection, the intersect all operation has no result set, and the entire intersect all has no data that meets the conditions and is not output. Therefore, the present invention chooses to convert the database operation statement so that the database operation statement does not output the result. An optional way of conversion is, for example: retain the first query branch in the parallel query branch; add a keyword to limit the number of returned rows after the first query branch, and limit the number of returned rows to 0 by limiting the number of returned rows. The final optimized SQL statement is shown in Formula 12:

[0153] select id from t1 where id < 4 limit 0 Equation 12

[0154] Compared with the SQL statement shown in Formula 11, the SQL statement shown in Formula 12 reduces query branches, reduces database resource consumption, and improves query efficiency.

[0155] Figure 4 FIG. 1 is a flow chart of a method for optimizing database operation statements according to another embodiment of the present invention. Figure 4 As shown, the method for optimizing the database operation statement at least includes the following steps S401 to S410.

[0156] Step S401, obtaining a database operation statement.

[0157] Step S402, determining whether the database operation statement contains a difference set deduplication keyword.

[0158] Step S403, when the judgment in step S402 is yes, it is determined whether the target columns and data source tables between the parallel query branches connected by the difference set deduplication keywords are the same.

[0159] Step S404: if the result of step S403 is yes, it is determined whether there is a subset relationship or an intersection relationship between the parallel query branches.

[0160] Step S405: if the answer in step S404 is yes, determine whether the first query branch is a subset of the second query branch.

[0161] Step S406, if the judgment in step S405 is no, the filtering condition of the second query branch is inverted, the filtering condition of the parallel query branch is connected with the logical keyword, and the unique keyword is retained to remove duplicates.

[0162] Step S407: if the result of step S405 is yes, convert the database operation statement so that the database operation statement does not output a result.

[0163] Step S408, if the judgment in step S404 is no, retain the first query branch, delete the difference set deduplication keyword and the second query branch, and use the retained unique keyword to perform deduplication.

[0164] Step S409: If the result of the determination in step S403 is no, it is determined whether there is an intersection relationship between the filter conditions corresponding to the parallel query branches.

[0165] Step S410, if the judgment in step S409 is no, retain the first query branch, delete the difference set deduplication keyword and the second query branch, and use the retained unique keyword to perform deduplication.

[0166] Through this method, the number of parallel query branches corresponding to the difference set deduplication keywords can be reduced, thereby reducing unnecessary database operations and table scans, which not only improves query efficiency, but also simplifies query logic, improves database query performance and reduces resource consumption.

[0167] In order to clearly illustrate the processing method of the present invention for the database operation statement containing the difference set deduplication keyword, a specific example is proposed as shown in Formula 13:

[0168] select id from t1 where id<5

[0169] except

[0170] select id from t1 where id < 4 Equation 13

[0171] As shown in the SQL statement of Formula 13, in the parallel query branches connected by the difference set deduplication keyword except, the first query branch is "select id from t1 where id<5", and the second query branch is "select id from t1where id<4".

[0172] First, step S402 is executed. Since it contains the difference set deduplication keyword except, it is determined to be yes. If step S402 is determined to be yes, step S403 is executed to determine whether the target columns and data source tables between the parallel query branches connected by the difference set deduplication keywords are the same. Since the target column in the first query branch is the id column and the data source table is the t1 table, the target column in the second query branch is also the id column and the data source table is also the t1 table, so it meets the conditions. If step S403 is determined to be yes, step S404 is executed to determine whether there is a subset relationship or an intersection relationship between the parallel query branches. Since the screening condition of the first query branch is "id<5" and the screening condition of the second query branch is "id<4", there is a subset relationship between the two. If step S404 is determined to be yes, step S405 is executed. Through the two screening conditions, combined with the target column and the data source table, it can be determined that the second query branch is a subset of the first query branch. Since except is to find the difference of the two query branches, that is, the data belonging to the first query branch but not in the second query branch, that is, id<5and not(id<4) or id<5and id>=4. So if the judgment in step S405 is no, execute step S406 to negate the filter condition of the second query branch, connect the filter condition of the parallel query branch with the logical keyword, and use the unique keyword to remove duplicates. An optional operation of negating the filter condition is to add not before the filter condition. The final optimized SQL statement is shown in formula 14:

[0173] select distinct t1.id from t1 where id < 5 and not(id < 4) Equation 14

[0174] The SQL statement shown in Formula 14 reduces query branches compared to the SQL statement shown in Formula 13, and the database does not need to repeatedly scan the t1 table, thereby reducing database resource consumption and improving query efficiency.

[0175] It should be noted that Formula 14 is only an optional operation for negating the filtering condition, and those skilled in the art can select the negation method according to actual conditions.

[0176] Another optional example is shown in Formula 15:

[0177] select id from t1 where id<5and age<5

[0178] except

[0179] select id from t1 where id < 6 Equation 15

[0180] As shown in the SQL statement of Formula 15, in the parallel query branches connected by the difference set deduplication keyword except, the first query branch is "select id from t1 where id<5and age<5", and the second query branch is "select idfrom t1 where id<6".

[0181] First, step S402 is executed. Since it contains the difference set deduplication keyword except, it is determined to be yes. If step S402 is determined to be yes, step S403 is executed to determine whether the target columns and data source tables between the parallel query branches connected by the difference set deduplication keywords are the same. Since the target column in the first query branch is the id column and the data source table is the t1 table, the target column in the second query branch is also the id column and the data source table is also the t1 table, so the condition is met. If step S403 is determined to be yes, step S404 is executed to determine whether there is a subset relationship or an intersection relationship between the parallel query branches. Since the screening condition of the first query branch is "id<5and age<5" and the screening condition of the second query branch is "id<6", there is a subset relationship between the two. If step S404 is determined to be yes, step S405 is executed. Since except is to find the difference of the two query branches, that is, the data belonging to the first query branch but not in the second query branch, but under the premise of the same target column and data source table, the result set range of "id<6" contains "id<5and age<5". age<5". Therefore, by combining the two screening conditions with the target column and the data source table, it can be determined that the first query branch is a subset of the second query branch. Therefore, under the effect of except, there is no content output after the first query branch except the second query branch. Therefore, if the judgment in step S405 is yes, step S407 is executed to convert the database operation statement so that the database operation statement does not output the result. An optional conversion method is, for example: retain the first query branch in the parallel query branch; add a keyword to limit the number of returned rows after the first query branch, and limit the number of returned rows to 0 by limiting the number of returned rows. The final optimized SQL statement is shown in Formula 16:

[0182] select t1.id from t1 where id < 5 limit 0 Equation 16

[0183] The SQL statement shown in Formula 16 reduces query branches compared to the SQL statement shown in Formula 15, and the database does not need to repeatedly scan the t1 table, thereby reducing database resource consumption and improving query efficiency.

[0184] Some other optional examples are shown in Formula 17:

[0185] select t1.id from t1 where id<5

[0186] except

[0187] select t1.id from t1 where id > 6 Equation 17

[0188] As shown in the SQL statement of Formula 17, in the parallel query branches connected by the difference set deduplication keyword except, the first query branch is "select t1.id from t1 where id<5", and the second query branch is "select t1.idfrom t1 where id>6".

[0189] First, execute step S402. Since it contains the difference set deduplication keyword except, it is judged as yes. If step S402 is judged as yes, execute step S403 to determine whether the target columns and data source tables between the parallel query branches connected by the difference set deduplication keywords are the same. Since the target column in the first query branch is the id column and the data source table is the t1 table, the target column in the second query branch is also the id column and the data source table is also the t1 table, so it meets the conditions. If step S403 is judged as yes, execute step S404 to determine whether there is a subset relationship or an intersection relationship between the parallel query branches. Since the screening condition of the first query branch is "id<5", the screening condition of the second query branch is "id<5". The screening condition is "id>6", so there is no subset relationship or intersection relationship between the two; because except is to find the difference between the two query branches, that is, the data that belongs to the first query branch but not in the second query branch, under the premise that the target column and the data source table are the same, the result sets of "id<5" and "id>6" cannot have an intersection, so under the effect of except, the final output result of the SQL statement is all the query results of the first query branch, so if step S404 is judged as no, execute step S408, retain the first query branch, delete the difference set deduplication keywords and the second query branch, and use the retained unique keywords (such as distinct) for deduplication. The final optimized SQL statement is shown in formula 18:

[0190] select distinct t1.id from t1 where id < 5 Equation 18

[0191] The SQL statement shown in Formula 18 reduces query branches compared to the SQL statement shown in Formula 17, and the database does not need to repeatedly scan the t1 table, thereby reducing database resource consumption and improving query efficiency.

[0192] There are some optional implementation examples as shown in Formula 19:

[0193] select id from t1 where id<5

[0194] except

[0195] select id from t2 where id = 6 Equation 19

[0196] As shown in the SQL statement of Formula 19, in the parallel query branches connected by the difference set deduplication keyword except, the first query branch is "select t1.id from t1 where id<5", and the second query branch is "select id from t2 where id=6".

[0197] First, execute step S402. Since it contains the difference set deduplication keyword except, it is judged as yes. If step S402 is judged as yes, execute step S403 to determine whether the target columns and data source tables between the parallel query branches connected by the difference set deduplication keywords are the same. Since the target column in the first query branch is the id column and the data source table is the t1 table, and the target column in the second query branch is the id column and the data source table is also the t2 table, the two data source tables are different and do not meet the conditions. If step S403 is judged as no, execute step S409 to determine whether there is an intersection relationship between the filtering conditions corresponding to the parallel query branches. Since the filtering condition of the first query branch is "id<5", the filtering condition of the second query branch is "id<5". The screening condition of the second query branch is "id=6", so there is no subset relationship or intersection relationship between the two; because except is to find the difference between the two query branches, that is, the data that belongs to the first query branch but not in the second query branch, under the premise that the target column and the data source table are different, the result sets of "id<5" and "id=6" cannot have an intersection, so under the effect of except, the final output result of the SQL statement is all the query results of the first query branch, so if step S409 is judged as no, execute step S410, retain the first query branch, delete the difference set deduplication keyword and the second query branch, and use the retained unique keyword (such as distinct) for deduplication. The final optimized SQL statement is shown in formula 20:

[0198] select distinct t1.id from t1 where id < 5 Equation 20

[0199] The SQL statement shown in Formula 20 reduces query branches compared to the SQL statement shown in Formula 19, and the database does not need to repeatedly scan the t1 table, thereby reducing database resource consumption and improving query efficiency.

[0200] In addition, when the target keyword is a difference set without duplicate keywords, there are also situations that can be optimized. An optional example is shown in Formula 21:

[0201] select id from t1 where id<5and age<5

[0202] except all

[0203] select id from t1 where id < 6 Equation 21

[0204] First, since it contains the difference set non-deduplication keyword except all, it is determined whether the target columns and data source tables between the parallel query branches connected by the difference set non-deduplication keywords are the same. Since the target column in the first query branch is id and the data source table is t1, and the target column in the second query branch is id and the data source table is t1, it meets the conditions; then it is determined whether there is a subset relationship between the parallel query branches. Since the screening condition of the first query branch is "id<5and age<5" and the screening condition of the second query branch is "id<6", on the premise that the target column and the data source table are the same, the first query branch is a subset of the second query branch.

[0205] Since except all is to find the difference between two query branches, that is, the data that belongs to the first query branch but not in the second query branch, but when the first query branch is a subset of the second query branch, the first query branch except all has no content output after the second query branch, so the database operation statement needs to be converted so that the database operation statement does not output results. An optional way of conversion is, for example: retain the first query branch in the parallel query branch; add a keyword to limit the number of returned rows after the first query branch, and limit the number of returned rows to 0 through the keyword to limit the number of returned rows. The final optimized SQL statement is shown in Formula 22:

[0206] select t1.id from t1 where id < 5 limit 0 Equation 22

[0207] Compared with the SQL statement shown in Formula 21, the SQL statement shown in Formula 22 reduces query branches, reduces database resource consumption, and improves query efficiency.

[0208] It should be noted that the above-mentioned conversion method of converting the database operation statement so that the database operation statement does not output the result is only an example. Technical personnel in this field can determine the conversion method according to the actual situation as long as the goal of not outputting the query result after the conversion can be achieved.

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

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

[0211] This embodiment also provides a computer program product 10 , a computer readable storage medium 20 , and a computer device 30 . Figure 5 is a schematic diagram of a computer program product 10 according to an embodiment of the present invention, Figure 6 is a schematic diagram of a computer-readable storage medium 20 according to an embodiment of the present invention, Figure 7 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 optimizing 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 optimizing 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.

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

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

[0214] 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 through 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.

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

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

[0217] 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 optimizing database operation statements, comprising: Obtaining the database operation statement; Determining whether the database operation statement contains a target keyword, wherein the target keyword is used to process a result set obtained by a parallel query branch to which the target keyword is connected; If so, the database operation statement is optimized according to the correspondence between the parallel query branches and the type of the target keyword, thereby reducing the query branches.

2. The method for optimizing database operation statements according to claim 1, wherein: The target keywords include union-set deduplication keywords, and the union-set deduplication keywords are used to merge the result sets of the parallel query branches and perform deduplication processing; The step of optimizing the database operation statement according to the corresponding relationship between the parallel query branches and the type of the target keyword comprises: Determine whether the target columns and data source tables between the parallel query branches connected by the union deduplication keywords are the same; In the case where the target columns and the data source tables between the parallel query branches are the same, determining whether there is a subset relationship between the parallel query branches; In the case where the subset relationship exists between the parallel query branches, retain the query branch with a larger result set range among the parallel query branches, and use the retained unique keywords to perform deduplication; In the case that the subset relationship does not exist between the parallel query branches, the filter conditions of the parallel query branches are connected by an OR logical keyword, and duplicate removal is performed by retaining unique keywords.

3. The method for optimizing database operation statements according to claim 1, wherein: The target keywords include intersection deduplication keywords, and the intersection deduplication keywords are used to obtain the intersection in the result set of the parallel query branch and perform deduplication processing; The step of optimizing the database operation statement according to the corresponding relationship between the parallel query branches and the type of the target keyword comprises: Determine whether the target columns and data source tables between the parallel query branches connected by the intersection deduplication keywords are the same; In the case where the target columns and the data source tables between the parallel query branches are the same, determining whether there is a subset relationship between the parallel query branches; In the case where the subset relationship exists between the parallel query branches, the query branch with a smaller result set range in the parallel query branches is retained, and duplicate removal is performed using the retained unique keywords; In the case that the subset relationship does not exist between the parallel query branches, the filtering conditions of the parallel query branches are connected with logical keywords, and duplicate removal is performed by retaining unique keywords.

4. The method for optimizing database operation statements according to claim 3, wherein: After the step of determining whether the target columns and data source tables between the parallel query branches connected by the intersection deduplication keywords are the same, the following step further comprises: In the case where the target columns and data source tables between the parallel query branches are different, determining whether there is an intersection relationship between the filter conditions corresponding to the target columns between the parallel query branches; If the filtering conditions do not have the intersection relationship, the database operation statement is converted so that the database operation statement does not output a result.

5. The method for optimizing database operation statements according to claim 1, wherein: The target keywords include intersection keywords without deduplication, and the intersection keywords without deduplication are used to obtain the intersection in the result set of the parallel query branch, and no deduplication processing is performed; The step of optimizing the database operation statement according to the corresponding relationship between the parallel query branches and the type of the target keyword comprises: Determine whether the target columns and data source tables between the parallel query branches connected by the intersection without deduplication of keywords are the same; In the case where the target columns and data source tables between the parallel query branches are different, determining whether there is an intersection relationship between the filter conditions corresponding to the target columns between the parallel query branches; If the filtering conditions do not have the intersection relationship, the database operation statement is converted so that the database operation statement does not output a result.

6. The method for optimizing database operation statements according to claim 1, wherein: The target keywords include difference set non-deduplication keywords, and the difference set non-deduplication keywords are used to obtain data that exists in the first query result set corresponding to the first query branch in the parallel query branch but does not exist in the second query result set corresponding to the second query branch, including duplicate data; The step of optimizing the database operation statement according to the corresponding relationship between the parallel query branches and the type of the target keyword comprises: Determine whether the target columns and data source tables between the parallel query branches connected by the difference set without deduplication of keywords are the same; In the case where the target columns and the data source tables between the parallel query branches are the same, determining whether there is a subset relationship between the parallel query branches; In the case where the subset relationship exists between the parallel query branches and the first query branch in the parallel query branches is a subset of the second query branch, the database operation statement is converted so that the database operation statement does not output a result.

7. The method for optimizing database operation statements according to claim 1, wherein: The target keyword includes a difference set deduplication keyword, and the difference set deduplication keyword is used to obtain non-repeated data that exists in the first query result set corresponding to the first query branch in the parallel query branch but does not exist in the second query result set corresponding to the second query branch; The step of optimizing the database operation statement according to the corresponding relationship between the parallel query branches and the type of the target keyword comprises: Determine whether the target columns and data source tables between the parallel query branches connected by the difference set deduplication keywords are the same; In the case where the target columns and data source tables between the parallel query branches are the same, determining whether there is a subset relationship or an intersection relationship between the parallel query branches; In the case where the subset relationship exists between the parallel query branches and the second query branch in the parallel query branch is a subset of the first query branch, the filtering condition of the second query branch is negated, the filtering condition of the parallel query branch is connected by AND logical keywords, and duplicate removal is performed by retaining unique keywords; In the case where the subset relationship exists between the parallel query branches and a first query branch in the parallel query branches is a subset of a second query branch, converting the database operation statement so that the database operation statement does not output a result; When the subset relationship and the intersection relationship do not exist between the parallel query branches, the first query branch is retained, the difference set deduplication keyword and the second query branch are deleted, and deduplication is performed using the retained unique keyword.

8. The method for optimizing database operation statements according to claim 7, wherein: After the step of determining whether the target columns and data source tables between the parallel query branches connected by the difference set deduplication keywords are the same, the following step further comprises: In the case where the target columns and data source tables between the parallel query branches are different, determining whether there is an intersection relationship between the filter conditions corresponding to the parallel query branches; In the case that the intersection relationship does not exist between the filtering conditions, the first query branch is retained, the difference set deduplication keyword and the second query branch are deleted, and deduplication is performed using the retained unique keyword.

9. The method for optimizing database operation statements according to any one of claims 4 to 7, wherein: The step of converting the database operation statement so that the database operation statement does not output a result includes: retaining the first query branch in the parallel query branches; A keyword for limiting the number of returned rows is added after the first query branch, and the number of returned rows is limited to 0 by using the keyword for limiting the number of returned rows.

10. A computer-readable storage medium having a computer program stored thereon, wherein when the computer program is executed by a processor, the computer program implements the steps of the method for optimizing database operation statements according to any one of claims 1 to 9.

Images