Database operation statement optimization method, medium, product and equipment

Abstract

The invention provides a database operation statement optimization method, a medium, a product and equipment. The method comprises the following steps: acquiring a database operation statement; identifying a target parent query and a target child query which have an internal connection relationship in the database operation statement, and determining a connection key corresponding to the internal connection; judging whether the target parent query and the target child query meet a preset predicate push-up condition or not; and under the condition that the target parent query and the target child query meet a preset predicate push-up condition, pushing up a screening condition corresponding to the connection key in the target child query to the target parent query. Through the method, the data in the data table in the parent query connection operation process can be filtered in advance, so that the scanning time is shortened, and the query performance of the database is improved.

Description

Technical Field

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

[0002] In the current database technology field, databases, as the core carrier of data storage and management, directly impact the operational efficiency of various application systems through their query performance. With the explosive growth of business data and the increasing complexity of query scenarios, database operation statements involving multi-level nested queries and multi-table joins have become the mainstream application. Therefore, improving data processing efficiency through optimizing query logic has become one of the core research directions in the database field.

[0003] Among these techniques, predicate push-up and push-down are key to improving performance. By rationally allocating filtering conditions, data can be pre-filtered, reducing the amount of data in subsequent complex operations. In complex statements containing subqueries and parent queries, when the tables in the subquery and parent query have inner join relationships (including equi-joins and non-equi-joins), the filtering conditions for the join fields in the subquery are of great significance for overall query optimization.

[0004] However, existing technologies lack the ability to deeply analyze the relationships and filtering conditions in multi-level queries, failing to effectively pass filtering conditions for join keys in subqueries to the parent query and related data tables. This results in the parent query needing to perform a full scan of the related tables or load large amounts of invalid data when performing join operations, significantly increasing storage I / O and computational overhead, leading to low query efficiency. This deficiency is particularly pronounced in scenarios with large amounts of data in the related tables and complex join relationships, making it difficult to meet the demands for efficient data processing. Therefore, there is an urgent need for an optimization scheme that can overcome the limitations of existing technologies and achieve efficient transmission of subquery filtering conditions in multi-level queries and multi-table join scenarios, thereby improving the execution performance of complex database operation statements and meeting the ever-increasing data processing needs. Summary of the Invention

[0005] In view of the above problems, the present invention proposes an optimization method, medium, product and device for database operation statements that overcomes or at least partially solves the above problems.

[0006] One objective of this invention is to reduce the amount of data involved in database operation statement join operations.

[0007] A further objective of this invention is to improve the execution efficiency of database operation statements.

[0008] Specifically, this invention provides a method for optimizing database operation statements, comprising: Retrieve database operation statements; Identify the target parent query and target subquery that have an inner join relationship in the database operation statement, and determine the join key corresponding to the inner join; Determine whether the target parent query and the target subquery meet the preset predicate push-up conditions; If the target parent query and the target subquery meet the preset predicate push-up conditions, the filter conditions related to the join key in the target subquery will be pushed up to the target parent query.

[0009] Optionally, the steps for determining whether the target parent query and the target subquery meet the preset predicate push-up conditions include: Retrieve the conditional clauses from the target subquery; Determine if the conditional clause contains a filter condition targeting the join key; If the conditional clause contains a filtering condition, it is determined that the target parent query and the target subquery meet the preset predicate push-up condition.

[0010] Optionally, the step of pushing the filter conditions corresponding to the join key in the target subquery up to the target parent query includes: The filtering conditions are derived from the equivalence class propagation mechanism by inferring the filtering conditions and the join conditions in the target parent query. Add the filtering conditions to the target parent query.

[0011] Optionally, the step of adding the filter conditions to the target parent query may include: Verify that the filter conditions are compatible with the database operation statements; If so, then proceed with the step of adding the filter conditions to the target parent query.

[0012] Optionally, the steps for identifying the target parent query and target subquery with an inner join relationship in the database operation statement include: Identify parent and child queries that have a join relationship; Determine whether the parent query contains an equi-join condition or a non-equi-join condition that includes the result set of the subquery; If the parent query contains an equi-join condition or a non-equi-join condition that includes the result set of the subquery, the parent query and the subquery are determined to be the target parent query and the target subquery with an inner join relationship.

[0013] Optionally, after the step of pushing the filter conditions corresponding to the join key in the target subquery up to the target parent query, the following may also be included: The identified inner join type, join key, filtering conditions, and push-up results are cached and recorded.

[0014] Optionally, after obtaining the database operation statement, the following steps may also be included: Determine if the same database operation statement exists in the cache; If it exists, the push-up result corresponding to the database operation statement is applied directly.

[0015] According to another aspect of the present invention, a computer-readable storage medium is also provided, on which a computer program is stored, wherein when the computer program is executed by a processor, the steps of the optimization method for database operation statements described above are implemented.

[0016] According to another aspect of the present invention, a computer program product is also provided, comprising a computer program that, when executed by a processor, implements the steps of the optimization method for database operation statements described above.

[0017] According to another aspect of the present invention, a computer device is also provided, including a memory, a processor, and a machine-executable program stored in the memory and running on the processor, wherein the processor executes the machine-executable program to implement the steps of the optimization method for database operation statements of any of the above.

[0018] The database operation statement optimization method of the present invention first obtains the database operation statement; then identifies the target parent query and target subquery with an inner join relationship in the database operation statement, and determines the join key corresponding to the inner join; determines whether the target parent query and target subquery meet the preset predicate push-up conditions; if the target parent query and target subquery meet the preset predicate push-up conditions, pushes the filtering conditions related to the join key in the target subquery to the target parent query. This method can pre-filter data in the data table during the parent query join operation process, thereby reducing scan time and improving database query performance.

[0019] The above and other objects, advantages and features of the present invention will become more apparent to those skilled in the art from the following detailed description of specific embodiments of the invention in conjunction with the accompanying drawings. Attached Figure Description

[0020] The following sections will describe some specific embodiments of the invention in detail by way of example and not limitation, with reference to the accompanying drawings. The same reference numerals in the drawings denote the same or similar parts or portions. Those skilled in the art should understand that these drawings are not necessarily drawn to scale. In the drawings: Figure 1 This is a flowchart illustrating a method for optimizing database operation statements according to an embodiment of the present invention; Figure 2 This is a flowchart illustrating a method for optimizing database operation statements according to another embodiment of the present invention; Figure 3 This is a schematic diagram of a computer program product according to an embodiment of the present invention; Figure 4 This is a schematic diagram of a computer-readable storage medium according to an embodiment of the present invention; and Figure 5 This is a schematic diagram of a computer device according to an embodiment of the present invention. Detailed Implementation

[0021] Those skilled in the art should understand that the embodiments described below are merely a part of the embodiments of the present invention, and not all of the embodiments of the present invention. These partial embodiments are intended to explain the technical principles of the present invention and are not intended to limit the scope of protection of the present invention. Based on the embodiments provided by the present invention, all other embodiments obtained by those skilled in the art without creative effort should still fall within the scope of protection of the present invention.

[0022] It should be noted that the logic and / or steps represented in the flowchart or otherwise described 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, or in conjunction with, an instruction execution system, apparatus or device (such as a computer-based system, a processor-included system or other system that can fetch and execute instructions from, an instruction execution system, apparatus or device).

[0023] In the current database technology field, SQL (Structured Query Language) is an alternative example of database operation statements. SQL statements have multiple functions such as data manipulation and data definition. This language is interactive and can provide users with great convenience. In specific query processing, the join operation of SQL statements is the core means to realize multi-table data association and complex logical queries, but some SQL statements have room for optimization.

[0024] An optional SQL example is shown in equation (1): select * from (select a ,d from s1 where a=1 group by a ,d ) ssh ,s2s where ssh.a =sa Formula (1) The SQL statement shown in equation (1) has a subquery of (select a ,d from s1 where a=1 group by a ,d ) ssh, and a parent query of select * from (...)ssh ,s2 s where ssh.a =sa. Its execution logic is as follows: 1. Execute the subquery to generate a temporary table ssh: Step 1: Scan table s1 and filter out all records where a=1; Step 2: Group the filtered records by a and d, merge records with duplicate a and d combinations, and finally generate a temporary table ssh containing only columns a and d (where the value of a in all records is 1 and the value of d is unique).

[0025] 2. The parent query performs an implicit inner join: Step 1: Read all data from table s2 (alias s); Step 2: Match records in the temporary table ssh and the table s2 (alias s) according to the join condition ssh.a=sa; Step 3: Keep only the successfully matched records, merge all columns of the two tables (columns a and d of ssh + all columns of s2 table), and return the final result.

[0026] Analysis reveals that the subquery in the SQL statement shown in equation (1) contains the filtering condition a=1, but an implicit inner join is performed in the parent query with the join condition ssh.a=sa. This causes the parent query to perform a full data scan of table s2 (alias s) before executing the inner join operation. Even though the join condition ssh.a=sa has already limited the value of the a field of the related data to be 1, all irrelevant records in the table still need to be loaded to participate in the join operation. This approach not only wastes storage I / O resources but also significantly increases the computational load of subsequent join operations. Especially in scenarios where the data volume of table s2 is large, it will significantly prolong the query response time, and there is a clear bottleneck in query performance optimization.

[0027] Therefore, this invention provides a method for optimizing database operation statements. Figure 1 This is a flowchart illustrating a method for optimizing database operation statements according to an embodiment of the present invention, as shown below. Figure 1 As shown, the optimization method for the database operation statement includes at least the following steps S101 to S104.

[0028] Step S101: Obtain database operation statements. In this step, the database operation statements can generally be obtained through user input or automatically generated by the database program during operation due to business requirements. Those skilled in the art can decide on the method of obtaining database operation statements based on the actual situation.

[0029] Step S102: Identify the target parent query and target subquery that have an inner join relationship in the database operation statement, and determine the join key corresponding to the inner join.

[0030] In some optional embodiments, the step of identifying the target parent query and target subquery with an inner join relationship in the database operation statement may generally include: identifying the parent query and subquery with a join relationship; determining whether there is an equi-join condition or a non-equi-join condition in the parent query that includes the result set of the subquery; and determining that the parent query and subquery are target parent queries and target subqueries with an inner join relationship if there is an equi-join condition or a non-equi-join condition in the parent query that includes the result set of the subquery.

[0031] Taking the SQL statement shown in equation (1) as an example, we first identify the parent query and subquery structure: the parent query is the outer global query (select * from (...) ssh, s2 s where ssh.a = sa), and the subquery is a query statement nested in the parent query (select a,d from s1 where a=1 group by a,d); Then check whether the join condition of the parent query contains the equi-join condition or non-equi-join condition of the result set of the subquery. In the SQL statement shown in Equation (1), there is an equi-join (ssh.a=sa) and the join object contains the result set of the subquery (alias ssh). Therefore, it is determined that the parent query is the target parent query and the subquery is the target subquery. The two constitute an inner join relationship.

[0032] Next, extract the join key: The join key generally refers to the field associated in the inner join condition. For example, in the SQL statement shown in equation (1), the 'a' field in the equi-join condition "ssh.a=sa" is the join key.

[0033] This step enables accurate identification of inner join relationships and join keys, providing a core basis for pushing up the filtering conditions and ensuring that subsequent optimization operations are only performed on query scenarios that meet the conditions.

[0034] Step S103: Determine whether the target parent query and the target subquery meet the preset predicate push-up conditions. The preset predicate push-up conditions are the core criterion in this invention for determining whether the filtering conditions of the subquery can be pushed up to the parent query. Essentially, it is to select scenarios where the push-up operation is both safe (does not change the query results) and effective.

[0035] Optionally, the steps for determining whether the target parent query and the target subquery meet the preset predicate push-up conditions generally include: obtaining the condition clause in the target subquery; determining whether there is a filtering condition for the join key in the condition clause; and determining that the target parent query and the target subquery meet the preset predicate push-up conditions if there is a filtering condition in the condition clause. The condition clause is a statement used to filter records in an SQL query based on specified conditions, and is generally a WHERE clause. In the SQL statement shown in equation (1), WHERE a=1 in the subquery statement is its condition clause. Combined with the join key 'a' determined in step S102, it can be determined that the condition clause in the target subquery contains a filtering condition for the join key, thus determining that it meets the preset predicate push-up conditions.

[0036] This step, by accurately determining the conditions for pushing upwards, avoids query errors caused by blindly pushing upwards, ensuring the safety and rationality of the optimization operation.

[0037] Step S104: If the determination in step S103 is yes, push the filter conditions corresponding to the join key in the target subquery up to the target parent query.

[0038] Specifically, the steps of pushing the filter conditions corresponding to the join key in the target subquery to the target parent query generally include: deriving the filter conditions based on the equivalence class propagation mechanism and the join conditions in the target parent query; and adding the filter conditions to the target parent query. The derivation process is generally based on the equivalence class propagation mechanism, combining the join conditions of the target parent query to logically derive the filter conditions corresponding to the join key in the target subquery, thus obtaining the filter conditions for the parent query's associated table. This can be further divided into equi-join scenarios and non-equi-join scenarios.

[0039] In equi-join scenarios, the subquery filter condition is typically `join key = target value` (e.g., `a = 1`), and the join condition is `target subquery.join key = related table.join key` (e.g., `ssh.a = sa`). Therefore, the filter condition `related table.join key = target value` (e.g., `sa = 1`) is derived. However, in non-equi-join scenarios, if the subquery filter condition is typically `join key = target value` (e.g., `a = 1`), and the join condition is `target subquery.join key > related table.join key` (e.g., `ssh.a > sa`), then the filter condition `related table.join key < target value` (e.g., `sa < 1`).

[0040] The derived filtering conditions are then added to the target parent query, forming a filtering logic together with the original join conditions, thereby enabling the data in the associated table to be filtered in advance.

[0041] The optimized result of the SQL statement shown in equation (1) is shown in equation (2): select ssh.* from (select a ,d from s1 where a =1 group by a ,d ) ssh,s2 s where ssh.a =sa and s. a=1 Formula (2) Its execution logic is as follows: 1. Execute the subquery to generate a temporary table ssh: Step 1: Scan table s1 and filter out all records where the value of field a is 1 (filter out irrelevant data where a ≠ 1). Step 2: Group the filtered records by the a and d fields, and merge records with duplicate a and d combinations (for example, two records with a=1 and d=2 will be merged into one). The final temporary table ssh is generated, containing only two columns, a and d. All records have an a value of 1 and a unique d value.

[0042] 2. Execute the parent query to complete the inner join and filtering: Step 1: Scan table s2 (alias s) and filter out records where a=1 using WHERE sa=1 (pre-filtering to reduce the amount of data in subsequent joins). Step 2: Match the records filtered by the temporary tables ssh and s2 according to the join condition ssh.a=sa (since the value of a is 1 in both, it is essentially matching all related records that meet the condition). Step 3: Only keep the records that match successfully, and only output all columns (columns a and d) of the temporary table ssh. The final result is a set of records in table s1 where a=1 and d is unique, and a set of records in table s2 where a=1.

[0043] By comparison, it can be seen that the SQL statement shown in Equation (2) performs filtering in advance during the connection process compared to the SQL statement shown in Equation (1), thereby greatly reducing the amount of data in the connection process and avoiding invalid scanning and calculation.

[0044] This method enables cross-table transmission of filtering conditions based on the equivalence class propagation mechanism, transforming the parent query's associated tables from a full scan to a scan after precise filtering, significantly reducing the amount of data scanned and the amount of join operations, and substantially improving query performance.

[0045] To further ensure the security of the push-up, a verification step can be added before adding the filtering conditions. In some alternative embodiments, the step of adding the filtering conditions to the target parent query can generally include: verifying whether the filtering conditions are compatible with the database operation statement; if so, then the step of adding the filtering conditions to the target parent query is executed. For example, checking whether there is a logical contradiction between the filtering conditions and the existing filtering conditions in the parent query (e.g., if the original condition is sa>2 and the derived condition is sa<1, then a contradiction is determined). Or, checking whether the filtering conditions are consistent with the grouping and aggregation logic of the subquery; if the verification passes, the addition operation is executed; otherwise, the push-up is terminated. This avoids query errors caused by conflicts between the filtering conditions and the original query logic, improves the stability and reliability of the solution, and ensures that the optimization operation can still be executed safely in complex query scenarios.

[0046] In addition, to further increase execution efficiency, the step of pushing up the filter conditions corresponding to the join key in the target subquery to the target parent query can generally include: caching the identified inner join type, join key, filter conditions and push-up results.

[0047] After retrieving the database operation statement, the process typically includes: checking if the same database operation statement exists in the cache; if so, directly applying the push-up result corresponding to the database operation statement. This operation can significantly reduce the time spent on repeated optimizations and shorten query response time.

[0048] Figure 2 This is a flowchart illustrating a method for optimizing database operation statements according to another embodiment of the present invention, as shown below. Figure 2 As shown, the optimization method for the database operation statement includes at least the following steps S201 to S208.

[0049] Step S201: Obtain the database operation statements.

[0050] Step S202: Identify parent and child queries that have a join relationship.

[0051] An optional SQL example is shown in equation (3): select * from (select a ,d from s1 where s. a=1 group by a ,d ) ssh ,s2 s where ssh.a>sa Formula (3) The SQL statement shown in equation (3) has a subquery of (select a,d from s1 where s.a=1 groupby a,d) ssh and a parent query of select * from (...) ssh,s2 s where ssh.a>sa. Its execution logic is: execute the subquery and generate a temporary table ssh. Step 1: Scan table s1 and filter out all original records where a=1; Step 2: Group the filtered records by a and d, merge the records with duplicate a and d combinations, and generate a temporary table ssh containing only columns a and d.

[0052] 2. The parent query performs an implicit non-equi-join: Step 1: Read all data from table s2 (alias s); Step 2: Match records in the temporary table ssh and table s2 based on the non-equi-join condition ssh.a>sa (because ssh.a=1, only records in table s2 with a<1 are actually matched). Step 3: Keep all successfully matched records, merge all columns of the two tables (columns a and d of ssh + all columns of s2), and return the final result.

[0053] Step S203: Determine whether the parent query contains an equi-join condition or a non-equi-join condition that includes the result set of the subquery. Based on the join relationship identified in step S202, further parse the WHERE clause or join condition expression of the parent query to determine the type of join condition: Equi-join condition: Use the "=" operator in the join expression, for example, "ssh.a=sa" (the value of field a in the temporary subquery table ssh is equal to the value of field a in the table s2). Non-equi-value join conditions: The join expression uses non-equal operators such as ">", "<", ">=", and "<=", for example, "ssh.a>sa"; Judgment result: If any of the above types of join conditions exist in the parent query and the join object contains the subquery result set, then proceed to step S204; if the join condition does not exist, or the join condition does not involve the subquery result set, then it is determined that the optimization scenario is not met, the current optimization operation is terminated, and the original statement execution logic is used.

[0054] Step S204: If the determination in step S203 is yes, determine that the parent query and the subquery are target parent query and target subquery with an inner join relationship. When step S203 determines that the parent query has an equi-join condition for the subquery result set, formally define the parent query as the target parent query and the subquery as the target subquery, and the two form an inner join relationship.

[0055] Step S205: Determine the join key corresponding to the inner join and obtain the condition clause in the target subquery.

[0056] The join key is the core field associated in the inner join condition. It needs to be extracted from the join condition identified in step S203. For example, the 'a' field in the join condition ssh.a>sa. This field needs to be clearly defined in the associated tables of both the target subquery and the target parent query to ensure that the transmission of the filtering conditions has a logical basis.

[0057] Then, the condition clause of the target subquery is obtained. The condition clause mainly refers to the WHERE clause in the target subquery (such as "where a=1" in the subquery in the example above). The core is to extract the filtering rules for the data in the subquery.

[0058] Step S206: Determine if there is a filter condition for the join key in the conditional clause. If step S206 determines no, the optimization operation ends. Compare the join key extracted in step S205 with the filter field in the subquery conditional clause. If there is a filter condition for the join key in the conditional clause (e.g., join key is 'a', filter condition is 'a=1'), then the predicate push-up condition is met, and proceed to step S207.

[0059] If the conditional clause does not contain any filtering conditions for the join key (such as the join key being "a" and the filtering conditions being "d=3" or "b>5", etc., which are fields unrelated to the join key), then it is determined that there is no value in pushing up, and the current optimization operation is terminated to avoid invalid derivation.

[0060] Step S207: If the determination in step S206 is yes, the filtering conditions are derived by deriving the filtering conditions and the join conditions in the target parent query based on the equivalence class propagation mechanism.

[0061] Step S208: Add the filtering conditions to the target parent query. The filtering conditions derived in step S207 are added to the WHERE clause of the target parent query in the form of a logical "AND," forming the filtering logic of the parent query together with the original join conditions. For example, if the original parent query condition is ssh.a>sa, and the derived filtering condition is sa<1, the optimized parent query condition is ssh.a>sa and sa<1. When the optimized statement is executed, the filtering conditions in the related tables of the parent query will be applied first, retaining only records that meet the conditions for subsequent join operations.

[0062] The optimized result of the SQL statement shown in equation (3) is shown in equation (4): select ssh.* from (select a ,d from s1 where a =1 group by a ,d ) ssh,s2 s where ssh.a>sa and s. a<1 Formula (4) 1. Execute the subquery to generate a temporary table ssh: Scan table s1 and filter out records where a=1; Grouping by a and d to remove duplicates, generating a temporary table ssh containing only a=1 (fixed value) and unique d values.

[0063] 2. The parent query performs an implicit inner join: Step 1: When scanning table s2 (alias s), prioritize executing WHERE sa<1 (the filtering condition pushed up in this invention), directly filtering out records where sa≥1, and only keeping a small amount of data that meets the condition (no need for a full scan). Step 2: For the filtered data in table s2, match it with the temporary table ssh according to the non-equi-join condition ssh.a>sa (because ssh.a=1, it actually matches records in table s2 where a<1, which is consistent with the logic of the pre-filtering condition). Step 3: Only retain the records that match successfully, and only return columns a and d of the temporary table ssh to obtain the final result.

[0064] The SQL statement shown in equation (3) requires a full scan of table s2 before matching the join conditions. The optimized SQL statement shown in equation (4) filters invalid data during the scanning phase by executing sa<1 in advance, without changing the query semantics and result set, thereby greatly reducing the amount of data in the join process and avoiding invalid scans and calculations.

[0065] The optimized process of this invention significantly improves the execution efficiency of complex database operation statements while ensuring logical rigor and semantic equivalence, and solves the core defect of insufficient predicate passing in the inner join scenario in the prior art.

[0066] The flowchart provided in this embodiment is not intended to indicate that the operations of the method will be performed in any particular order, or that all operations of the method are included in every case. Furthermore, the method may include additional operations. Within the scope of the technical concept provided by the method in this embodiment, additional variations can be made to the above method.

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

[0068] This embodiment also provides a computer program product 10, a computer-readable storage medium 20, and a computer device 30. Figure 3 This is a schematic diagram of a computer program product 10 according to an embodiment of the present invention. Figure 4 This is a schematic diagram of a computer-readable storage medium 20 according to an embodiment of the present invention. Figure 5 This 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, when executed by the processor 32, implements the steps of the database operation statement optimization method described above. A computer-readable storage medium 20 stores the computer program 11 thereon, which, when executed by the processor 32, implements the steps of the database operation statement optimization method described above. The computer device 30 may include a memory 31, a processor 32, and the computer program 11 stored on the memory 31 and running on the processor 32.

[0069] The computer program 11 used to perform the operations of this invention may be assembly instructions, Instruction Set Architecture (ISA) instructions, machine instructions, machine-dependent instructions, microcode, firmware instructions, state setting data, integrated circuit configuration data, or source code or object code written in any combination of one or more programming languages ​​and procedural programming languages. The computer program 11 may execute entirely on the user's computer, partially on the user's computer, as a standalone 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 Wide Area Network (WAN), or may be connected to an external computer (e.g., via the Internet using an Internet service provider). In some embodiments, to perform aspects of this invention, electronic circuits, including, for example, programmable logic circuits, Field-Programmable Gate Arrays (FPGAs), or Programmable Logic Arrays (PLAs), may execute computer-readable program instructions to personalize the electronic circuits by utilizing state information from computer-readable program instructions.

[0070] For the purposes of this embodiment, computer program product 10 is a related product containing computer program 11. For the purposes of this embodiment, computer-readable storage medium 20 is a tangible device capable of holding and storing computer program 11, and can be any device capable of containing, storing, communicating, propagating, or transmitting program 11 for use by or in conjunction with an instruction execution system, apparatus, or device. More specific examples (a non-exhaustive list) of computer-readable storage medium 20 include: portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), static random access memory (SRAM), portable optical disc read-only memory (CD-ROM), digital versatile disc (DVD), memory stick, floppy disk, mechanical encoding device, and any suitable combination thereof.

[0071] Computer device 30 can be, for example, a server, desktop computer, laptop computer, tablet computer, or smartphone. In some examples, computer device 30 can be a cloud computing node. Computer device 30 can be described in the general context of computer system executable instructions (such as program modules) executed by a computer system. Typically, program modules can include routines, programs, object programs, components, logic, data structures, etc., that perform specific tasks or implement specific abstract data types. Computer device 30 can be implemented in a distributed cloud computing environment where tasks are performed by remote processing devices linked through a communication network. In a distributed cloud computing environment, program modules can reside on local or remote computing system storage media, including storage devices.

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

[0073] Computer device 30 may also include a network adapter / interface and an input / output (I / O) interface. The I / O interface allows external devices that can be connected to the computer device to input and output data. The network adapter / interface provides communication between the computer device and a network, typically represented as a communication network.

[0074] Therefore, those skilled in the art should recognize that although numerous exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications conforming to the principles of the present invention can be directly determined or derived from the disclosure of the present invention without departing from the spirit and scope of the invention. Thus, the scope of the present invention should be understood and construed as covering all such other variations or modifications.

Claims

1. A method for optimizing database operation statements, comprising: Obtain the database operation statement; Identify the target parent query and target subquery that have an inner join relationship in the database operation statement, and determine the join key corresponding to the inner join; Determine whether the target parent query and the target subquery meet the preset predicate push-up conditions; If the target parent query and the target subquery meet the preset predicate push-up conditions, the filter conditions in the target subquery corresponding to the join key will be pushed up to the target parent query.

2. The method for optimizing database operation statements according to claim 1, wherein, The step of determining whether the target parent query and the target subquery meet the preset predicate push-up conditions includes: Retrieve the conditional clauses from the target subquery; Determine whether there are filtering conditions for the join key in the conditional clause; If the conditional clause contains the filtering condition, it is determined that the target parent query and the target subquery meet the preset predicate push-up condition.

3. The method for optimizing database operation statements according to claim 1, wherein, The step of pushing the filter conditions corresponding to the join key in the target subquery up to the target parent query includes: The filtering conditions are derived from the filtering conditions and the join conditions in the target parent query based on the equivalence class propagation mechanism. Add the filtering conditions to the target parent query.

4. The method for optimizing database operation statements according to claim 3, wherein, The step of adding the filtering condition to the target parent query also includes: Verify whether the filtering conditions are compatible with the database operation statements; If so, then perform the step of adding the filtering condition to the target parent query.

5. The method for optimizing database operation statements according to claim 1, wherein, The step of identifying the target parent query and target subquery that have an inner join relationship in the database operation statement includes: Identify parent and child queries that have a join relationship; Determine whether the parent query contains an equi-join condition or a non-equi-join condition that includes the result set of the subquery; If the parent query contains an equi-join condition or a non-equi-join condition that includes the result set of the subquery, then the parent query and the subquery are determined to be the target parent query and the target subquery with an inner join relationship.

6. The method for optimizing database operation statements according to claim 1, wherein, The step of pushing the filter conditions corresponding to the join key in the target subquery up to the target parent query further includes: The identified inner join type, the join key, the filtering conditions, and the push-up results are cached and recorded.

7. The method for optimizing database operation statements according to claim 6, wherein, Following the step of obtaining the database operation statement, the following is also included: Determine whether the same database operation statement exists in the cache; If it exists, the push-up result corresponding to the database operation statement is applied directly.

8. A computer-readable storage medium having a computer program stored thereon, the computer program being executed by a processor to implement 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 that, 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, when executing the machine-executable program, implements the steps of the method for optimizing database operation statements according to any one of claims 1 to 7.

Images