IN-MEMORY CURSOR DURATION TEMP TABLES

• US 20170116266A1
• 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: Lahorani, Janaki Latha, Kim, You Jung, Witkowski, Andrew, Subramanian, Sankar
• Granted Patent: US 10,452,655 B2
• Time in Patent Office: Days
• CPC Class Codes: G06F 16/24532 of parallel queries
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: G06F 16/24561 Intermediate data storage techniques for performance improvement

Abstract

Techniques are provided herein for processing a query using in-memory cursor duration temporary tables. The techniques involve storing a part of the temporary table in memory of nodes in a database cluster. A part of the temporary table may be stored in disk segments of nodes in the database cluster. Writer threads running on a particular node writes data for the temporary table to the memory of the particular node. Excess data may be written to the disk segment of the particular node. Reader threads running on the particular node reads data for the temporary table from the memory of the particular node and the disk segment of the particular node.

Claims

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

assigning multiple threads on each node of said multi-node database system for processing the query, said multiple threads including a plurality of writer threads and a plurality of reader threads;

wherein for each node of said multi-node database system;

one or more respective writer threads from said plurality of writer threads run on said each node, and

one or more respective reader threads from said plurality of reader threads run on said each node;

creating an in-memory temporary table for storing intermediate results generated for said query, wherein a memory of each node of said multi-node database system stores a respective local in-memory temporary table part;

on each node of said multi-node database system, the one or more respective writer threads writing to the respective local in-memory temporary table part;

on each node of said multi-node database system, generating a respective data structure based on the respective local in-memory temporary table part; and

on each node of said multi-node database system, based on the respective data structure, the one or more respective reader threads reading from the respective local in-memory temporary table part.

2. The method of claim 1, further comprising:

wherein said memory of each node of said multi-node database system comprises one or more memory segments; and

on each node of said multi-node database system, assigning a respective memory segment to each of the one or more respective writer threads.

3. The method of claim 2, wherein for each node of said multi-node database system, writing to the respective local in-memory temporary table part comprises each writer thread of said one or more respective writer threads writing to a respective memory segment assigned to said each writer thread.

4. The method of claim 2, wherein for each node of said multi-node database system, the respective data structure maps the respective local in-memory temporary table part to the respective one or more memory segments and reading from the respective local in-memory temporary table part comprises reading from the respective one or more memory segments.

5. The method of claim 2, the method further comprising:

for each node of said multi-node database system;

wherein a respective disk accessible to said each node comprises one or more disk segments; and

assigning a respective disk segment of the respective disk to each of the one or more respective writer threads.

6. The method of claim 5, the method further comprising:

for each node of said multi-node database system, each writer thread of the one or more respective writer threads writing to the respective disk segment assigned to the writer thread when additional space is needed than is available in a respective memory segment.

7. The method of claim 6, wherein for each node of said multi-node database system:

the respective data structure maps the respective in-memory temporary table part to the respective one or more memory segments and the respective one or more disk segments; and

reading from the respective local in-memory temporary table part comprises reading from the respective one or more memory segments and the respective one or more disk segments.

8. One or more non-transitory computer-readable media storing instructions, wherein the instructions include instructions which, when executed by one or more hardware processors, cause:

assigning multiple threads on each node of a multi-node database system for processing a query, said multiple threads including a plurality of writer threads and a plurality of reader threads;

wherein for each node of said multi-node database system;

one or more respective writer threads from said plurality of writer threads run on said each node, and

one or more respective reader threads from said plurality of reader threads run on said each node;

creating an in-memory temporary table for storing intermediate results generated for said query, wherein a memory of each node of said multi-node database system stores a respective local in-memory temporary table part;

on each node of said multi-node database system, the one or more respective writer threads writing to the respective local in-memory temporary table part;

on each node of said multi-node database system, generating a respective data structure based on the respective local in-memory temporary table part; and

on each node of said multi-node database system, based on the respective data structure, the one or more respective reader threads reading from the respective local in-memory temporary table part.

9. The one or more non-transitory computer-readable media of claim 8, wherein said memory of each node of said multi-node database system comprises one or more memory segments, the instructions further including:

instructions which, when executed by one or more hardware processors, cause on each node of said multi-node database system, assigning a respective memory segment to each of the one or more respective writer threads.

10. The one or more non-transitory computer-readable media of claim 9, wherein for each node of said multi-node database system, writing to the respective local in-memory temporary table part comprises each writer thread of said one or more respective writer threads writing to a respective memory segment assigned to each writer thread.

11. The one or more non-transitory computer-readable media of claim 9, wherein for each node of said multi-node database system, the respective data structure maps the respective local in-memory temporary table part to the respective one or more memory segments and reading from the respective local in-memory temporary table part comprises reading from the respective one or more memory segments.

12. The one or more non-transitory computer-readable media of claim 9, the instructions further including:

instructions which, when executed by one or more hardware processors, cause for each node of said multi-node database system;

wherein a respective disk accessible to said each node comprises one or more disk segments; and

assigning a respective disk segment of the respective disk to each of the one or more respective writer threads.

13. The one or more non-transitory computer-readable media of claim 12, the instructions further including:

instructions which, when executed by one or more hardware processors, cause for each node of said multi-node database system, each writer thread of the one or more respective writer threads writing to the respective disk segment assigned to the writer thread when additional space is needed than is available in a respective memory segment.

14. The one or more non-transitory computer-readable media of claim 13, wherein for each node of said multi-node database system:

the respective data structure maps the respective in-memory temporary table part to the respective one or more memory segments and the one or more disk segments; and

reading from the respective local in-memory temporary table part comprises reading from the respective one or more memory segments and the respective one or more disk segments.