Database operation statement optimization method and related product

Abstract

The invention provides a database operation statement optimization method and a related product. The method comprises the following steps: acquiring a database operation statement; judging whether a sub-query statement exists in the database operation statement or not and whether a duplicate removal operation exists in the sub-query statement or not; under the condition that the sub-query statements exist in the database operation statements and duplicate removal operation exists in the sub-query statements, whether the database operation statements meet preset conditions or not is judged, and the preset conditions are used for determining that the database operation statements belong to the condition that duplicate values are not sensitive; and if the database operation statement meets the preset condition, deleting the duplicate removal operation in the sub-query statement. By means of the method, the duplicate removal operation in the sub-query statements of the database operation statements meeting the conditions can be eliminated, and therefore the situation that under the condition that duplicate values are not sensitive, duplicate removal is conducted on data, redundant operation is caused is avoided, and query efficiency is improved.

Description

Technical Field

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

[0002] In the current common database technology, the execution efficiency and accuracy of database operation statements play a vital role in the performance of various information systems. With the continuous growth of data volume and the increasing complexity of data processing requirements, developers need to write efficient and accurate SQL query statements to obtain the required information from the database.

[0003] In database query operations, subquery statements are widely used in various complex data retrieval scenarios. Developers often use subqueries to build multi-level data screening, association, and aggregation logic to extract specific data subsets from multiple tables in the database. When there is a deduplication operation in a subquery statement, the database will perform a deduplication operation on the query results at the subquery node. However, in some cases where duplicate values ​​are not sensitive, the deduplication operation of the subquery will affect the execution efficiency of the database, so it needs to be optimized. Summary of the invention

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

[0005] An object of the present invention is to improve the execution efficiency of database operation statements.

[0006] A further object of the present invention is to eliminate redundant deduplication operations in situations where duplicate values ​​are insensitive.

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

[0008] Get database operation statements;

[0009] Determine whether there is a subquery statement in the database operation statement and whether there is a deduplication operation in the subquery statement;

[0010] In the case where a database operation statement contains a subquery statement and a deduplication operation exists in the subquery statement, it is determined whether the database operation statement meets a preset condition, and the preset condition is used to determine whether the database operation statement is insensitive to duplicate values;

[0011] If the database operation statement meets the preset conditions, the deduplication operation in the subquery statement is deleted.

[0012] Optionally, the step of determining whether there is a deduplication operation in the subquery statement includes:

[0013] Determine whether there are keywords with deduplication properties in the subquery statement;

[0014] If so, it is determined that there is a deduplication operation in the subquery statement.

[0015] Optionally, the keywords with deduplication properties include: deduplication keywords and grouping keywords

[0016] The steps to remove duplicate operations in a subquery statement include:

[0017] Get the execution plan of the database operation statement;

[0018] If there is a deduplication keyword in the subquery statement, delete the deduplication keyword and delete the deduplication node corresponding to the subquery statement in the execution plan;

[0019] When a subquery statement contains a grouping keyword, determine whether there is an aggregate function in the target column of the subquery statement;

[0020] If there is no aggregation function in the target column of the subquery statement, delete the grouping keyword and delete the deduplication node corresponding to the subquery statement in the execution plan.

[0021] Optionally, the step of determining whether the database operation statement meets a preset condition includes:

[0022] Determine whether the parent query statement in the database operation statement is connected to the sub-query statement through the condition judgment keyword;

[0023] If so, it is determined that the database operation statement meets the preset conditions.

[0024] Optionally, the step of determining whether the database operation statement meets a preset condition includes:

[0025] Determine whether the database operation statement is connected to the subquery statement through the deduplication set operation;

[0026] If so, it is determined that the database operation statement meets the preset conditions.

[0027] Optionally, the step of determining whether the database operation statement meets a preset condition includes:

[0028] Determine whether there is a deduplication operation in the parent query statement in the database operation statement;

[0029] If so, it is determined that the database operation statement meets the preset conditions.

[0030] Optionally, the step of determining whether the database operation statement meets a preset condition includes:

[0031] Determine whether the parent query statement in the database operation statement is connected to the subquery statement through a semi-join or anti-join;

[0032] If so, it is determined that the database operation statement meets the preset conditions.

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

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

[0035] 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 optimization methods when executing the machine executable program.

[0036] The optimization method of database operation statements of the present invention obtains database operation statements; determines whether there are sub-query statements in the database operation statements and whether there are deduplication operations in the sub-query statements; in the case where there are sub-query statements in the database operation statements and there are deduplication operations in the sub-query statements, determines whether the database operation statements meet preset conditions, and the preset conditions are used to determine that the database operation statements are insensitive to duplicate values; if the database operation statements meet the preset conditions, the deduplication operations in the sub-query statements are deleted. Through this method, the deduplication operations in the sub-query statements of the database operation statements that meet the conditions can be eliminated, thereby avoiding deduplication of data in the case where duplicate values ​​are insensitive, causing redundant operations, and improving query efficiency.

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

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

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

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

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

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

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

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

[0045] It should be noted that the logic and / or steps represented in the flowchart or described in other ways herein can be considered as a sequenced list of executable instructions for implementing logical functions, which 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.

[0046] 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 database operation statements at least includes the following steps S101 to S104.

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

[0048] Step S102, determine whether there is a subquery statement in the database operation statement and whether there is a deduplication operation in the subquery statement. Wherein a subquery generally refers to another SQL query statement nested inside a SQL query statement. The subquery is usually used as part of the main query (outer query) to provide data, condition judgment and other basis for it. For example, it can appear in the SELECT clause, FROM clause, WHERE clause, HAVING clause and other positions.

[0049] In some optional embodiments, the step of determining whether there is a deduplication operation in the subquery statement may generally include: determining whether there is a keyword with a deduplication property in the subquery statement; if so, determining that there is a deduplication operation in the subquery statement. Examples of keywords with a deduplication property generally include: DISTINCT, GROUP BY and other keywords. DISTINCT is a keyword in SQL specifically used to remove duplicate rows in a result set. When DISTINCT is used in a SELECT statement, the database system scans the result set returned by the query, treats identical record rows as duplicate data, and retains only one copy of them, thereby achieving a deduplication effect. The main function of GROUP BY is to group the query results according to one or more specified columns. During the grouping process, for the columns used as the basis for grouping (i.e., the columns specified after GROUP BY), records with the same values ​​are grouped together. From the perspective of deduplication, in the final result set, the values ​​of these columns used as the basis for grouping will not appear repeatedly, and each different value will only appear once as a representative of a group, which produces a certain deduplication effect.

[0050] Step S103, if the judgment in step S102 is yes, it is judged whether the database operation statement meets the preset condition. The preset condition is used to determine whether the database operation statement is insensitive to repeated values.

[0051] Step S104: if the result of step S103 is yes, delete the de-duplication operation in the sub-query statement. The keywords with de-duplication property include: de-duplication keywords and grouping keywords.

[0052] In some other optional embodiments, the step of deleting the deduplication operation in the subquery statement may generally include: obtaining the execution plan of the database operation statement; in the case where the subquery statement contains deduplication keywords, deleting the deduplication keywords and deleting the deduplication node corresponding to the subquery statement in the execution plan; in the case where the subquery statement contains grouping keywords, determining whether there is an aggregation function in the target column of the subquery statement; in the case where there is no aggregation function in the target column of the subquery statement, deleting the grouping keywords and deleting the deduplication node corresponding to the subquery statement in the execution plan. The execution plan is an internal execution blueprint generated by the database management system (DBMS) for a given SQL statement, which describes how the database engine will process the statement, including in what order to access tables, which indexes to use, how to perform join operations, and execute various filtering, aggregation, sorting and other nodes, including deduplication nodes.

[0053] When a subquery statement contains a deduplication keyword, you can directly delete the keyword from the subquery's SELECT statement. At the same time, you need to find the node corresponding to the subquery deduplication operation in the execution plan and delete it. When a subquery statement contains a grouping keyword, you need to first determine whether there is an aggregation function in the target column of the subquery statement. If there is no aggregation function in the target column of the subquery statement, the grouping operation at this time may be redundant or inconsistent with the expected data processing logic (you may just want the original data instead of the uncertain data presentation after grouping, etc.), so you can delete the grouping keyword. At the same time, you need to find the node corresponding to the subquery deduplication operation in the execution plan and delete it.

[0054] Through this method, the deduplication operation in the subquery statement of the database operation statement that meets the conditions can be eliminated, thereby avoiding deduplication of data when the duplicate values ​​are not sensitive, causing redundant operations and improving query efficiency.

[0055] 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 S209.

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

[0057] Step S202, determine whether there is a subquery statement in the database operation statement and whether there is a deduplication operation in the subquery statement. Wherein a subquery generally refers to another SQL query statement nested inside a SQL query statement. The subquery is usually used as part of the main query (outer query) to provide data, condition judgment and other basis for it. For example, it can appear in the SELECT clause, FROM clause, WHERE clause, HAVING clause and other positions.

[0058] In some optional embodiments, the step of determining whether there is a deduplication operation in the subquery statement may generally include: determining whether there is a keyword with a deduplication property in the subquery statement; if so, determining that there is a deduplication operation in the subquery statement. Examples of keywords with a deduplication property generally include: DISTINCT, GROUP BY and other keywords. DISTINCT is a keyword in SQL specifically used to remove duplicate rows in a result set. When DISTINCT is used in a SELECT statement, the database system scans the result set returned by the query, treats identical record rows as duplicate data, and retains only one copy of them, thereby achieving a deduplication effect. The main function of GROUP BY is to group the query results according to one or more specified columns. During the grouping process, for the columns used as the basis for grouping (i.e., the columns specified after GROUP BY), records with the same values ​​are grouped together. From the perspective of deduplication, in the final result set, the values ​​of these columns used as the basis for grouping will not appear repeatedly, and each different value will only appear once as a representative of a group, which produces a certain deduplication effect.

[0059] If the answer in step S202 is yes, then steps S203 to S206 are executed according to the database operation statement. If the answer in any of steps S203 to S206 is yes, then step S207 is executed.

[0060] Step S203, determine whether the parent query statement is connected to the sub-query statement through a conditional judgment keyword. The conditional judgment keyword refers to a keyword that only returns true or false for the sub-query result. Such a conditional judgment keyword is not sensitive to whether the sub-query result is deduplicated. At this time, a deduplication operation is used in the keyword sub-link to deduplicate the result set, which is somewhat redundant.

[0061] Optionally, the conditional judgment keywords may generally include keywords such as in, any, exists, semo, all, >, <, =, ! =, etc. Some optional embodiments of different conditional judgment keywords are as follows:

[0062] Embodiment 1:

[0063] IN is a keyword used for filtering, which is used to determine whether a value is in a set (the set of values ​​returned by the subquery). An optional example of using IN is shown in Formula 1:

[0064] SELECT column1 FROM table1 WHERE column1 IN(SELECT DISTINCT column2FROM table2) Formula 1

[0065] When the database faces the SQL statement shown in Formula 1, the outer query will check whether each value of column1 in table1 exists in the value set of column2 returned by the subquery. As long as there is a value in the set returned by the subquery that is equal to the current check value of column1, the condition is true. It is usually redundant to perform deduplication in the IN sublink. Because the judgment logic of IN will not change due to duplicate values ​​in the set, deduplication will not affect the final screening results of the outer query records. Therefore, this type of statement can execute step S207, and the optimized statement is shown in Formula 2:

[0066] SELECT column1 FROM table1 WHERE column1 IN(SELECT column2FROMtable2) Formula 2

[0067] For the SQL statement shown in Formula 2, the subquery statement does not need to perform duplicate removal operations without affecting the execution results, thus greatly improving the execution efficiency.

[0068] Embodiment 2:

[0069] ANY is used with comparison operators (such as >, <, >=, <=, !=), which means that any value (returned by the subquery) that meets the comparison condition will do. An optional example of using ANY is shown in Formula 3:

[0070] SELECT column1 FROM table1 WHERE column1>ANY(SELECT DISTINCT column2FROM table2) Formula 3

[0071] For the SQL statement shown in Formula 3, it is generally not necessary to remove duplicates in the ANY sub-link. Because its judgment is based on whether there is any value that meets the comparison condition, the sub-query result set after removal of duplicates will not change the nature of this conditional judgment, and will not have a substantial impact on the screening results of the outer query. For example, the sub-query returns [1,2,2,3]. For the record where the value of column1 in the outer query is 2, under the condition of column1>ANY, because there is a smaller value of 1, the condition is true, and the number of repetitions of 2 does not affect this judgment. Therefore, this type of statement can execute step S207, and the optimized statement is shown in Formula 4:

[0072] SELECT column1 FROM table1 WHERE column1>ANY(SELECT column2FROMtable2) Formula 4

[0073] For the SQL statement shown in Formula 4, the subquery statement does not need to perform duplicate removal operations without affecting the execution results, thus greatly improving the execution efficiency.

[0074] Embodiment three:

[0075] EXISTS is used to check whether the subquery returns at least one record. An optional example of using EXISTS is shown in Formula 5:

[0076] SELECT column1 FROM table1 WHERE EXISTS(SELECT DISTINCT column2 FROM table2 WHERE table1.key = table2.key) Formula 5

[0077] As shown in the SQL statement of Formula 5, the use of DISTINCT deduplication operation in this EXISTS subquery is actually redundant in many cases. Because the EXISTS subquery mainly focuses on whether there are records that meet the association conditions, as long as there is at least one record that meets the condition that table1.key = table2.key, regardless of whether the column2 field has duplicate values ​​in the subquery result set, EXISTS will return true, which will affect the screening results of the outer query. Therefore, this type of statement can execute step S207, and the optimized statement is shown in Formula 6:

[0078] SELECT column1 FROM table1 WHERE EXISTS(SELECT column2 FROM table2WHERE table1.key = table2.key) Formula 6

[0079] For the SQL statement shown in Formula 6, the subquery statement does not need to perform duplicate removal operations without affecting the execution results, thus greatly improving the execution efficiency.

[0080] Embodiment 4:

[0081] SOME and ANY have basically the same functions and are used together with comparison operators to indicate that only some (at least one) value (returned by the subquery) that satisfies the comparison condition will be sufficient. An optional example of using SOME is shown in Formula 7:

[0082] SELECT column1 FROM table1 WHERE column1>=SOME(SELECT DISTINCTcolumn2 FROM table2) Formula 7

[0083] As shown in the sql statement of formula 7, SOME focuses on whether there is at least one value that meets the comparison condition, and the repeated values ​​in the subquery result do not affect this judgment. For example, the subquery returns [1,1,2,3]. For the record whose column1 value is 1 in the outer query, under the condition of column1>=SOME, because there is an equal value 1, the condition is true, and the repeated 1 does not affect the result. Therefore, this type of statement can execute step S207, and the optimized statement is shown in formula 8:

[0084] SELECT column1 FROM table1 WHERE column1>=SOME(SELECT DISTINCTcolumn2 FROM table2) Formula 8

[0085] For the SQL statement shown in Formula 8, the subquery statement does not need to perform duplicate removal operations without affecting the execution results, thus greatly improving the execution efficiency.

[0086] Embodiment five:

[0087] ALL is used with comparison operators to indicate that the comparison condition must be met with all values ​​returned by the subquery. An optional example of using ALL is shown in Formula 9:

[0088] SELECT column1 FROM table1 WHERE column1>ALL(SELECT DISTINCT column2FROM table2) Formula 9

[0089] As shown in the SQL statement of formula 9, ALL focuses on the comparison results with all subquery return values, rather than whether the value is repeated. For example, the subquery returns [1,1,2,3]. For the record with the value of column1 in the outer query being 4, under the condition of column1>ALL, because 4 is greater than all values ​​in the set, the condition is true, and the repeated 1 does not affect this judgment. Therefore, this type of statement can execute step S207, and the optimized statement is shown in formula 10:

[0090] SELECT column1 FROM table1 WHERE column1>ALL(SELECT column2FROMtable2) Formula 10

[0091] For the SQL statement shown in Formula 10, the subquery statement does not need to perform duplicate removal operations without affecting the execution results, thus greatly improving the execution efficiency.

[0092] Step S204, determine whether to connect the sub-query statements through a deduplication set operation. The set operation used to connect the sub-query statements and with deduplication set operations generally includes set operations such as UNION and EXCEPT. Some optional embodiments of different deduplication set operations are as follows:

[0093] Embodiment six:

[0094] UNION is used to merge the result sets of multiple SELECT statements. It has the feature of automatic deduplication. That is, when the results from different queries are merged together, the database system will check whether there are duplicate rows in the entire merged result set. If there are duplicate rows, only one of them will be retained, and the final result is the merged result after deduplication. An optional example of using UNION is shown in Formula 11:

[0095] SELECT DISTINCT column1,column2 FROM table1 UNION SELECT DISTINCTcolumn3,column4FROM table2 Formula 11

[0096] As shown in the sql statement of formula 11, the subquery results after DISTINCT deduplication from table1 and table2 are combined into a deduplication result set through the UNION operation, which is convenient for obtaining non-duplicate data of specific field combinations in the two tables. Since UNION has the feature of automatic deduplication, there is no need for subquery statements to deduplication, so this type of statement can execute step S207, and the optimized statement is shown in formula 12:

[0097] SELECT column1,column2 FROM table1 UNION SELECT column3,column4 FROMtable2 Formula 12

[0098] For the SQL statement shown in Formula 12, the subquery statement does not need to perform duplicate removal operations without affecting the execution results, thus greatly improving the execution efficiency.

[0099] Embodiment seven:

[0100] The EXCEPT operation is used to return rows that exist in the first subquery result set but not in the second subquery result set, and to remove duplicates from the results. An optional example of using EXCEPT is shown in Formula 13:

[0101] SELECT DISTINCT product_id,product_name,category FROM products_1

[0102] EXCEPT SELECT DISTINCT product_id,product_name,category FROMproducts_2 Formula 13

[0103] For the SQL statement shown in Formula 13, the database will first execute two subqueries, then remove the records that also appear in the second subquery result from the result of the first subquery, and deduplicate the remaining records, and finally return the product information that is only in the products_1 table. Since EXCEPT has the feature of automatic deduplication, there is no need to deduplicate the subquery statement, so this type of statement can execute step S207, and the optimized statement is shown in Formula 14:

[0104] SELECT product_id,product_name,category FROM products_1

[0105] EXCEPT

[0106] SELECT product_id,product_name,category FROM products_2 Formula 14

[0107] For the SQL statement shown in Formula 14, the subquery statement no longer needs to perform deduplication operations without affecting the execution result, thus greatly improving the execution efficiency.

[0108] Step S205, determine whether there is a deduplication operation in the parent query statement. An optional example of whether there is a deduplication operation in the parent query statement is shown in Formula 15:

[0109] SELECT DISTINCT*FROM t1,(SELECT DISTINCT t2.id FROM t2)as vEquation 15

[0110] As shown in the SQL statement of Formula 15, the parent query statement uses the DISTINCT keyword and involves a table join operation. The subquery also uses the DISTINCT keyword to obtain unique values ​​of a specific column, and uses the subquery result set as a temporary data source to participate in the entire query by giving it an alias (as v). Since the parent query statement already needs to deduplicate the final result, the deduplication operation in the subquery is a redundant operation, so this type of statement can execute step S207, and the optimized statement is shown in Formula 16:

[0111] SELECT DISTINCT*FROM t1,(SELECT t2.id FROM t2)as vEquation 16

[0112] For the SQL statement shown in Formula 14, the subquery statement no longer needs to perform deduplication operations without affecting the execution result, thus greatly improving the execution efficiency.

[0113] Step S206, determining whether the parent query statement is connected to the child query statement through a semi-join or an anti-join, wherein the semi-join refers to SEMI JOIN and the anti-join refers to ANTI JOIN.

[0114] The main function of SEMI JOIN is to filter out rows from one table (usually called the primary table or left table, corresponding to the table in the parent query) that have matching records in another table (the table represented by the subquery, which can be regarded as the right table). It is often used in scenarios where it is necessary to confirm whether the data in a certain table has corresponding associated data in another data source. The function of ANTI JOIN is the opposite of SEMI JOIN. It is used to filter out rows from the primary table (the table in the parent query) that do not have matching records in the table represented by the subquery.

[0115] When the parent query statement is connected to the subquery statement through a semi-join or anti-join, if there is a deduplication operation in the subquery, it is actually redundant. Because the semi-join or anti-join mainly focuses on whether there are records that meet the association condition. Therefore, such statements can execute step S207.

[0116] Step S207, when any of steps S203 to S206 is judged to be yes, delete the deduplication operation in the subquery statement.

[0117] In some other optional embodiments, the step of deleting the deduplication operation in the subquery statement may generally include: obtaining the execution plan of the database operation statement; in the case where the subquery statement contains deduplication keywords, deleting the deduplication keywords and deleting the deduplication node corresponding to the subquery statement in the execution plan; in the case where the subquery statement contains grouping keywords, determining whether there is an aggregation function in the target column of the subquery statement; in the case where there is no aggregation function in the target column of the subquery statement, deleting the grouping keywords and deleting the deduplication node corresponding to the subquery statement in the execution plan. The execution plan is an internal execution blueprint generated by the database management system (DBMS) for a given SQL statement, which describes how the database engine will process the statement, including in what order to access tables, which indexes to use, how to perform join operations, and execute various filtering, aggregation, sorting and other nodes, including deduplication nodes.

[0118] When a subquery statement contains a deduplication keyword, you can directly delete the keyword from the subquery's SELECT statement. At the same time, you need to find the node corresponding to the subquery deduplication operation in the execution plan and delete it. When a subquery statement contains a grouping keyword, you need to first determine whether there is an aggregation function in the target column of the subquery statement. If there is no aggregation function in the target column of the subquery statement, the grouping operation at this time may be redundant or inconsistent with the expected data processing logic (you may just want the original data instead of the uncertain data presentation after grouping, etc.), so you can delete the grouping keyword. At the same time, you need to find the node corresponding to the subquery deduplication operation in the execution plan and delete it.

[0119] Through this method, the deduplication operation in the subquery statement of the database operation statement that meets the conditions can be eliminated, thereby avoiding deduplication of data when the duplicate values ​​are not sensitive, causing redundant operations and improving query efficiency.

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

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

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

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

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

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

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

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

[0128] 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; Determine whether the database operation statement contains a subquery statement and whether a deduplication operation exists in the subquery statement; In the case where the database operation statement contains the subquery statement and the deduplication operation exists in the subquery statement, judging whether the database operation statement meets a preset condition, wherein the preset condition is used to determine whether the database operation statement is insensitive to duplicate values; If the database operation statement meets the preset condition, the deduplication operation in the subquery statement is deleted.

2. The method for optimizing database operation statements according to claim 1, wherein: The step of determining whether there is a deduplication operation in the subquery statement includes: Determine whether there is a keyword with a deduplication property in the subquery statement; If so, it is determined that the deduplication operation exists in the subquery statement.

3. The method for optimizing database operation statements according to claim 2, wherein: The keywords with deduplication properties include: deduplication keywords and grouping keywords The step of deleting the deduplication operation in the subquery statement includes: Obtaining an execution plan for the database operation statement; In the case where the subquery statement contains the deduplication keyword, deleting the deduplication keyword and deleting the deduplication node corresponding to the subquery statement in the execution plan; In the case where the subquery statement contains the grouping keyword, determining whether there is an aggregation function in the target column of the subquery statement; In the case that the aggregation function does not exist in the target column of the subquery statement, the grouping keyword is deleted and the deduplication node corresponding to the subquery statement in the execution plan is deleted.

4. The method for optimizing database operation statements according to claim 1, wherein: The step of determining whether the database operation statement meets the preset conditions comprises: Determine whether the parent query statement in the database operation statement is connected to the child query statement through a condition judgment keyword; If so, it is determined that the database operation statement meets the preset condition.

5. The method for optimizing database operation statements according to claim 1, wherein: The step of determining whether the database operation statement meets the preset conditions comprises: Determine whether the database operation statement is connected to the subquery statement through a deduplication set operation; If so, it is determined that the database operation statement meets the preset condition.

6. The method for optimizing database operation statements according to claim 1, wherein: The step of determining whether the database operation statement meets the preset conditions comprises: Determine whether there is a deduplication operation in the parent query statement in the database operation statement; If so, it is determined that the database operation statement meets the preset condition.

7. The method for optimizing database operation statements according to claim 1, wherein: The step of determining whether the database operation statement meets the preset conditions comprises: Determine whether the parent query statement in the database operation statement is connected to the child query statement through a semi-join or an anti-join; If so, it is determined that the database operation statement meets the preset condition.

8. 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 7.

9. A computer program product, comprising a computer program, which, when executed by a processor, implements the steps of the method for optimizing database operation statements 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 optimizing database operation statements according to any one of claims 1 to 7 when executing the machine executable program.

Images