Query based invalidation subscription

• US 20050055384A1
• Filed: 06/10/2004
• Published: 03/10/2005
• Est. Priority Date: 09/04/2003
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Sinha, Bipul, Ganesh, Amit, Vemuri, Srinivas, De Groot, Michael, Bing, Eric W.
• Granted Patent: US 7,962,481 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24552 Database cache management
• CPC Class Codes: G06F 16/24568 Data stream processing; Continuous queries

Abstract

A query result set is an example of a type of body of data whose content is defined by a query. Modifications to a database can change the result of a query and therefore what the content of such a body of data, such as a result set, should be. Active Queries is an approach that enables a database server to detect database changes that alter the result of a query registered for a client and to notify a client of those changes. The notifications may be sent to, for example, a cache manager responsible for maintaining coherency of a result set cache generated by the query. The notification notifies the cache manager of when and/or what changes are needed to maintain the coherency of the cache.

Claims

1. A method for tracking changes to data, the method comprising the steps of:

a database server registering a query, wherein said database server manages access to a database;

after registering the query, determining that a change to the database may change the result of said query; and

in response to determining that said change to the database affects the result of said query, transmitting a notification to indicate that a change to the database may have changed the result of the query.

2. The method of claim 1, wherein the steps further include:

computing said query to generate the result as said body of data; and

maintaining coherency between the database and the body of data based on said notification.

3. The method of claim 2,

wherein the body of data contains first data items that are copies of a current version of second data items in the database;

and

wherein the step of maintaining coherency includes invalidating, in response to changing at least a second data item of said second data items, a first data item of said first data items that correspond to said at least one second data item.

4. The method of claim 3, wherein invalidating includes marking said first data item as an invalid copy.

5. The method of claim 3, wherein:

said first data item is associated with a coherency interval; and

wherein the step of invalidating includes establishing a new coherency interval for said first data item.

6. The method of claim 2, wherein the step of maintaining coherency includes maintaining coherency between said body of data and said database according to ACID principles.

7. The method of claim 2,

wherein said database server belongs to a multi-tier database system that includes a middle tier;

and

wherein said body of data is a result set generated for a client of the database server and stored in a result set cache in the middle tier.

8. The method of claim 7, wherein:

a first cache copy in said result set cache corresponds to a first data item in said database;

the method further includes the steps of;

executing a transaction initiated by said client;

committing said transaction;

before committing said transaction on said database server;

said database server receiving a request to change said first data item;

said database server making said change to said first data item, wherein making said change causes said database server to make another change to another data item that belongs to the result said query;

the step of transmitting a notification includes transmitting a notification that indicates that said other change may have changed the result of the query.

9. The method of claim 1, wherein:

the method further includes;

said database server registering said query in response to a request from a subscriber, and

said database server committing a plurality of transactions, including a transaction initiated by a client different than said subscriber; and

the step of transmitting a notification includes transmitting, after committing said transaction, a notification to said subscriber that indicates that a change made by said transaction may have changed the result of the query.

10. The method of claim 1, wherein:

the method further includes;

said database server registering said query in response to a request from a subscriber, and

said database server committing a plurality of transactions, including a transaction initiated by said subscriber; and

the step of transmitting a notification includes transmitting, before committing said transaction, a notification to said subscriber that indicates that a change made by said transaction may have changed the result of the query.

11. The method of claim 1, wherein:

the method further includes;

said database server registering said query in response to request from a subscriber;

said database server committing a plurality of transactions, including a transaction initiated by said subscriber; and

the step of transmitting a notification includes transmitting, before committing said transaction, a notification to said subscriber that indicates that a change made by said transactions may have changed the result of the query.

12. The method of claim 1, the method further including the step of receiving the query, said query containing data that specifies that said database server should inform a client of changes to the result of said query.

13. The method of claim 12, wherein the query specifies a period of time during which said database server should inform the client of changes to the result of the query.

14. The method of claim 12, wherein the query specifies a pseudo-column that contains an identifier that the database server uses to identify the query.

15. The method of claim 12, wherein the query specifies a routine that said database server may invoke to transmit said notification.

16. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 1.

17. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 2.

18. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 3.

19. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 4.

20. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 5.

21. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 6.

22. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 7.

23. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 8.

24. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 9.

25. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 10.

26. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 11.

27. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 12.

28. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 13.

29. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 14.

30. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 15.

31. A computer-readable medium carrying one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the method recited in claim 16.