Caching external data sources for SQL processing

Patent metadata

Field	Value
Publication number	US-9916352-B2
Application number	US-201615012742-A
Country	US
Kind code	B2
Filing date	Feb 1, 2016
Priority date	Apr 19, 2013
Publication date	Mar 13, 2018
Grant date	Mar 13, 2018

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.

First claim

What is claimed is:

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 first query that requires foreign data from the external data source;
in response to receiving the first 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;
mapping, based on the metadata for the external table, the foreign data to a first column granule of the external table; and
storing the first column granule in the cache;

generating a query result for the first query using the foreign data stored in the cache;
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;
in response to determining that the foreign data currently resides in the cache, retrieving, from the cache, the first column granule populated with the foreign data currently residing in the cache;
wherein the method is performed by one or more computing devices.

2. The method of claim 1 , wherein the first column granule is for a column referenced by the first and second queries.

3. The method of claim 1 ,
wherein the first column granule is a first portion of a column referenced by the first and second queries, 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 first and second queries.

4. The method of claim 1 ,
wherein the second query includes a different expression than the first query.

5. The method of claim 1 , further comprising:
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 first column granule of the external table; and
storing the first column granule with the updated foreign data in the cache;

generating a query result for the second query using the updated foreign data stored in the cache.

6. 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 first column granule in the data cache.

7. 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.

8. 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.

9. The method of claim 1 ,
wherein the first column granule holds data for a column of a view of said external table, said view being annotated to enable caching of foreign data.

10. One or more non-transitory computer-readable media storing instructions, which when executed by one or more hardware 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 first query that requires foreign data from the external data source;
in response to receiving the first 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;
mapping, based on the metadata for the external table, the foreign data to a first column granule of the external table; and
storing the first column granule in the cache;

generating a query result for the first query using the foreign data stored in the cache;
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;
in response to determining that the foreign data currently resides in the cache, retrieving, from the cache, the first column granule populated with the foreign data currently residing in the cache.

11. The non-transitory computer-readable media of claim 10 , wherein the first column granule is an entire column referenced by the first and second queries.

12. The non-transitory computer-readable media of claim 10 ,
wherein the first column granule is a first portion of a column referenced by the first and second queries;
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 first and second queries.

13. The non-transitory computer-readable media of claim 10 ,
wherein the second query includes a different expression than the first query.

14. The non-transitory computer-readable media of claim 10 , wherein the instructions further include instructions that, when executed by said one or more processors, cause:
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 first column granule of the external table; and
storing the first column granule populated with the updated foreign data in the cache;

generating a query result for the second query using the updated foreign data stored in the cache.

15. The non-transitory computer-readable media of claim 10 , wherein the instructions further include instructions that, when executed by said 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 first column granule in the cache.

16. The non-transitory computer-readable media of claim 10 , wherein the instructions further include instructions that, when executed by said one or more processors, cause:
determining statistics associated with the foreign data;
storing, in the cache, the statistics associated with the foreign data.

17. The non-transitory computer-readable media of claim 10 , wherein the instructions further include instructions that, when executed by said one or more processors, cause:
evicting a second column granule of the external

Assignees

• Oracle Int Corp

Inventors

• Chaudhry Atif
• Iyer Chandrasekharan

Classifications

• G06F16/24539 Primary

  using cached or materialised query results · CPC title

• G06F16/24552 Primary

  Database cache management · CPC title

• G06F17/3048

  Physics · mapped topic

• G06F17/30457 Primary

  Physics · mapped topic