Caching execution plans for queries that use timestamps

• US 8,250,064 B1
• Filed: 04/06/2010
• Issued: 08/21/2012
• Est. Priority Date: 04/06/2010
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Ramesh, Bhashyam, Koppuravuri, Manjula
• Primary Examiner(s): Lewis, Cheryl
• Assistant Examiner(s): Bui, Tiffany Thuy
• Application Number:
  US12/754,694
• Time in Patent Office: 868 Days
• Field of Search: 707/714, 707/718
• US Class Current: 707/718
• CPC Class Codes: G06F 16/24552 Database cache management
• CPC Class Codes: G06F 16/24553 of query operations

Abstract

A computer system programmed as a database receives a query Q1. Q1 includes a predicate. The predicate includes a reference to a current timestamp of time TT1. The database has R data structures. The computer system creates a plan, Plan 1, to execute Q1. Plan 1 includes the identification of a first set of data structures, P1, to be accessed in executing Plan 1, at the current timestamp of time TT1. The number of data structures in P1 is less than R. The computer system determines that Plan 1 is valid for a period of time. The computer system stores in a cache Plan 1 and an indication that Plan 1 is valid for the period of time.

Claims

1. A method comprising:

receiving a query Q1 in a computer system programmed as a database, Q1 including a predicate, the predicate including a reference to a current timestamp of time TT1, the database having R data structures;

the computer system creating a plan, Plan 1, to execute Q1, Plan 1 including the identification of a first set of data structures, P1, to be accessed in executing Plan 1, at the current timestamp of time TT1, the number of data structures in P1 being less than R;

the computer system determining that Plan 1 is valid for a period of time by determining that a plan, Plan 2, to execute Q1 during a period between TT1 and TT2, TT2 being later than TT1, accesses a second set of data structures, P2, P2 being contained in P1;

storing in a cache;

Plan 1; and

an indication that Plan 1 is valid for the period of time;

receiving a query Q2 in the database, Q2 including a predicate, the predicate including a reference to a current timestamp of time TT3; and

determining that Q2 is equivalent to Q1 and that TT3 is in the period between TT1 and TT2 and in response, using the cached plan, Plan 1, to execute Q2.

2. The method of claim 1 wherein determining that Plan 1 is valid for a period of time comprises:

determining that Plan 2 uses an index data structure JI1 used by Plan 1 in executing Q1.

3. The method of claim 2 wherein:

the index data structure JI1 is a join index.

4. The method of claim 1 wherein determining that Plan 1 is valid for the period of time comprises:

enhancing Q1 to include a condition testing a column of the database to determine if the values of the column fall in the period of time;

determining that the plan, Plan 2, to execute the enhanced Q1 during the period of time, accesses a second set of data structures, P2, P2 being contained in P1; and

determining that Plan 2 uses an index data structure used by Plan 1 in executing Q1.

5. The method of claim 1 wherein determining that Plan 1 is valid for the period of time comprises:

recognizing that Q1 includes a condition testing a column of the database to determine if the values of the column are after the current timestamp; and

setting the period of time to be all time after the current timestamp.

6. The method of claim 1 wherein the data structures are partitions.

7. The method of claim 1 wherein P2 is contained in P1 if every data structure in P2 is also in P1.

8. The method of claim 1 wherein determining that Q2 is equivalent to Q1 comprises determining that Q2 is identical to Q1 except for their respective current timestamp values.

9. A computer program, stored in a non-transitory computer-readable medium, the program comprising executable instructions that cause a computer to:

receive a query Q1 in a computer system programmed as a database, Q1 including a predicate, the predicate including a reference to a current timestamp of time TT1, the database having R data structures;

create a plan, Plan 1, to execute Q1, Plan 1 including the identification of a first set of data structures, P1, to be accessed in executing Plan 1, at the current timestamp of time TT1, the number of data structures in P1 being less than R;

determine that Plan 1 is valid for a period of time by determining that a plan, Plan 2, to execute Q1 during a period between TT1 and TT2, TT2 being later than TT1, accesses a second set of data structures, P2, P2 being contained in P1;

store in a cache;

Plan 1; and

an indication that Plan 1 is valid for the period of time;

receive a query Q2 in the database, Q2 including a predicate, the predicate including a reference to a current timestamp of time TT3; and

determine that Q2 is equivalent to Q1 and that TT3 is in the period between TT1 and TT2 and in response, use the cached plan, Plan 1, to execute Q2.

10. The computer program of claim 9 wherein, when determining that Plan 1 is valid for a period of time, the computer:

determines that Plan 2 uses an index data structure JI1 used by Plan 1 in executing Q1.

11. The computer program of claim 10 wherein:

the index data structure JI1 is a join index.

12. The computer program of claim 9 wherein, when determining that Plan 1 is valid for the period of time, the computer:

enhances Q1 to include a condition testing a column of the database to determine if the values of the column fall in the period of time;

determines that the plan, Plan 2, to execute the enhanced Q1 during the period of time, accesses a second set of data structures, P2, P2 being contained in P1; and

determines that Plan 2 uses an index data structure used by Plan 1 in executing Q1.

13. The computer program of claim 9 wherein, when determining that Plan 1 is valid for the period of time, the computer:

recognizes that Q1 includes a condition testing a column of the database to determine if the values of the column are after the current timestamp; and

sets the period of time to be all time after the current timestamp.

14. The computer program of claim 9 wherein the data structures are partitions.

15. The computer program of claim 9 wherein P2 is contained in P1 if every data structure in P2 is also in P1.

16. The computer program of claim 9 wherein, when determining that Q2 is equivalent to Q1, the computer determines that Q2 is identical to Q1 except for their respective current timestamp values.