Database query statement optimization method, storage medium and device

Abstract

The application provides a database query statement optimization method, a storage medium and equipment. The method comprises the following steps: judging whether the database query statement meets the condition of implementing the statement optimization method; if yes, extracting a relevant scalar subquery statement in the database query statement to an outermost target column of the database query statement; and modifying a constraint condition in the relevant scalar subquery. After the relevant scalar subquery is extracted to the outermost target column through the method, the database query statement performs a ROWNUM paging operation on the data to be queried first, and then executes the relevant scalar subquery according to the data after paging, so that the number of times of circulating traversal of the relevant scalar subquery is reduced, and the execution efficiency of the database query statement is improved.

Description

TECHNICAL FIELD

[0001] The present application relates to database technology, and in particular, to a method for optimizing a database query statement, a storage medium and an apparatus. BACKGROUND

[0002] In the prior art, there is often a need to query a limited number of rows or page query in a database. One optional method is to use ROWNUM. ROWNUM is a number assigned by the system to the rows returned from the query in sequence, and the first row returned is assigned 1, the second row is assigned 2, and so on. It is a pseudo column added based on the result set first, and then the result set. ROWNUM is used to make a judgment in the constraint condition, and the detection is stopped when false is detected. For example, ROWNUM>3 is used. Since the sorting of ROWNUM always starts from 1, false is directly returned, and no further checking is performed. Therefore, when performing a paging operation, a nested method is required, in which ROWNUM is assigned an alias in the inner query, and ROWNUM is used as a query result in the outer query. A specific example is as follows:

[0003] select*from(select*,ROWNUM as row_id from t1 where ROWNUM<10)whererow_id>3;

[0004] This statement first takes out data with a ROWNUM less than 10 in the inner part, and then takes out data with a ROWNUM greater than 3 as a query condition. SUMMARY

[0005] An object of the present application is to improve the execution efficiency of a database query statement.

[0006] A further object of the present application is to reduce the amount of data processed by a database query statement.

[0007] A further object of the present application is to convert the execution order of a database query statement that meets the optimization condition.

[0008] In particular, the present application provides a method for optimizing a database query statement, which comprises:

[0009] determining whether the database query statement meets the condition for implementing the statement optimization method;

[0010] if so, extracting a relevant scalar subquery statement in the database query statement to the outermost target column of the database query statement;

[0011] modifying the constraint condition in the relevant scalar subquery.

[0012] Optionally, the step of judging whether the database query statement meets the condition for implementing the statement optimization method comprises:

[0013] judging whether the database query statement is in the form of ROWNUM paging operation;

[0014] if the database query statement is in the form of ROWNUM paging operation, judging whether there is a correlated scalar subquery in the inner target column of the database query statement.

[0015] Optionally, the step of judging whether there is a correlated scalar subquery in the inner target column of the database query statement further comprises:

[0016] if there is a correlated scalar subquery in the inner target column of the database query statement, judging whether the inner target column of the database query statement contains a sorting and / or grouping operation statement.

[0017] Optionally, the step of judging whether there is a correlated scalar subquery in the inner target column of the database query statement further comprises:

[0018] if there is no correlated scalar subquery in the inner target column of the database query statement, determining that the condition for implementing the statement optimization method is not met.

[0019] Optionally, the step of judging whether the inner target column of the database query statement contains a sorting and / or grouping operation statement further comprises:

[0020] if the inner target column of the database query statement contains a sorting and / or grouping operation statement, judging whether the action object of the sorting and / or grouping operation statement is related to the result of the correlated scalar subquery.

[0021] Optionally, the step of judging whether the inner target column of the database query statement contains a sorting and / or grouping operation statement further comprises:

[0022] if the inner target column of the database query statement does not contain a sorting and / or grouping operation statement, determining that the condition for implementing the statement optimization method is met.

[0023] Optionally, the step of judging whether the action object of the sorting and / or grouping operation statement is related to the result of the correlated scalar subquery further comprises:

[0024] if the action object of the sorting and / or grouping operation statement is not related to the result of the correlated scalar subquery, determining that the condition for implementing the statement optimization method is met.

[0025] Optionally, the step of judging whether the action object of the sorting and / or grouping operation statement is related to the result of the correlated scalar subquery further comprises:

[0026] If the action object of the sorting and / or grouping operation statement is related to the result of the dependent scalar subquery, it is determined that the condition for implementing the statement optimization method is not satisfied.

[0027] According to another aspect of the present application, there is also provided a machine readable storage medium having stored thereon a machine executable program which, when executed by a processor, implements any of the above database query statement optimization methods.

[0028] According to yet another aspect of the present application, there is also provided a computer device comprising a memory, a processor, and a machine executable program stored on the memory and running on the processor, and the processor implements any of the above database query statement optimization methods when executing the machine executable program.

[0029] The database query statement optimization method of the present application filters out database query statements that satisfy the optimization condition, and then changes the execution order of dependent scalar subqueries in the database query statement, thereby reducing the number of times of loop traversal when the query statement is executed, and improving the execution efficiency of the database query statement.

[0030] Further, the database query statement optimization method of the present application first determines whether the database query statement is in the form of ROWNUM paging operation; if so, it determines whether there is a dependent scalar subquery inside the database query statement; if there is a dependent scalar subquery inside the database query statement, it determines whether there is a sorting and / or grouping operation in the query statement, and if there is a sorting and / or grouping operation, it determines whether the action object of the sorting and / or grouping operation statement is related to the result of the dependent scalar subquery; if the called object is not related to the result of the dependent scalar subquery or there is no sorting and / or grouping operation, the dependent scalar subquery can be extracted to the outermost position, and the related constraint condition content in the dependent scalar subquery is modified. After the dependent scalar subquery is extracted to the outermost position by this method, the database query statement will first perform ROWNUM paging operation on the data to be queried, and then loop through the ROWNUM paging operation result set according to the result of the dependent scalar subquery, thereby reducing the number of times of loop traversal of the dependent scalar subquery and improving the execution efficiency of the database query statement.

[0031] The above and other objects, advantages and features of the present application will become more apparent from the following detailed description of specific embodiments thereof, when taken in conjunction with the accompanying drawings. BRIEF DESCRIPTION OF DRAWINGS

[0032] Some specific embodiments of the present application will be described in detail below with reference to the accompanying drawings. The same or similar components or parts are designated by the same or similar reference numerals in the drawings. It should be understood by those skilled in the art that the drawings are not necessarily drawn to scale. In the drawings:

[0033] Figure 1 is a flowchart of a method for optimizing a database query statement according to an embodiment of the present application;

[0034] Figure 2 is a flowchart of a method for optimizing a database query statement according to another embodiment of the present application;

[0035] Figure 3 is a schematic diagram of a machine-readable storage medium in a method for optimizing a database query statement according to an embodiment of the present application; and

[0036] Figure 4 is a schematic diagram of a computer device in a method for optimizing a database query statement according to an embodiment of the present application. DETAILED DESCRIPTION

[0037] Those skilled in the art should understand that the embodiments described below are only a part of the embodiments of the present application, and are not intended to limit the protection scope of the present application. Based on the embodiments provided by the present application, all other embodiments obtained by those of ordinary skill in the art without creative labor should fall within the protection scope of the present application.

[0038] In the prior art, there is often a need to query a limited number of rows or a page query in a database. One optional method is to use ROWNUM. ROWNUM is a number assigned by the system to the rows returned from the query in sequence, and the first returned row is assigned 1, the second row is 2, and so on. It is a pseudo column added based on the result set first. ROWNUM is used to make a judgment in the constraint condition, and the detection is false, which stops. For example, if ROWNUM>3 is used, because the sorting of ROWNUM always starts from 1, false is directly returned, and no further checking is performed. Therefore, when performing a paging operation, a nested method is required, in which ROWNUM is assigned an alias in the inner query, and ROWNUM is used as a query result in the outer query. A specific example is as follows:

[0039] select*from(select*,ROWNUM as row_id from t1 where ROWNUM<10)whererow_id>3;

[0040] This statement is first to take out the data with ROWNUM number less than 10 internally, and then take out the data with ROWNUM number greater than 3 as a query condition.

[0041] When using ROWNUM for paging processing, if the inner target column contains a relevant scalar subquery, the content of the relevant scalar subquery is related to the result of the internal query, but only through the call of the result for separate query, and in the paging processing

[0042] There is no other operation in the inner layer according to the relevant scalar subquery. Essentially, after the internal query result is paged, it is also possible to use the screened internal result to call the relevant subquery in the target column, that is, to adjust the relevant scalar subquery of the inner target column to the outermost layer. The adjusted statement reduces unnecessary calls to the scalar subquery in execution, and does not have to follow the paging processing loop multiple times.

[0043] Figure 1 It is a flowchart of the optimization method of the database query statement according to an embodiment of the present application. The flowchart can include:

[0044] Step S101, determine whether the database query statement meets the conditions for implementing the statement optimization method. This step includes: determining whether the database query statement is in the form of ROWNUM paging operation; if so, determining whether there is a relevant scalar subquery in the database query statement; if there is a relevant scalar subquery, determining whether there is a sorting and / or grouping operation in the database query statement, if there is a sorting and / or grouping operation, determining whether the object of the sorting and / or grouping operation statement is related to the result of the relevant scalar subquery; if the called object is not related to the result of the relevant scalar subquery or the database query statement does not have a sorting and / or grouping operation, the relevant scalar subquery can be extracted to the outermost position, and the relevant constraint condition content in the relevant scalar subquery is modified. After the relevant scalar subquery is extracted to the outermost target column by this method, the database query statement will first perform ROWNUM paging operation on the data to be queried, and then perform loop traversal on the ROWNUM paging operation result set according to the result of the relevant scalar subquery, reducing the number of loop traversals and times of the relevant scalar subquery, and improving the execution efficiency of the database query statement.

[0045] Among them, the so-called relevant scalar subquery refers to the existence of a scalar subquery in the database query statement, and the constraint condition of the scalar subquery is related to the result outside the scalar subquery. An optional example is:

[0046]

[0047] The select u from t1 where t1.u=t2.u in the statement is a scalar subquery, and the constraint condition where t1.u=t2.u in the scalar subquery uses the t2 table outside the scalar subquery, so the scalar subquery is determined to be a dependent scalar subquery. Those skilled in the art can determine whether the scalar subquery is a dependent scalar subquery according to actual conditions.

[0048] In step S102, the dependent scalar subquery in the database query statement is extracted to the outermost target column of the query statement.

[0049] In step S103, the constraint condition in the dependent scalar subquery is modified.

[0050] After the dependent scalar subquery is extracted to the outermost position by the method, the database query statement performs a ROWNUM paging operation on the data to be queried first, and then performs the dependent scalar subquery according to the data after paging, thereby reducing the number of times of traversing the dependent scalar subquery and improving the execution efficiency of the database query statement.

[0051] Figure 2 FIG. 1 is a flowchart of a method for optimizing a database query statement according to another embodiment of the present application. The flowchart can include the following steps:

[0052] In step S201, it is determined whether it is a ROWNUM paging operation.

[0053] In step S202, if the result of step S201 is yes, it is determined whether there is a dependent scalar subquery in the inner target column.

[0054] In step S203, if the result of step S202 is yes, it is determined whether there is a grouping and / or sorting operation in the inner layer.

[0055] In step S204, if the result of step S203 is yes, it is determined whether the action object of the grouping and / or sorting operation is related to the result of the dependent scalar subquery. When the action object of the grouping and / or sorting operation is related to the result of the dependent scalar subquery, extracting the dependent scalar subquery to the outermost target column will affect the result set of the database query statement. Through this step, the case that the action object of the grouping and / or sorting operation is related to the result of the dependent scalar subquery can be excluded, so as to avoid affecting the result set of the database query statement.

[0056] In step S205, if the result of step S203 is no or the result of step S204 is no, the dependent scalar subquery is extracted to the outermost target column.

[0057] In step S206, the constraint condition in the dependent scalar subquery is modified.

[0058] Step S207, step S201 is false, step S202 is false, step S204 is true, and step S206 is executed, in any of the above cases, end the flow.

[0059] In order to more clearly illustrate the flow, a specific example is provided to illustrate:

[0060]

[0061] Step S201 is to determine whether the database query statement is in the form of ROWNUM paging operation.

[0062] Step S202 is to determine whether the relevant scalar subquery exists in the inner target column, such as ①, ② shown in the position is considered in the inner target column;

[0063] A specific example of the constraint condition of the relevant scalar subquery in step S202 is as follows:

[0064] ① (select u from t1 where t1.u=tmp.u)sub_id

[0065] ② (select u from t1 where t1.u=t2.u)sub_id

[0066] The result set after the where keyword in ① and ② is used, which is the outer result set of the scalar subquery, then the scalar subquery is determined to be a relevant scalar subquery.

[0067] A specific example of grouping and / or sorting operation in step S203 and step S204 is the two keywords of orderby and group by.

[0068] If order by and group by are processed in ROWNUM paging, the original data order will be modified, so the position cannot be easily changed.

[0069] By this method, the relevant scalar subquery is extracted to the outermost target column, and then the database query statement is first subjected to ROWNUM paging operation on the data to be queried, and then the relevant scalar subquery is executed according to the data after paging, which reduces the number of times of loop traversal of the relevant scalar subquery and improves the execution efficiency of the database query statement.

[0070] In order to better illustrate the method, an example is provided to illustrate the flow:

[0071] For example, the following database query statement:

[0072]

[0073] In this example, the correlated scalar subquery is (select u from t1 where t1.u=t2.u),

[0074] Before applying the method, the execution order of the statement is:

[0075] 1. Execute select u, (select u from t1 where t1.u=t2.u) sub_id from t2; and take the result set as tmp;

[0076] 2. Execute select *, ROWNUM row_id from tmp where ROWNUM<=1000000, and take the result set generated by the ROWNUM operation as row_id;

[0077] 3. Execute Select * from (select *, ROWNUM row_id from tmp where ROWNUM<=1000000) where row_id>999990, and take the final data from the result set in step 3 according to row_id as the constraint condition.

[0078] At this time, every time a row is read from t2, the innermost correlated scalar subquery is also executed once, until 1000000,

[0079] After applying the method, the statement is modified to:

[0080]

[0081] At this time, the execution order of the statement becomes: 1. Execute select u from t2; and take the result set as tmp;

[0082] 2. Execute select *, ROWNUM row_id from tmp where ROWNUM<=1000000, and take the result set generated by the ROWNUM operation as row_id;

[0083] 3. Execute

[0084]

[0085] The database query statement first takes out data with serial numbers between 999990 and 1000000 according to the ROWNUM sorting, and executes the select u from t1 where t1.u=tmp.u once for each data.

[0086] Through the application of the method, the relevant scalar subquery is executed 10 times instead of 1000000 times, without affecting the result of the database query statement, thereby greatly improving the execution efficiency of the database query statement.

[0087] After the actual database applies the scheme, the execution times of the relevant scalar subquery before applying the scheme is 1000 times, and the execution times of the relevant scalar subquery after applying the scheme is 11 times, and the performance is greatly improved.

[0088] The embodiment also provides a machine readable storage medium and a computer device. Figure 3 FIG. 1 is a schematic diagram of a machine readable storage medium 301 according to an embodiment of the present application, Figure 4 FIG. 2 is a schematic diagram of a computer device 403 according to an embodiment of the present application.

[0089] The machine readable storage medium 301 has a machine executable program 302 stored thereon, and the machine executable program 302 is executed by a processor to implement the database query statement optimization method of any of the above embodiments.

[0090] The computer device 403 can include a memory 401, a processor 402, and a machine executable program 302 stored on the memory 401 and running on the processor 402, and the processor 402 executes the machine executable program 302 to implement the database query statement optimization method of any of the above embodiments.

[0091] It should be noted that the logic and / or steps represented in the flowchart or otherwise described herein, such as taking the relevant scalar subquery to the outermost target column, can be embodied in any machine readable storage medium for use by or in connection with an instruction execution system, apparatus, or device, such as a computer-based system, processor- based system, or other system that can fetch the instructions from the instruction execution system, apparatus, or device and execute the instructions, or in conjunction with the instruction execution system, apparatus, or device.

[0092] For the description of this embodiment, the machine-readable storage medium 301 can be any device that can contain, store, communicate, propagate or transport the program for use by or in connection with the instruction execution system, apparatus or device. More specific examples (non-exhaustive list) of the machine-readable storage medium 301 include the following: electrical connections having one or more wires (electronic devices), portable computer diskette (magnetic devices), random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fiber devices, and portable compact disc read-only memory (CDROM). In addition, the machine-readable storage medium 301 can even be paper or other suitable medium on which the program is printed, as the program can be electronically obtained, for example by optical scanning of the paper or other medium, followed by electronic conversion into the program, and then stored in the computer memory.

[0093] It should be understood that parts of the application can be implemented in hardware, software, firmware or a combination thereof. In the above-described embodiments, a plurality of steps or methods can be implemented in software or firmware stored in a memory and executed by a suitable instruction execution system.

[0094] The computer device 403 can be, for example, a server, a desktop computer, a notebook computer, a tablet computer or a smart phone. In some examples, the computer device 403 can be a cloud computing node. The computer device 403 can be described in the general context of computer system executable instructions, such as program modules, executed by the computer system. Generally, program modules can include routines, programs, objects, components, logic, data structures, etc. that perform particular tasks or implement particular abstract data types. The computer device 403 can be implemented in a distributed cloud computing environment in which remote processing devices are linked through a communication network. In a distributed cloud computing environment, program modules can be located on local or remote computer system storage media including storage devices.

[0095] The computer device 403 can include a processor 402 suitable for executing stored instructions, a memory 401 providing temporary storage space for the operation of the instructions during operation. The processor 402 can be a single-core processor, a multi-core processor, a computing cluster or any number of other configurations. The memory 401 can include random access memory (RAM), read-only memory, flash memory or any other suitable storage system.

[0096] The processor 402 can be connected through the system interconnect (e.g., PCI, PCI-Express, etc.) to an I / O interface (input / output interface) adapted to connect the computer device 403 to one or more I / O devices (input / output devices). The I / O devices can include, for example, a keyboard and a pointing device, where the pointing device can include a touchpad or a touchscreen, etc. The I / O devices can be built-in components of the computer device 403, or can be devices externally connected to the computer device.

[0097] The processor 402 can also be linked through the system interconnect to a display interface adapted to connect the computer device 403 to a display device. The display device can include a display screen that is a built-in component of the computer device 403. The display device can also include a computer monitor, a television, or a projector, etc. that is externally connected to the computer device 403. In addition, a network interface controller (NIC) can be adapted to connect the computer device 403 to a network through the system interconnect. In some embodiments, the NIC can use any suitable interface or protocol (such as Internet Small Computer System Interface, etc.) to transfer data. The network can be a cellular network, a radio network, a wide area network (WAN), a local area network (LAN), or the Internet, etc. Remote devices can be connected to the computer device through the network.

[0098] The flowcharts provided in this embodiment are not intended to indicate that the operations of the methods will be executed in any particular order, or that all of the operations included in the methods will be included in every implementation of the methods. In addition, the methods can include additional operations. Additional changes can be made to the above-described methods within the scope of the technical ideas provided by the methods.

[0099] Up to now, those skilled in the art should recognize that, although the multiple exemplary embodiments of the present application have been shown and described in detail herein, many other variations and modifications that do not depart from the spirit and scope of the present application can be directly determined or deduced from the disclosure of the present application according to the technical ideas provided by the methods. Therefore, the scope of the present application should be understood and recognized as covering all such other variations or modifications.

Claims

1. A method for optimizing database query statements, comprising: Determine whether the database query statement meets the conditions for implementing the statement optimization method; If so, the relevant scalar query statements in the database query statement are extracted to the outermost target column of the database query statement. The relevant scalar query refers to the scalar query that exists inside the database query statement, and the constraint conditions of the scalar query are related to the result of the outer scalar query. Modify the constraints in the relevant scalar quantum query; The step of determining whether the database query statement meets the conditions for implementing the statement optimization method includes: determining whether the database query statement is a ROWNUM pagination operation; if the database query statement is a ROWNUM pagination operation, then determining whether there is a related scalar subquery in the inner target column of the database query statement; if there is a related scalar subquery in the inner target column of the database query statement, then determining whether the inner target column of the database query statement contains sorting and / or grouping operation statements; if the inner target column of the database query statement does not contain the sorting and / or grouping operation statements, then determining that the conditions for implementing the statement optimization method are met.

2. The method for optimizing database query statements according to claim 1, wherein, After determining whether a related scalar subquery exists in the inner target column of the database query statement, the following steps are also included: If the target column within the database query statement does not contain the relevant scalar subquery, then the conditions for implementing the statement optimization method are not met.

3. The method for optimizing database query statements according to claim 1, wherein, The step of determining whether the inner target column of the database query statement contains sorting and / or grouping operations includes: If the inner target column of the database query statement contains the sorting and / or grouping operation statement, then it is determined whether the object of the sorting and / or grouping operation statement is related to the result of the relevant scalar subquery.

4. The method for optimizing database query statements according to claim 3, wherein, Following the step of determining whether the target of the sorting and / or grouping operation statement is related to the result of the relevant scalar subquery, the following is included: If the sorting and / or grouping operation statement is not related to the result of the relevant scalar subquery, then the condition for implementing the statement optimization method is met.

5. The method for optimizing database query statements according to claim 3, wherein, After determining whether the target of the sorting and / or grouping operation statement is related to the result of the relevant scalar subquery, the method further includes: If the object of the sorting and / or grouping operation statement is related to the result of the relevant scalar subquery, then the condition for implementing the statement optimization method is not met.

6. A machine-readable storage medium having a machine-executable program stored thereon, the machine-executable program, when executed by a processor, implementing the method for optimizing database query statements according to any one of claims 1 to 5.

7. 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 method for optimizing a database query statement according to any one of claims 1 to 5.

Images