Method and apparatus for determining SQL execution plan

Patent metadata

Field	Value
Publication number	US-10901976-B2
Application number	US-201715495569-A
Country	US
Kind code	B2
Filing date	Apr 24, 2017
Priority date	Feb 19, 2016
Publication date	Jan 26, 2021
Grant date	Jan 26, 2021

Abstract

A method and an apparatus for determining a structured query language (SQL) execution plan are provided to optimize determining of the SQL execution plan and improve execution efficiency of the SQL execution plan. The SQL execution plan corresponds to at least one relation table. During an N th iteration, the method includes obtaining a first iteration parameter generated after a first plan tree is executed on the at least one relation table during an (N−1) th iteration, where N is a natural number greater than 1, establishing a second plan tree according to the first iteration parameter, and determining the first plan tree or the second plan tree as the SQL execution plan when a difference between the second plan tree and the first plan tree is not greater than a first threshold.

First claim

What is claimed is:

1. A structured query language (SQL) statement performing apparatus for determining a SQL execution plan, wherein the SQL execution plan corresponds to at least one relation table and a SQL statement, and wherein the SQL statement performing apparatus comprises:
a memory configured to store instructions and data; and
a processor coupled to the memory, wherein during an N th iteration, the instructions cause the processor to be configured to:
obtain a first iteration parameter generated after a first plan tree is executed for the SQL statement on the at least one relation table during an (N−1) th iteration, wherein the first iteration parameter comprises first relation tuples in any one or more of the at least one relation table corresponding to the SQL execution plan, and wherein N is a natural number greater than 1;
establish a second plan tree for the same SQL statement according to the first iteration parameter;
execute the second plan tree to generate a second iteration parameter, wherein the second iteration parameter comprises second relation tuples in any one or more of the at least one relation table corresponding to the SQL execution plan;
record the second iteration parameter into the memory;
determine the second plan tree as the SQL execution plan and perform the SQL statement based on the determined SQL execution plan when a difference between the second plan tree and the first plan tree is not greater than a first predetermined threshold;
establish the second plan tree different from the first plan tree according to the first iteration parameter by prohibiting generation of the second plan tree that is the same as the first plan tree when the second iteration parameter is the same as the first iteration parameter;
update the first iteration parameter according to the second iteration parameter when a difference between the second plan tree and the first plan tree is greater than the first predetermined threshold; and
set the second plan tree during the N th iteration as a first plan tree during an (N+1) th iteration based on the updated first iteration parameter.

2. The SQL statement performing apparatus of claim 1 , wherein the first predetermined threshold is 0.

3. The SQL statement performing apparatus of claim 1 , wherein the SQL execution plan corresponds to at least two relation tables, and wherein the first iteration parameter further comprises relation tuples in a result set obtained after a join operation is performed on any one or more relation tables in the at least two relation tables.

4. The SQL statement performing apparatus of claim 1 , wherein after determining the first plan tree as the SQL execution plan, the instructions further cause the processor to be configured to record a state of the SQL execution plan as a determined state into the memory.

5. The SQL statement performing apparatus of claim 1 , wherein the SQL execution plan corresponds to at least two relation tables, and wherein the second iteration parameter further comprises relation tuples in a result set obtained after a join operation is performed on any one or more relation tables in the at least two relation tables.

6. The SQL statement performing apparatus of claim 1 , wherein the instructions further cause the processor to be configured to:
record an execution time for executing the second plan tree into the memory; and
set the second plan tree with shortest execution time during a plurality of N iterations as the SQL execution plan when N is greater than a second predetermined threshold.

7. The SQL statement performing apparatus of claim 6 , wherein after setting the second plan tree with the shortest execution time during the plurality of N iterations as the SQL execution plan, the instructions further cause the processor to be configured to record a state of the SQL execution plan as a determined state into the memory.

8. The SQL statement performing apparatus of claim 1 , wherein before updating the first iteration parameter, the instructions further cause the processor to be configured to:
record an execution time for executing the second plan tree into the memory; and
set the second plan tree with shortest execution time during a plurality of N iterations as the SQL execution plan when N is greater than a third predetermined threshold.

9. The SQL statement performing apparatus of claim 8 , wherein after setting the second plan tree with the shortest execution time during the plurality of N iterations as the SQL execution plan, the instructions further cause the processor to be configured to record a state of the SQL execution plan as a determined state into the memory.

10. The SQL statement performing apparatus of claim 1 , wherein the instructions further cause the processor to be configured to:
obtain a union set of the second iteration parameter and the first iteration parameter; and
set a result of the union set as the first iteration parameter.

11. The SQL statement performing apparatus of claim 1 , wherein the instructions further cause the processor to be configured to:
establish an initial plan tree according to a pre-stored estimation iteration parameter when an initial iteration is performed;
execute the initial plan tree to obtain an initial iteration parameter; and
update the estimation iteration parameter according to the initial iteration parameter to obtain the first iteration parameter.

12. The SQL statement performing apparatus of claim 11 , wherein the SQL execution plan corresponds to at least two relation tables, and wherein the initial iteration parameter further comprises relation tuples in a result set obtained after a join operation is performed on any one or more relation tables in the at least two relation tables.

13. The SQL statement performing apparatus of claim 11 , wherein the instructions further cause the processor to be configured to:
obtain a union set of the initial iteration parameter and the estimation iteration parameter; and
set a result of the union set as the first iteration parameter.

14. The SQL statement performing apparatus of claim 11 , wherein before the initial iteration is performed, the instructions further cause the processor to be configured to:
query whether a state of the SQL execution plan is a determined state; and
execute the SQL execution plan when the state of the SQL execution plan is the determined state.

15. The SQL statement performing apparatus of claim 1 , wherein the second plan tree is established based on the first iteration parameter updated by executing the first plan tree, and wherein the second plan tree does not depend on an estimation iteration parameter previously collected in a database to improve execution efficiency of the SQL execution plan.

16. The SQL statement performing apparatus of claim 1 , wherein the second plan tree is set as the SQL execution plan when the second plan tree is the same as the first plan tree such that an accurate SQL execution plan is determined to improve execution efficiency of the SQL execution plan.

17. The SQL statement performing apparatus of claim 1 , wherein the SQL statement performing apparatus comprises a computing system, wherein the memory comprises a non-transitory computer readable medium, wherein the processor comprises a computer microprocessor, and wherein the SQL execution plan is executed by querying the at least one relation table.

18. A method for determining a structured query language (SQL) execution plan, wherein the SQL execution plan corresponds to at least one relation tabl

Assignees

• Huawei Tech Co Ltd

Inventors

• Liu Huijun
• Ren Yang
• Li Maozeng
• Han Feng

Classifications

• G06F16/24545 Primary

  Selectivity estimation or determination · CPC title

• G06F16/24542

  Plan optimisation · CPC title

• G06F16/24544

  Join order optimisation · CPC title

• G06F16/2246

  Trees, e.g. B+trees · CPC title

• G06F16/235 Primary

  Update request formulation · CPC title