Caching external data sources for SQL processing

• US 9,251,210 B2
• Filed: 04/19/2013
• Issued: 02/02/2016
• Est. Priority Date: 04/19/2013
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Chaudhry, Atif, Iyer, Chandrasekharan
• Primary Examiner(s): Choi, Yuk Ting
• Application Number:
  US13/866,866
• Publication Number:
  US 20140317084A1
• Time in Patent Office: 1,019 Days
• Field of Search: None
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: G06F 16/24552 Database cache management

Abstract

Techniques are provided for caching external data sources for SQL processing. According to an embodiment, a database system receives a query that requires foreign data from an external data source. In response to receiving the query, the database system determines whether the foreign data currently resides in a cache of the database system. In response to determining that the foreign does not currently reside in the cache, the database system retrieves the foreign data from the external data source, maps the foreign data to a column granule of a table, and stores the column granule in the cache. In another embodiment, the database system receives a second query that requires the foreign data. In response to determining that the foreign data is cached with the stored column granule, the database system retrieves the column granule from the cache.

Claims

1. A method for optimizing query processing, the method comprising:

storing, at a database system, metadata for an external table;

wherein the metadata for the external table defines one or more columns for the external table and how data from an external data source maps to the one or more columns of the external table;

receiving, at the database system, a query that requires foreign data from the external data source;

in response to receiving the query, determining whether the foreign data currently resides in a cache of the database system;

in response to determining that the foreign data does not currently reside in the cache;

retrieving the foreign data from the external data source;

wherein the external data source has a different structure and format than the database system and the foreign data is stored in a particular format and structure associated with the external data source;

mapping, based on the metadata for the external table, the foreign data from the particular format and structure associated with the external data source to a column granule of the external table; and

storing the column granule in the cache.

2. The method of claim 1, wherein the query is a first query, the method further comprising:

receiving, at the database system, a second query that requires second foreign data from the external data source;

in response to receiving the second query, determining whether the second foreign data currently resides in the cache of the database system;

in response to determining that the second foreign data does not currently reside in the cache;

retrieving the foreign data from the external data source;

mapping the foreign data to a column granule of a table; and

storing the column granule in the cache.

3. The method of claim 1, wherein the column granule is an entire column referenced by the query.

4. The method of claim 1,

wherein the column granule is a first portion of a column referenced by the query, wherein the cache is a first result cache of a first database server;

wherein a second result cache of a second database server stores a second column granule;

wherein the second column granule is a second portion of the column referenced by the query.

5. The method of claim 1, wherein the query is a first query, the method further comprising:

receiving, at the database system, a second query that requires the foreign data from the external data source;

wherein the second query includes a different expression than the first query;

in response to receiving the second query, determining whether the foreign data currently resides in the cache of the database system;

in response to determining that the foreign data currently resides in the cache;

retrieving, from the cache, the column granule populated with the foreign data currently residing in the cache.

6. The method of claim 1, wherein the query is a first query, the method further comprising:

receiving, at the database system, a second query that requires the foreign data from the external data source;

in response to receiving the second query, determining whether the foreign data currently resides in the cache of the database system;

querying the external data source to determine whether the foreign data is still valid;

in response to determining that the foreign data is no longer valid;

retrieving updated foreign data from the external data source;

mapping the updated foreign data to the column granule of the external table; and

storing the column granule with the updated foreign data in the cache.

7. The method of claim 1, further comprising:

receiving, at the database system, a notification from the external data source that the foreign data in the external data source has changed;

in response to receiving the notification, invalidating the column granule in the cache.

8. The method of claim 1, further comprising:

determining statistics associated with the foreign data;

storing, in the cache, the statistics associated with the foreign data.

9. The method of claim 1, further comprising:

evicting a second column granule of the external table from the cache;

wherein evicting the second column granule of the external table comprises;

deleting the second column granule from the cache;

generating a temporary file to store the second column granule;

storing a pointer to the temporary file in the cache.

10. The method of claim 1,

wherein the column granule is part of a view of the external table that has been annotated to enable caching of foreign data;

wherein a second column granule which does not belong to the view of the external table that has been annotated is not stored in the cache.

11. One or more non-transitory computer-readable media storing sequences of instructions, that when executed by one or more processors, cause:

storing, at a database system, metadata for an external table;

wherein the metadata for the external table defines one or more columns for the external table and how data from an external data source maps to the one or more columns of the external table;

receiving, at the database system, a query that requires foreign data from the external data source;

in response to receiving the query, determining whether the foreign data currently resides in a cache of the database system;

in response to determining that the foreign data does not currently reside in the cache;

retrieving the foreign data from the external data source;

wherein the external data source has a different structure and format than the database system and the foreign data is stored in a particular format and structure associated with the external data source;

mapping, based on the metadata for the external table, the foreign data from the particular format and structure associated with the external data source to a column granule of the external table; and

storing the column granule in the cache.

12. The non-transitory computer-readable media of claim 11, wherein the query is a first query, and the sequences of instructions include instructions that when executed by one or more processors, cause:

receiving, at the database system, a second query that requires second foreign data from the external data source;

in response to receiving the second query, determining whether the second foreign data currently resides in the cache of the database system;

in response to determining that the second foreign data does not currently reside in the cache;

retrieving the foreign data from the external data source;

mapping the foreign data to a column granule of a table; and

storing the column granule in the cache.

13. The non-transitory computer-readable media of claim 11, wherein the column granule is an entire column referenced by the query.

14. The non-transitory computer-readable media of claim 11,

wherein the column granule is a first portion of a column referenced by the query;

wherein the cache is a first result cache of a first database server;

wherein a second result cache of a second database server stores a second column granule;

wherein the second column granule is a second portion of the column referenced by the query.

15. The non-transitory computer-readable media of claim 11, wherein the query is a first query, and the sequences of instructions include instructions, that when executed by one or more processors, cause:

receiving, at the database system, a second query that requires the foreign data from the external data source;

wherein the second query includes a different expression than the first query;

in response to receiving the second query, determining whether the foreign data currently resides in the cache of the database system;

in response to determining that the foreign data currently resides in the cache;

retrieving, from the cache, the column granule currently residing in the cache.

16. The non-transitory computer-readable media of claim 11, wherein the query is a first query, and the sequences of instructions include instructions, that when executed by one or more processors, cause:

receiving, at the database system, a second query that requires the foreign data from the external data source;

in response to receiving the second query, determining whether the foreign data currently resides in the cache of the database system;

querying the external data source to determine whether the foreign data is still valid;

in response to determining that the foreign data is no longer valid;

retrieving updated foreign data from the external data source;

mapping the updated foreign data to the column granule of the external table; and

storing the column granule populated with the updated foreign data in the cache.

17. The non-transitory computer-readable media of claim 11,

wherein the sequences of instructions include instructions, that when executed by one or more processors, cause:

receiving, at the database system, a notification from the external data source that the foreign data in the external data source has changed;

in response to receiving the notification, invalidating the column granule in the cache.

18. The non-transitory computer-readable media of claim 11,

wherein the sequences of instructions include instructions, that when executed by one or more processors, cause:

determining statistics associated with the foreign data;

storing, in the cache, the statistics associated with the foreign data.

19. The non-transitory computer-readable media of claim 11, wherein

the sequences of instructions include instructions, that when executed by one or more processors, cause:

evicting a second column granule of the external table from the cache;

wherein evicting the second column granule of the external table comprises;

deleting the second column granule from the cache;

generating a temporary file to store the second column granule;

storing a pointer to the temporary file in the cache.

20. The non-transitory computer-readable media of claim 11,

wherein the column granule is part of a view of the external table that has been annotated to enable caching of foreign data;

wherein a second column granule for which does not belong to the view of the external table that has been annotated is not stored in the cache.