Generating continuous query notifications

• US 8,037,040 B2
• Filed: 08/08/2008
• Issued: 10/11/2011
• Est. Priority Date: 08/08/2008
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Chitti, Subramanyam B., Vemuri, Srinivas S., Sinha, Bipul, Ganesh, Amit
• Primary Examiner(s): NGUYEN, CAM LINH T
• Application Number:
  US12/189,078
• Publication Number:
  US 20100036831A1
• Time in Patent Office: 1,159 Days
• Field of Search: 707688-690, 707694-696, 707/700, 707/702, 707/739, 707/758, 707/760, 707/769, 707/773
• US Class Current: 707/702
• CPC Class Codes: G06F 16/24568 Data stream processing; Continuous queries

Abstract

Techniques are described to allow a query to be registered as a persistent stored entity within the database, and to generate notifications as and when the query result changes continuously as long as the query continues to be registered with the database. According to one aspect, for a table referenced in a query, a filter condition is generated based, at least in part, on a predicate of the query. Then, the database server determines whether the filter condition is satisfied by either a before image of a row, or an after image of the row, that was modified by a transaction. If the filter condition is satisfied by either the before image or the after image, then the query is added to a first set of queries whose result sets may have been affected by the transaction. From among the first set of queries, a second set of queries that have result sets that were actually affected by the transaction is determined. Notifications are then sent based on the second set of queries.

Claims

1. A method for determining whether a change affects results produced by queries that belong to a set of registered queries, the method comprising:

for a table referenced in a query from the set of registered queries, generating a filter condition based, at least in part, on a predicate of the query;

determining whether the filter condition is satisfied by either (a) a before image of a row of the table, wherein the before image reflects the state of the row before the row was modified by a transaction, or (b) an after image of the row, wherein the after image of the row reflects the state of the row after the row was modified by the transaction;

in response to determining that the filter condition is satisfied by either the before image or the after image, adding the query to a first set of queries, from the set of registered queries, whose result sets may have been affected by the transaction;

wherein at least one query in the set of registered queries is not added to the first set of queries because a condition associated with the at least one query is not satisfied by either the before image or the after image;

from among the first set of queries, determining a second set of queries that have result sets that were actually affected by the transaction;

wherein one or more queries in the first set of queries are not in the second set of queries because the result sets of the one or more queries were not actually affected by the transaction; and

sending notifications based on the second set of queries;

wherein the method is performed by one or more computing devices.

2. The method of claim 1 wherein the step of determining a second set of queries includes, for each query in the first set of queries:

generating a delta query based on the query, wherein the delta query replaces references to the table with references to a change record log; and

executing the delta query to determine whether the result set of the query was affected by the transaction.

3. The method of claim 1 wherein:

the table is a first table in a join clause of the query;

the join clause joins the first table with a second table;

the step of generating a filter condition include;

determining values that satisfied the join clause during an execution of the query that occurred prior to the transaction; and

generating the filter condition based on the values that satisfied the join clause during said execution.

4. The method of claim 3 wherein:

the transaction affected which values satisfy the join clause; and

the method further comprises the step of revising the filter condition to reflect which values current satisfy the join clause.

5. The method of claim 1 wherein:

the table is a first table in a join clause of the query;

the join clause joins the first table with a second table;

the transaction performed an operation on a second row in the second table;

the method further comprises the steps of;

generating a second filter condition based, at least in part, on said predicate of the query;

determining whether the filter condition is satisfied by either (a) a before image of the second row, or (b) an after image of the second row; and

if the second filter condition is satisfied by either the before image of the second row or the after image of the second row, then adding the query to the first set of queries whose result sets may have been affected by the transaction.

6. The method of claim 5 wherein the step of determining a second set of queries includes:

generating a first delta query based on the query, wherein, within the first delta query, references to the first table are replaced with references to a first change record log; and

executing the first delta query to determine whether the result set of the query was affected by the transaction;

generating a second delta query based on the query, wherein, within the second delta query, references to the second table are replaced with references to a second change record log; and

executing the second delta query to determine whether the result set of the query was affected by the transaction.

7. The method of claim 6 wherein:

the first delta query includes a reference to the second table, and retrieves data from the second table as of a time before the transaction committed; and

the second delta query includes a reference to the first table, and retrieves data from the first table as of a time after the transaction committed.

8. The method of claim 1 wherein the step of generating a filter condition includes:

(a) publishing the query to disk;

(b) making the query visible at each of a plurality of database servers;

(c) obtaining and releasing an exclusive lock on each of the tables referenced in the query; and

(d) waiting until (a), (b) and (c) have completed before acquiring a snapshot time for an initial execution of the query.

9. The method of claim 1 wherein:

the step of determining whether the filter condition is satisfied is performed before the transaction commits; and

the step of determining a second set of queries is performed after the transaction commits.

10. The method of claim 1 wherein:

the step of determining whether the filter condition is satisfied is performed before the transaction commits; and

the step of determining a second set of queries is performed before the transaction commits.

11. The method of claim 1 wherein:

the step of determining whether the filter condition is satisfied is performed using a filter index;

the method further comprises the steps of

reading a portion of the filter index into cache;

maintaining data that indicates a first version of the filter index at the time at which the portion was read into cache;

determining whether the filter condition is satisfied based on the portion; and

determining whether the portion is stale by comparing the first version against data that indicates a second version of the filter index at the time that the transaction committed.

12. The method of claim 11 further comprising, in response to determining that the portion is stale, performing the steps of:

loading a new portion of the filter index into cache; and

recomputing the first set of the queries by based on undo log records and the new portion.

13. The method of claim 11 further comprising, in response to determining that the portion is stale, automatically establishing all registered queries that have filter conditions on said table as members of the first set of queries.

14. The method of claim 1 wherein the step of determining a second set of queries that have result sets that were actually affected by the transaction includes:

constructing and executing delta queries for each query in the first set of queries to produce uncompressed query deltas; and

executing compression queries over the uncompressed query deltas to determine if query result sets have changed.

15. A non-transitory computer-readable storage medium storing 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.

16. A non-transitory computer-readable storage medium storing 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.

17. A non-transitory computer-readable storage medium storing 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.

18. A non-transitory computer-readable storage medium storing 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.

19. A non-transitory computer-readable storage medium storing 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.

20. A non-transitory computer-readable storage medium storing 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.

21. A non-transitory computer-readable storage medium storing 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.

22. A non-transitory computer-readable storage medium storing 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.

23. A non-transitory computer-readable storage medium storing 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.

24. A non-transitory computer-readable storage medium storing 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.

25. A non-transitory computer-readable storage medium storing 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.

26. A non-transitory computer-readable storage medium storing 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.

27. A non-transitory computer-readable storage medium storing 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.

28. A non-transitory computer-readable storage medium storing 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.