System and method for adaptive database caching

• US 20060026154A1
• Filed: 07/30/2004
• Published: 02/02/2006
• Est. Priority Date: 07/30/2004
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Bornhoevd, Christof, Reinwald, Berthold, Pirahesh, Mir Hamid, Krishnamurthy, Saileshwar, Mohan, Chandrasekaran, Altinel, Mehmet
• Granted Patent: US 7,395,258 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24552 Database cache management
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching

Abstract

A local database cache enabling persistent, adaptive caching of either full or partial content of a remote database is provided. Content of tables comprising a local cache database is defined on per-table basis. A table is either: defined declaratively and populated in advance of query execution, or is determined dynamically and asynchronously populated on-demand during query execution. Based on a user input query originally issued against a remote DBMS and referential cache constraints between tables in a local database cache, a Janus query plan, comprising local, remote, and probe query portions is determined. A probe query portion of a Janus query plan is executed to determine whether up-to-date results can be delivered by the execution of a local query portion against a local database cache, or whether it is necessary to retrieve results from a remote database by executing a remote query portion of Janus query plan.

Claims

1. A method for maintaining a consistent, local instance of a remote database management system (DBMS), said method comprising:

a. creating a local cache database of at least one table caching dynamically changing subsets of data of said at least one table in a remote backend database;

b. executing against said local cache database, a query issued against said remote backend database;

c. asynchronously populating said at least one table of said local cache database based on outcome of said execution step; and

d. asynchronously updating content of said at least one table in said local cache database with respect to changes to said at least one table in said remote backend database.

2. A method for maintaining a consistent, local instance of a remote database, as per claim 1, wherein said at least one table in local cache database is semantically equivalent to said at least one table in remote DBMS.

3. A method for maintaining a consistent, local instance of a remote database, as per claim 1, wherein said at least one table in said local cache database is populated asynchronously if said outcome of said execution of query indicates a cache miss.

4. A method for maintaining a consistent, local instance of a remote database, as per claim 1, wherein consistency of said local cache database, with respect to said dynamically changing subsets of data, is determined by a set comprised of:

cache keys and referential cache constraints.

5. A method for maintaining a consistent, local instance of a remote database, as per claim 4, wherein said referential cache constraint is based on a pair of columns, each of said columns from a distinct cache table;

each of said distinct cache tables having correlated value pairs from said pair of columns;

each of said distinct cache tables satisfying conditions necessary to allow a join operation to be executed between said columns in pair.

6. A method for maintaining a consistent, local instance of a remote database, as per claim 4, wherein said cache key constraint is based on a cache key column from a table from said local cache database;

said cache key column maintained as domain complete;

said table from said local cache database populated with missing cache key column values determined based on said cache key column.

7. A method for maintaining a consistent, local instance of a remote database, as per claim 6, wherein a domain complete property is maintained for said column of said table in local cache database if the same rows in said table in said remote backend database exist in said table in local cache database for a given value for said column;

satisfying the condition to execute an equality predicate for said column in said local cache database.

8. A method for maintaining a consistent, local instance of a remote database, as per claim 4, wherein said distinct cache tables having a referential cache relationship are member tables of a common cache group if said distinct cache tables are populated based on said at least one cache key column of a root cache table.

9. A method for maintaining a consistent, local instance of a remote database, as per claim 8, wherein said population is performed by a cache maintenance component;

said cache maintenance component provided as input, an outcome of said execution of query indicating a cache miss and said set of referential cache constraints.

10. A method for maintaining a consistent, local instance of a remote database, as per claim 9, wherein said cache maintenance component derives a set of rows from said outcome of execution step indicating said cache miss, and from said set of referential cache constraints.

11. A method for maintaining a consistent, local instance of a remote database, as per claim 4, wherein said cache maintenance component performs, based on derived set of rows from said cache constraints, cache maintenance functions comprising:

a. copying said derived row data to said at least one table in said local cache database mirroring said at least one table in said remote DBMS, if an insertion occurs at said at least one table in said remote DBMS;

b. deleting said derived row data from said at least one table in said local cache database mirroring said at least one table in said remote DBMS, if a deletion occurs at said at least one table in said remote DBMS; and

c. changing said at least one table in said local cache database mirroring said at least one table in said remote DBMS with said derived row data, if a change occurs at said at least one table in said remote DBMS.

12. A method for maintaining a consistent, local instance of a remote database, as per claim 4, wherein said cache maintenance component performs garbage collection functions compromising:

generating and issuing statements derived from said cache constraints against said local cache database in deletion of rows from said at least one table of local cache database.

13. A method for maintaining a consistent, local instance of a remote database, as per claim 12, wherein said deletion is based on any of:

statistics for said local cache database, a specified maximum size for said local cache database, and said set of referential cache constraints.

14. A method for determining, from a user input query, a Janus query plan for execution against a local cache database mirroring content of a remote DBMS and against said remote DBMS;

said method comprising;

a. generating a probe query portion for said Janus query plan based on equality predicates in said input query;

b. creating a remote portion of said Janus query plan by substituting for, in said input query, a set of references indicating tables in said local DBMS, a set of references indicating tables in said remote database;

c. creating a local portion of said Janus query plan by cloning a query plan for said user input query and substituting for a set of cloned references indicating tables in said remote DBMS, a set of references indicating tables in said local cache database;

said creation based on results of determination of cache table availability; and

d. associating said portions with said Janus query plan using a conditional switch operator.

15. A method for determining, from a user input query, as per claim 14, wherein said tables indicated by said set of references to local cache database are semantically equivalent to said tables in remote DBMS indicated by said set of cloned references.

16. A method for determining, from a user input query, as per claim 14, wherein said cache table availability determination is comprised of:

a. creating a subset of tables from said set of tables in said local cache database eligible to provide results to said local portion of Janus query plan;

said eligibility based on a set of cache constraints; and

b. checking whether tables in said set of eligible tables contain row and column information necessary to provide results to said local portion of Janus query plan.

17. A method for determining, from a user input query, as per claim 14, wherein said probe query result is used to select either of:

said local portion or said remote portion of Janus query plan for execution;

said local portion of Janus query plan selected, if tables in said set of eligible tables contain said necessary row and column values, and said remote portion of Janus query plan is selected, otherwise.

18. A method for determining, from a user input query, as per claim 17, wherein said local portion of Janus query plan is executed against said set of eligible tables, if said local portion of Janus query plan is selected, and said remote portion of Janus query plan is executed against said tables in remote DBMS, if said remote portion of Janus query plan is selected.

19. A method for determining, from a user input query, as per claim 18, wherein said determination is made during time of execution.

20. A method for determining from a user input query, as per claim 14, wherein said probe query portion for said Janus query plan can be generated to include predicates for determining freshness of said eligible tables used in said local portion of said Janus query plan based on refresh times of said eligible cache tables.

21. A method for determining from a user input query, as per claim 20, wherein said probe query result is used to select either of:

said local portion or said remote portion of Janus query plan for execution;

said local portion of Janus query plan selected, if tables in said set of eligible tables satisfy said necessary freshness values, and said remote portion of Janus query plan is selected, otherwise.

22. A method for determining, from a user input query, as per claim 21, wherein said local portion of Janus query plan is executed against said set of eligible tables, if said local portion of Janus query plan is selected, and said remote portion of Janus query plan is executed against said tables in remote DBMS, if said remote portion of Janus query plan is selected.

23. A method for determining, from a user input query, as per claim 22, wherein said determination is made during time of execution.

24. An system for maintaining a consistent, local instance of a remote database management system (DBMS);

said system comprising;

a. a local cache database of at least one table mirroring content of at least one table in said remote DBMS;

b. a user update query issued against said remote DBMS;

c. a cache maintenance component writing content to said at least one table in said local cache database with respect to any of;

changes, insertions, or deletions to said at least one table in remote DBMS;

or a cache miss resulting from executing said user input query.

25. An article of manufacture comprising a computer usable medium having computer readable program code embodied therein which implements the maintenance of a consistent, local instance of a remote database management system (DBMS);

said medium comprising modules;

a. creating a local cache database of at least one table caching dynamically changing subsets of data of said at least one table in a remote backend database;

b. executing against said local cache database, a query issued against said remote backend database;

c. asynchronously populating said at least one table of said local cache database based on outcome of said execution step; and

d. asynchronously updating content of said at least one table in said local cache database with respect to changes to said at least one table in said remote backend database.

26. An article of manufacture comprising a computer usable medium having computer readable program code embodied therein which implements a Janus query plan enabled to execute against a local cache database mirroring content of a remote DBMS and against said remote DBMS, from a user input query;

said medium comprising modules;

a. generating a probe query portion for said Janus query plan based on either of;

equality predicates or refresh times of eligible cache tables in said input query;

b. determining cache table availability by accessing data at first subset of tables in said local cache database;

said first subset chosen on the basis of equality predicates in said input query;

c. cloning said input query to determine a remote portion of said Janus query plan;

d. creating a local portion of said Janus query plan by cloning said input query and changing a set of cloned references indicating tables in said remote DBMS to a set of references indicating tables in said local cache database;

said creation based on results of said cache table availability determination; and

e. associating said portions with said Janus query plan using conditional switch operator.