EFFICIENT DATA RETRIEVAL IN STAGED USE OF IN-MEMORY CURSOR DURATION TEMPORARY TABLES

• US 20170116272A1
• Filed: 09/16/2016
• Published: 04/27/2017
• Est. Priority Date: 10/23/2015
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Ziauddin, Mohamed, Lahorani, Janaki Latha, Kim, You Jung
• Granted Patent: US 10,783,142 B2
• Time in Patent Office: Days
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: G06F 16/2455 Query execution
• CPC Class Codes: G06F 16/24561 Intermediate data storage techniques for performance improvement

Abstract

Techniques are presented herein for storing cursor duration temporary tables in memory of nodes in a clustered database system in association with iterations of an iterative query operation. The techniques involve associating a portion of memory with one or more iteration values. The iteration values indicate which iterations correspond to data that is stored in the portion of memory. When data is requested for a particular iteration, portions of memory are checked to determine if it stores the particular iteration.

Claims

1. A method for processing a query on a multi-node database system against a shared database, the method comprising:

creating an in-memory temporary table for storing intermediate results for the query, wherein said temporary table comprises multiple table segments;

wherein an execution plan for said query comprises multiple query plan operations, said multiple query plan operations including a first query plan operation and an iterative query plan operation that is executed in multiple iterations, wherein;

said first query plan operation includes reading data from a certain table and storing results generated by said query plan operation in one or more table segments in association with an initial iteration of said multiple iterations;

executing said iterative query plan operation in subsequent iterations of said multiple iterations, wherein for each iteration of said subsequent iterations;

determining one or more table segments associated with one or more previous iterations;

reading said one or more table segments to execute said iterative query plan operation; and

storing results of executing said iterative query plan operation generated during said each iteration in one or more table segments in association with said each iteration.

2. The method of claim 1, wherein executing said execution plan includes multiple processes executing said iterative query plan operation in parallel.

3. The method of claim 1, wherein executing said iterative query plan operation further comprises, for each iteration of said subsequent iterations:

determining an iteration value in association with said each iteration; and

storing said iteration value in association with said results generated during said each iteration.

4. The method of claim 3, wherein executing said iterative query plan operation further comprises, for each iteration of said subsequent iterations:

determining a respective one or more iteration values associated with said one or more previous iterations; and

determining said one or more table segments associated with said one or more previous iterations based on the respective one or more iteration values.

5. The method of claim 3 wherein executing said iterative query plan operation further comprises, for each iteration of said subsequent iterations:

determining a respective one or more iteration values associated with said one or more previous iterations;

determining one or more memory segments associated with said one or more previous iterations based on said respective one or more iteration values; and

reading said one or more table segments from said one or more memory segments.

6. The method of claim 3, wherein said multiple table segments are stored in one or more memory segments in memory of nodes in the multi-node database system and wherein executing said iterative query plan operation further comprises, for each iteration of said subsequent iterations:

storing results generated during said each iteration in one or more memory segments in association with said each iteration; and

storing data that associates said one or more memory segments with said iteration value.

7. The method of claim 6, wherein storing data that associates said one or more memory segments with said iteration value further comprises, for each memory segment of said one or more memory segments:

storing data that associates a respective starting location in said each memory segment with said iteration value; and

storing data that associates a respective ending location in said each memory segment with said iteration value.

8. The method of claim 7, wherein for each iteration of said subsequent iterations, reading said one or more table segments further comprises:

for each iteration value associated with said one or more previous iterations, determining one or more memory segments associated with said each iteration value;

for each of said one or more memory segments, determining a respective starting location and a respective ending location associated with said each iteration value; and

reading from said respective starting location to said respective ending location.

9. One or more non-transitory computer-readable media storing instructions for processing a query on a multi-node database system against a shared database, wherein the instructions include:

instructions which, when executed by one or more hardware processors, cause creating an in-memory temporary table for storing intermediate results for the query, wherein said temporary table comprises multiple table segments;

wherein an execution plan for said query comprises multiple query plan operations, said multiple query plan operations including a first query plan operation and an iterative query plan operation that is executed in multiple iterations, wherein;

said first query plan operation includes reading data from a certain table and storing results generated by said query plan operation in one or more table segments in association with an initial iteration of said multiple iterations;

instructions which, when executed by one or more hardware processors, cause executing said iterative query plan operation in subsequent iterations of said multiple iterations, wherein for each iteration of said subsequent iterations;

determining one or more table segments associated with one or more previous iterations;

reading said one or more table segments to execute said iterative query plan operation; and

storing results of executing said iterative query plan operation generated during said each iteration in one or more table segments in association with said each iteration.

10. The one or more non-transitory computer-readable media of claim 9, wherein executing said execution plan includes multiple processes executing said iterative query plan operation in parallel.

11. The one or more non-transitory computer-readable media of claim 9, wherein executing said iterative query plan operation further comprises, for each iteration of said subsequent iterations:

determining an iteration value in association with said each iteration; and

storing said iteration value in association with said results generated during said each iteration.

12. The one or more non-transitory computer-readable media of claim 11, wherein executing said iterative query plan operation further comprises, for each iteration of said subsequent iterations:

determining a respective one or more iteration values associated with said one or more previous iterations; and

determining said one or more table segments associated with said one or more previous iterations based on the respective one or more iteration values.

13. The one or more non-transitory computer-readable media of claim 11 wherein executing said iterative query plan operation further comprises, for each iteration of said subsequent iterations:

determining a respective one or more iteration values associated with said one or more previous iterations;

determining one or more memory segments associated with said one or more previous iterations based on said respective one or more iteration values; and

reading said one or more table segments from said one or more memory segments.

14. The one or more non-transitory computer-readable media of claim 11, wherein said multiple table segments are stored in one or more memory segments in memory of nodes in the multi-node database system and wherein executing said iterative query plan operation further comprises, for each iteration of said subsequent iterations:

storing results generated during said each iteration in one or more memory segments in association with said each iteration; and

storing data that associates said one or more memory segments with said iteration value.

15. The one or more non-transitory computer-readable media of claim 14, wherein storing data that associates said one or more memory segments with said iteration value further comprises, for each memory segment of said one or more memory segments:

storing data that associates a respective starting location in said each memory segment with said iteration value; and

storing data that associates a respective ending location in said each memory segment with said iteration value.

16. The one or more non-transitory computer-readable media of claim 15, wherein for each iteration of said subsequent iterations, reading said one or more table segments further comprises:

for each iteration value associated with said one or more previous iterations, determining one or more memory segments associated with said each iteration value;

for each of said one or more memory segments, determining a respective starting location and a respective ending location associated with said each iteration value; and

reading from said respective starting location to said respective ending location.