Self-adjusting database-query optimizer

Patent metadata

Field	Value
Publication number	US-9886492-B2
Application number	US-28152705-A
Country	US
Kind code	B2
Filing date	Nov 17, 2005
Priority date	Dec 22, 2004
Publication date	Feb 6, 2018
Grant date	Feb 6, 2018

Abstract

A database-management system (DBMS) initiates execution of an incoming database query according to an initial query-execution plan that identifies an expected path for execution. Then, at some point after execution of the query has begun, the DBMS concludes that execution has not proceeded along the expected path and, in response, chooses an alternative query-execution plan for continued execution of the query.

First claim

I claim:

1. A computer-implemented method for use in executing a query in a database system, the method comprising:
formulating an initial query-execution plan that identifies an expected path for execution of the query;
formulating, prior to an execution of said query according to said initial query-execution plan, an alternative query-execution plan for execution of the query, said alternative query-execution plan differing from the initial query-execution plan from a point that occurs after an intermediate checkpoint in the initial query-execution plan;
initiating the execution of the query according to said initial query-execution plan;
at said intermediate checkpoint in the initial query-execution plan:
concluding that an actual result obtained at the intermediate checkpoint in the initial query-execution plan does not match an expected result; and
in response, choosing said alternative query-execution plan for the execution of the query;

continuing the execution of the query according to said alternative query-execution plan from said point that occurs after said intermediate checkpoint; and
abandoning the execution of the query according to said initial query-execution plan from said point that occurs after said intermediate checkpoint.

2. The method of claim 1 , wherein concluding that the actual result does not match the expected result includes concluding that the actual result differs from the expected result by more than an amount equal to a predefined margin-of-error.

3. The method of claim 1 , wherein concluding that the actual result does not match the expected result includes concluding that the actual result differs from the expected result by more than an amount equal to one of at least two predefined margins-of-error.

4. The method of claim 3 , wherein choosing said alternative query-execution plan includes choosing one of at least two alternative query-execution plans, where each of the at least two alternative query-execution plans corresponds to one of the at least two predefined margins-of-error.

5. The method of claim 1 , wherein concluding that an actual result does not match an expected result includes concluding that an actual row count for a database table does not match an expected row count.

6. The method of claim 1 , further comprising:
concluding that an actual result obtained at a second intermediate checkpoint does not match an expected result for the second intermediate checkpoint; and
choosing another alternative query-execution plan for the execution of the query following a point after said second intermediate checkpoint.

7. A computer-implemented method for use in executing a query in a database system, the method comprising:
formulating an initial query-execution plan that identifies an expected path for execution of the query;
formulating, prior to an execution of said query according to said initial query-execution plan, an alternative query-execution plan for execution of the query;
initiating the execution of the query according to said initial query-execution plan;
and at some point after the execution of the query has begun:
concluding that an actual result obtained at a first intermediate checkpoint in the initial query-execution plan matches an expected result for the first intermediate checkpoint;
concluding that an actual result for a second intermediate checkpoint in the initial query-execution plan does not match an expected result for the second intermediate checkpoint; and
in response, choosing said alternative query-execution plan for the execution of the query;

continuing the execution of the query according to said alternative query-execution plan from a point that occurs after said second intermediate checkpoint; and
abandoning the execution of the query according to said initial query-execution plan from said point that occurs after said second intermediate checkpoint.

8. A tangible, non-transitory computer readable storage medium, having a computer program for use in executing a query in a database system, the program comprising computer-readable instructions that, when executed by a computer, cause the computer to:
formulate an initial query-execution plan that identifies an expected path for execution of the query;
formulate, prior to an execution of said query according to said initial query-execution plan, an alternative query-execution plan for execution of the query, said alternative-execution plan differing from the initial query-execution plan from a point that occurs after an intermediate checkpoint in the initial query-execution plan;
initiate the execution of the query according to said initial query-execution plan;
at said intermediate checkpoint in the initial query-execution plan:
conclude that an actual result obtained at the intermediate checkpoint in the initial query-execution plan does not match an expected result; and
in response, choose said alternative query-execution plan for the execution of the query;

continue the execution of the query according to said alternative query-execution plan from said point that occurs after said intermediate checkpoint; and
abandon the execution of the query according to said initial query-execution plan from said point that occurs after said intermediate checkpoint.

9. The tangible, non-transitory computer readable storage medium, having a computer program for use in executing a query in a database system in accordance with claim 8 , wherein, in concluding that the actual result does not match the expected result, the computer concludes that the actual result differs from the expected result by more than an amount equal to a predefined margin-of-error.

10. The tangible, non-transitory computer readable storage medium, having a computer program for use in executing a query in a database system in accordance with claim 8 , wherein, in concluding that the actual result does not match the expected result, the computer concludes that the actual result differs from the expected result by more than an amount equal to one of at least two predefined margins-of-error.

11. The tangible, non-transitory computer readable storage medium, having a computer program for use in executing a query in a database system in accordance with claim 10 , wherein, in choosing said alternative query-execution plan, the computer chooses one of at least two alternative query-execution plans, where each of the at least two alternative query-execution plans corresponds to one of the at least two predefined margins-of-error.

12. The tangible, non-transitory computer readable storage medium, having a computer program for use in executing a query in a database system in accordance with of claim 8 , wherein, in concluding that an actual result does not match an expected result, the computer concludes that an actual row count for a database table does not match an expected row count.

13. The tangible, non-transitory computer readable storage medium, having a computer program for use in executing a query in a database system in accordance with claim 8 , wherein the program also causes the computer to:
conclude that an actual result obtained at a second intermediate checkpoint does not match an expected result for the second intermediate checkpoint; and
choose another alternative query-execution plan for the execution of the query following a point after said second intermediate checkpoint.

14. A tangible, non-transitory computer readable storage medium, having a computer program for use in executing a query in a database system, the computer program including computer-readable instructions that, when executed by a computer, cause

Assignees

• Brobst Stephen
• Teradata Us Inc

Inventors

• Brobst Stephen

Classifications

• G06F16/284 Primary

  Relational databases · CPC title

• G06F16/24549

  Run-time optimisation · CPC title

• G06F16/24542

  Plan optimisation · CPC title

• G06F17/30474

  Physics · mapped topic

• G06F17/30463

  Physics · mapped topic