FIELD OF THE INVENTION

This application relates to the database technology and in particular, to a method of optimizing query and
an optimizer.

BACKGROUND

Structured query language (SQL) is one kind of database query and programming language, it is used to store and retrieve the data as well as allow querying in the relational database system.
The SQL language's development began in 1974, proposed by Boyce and Chamberlin, at that time called SEQUEL; In 1976, IBM Corporation's Sanjase research institute changes SQL when developed RDBMS SYSTEM R; ORACLE Corporation published firstly in 1979 based on the SQL commercialization RDBMS product; American country Standardization organization ANSI announced that SQL takes the database industrial standard in 1986.

The SQL language contains 4 parts: A). Data definition language (DDL), contains CREATE, DROP and ALTER. B). Data manipulation language (DML), contains INSERT, UPDATE and DELETE. C). Data Query language (DQL), contains SELECT. D). Data management language (DCL), contains SQL statements such as GRANT, REVOKE, COMMIT, ROLLBACK.

Commonly, the execution step of SQL sentence may include:

1. Parse (Syntax Check), It parses the SQL statement syntax and decides whether it conform to the standard.

2. Analyze, It checks the objects (tables, columns, etc.) used in SQL statement exist in Database
and extracts any bind variable if there are any.

3. Optimize, It chooses the best plan for query execution based on cost.

4. Execute, It executes the best plan generates in previous step and returns the result.

However, the applicant found that the qualification does not get pushed down in current systems. In some cases like the sub-query fetches first N rows (such as LIMIT OFFSET or ROWNUM clause), at first the inner query will be executed; then the outer query will be executed on the result set from the inner query; and the final result is given back to the client.

In those cases, the number of rows as part of inner query execution or the number of values needed to index scan will be higher than actually required. Thus the performance of query execution needs to be improved.

SUMMARY

Embodiments of the present invention pertain to a method of optimizing query and an optimizer. The aim is to optimize the SQL query to a higher degree and reduce the overall query execution time.

According to a first aspect of the embodiments of the present invention, there is provided a method of optimizing query, the method comprising:

verifying there is a sub-query which returns first N rows in an inputted SQL query statement;

determining whether the sub-query is satisfied conditions to merge with the outer query of the SQL query statement;

generating an execution plan of pushing down the outer query into the sub-query if the sub-query is satisfied the conditions.

According to a second aspect of the embodiments of the present invention, there is provided an optimizer, the optimizer comprising:

a verifying unit, configured to verify whether there is any sub-query which returns first N rows in an inputted SQL query statement;

a first determining unit, configured to determine whether the sub-query is satisfied conditions to merge with the outer query of the SQL query statement;

a generating unit, configured to generate an execution plan of pushing down the outer query into the sub-query if the sub-query is satisfied the conditions.

The advantages of the present invention exist in that: the performance of query execution can be improved by pushing down the outer query into the sub-query. The SQL query may be optimized to a higher degree and the overall query execution time may be reduced.

These and further aspects and features of the present invention will be apparent with reference to the following description and attached drawings. In the description and drawings, particular embodiments of the invention have been disclosed in detail as being indicative of some of the ways in which the principles of the invention may be employed, but it is understood that the invention is not limited correspondingly in scope. Rather, the invention includes all changes, modifications and equivalents coming within the spirit and terms of the appended claims.

Features that are described and/or illustrated with respect to one embodiment may be used in the same way or in a similar way in one or more other embodiments and/or in combination with or instead of the features of the other embodiments.

It should be emphasized that the term "comprises/comprising" when used in this specification is taken to specify the presence of stated features, integers, steps or components but does not preclude the presence or addition of one or more other features, integers, steps, components or groups thereof.

Many aspects of the invention can be better understood with reference to the following drawings. The components in the drawings are not necessarily to scale, emphasis instead being placed upon clearly illustrating the principles of the present invention. To facilitate illustrating and describing some parts of the invention, corresponding portions of the drawings may be exaggerated in size, e.g., made larger in relation to other parts than in an exemplary device actually made according to the invention. Elements and features depicted in one drawing or embodiment of the invention may be combined with elements and features depicted in one or more additional drawings or embodiments. Moreover, in the drawings, like reference numerals designate corresponding parts throughout the several views and may be used to designate like or similar parts in more than one embodiment.

BRIEF DESCRIPTION OF THE DRAWING

The drawings are included to provide further understanding of the present invention, which constitute a part of the specification and illustrate the preferred embodiments of the present invention, and are used for setting forth the principles of the present invention together with the description. The same element is represented with the same reference number throughout the drawings.

In the drawings:

Figure 1 is a flowchart of the method of an embodiment of the present invention;

Figure 2 is a flowchart showing an example of the present invention;

Figure 3 is a flowchart showing another example of the present invention;

Figure 4 is a flowchart showing another example of the present invention;

Figure 5 is a schematic diagram of the optimizer of an embodiment of the present invention;

Figure 6 is a schematic diagram showing an example of the first determining unit of the present invention;

Figure 7 is a schematic diagram showing another example of the first determining unit of the present invention;

Figure 8 is a schematic diagram showing another example of the first determining unit of the present invention.

DETAILED DESCRIPTION

The many features and advantages of the embodiments are apparent from the detailed specification and, thus, it is intended by the appended claims to cover all such features and advantages of the embodiments that fall within the true spirit and scope thereof. Further, since numerous modifications and changes will readily occur to those skilled in the art, it is not desired to limit the inventive embodiments to the exact construction and operation illustrated and described, and accordingly all suitable modifications and equivalents may be resorted to, falling within the scope thereof.

The preferred embodiments of the present invention are described as follows in reference to the drawings.

Nowadays, there are some SQL query statements with an inner query (sub-query) and an outer query. In general the outer query is not pushed for the inner query when the inner query contains a LIMIT OFFSET clause.

For example, there is a table Tb1, and the Tb1 contains 1000 records, the IDs of Tbl are separately from 1 to 1000. Please see the SQL query statement below:

Select * from (select * from tb1 where id < 100 order by id desc limit 10 offset 1) as A where A.id < 50;
In a case, Execution plan will be generated when id < 100 does not merge A.id < 50.

In this case, the inner query (select * from tbl where id < 100 order by id desc limit10 offset 1) first selects 10 values such that id < 100 and values are in descending order. The result will be 99, 98, 97, 96, 95, 94, 93, 92, 91, and 90. Then it applies the outer clause id < 50 on the result set {99, 98, 97, 96, 95, 94, 93, 92, 91}, and the final output is 0 rows.

In another case, Execution plan will be generated when the outer clause A.id < 50 is pushed to the inner query.

In this case, the inner query chooses 10 rows according to clause id<100, and id<50 in descending order. The Result will be 49, 48, 47, 46, 45, 44, 43, 42, 41, and 40.

It is clearly visible from above result that if we push down the outer query to the inner query containing LIMIT OFFSET clause, the results can change. Thus we cannot always push-down the outer clause.

This invention will specify the scenarios when it can be pushed down to improve performance.

Embodiment 1
This embodiment of the present invention provides a method of optimizing query. Figure 1 is a flowchart of the method of an embodiment of the present invention. As shown in Figure 1, the method includes:

Step 101, verifying there is a sub-query which returns first N rows in an inputted SQL query statement;

Step 102, determining whether the sub-query is satisfied conditions to merge with the outer query of the SQL query statement;

Step 103, generating an execution plan of pushing down the outer query into the sub-query if the sub-query is satisfied the conditions.

In this embodiment, the sub-query may exist in FROM clause, or WHERE clause, or TARGET clause.

However it is not limited thereto and particular implement clause may be determined as actually required.

In an implement way, the step 102 of determining whether the sub-query is satisfied conditions to merge with the outer query of the SQL query statement specifically comprising:

determining whether the sub-query takes effect on the same column as the outer query;

determining whether the sub-query has a ORDER BY DESC clause and the outer query has a greater operator, or the sub-query has a ORDER BY ASC clause and the outer query has a less operator.

Figure 2 is a flowchart showing an example of the present invention. As shown in Figure 2, the method includes:

Step 201, the receiver receives a SQL query statement inputted by a user.

Step 202, the parser generates parse tree for the SQL query statement.

Step 203, the optimizer verifies if there is any sub-query which returns first N rows; execute step 204 if there is a sub-query which returns first N rows, otherwise execute step 207.

Step 204, the optimizer determines whether the sub-query takes effect on the same column as the outer query; execute step 205 if yes, otherwise execute step 207.

Step 205, the optimizer determines whether the sub-query has a ORDER BY DESC clause and the outer query has a greater operator, or the sub-query has a ORDER BY ASC cause and the outer query has a less operator; execute step 206 if yes, otherwise execute step 207.

Step 206, the optimizer pushes down the outer query into the sub-query.

Step 207, the optimizer generates an execution plan.

In this implement way, the outer query can be pushed when the inner query and the out query satisfy: (1) take effect on the same column, the sub-query has a ORDER BY DESC cause and the outer query has a greater operator; or (2) take effect on the same column, the sub-query has a ORDER BY ASC cause and the outer query has a less operator.

There is an example for this implement way as described below:

Select * from (Select * from tbl where id < 100 order by id desc limit 10 offset 1) as A where A.id > 50;

In the above cases, clause id > 50 can be pushed down into sub-query as id < 100 AND id > 50.

In this implement way, as for the SQL statement, the execution plan in the prior art and the execution plan in the present invention are also shown as below:

The execution plan in the prior art:

The execution plan in the present invention:

From this example it is clear that number of rows to sort has been decreased and extra expression evaluation is reduce which will improve query performance.

In another implement way, the step 102 of determining whether the sub-query is satisfied conditions to merge with the outer query of the SQL query statement specifically comprising:

determining whether the sub-query takes effect on the same column as the outer query; determining whether the column has a DESC index scan and the outer query has a greater operator, or the column has a ASC index scan and the outer query has a less operator.

Figure 3 is a flowchart showing an example of the present invention. As shown in Figure 3, the method includes:

Step 301, the receiver receives a SQL query statement inputted by a user.

Step 302, the parser generates parse tree for the SQL query statement.

Step 303, the optimizer verifies if there is any sub-query which returns first N rows; execute step 304 if there is a sub-query which returns first N rows, otherwise execute step 307.

Step 304, the optimizer determines whether the sub-query takes effect on the same column as the outer query; execute step 305 if yes, otherwise execute step 307.

Step 305, the optimizer determines whether the column has a DESC index scan and the outer query has a greater operator, or the column has a ASC index scan and the outer query has a less operator; execute step 306 if yes, otherwise execute step 307.

Step 306, the optimizer pushes down the outer query into the sub-query.

Step 307, the optimizer generates an execution plan.

In this implement way, the outer query can be pushed when the inner query and the out query satisfy: (1) take effect on the same column, the column has a DESC index scan and the outer query has a greater operator; or (2) take effect on the same column, the column has a ASC index scan and the outer query has a less operator.

There is an example for this implement way as described below:

Create Desc index idx on tbl (id);

Select * from (Select * from tbl where id < 100 limit 10 offset 1) as A where A.id > 50;

In the above cases, clause id > 50 can be pushed down into sub-query as id < 100 AND id > 50.

In this implement way, as for the SQL statement, the execution plan in the prior art and the execution plan in the present invention are also shown as below:

The execution plan in the prior art:

The execution plan in the present invention:

From this example it is clear that number of index tree value scans has been decreased and extra expression evaluation is reduce which will improve query performance. The index scan is random access which is costlier, so if the number of values to scan gets reduced, it will improve performance.

In another implement way, the step 102 of determining whether the sub-query is satisfied conditions to merge with the outer query of the SQL query statement specifically comprising:

determining whether the sub-query takes effect on the same column as the outer query;

determining whether the sub-query has a LIMIT OFFSET cause, and the outer query is contained in the sub-query or the sub-query is contained in the outer query.

Figure 4 is a flowchart showing an example of the present invention. As shown in Figure 4, the method includes:

Step 401, the receiver receives a SQL query statement inputted by a user.

Step 402, the parser generates parse tree for the SQL query statement.

Step 403, the optimizer verifies if there is any sub-query which returns first N rows; execute step 404 if there is a sub-query which returns first N rows, otherwise execute step 407.

Step 404, the optimizer determines whether the sub-query takes effect on the same column as the outer query; execute step 405 if yes, otherwise execute step 407.

Step 405, the optimizer determines whether the sub-query has a LIMIT OFFSET cause, and the condition of the sub-query is contained in the condition of the outer query; execute step 406 if yes, otherwise execute step 407.

Step 406, the optimizer pushes down the outer query into the sub-query.

Step 407, the optimizer generates an execution plan.

In this implement way, the outer query can be pushed when the inner query and the out query satisfy: (1) take effect on the same column, (2) the sub-query has a LIMIT OFFSET cause and the sub-query is contained in the outer query.

There is an example for this implement way as described below:

Select * from (Select * from tbl where id > 100 limit 10 offset 1) as A where A.id > 50;

In the above cases, the condition of the sub-query "id > 100" is contained in the condition of the outer query "id > 50", so clause id > 50 can be pushed down into sub-query as id > 100 AND id > 50.

In this implement way, as for the SQL statement, the execution plan in the prior art and the execution plan in the present invention are also shown as below:

The execution plan in the prior art:

The execution plan in the present invention:

From this example it is clear that extra expression evaluation is reduce which will improve query performance.

In this embodiment, the outer query qualification can be pushed to inner sub-query, when inner sub-query contains Order By and Fetch N Rows clause. Or the outer query qualification can be pushed to inner sub-query, when inner sub-query contains does index scan and have Fetch N Rows clause. Or the outer query qualification can be pushed to inner sub-query, when outer query contains qualification which can be merged with inner query qualification and inner query contains Fetch N Rows clause.

It can be seen from the above embodiment that the performance of query execution can be improved by pushing down the outer query into the sub-query. The SQL query may be optimized to a higher degree and the overall query execution time may be reduced.

Embodiment 2
This embodiment of the present invention further provides an optimizer, applied for optimizing query. This embodiment corresponds to the method of the above embodiment 1 and the same content will not be described.

Figure 5 is a schematic diagram of the optimizer of an embodiment of the present invention. As shown in Figure 5, the optimizer 500 includes: a verifying unit 501, a first determining unit 502 and a generating unit 503. Other parts of the optimizer 500 can refer to the existing technology and not be described in the present application.

Wherein, the verifying unit 501 is configured to verify whether there is any sub-query which returns first N rows in an inputted SQL query statement; the first determining unit 502 is configured to determine whether the sub-query is satisfied conditions to merge with the outer query of the SQL query statement; the generating unit 503 is configured to generate an execution plan of pushing down the outer query into the sub-query if the sub-query is satisfied the conditions.

In this embodiment, the sub-query may exist in FROM clause, or WHERE clause, or TARGET clause. However it is not limited thereto and particular implement clause may be determined as actually required.

Figure 6 is a schematic diagram showing an example of the first determining unit of the present invention.
As shown in Figure 6, the first determining unit 502 may specifically include: a second determining unit 601 and a third determining unit 602;

Wherein the second determining unit 601 is configured to determine whether the sub-query takes effect on the same column as the outer query; the third determining unit 602 is configured to determine whether the sub-query has a ORDER BY DESC cause and the outer query has a greater operator, or the sub-query has a ORDER BY ASC cause and the outer query has a less operator.

Figure 7 is a schematic diagram showing an example of the first determining unit of the present invention. As shown in Figure 7, the first determining unit 502 may specifically include: a second determining unit 701 and a fourth determining unit 702;

Wherein the second determining unit 701 is configured to determine whether the sub-query takes effect on the same column as the outer query; the fourth determining unit 702 is configured to determine whether the column has a DESC index scan and the outer query has a greater operator, or the column has a ASC index scan and the outer query has a less operator.

Figure 8 is a schematic diagram showing an example of the first determining unit of the present invention. As shown in Figure 8, the first determining unit 502 may specifically include: a second determining unit 801 and a fifth determining unit 802;

Wherein the second determining unit 801 is configured to determine whether the sub-query takes effect on the same column as the outer query; the fifth determining unit 802 is configured to determine whether the sub-query has a LIMIT OFFSET cause and the sub-query is contained in the outer query.

It can be seen from the above embodiment that the performance of query execution can be improved by pushing down the outer query into the sub-query. The SQL query may be optimized to a higher degree and the overall query execution time may be reduced.

It should be understood that each of the parts of the present invention may be implemented by hardware, software, firmware, or a combination thereof. In the above embodiments, multiple steps or methods may be realized by software or firmware that is stored in the memory and executed by an appropriate instruction executing system. For example, if it is realized by hardware, it may be realized by any one of the following technologies known in the art or a combination thereof as in another embodiment: a discrete logic circuit having a logic gate circuit for realizing logic functions of data signals, application-specific integrated circuit having an appropriate combined logic gate circuit, a programmable gate array (PGA), and a field programmable gate array (FPGA), etc.

The description or blocks in the flowcharts or of any process or method in other manners may be understood as being indicative of comprising one or more modules, segments or parts for realizing the codes of executable instructions of the steps in specific logic functions or processes, and that the scope of the preferred embodiments of the present invention comprise other implementations, wherein the functions may be executed in manners different from those shown or discussed, including executing the functions according to the related functions in a substantially simultaneous manner or in a reverse order, which should be understood by those skilled in the art to which the present invention pertains.

The logic and/or steps shown in the flowcharts or described in other manners here may be, for example, understood as a sequencing list of executable instructions for realizing logic functions, which may be implemented in any computer readable medium, for use by an instruction executing system, device or apparatus (such as a system including a computer, a system including a processor, or other systems capable of extracting instructions from an instruction executing system, device or apparatus and executing the instructions), or for use in combination with the instruction executing system, device or apparatus.

The above literal description and drawings show various features of the present invention. It should be understood that those skilled in the art may prepare appropriate computer codes to carry out each of the steps and processes as described above and shown in the drawings. It should be also understood that all the terminals, computers, servers, and networks may be any type, and the computer codes may be prepared according to the disclosure to carry out the present invention by using the apparatus.

Particular embodiments of the present invention have been disclosed herein. Those skilled in the art will readily recognize that the present invention is applicable in other environments. In practice, there exist many embodiments and implementations. The appended claims are by no means intended to limit the scope of the present invention to the above particular embodiments. Furthermore, any reference to "a device to..." is an explanation of device plus function for describing elements and claims, and it is not desired that any element x using no reference to "a device to..." is understood as an element of device plus function, even though the wording of "device" is included in that claim.

Although a particular preferred embodiment or embodiments have been shown and the present invention has been described, it is obvious that equivalent modifications and variants are conceivable to those skilled in the art in reading and understanding the description and drawings. Especially for various functions executed by the above elements (portions, assemblies, apparatus, and compositions, etc.), except otherwise specified, it is desirable that the terms (including the reference to "device") describing these elements correspond to any element executing particular functions of these elements (i.e. functional equivalents), even though the element is different from that executing the function of an exemplary embodiment or embodiments illustrated in the present invention with respect to structure. Furthermore, although the a particular feature of the present invention is described with respect to only one or more of the illustrated embodiments, such a feature may be combined with one or more other features of other embodiments as desired and in consideration of advantageous aspects of any given or particular application.

WE CLAIM :

1. A method of optimizing query, the method comprising:

verifying there is a sub-query which returns first N rows in an inputted SQL query statement;

determining whether the sub-query is satisfied conditions to merge with the outer query of the SQL query statement;

generating an execution plan of pushing down the outer query into the sub-query if the sub-query is satisfied the conditions.

2. The method according to claim 1, wherein determining whether the sub-query is satisfied conditions to merge with the outer query of the SQL query statement specifically comprising:

determining whether the sub-query takes effect on the same column as the outer query;

determining whether the sub-query has a ORDER BY DESC cause and the outer query has a greater operator, or the sub-query has a ORDER BY ASC cause and the outer query has a less operator.

3. The method according to claim 1, wherein determining whether the sub-query is satisfied conditions to merge with the outer query of the SQL query statement specifically comprising:

determining whether the sub-query takes effect on the same column as the outer query;

determining whether the column has a DESC index scan and the outer query has a greater operator, or the column has a ASC index scan and the outer query has a less operator.

4. The method according to claim 1, wherein determining whether the sub-query is satisfied conditions to merge with the outer query of the SQL query statement specifically comprising:

determining whether the sub-query takes effect on the same column as the outer query; determining whether the sub-query has a LIMIT OFFSET cause and the condition of the sub-query is contained in the condition of the outer query.

5. The method according to claim 1, wherein the sub-query exists in FROM clause, or WHERE clause, or TARGET clause.

6. An optimizer, the optimizer comprising:

a verifying unit, configured to verify whether there is any sub-query which returns first N rows in an inputted SQL query statement;

a first determining unit, configured to determine whether the sub-query is satisfied conditions to merge with the outer query of the SQL query statement;

a generating unit, configured to generate an execution plan of pushing down the outer query into the sub-query if the sub-query is satisfied the conditions.

7. The optimizer according to claim 6, wherein the first determining unit specifically comprising:

a second determining unit, configured to determine whether the sub-query takes effect on the same column as the outer query;

a third determining unit, configured to determine whether the sub-query has a ORDER BY DESC clause and the outer query has a greater operator, or the sub-query has a ORDER BY ASC cause and the outer query has a less operator.

8. The optimizer according to claim 6, wherein the first determining unit specifically comprising:

a second determining unit, configured to determine whether the sub-query takes effect on the same column as the outer query;

a fourth determining unit, configured to determine whether the column has a DESC index scan and the outer query has a greater operator, or the column has a ASC index scan and the outer query has a less operator.

9. The optimizer according to claim 6, wherein the first determining unit specifically comprising:
a second determining unit, configured to determine whether the sub-query takes effect on the same column as the outer query;

a fifth determining unit, configured to determine whether the sub-query has a LIMIT OFFSET cause and the condition of the sub-query is contained in the condition of the outer query.

10. The optimizer according to claim 6, wherein the sub-query exists in FROM clause, or WHERE clause, or TARGET clause.