Consistent client-side caching for fine grained invalidations

Patent metadata

Field	Value
Publication number	US-11567934-B2
Application number	US-201815958517-A
Country	US
Kind code	B2
Filing date	Apr 20, 2018
Priority date	Apr 20, 2018
Publication date	Jan 31, 2023
Grant date	Jan 31, 2023

Abstract

An approach for implementing function semantic based partition-wise SQL execution and partition pruning in a data processing system is provided. The system receives a query directed to a range-partitioned table and determines if operation key(s) of the query include function(s) over the table partitioning key(s). If so, the system obtains a set of values corresponding to each partition by evaluating the function(s) on a low bound and/or a high bound table partitioning key value corresponding to the partition. The system may then compare the sets of values corresponding to different partitions and determine whether to aggregate results obtained by executing the query over the partitions based on the comparison. The system may also determine whether to prune any partitions from processing based on a set of correlations between the set of values for each partition and predicate(s) of the query including function(s) over the table partitioning key(s).

First claim

What is claimed is:

1. A method for caching query results in a client-side cache, comprising:
caching results for executing a query as cached query results at a client-side cache stored at a client, wherein the query is directed to a database table stored at a server that is partitioned into a plurality of partitions;
generating, for a plurality of clients, a client status table and a registration table stored at a server, wherein the client status table interacts with the registration table to determine whether an update to a table partition relied upon by one or more queries associated with a specific client of the plurality of clients has occurred that invalidates the cached query results at the client-side cache of the specific client, by:
generating the client status table comprising a first client status table column identifying a client and a second client status table column identifying information corresponding to a client interaction time with the server, wherein the client status table is used to determine a last invalidation check of the client-side cache for the client, and
generating the registration table comprising a first registration table column identifying the query, a second registration table column identifying a table partition from a plurality of partitions that was relied upon by the query to generate the cached query results that are cached in the client-side cache, and a third registration table column identifying information corresponding to a last change time to the table partition from the plurality of partitions that was relied upon by the query; and

executing invalidation of the cached query results at the specific client in response to the last change time to the table partition from the registration table for the one or more queries associated with the specific client being greater than the client interaction time from the client status table corresponding to the last invalidation check of the client-side cache for the specific client.

2. The method of claim 1 , wherein the registration table further comprises one or more additional entries corresponding to a level of granularity less than a granularity of the table corresponding to at least one of column-based information, row-based information, or bind-variable information for a bind variable pertaining to the query.

3. The method of claim 1 , further comprising:
sending first reference information with a server request indicating a state of a database after a previous database server request by the client;
receiving second reference information with the query results indicating a current state of the database; and
updating the first reference information with the second reference information.

4. The method of claim 1 , further comprising:
invalidating some or all of the cached query results that have been indicated as invalid for a client session, wherein the some or all of the cached query results comprise cached results for the client session, and the cached query results relate to one or more uncommitted database changes made by the client with the client session.

5. The method of claim 3 , further comprising receiving one or more cached result identifiers, wherein the one or more cached result identifiers relate to one or more transactions that occurred between the first reference information and the second reference information.

6. The method of claim 1 , further comprising:
executing the invalidation at a server in response to the cached query results at the client-side cache being invalid due to a database operation based at least in part upon a first commit number or timestamp associated with a last change associated with the query and a second commit number or timestamp associated with the database operation.

7. The method of claim 1 , further comprising:
maintaining a client status table that comprises an entry for the client and a last check of a commit number or timestamp for the client;
wherein the last check of the commit number or timestamp for the client tracks when the client was last notified of a possible invalidation.

8. The method of claim 1 , wherein a registration entry in the registration table further comprises a timestamp or a commit number associated with the registration entry, wherein if a subsequent change is applied to any of a plurality of queries identified in the registration table then the timestamp or the commit number associated with the registration entry changes.

9. The method of claim 1 , wherein invalidation information comprising one or more invalid cached result identifiers corresponding to the cached query results are piggybacked onto a server response that was issued for another query that is different from the query that produced the cached query results.

10. A computer program product comprising a non-transitory computer readable medium having executable code which, when executed by a processor, causes the processor to perform a set of acts for caching query results in a client-side cache, the set of acts comprising:
caching results for executing a query as cached query results at a client-side cache stored at a client, wherein the query is directed to a database table stored at a server that is partitioned into a plurality of partitions;
generating, for a plurality of clients, a client status table and a registration table stored at a server, wherein the client status table interacts with the registration table to determine whether an update to a table partition relied upon by one or more queries associated with a specific client of the plurality of clients has occurred that invalidates the cached query results at the client-side cache of the specific client, by:
generating the client status table comprising a first client status table column identifying a client and a second client status table column identifying information corresponding to a client interaction time with the server, wherein the client status table is used to determine a last invalidation check of the client-side cache for the client, and
generating the registration table comprising a first registration table column identifying the query, a second registration table column identifying a table partition from a plurality of partitions that was relied upon by the query to generate the cached query results that are cached in the client-side cache, and a third registration table column identifying information corresponding to a last change time to the table partition from the plurality of partitions that was relied upon by the query; and

executing invalidation of the cached query results at the specific client in response to the last change time to the table partition from the registration table for the one or more queries associated with the specific client being greater than the client interaction time from the client status table corresponding to the last invalidation check of the client-side cache for the specific client.

11. The computer program product of claim 10 , wherein the registration table further comprises one or more additional entries corresponding to a level of granularity less than a granularity of the table corresponding to at least one of column-based information, row-based information, or bind-variable information for a bind variable pertaining to the query.

12. The computer program product of claim 10 , wherein the set of acts by the executable code further comprises:
sending first reference information with a server request indicating a state of a database after a previous database server request by the client;
receiving second reference information with the results indicating a current state of the database; and
updating the first reference in

Assignees

• Oracle Int Corp

Inventors

• Bastawala Mehul
• Mullick Atrayee
• Eadon George
• Kumar Ramesh

Classifications

• G06F16/24539 Primary

  using cached or materialised query results · CPC title

• G06F2212/1044

  Space efficiency improvement · CPC title

• G06F16/2379 Primary

  Updates performed during online database operations; commit processing · CPC title

• G06F12/0891

  using clearing, invalidating or resetting means · CPC title