Database Caching and Invalidation using Database Provided Facilities for Query Dependency Analysis

• US 20060271510A1
• Filed: 05/25/2006
• Published: 11/30/2006
• Est. Priority Date: 05/25/2005
• Status: Abandoned Application

• Current Assignee: Terracotta, Inc. (Software AG)
• Original Assignee: Terracotta, Inc. (Software AG)
• Inventors: Harward, Nathaniel D., Voskoboynik, Alexander, Geweke, Andrew R.
• Application Number:
  US11/420,451
• Publication Number:
  US 20060271510A1
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 11/1479 Generic software techniques for error detection or fault masking
• CPC Class Codes: G06F 11/203 using migration
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: G06F 16/24552 Database cache management
• CPC Class Codes: G06F 2209/522 Manager
• CPC Class Codes: G06F 9/445 Program loading or initiating bootstrapping G06F9/4401; security arrangements for program loading or initiating G06F21/57
• CPC Class Codes: G06F 9/45504 Abstract machines for programme code execution, e.g. Java virtual machine [JVM], interpreters, emulators
• CPC Class Codes: G06F 9/52 Program synchronisation; Mutual exclusion, e.g. by means of semaphores
• CPC Class Codes: G06F 9/526 Mutual exclusion algorithms
• CPC Class Codes: G06F 9/544 Buffers; Shared memory; Pipes

Abstract

Database data is maintained reliably and invalidated based on actual changes to data in the database. Updates or changes to data are detected without parsing queries submitted to the database. The dependencies of a query can be determined by submitting a version of the received query to the database through a native facility provided by the database to analyze how query structures are processed. The caching system can access the results of the facility to determine the tables, rows, or other partitions of data a received query is dependent upon or modifies. An abstracted form of the query can be cached with an indication of the tables, rows, etc. that queries of that structure access or modify. The tables a write or update query modifies can be cached with a time of last modification. When a query is received for which the results are cached, the system can readily determine dependency information for the query, the last time the dependencies were modified, and compare this time with the time indicated for when the cached results were retrieved. By passing versions of write queries to the database, updates to the database can be detected.

Claims

1. A method of caching database query results, comprising:

receiving a request for information from a database;

determining if previously received results for said request are maintained locally;

if previously received results are not maintained locally for said request;

passing a form of said request to said database by calling a native analysis facility at said database to assess how said database processes requests of said form;

accessing results of said assessment by said native analysis facility;

determining from said results one or more dependencies of said database for requests of said form;

maintaining data indicating that requests of said form for said database include said one or more dependencies;

passing said request to said database and receiving results; and

maintaining information including said request and said results of said request.

2. The method of claim 1, wherein:

maintaining said information includes maintaining an indication of a time said results were determined.

3. The method of claim 2, further comprising:

if previously received results are being maintained for said request;

accessing said data to determine said one or more dependencies;

accessing information indicating a time of last update to said one or more dependencies;

determining whether said previously received results are valid by comparing said indication of said time said results were determined with said time of last update to said one or more dependencies;

returning said results in response to said request if said previously received results are valid.

4. The method of claim 3, further comprising:

if said previously received results are determined to be invalid;

passing said request to said database and receiving updated results;

updating said information maintained for said request to indicate said updated results.

5. The method of claim 1, wherein:

said native analysis facility is “

EXPLAIN PLAN.”

6. The method of claim 1, wherein:

said native analysis facility is a debugging facility provided by said database.

7. The method of claim 1, wherein:

said native analysis facility is a performance analysis facility provided by said database.

8. The method of claim 1, wherein:

said one or more dependencies of said database for requests of said form are one or more tables of said database upon which requests of said form depend for results.

9. The method of claim 1, wherein:

said one or more dependencies of said database for requests of said form are one or more rows of one or more tables of said database upon which requests of said form depend for results.

10. The method of claim 1, further comprising, if previously received results are not being maintained for said request:

abstracting said request by removing any literal expressions included therein to generate said form of said request;

wherein said request is a database query and said form of said request is a skeleton of said database query.

11. The method of claim 1, wherein:

receiving said request includes receiving said request at a first caching system;

passing said form of said request includes passing said form of said request from a second caching system to said database after receiving at least one of said request and said request at said second caching system from said first caching system; and

maintaining said data and maintaining said information include maintaining said data and said information at said second caching system.

12. The method of claim 11, wherein:

maintaining said data and maintaining said information include maintaining said data and information at said first caching system and said second caching system.

13. A database caching system, comprising:

a database including a native analysis facility to analyze query structures;

a caching intermediary in communication with said database and an application accessing said database, said caching intermediary maintains one or more queries and results received from said database in response to said one or more queries, said caching intermediary receives a first query from said application and determines if said first query and previously received results for said first query are maintained by said caching intermediary, said caching intermediary passes a form of said first query to said native analysis facility at said database if said caching intermediary is not maintaining results for said first query and accesses results of said analysis facility to determine one or more tables of said database upon which queries of said form depend, said caching intermediary further maintains said form of said first query and an indication of said one of more tables of said database upon which queries of said form depend.

14. The database caching system of claim 13, wherein:

said caching intermediary maintains an indication of dependencies from said database and a time of last modification of each of said dependencies;

said caching intermediary receives a second query from said application and after determining that said second query is not maintained at said caching intermediary, passes a form of said second query to said native analysis facility and accesses results of said analysis facility to determine if said second query reads or modifies data at said database, said caching intermediary determines from said results one or more tables of said database modified by queries of said form of said second query if said second query updates data at said database, said caching intermediary passes said second query to said database and receives results in return, said caching intermediary maintains said second query with an indication of said results of said second query and also maintains an indication of said one or more tables of said database modified by queries of said form of said second query with an indication of a time of most recent modification to said one or more tables.

15. The database caching system of claim 14, wherein:

said caching intermediary maintains said form of said second query with an indication of said one or more tables modified by queries of said form of said second query.

16. The database caching system of claim 13, wherein:

said form of said first query is a structure of said first query generated by removing any literal expressions from said first query.

17. The database caching system of claim 13, wherein:

said caching intermediary includes a first caching intermediary at a first processing system and a second caching intermediary at a second processing system.

18. The database caching system of claim 17, wherein:

said caching intermediary further includes one or more additional caching intermediaries;

said second caching intermediary receives queries received at said first caching intermediary and said one or more additional caching intermediaries.

19. One or more processor readable storage devices having processor readable code embodied on said one or more processor readable storage devices, said processor readable code for programming one or more processors to perform a method comprising:

receiving a request for information from a database;

determining if previously received results for said request are maintained locally; and

if previously received results are not maintained locally for said request;

passing a form of said request to said database by calling a native analysis facility at said database to assess how said database processes requests of said form;

accessing results of said assessment by said native analysis facility;

determining from said results one or more dependencies of said database for requests of said form;

maintaining data indicating that requests of said form for said database include said one or more dependencies;

passing said request to said database and receiving results; and

maintaining information including said request and said results of said request.