Immediate maintenance of materialized views

• US 8,065,269 B2
• Filed: 12/19/2008
• Issued: 11/22/2011
• Est. Priority Date: 12/19/2008
• Status: Active Grant

• Current Assignee: iAnywhere Solutions Incorporated (SAP SE)
• Original Assignee: iAnywhere Solutions Incorporated (SAP SE)
• Inventors: Eflov, Evguenia, Nica, Anisoara
• Primary Examiner(s): Mofiz; Apu
• Assistant Examiner(s): Le; Hung
• Application Number:
  US12/339,430
• Publication Number:
  US 20100161555A1
• Time in Patent Office: 1,068 Days
• Field of Search: None
• US Class Current: 707/625
• CPC Class Codes: G06F 16/2393 Updating materialised views
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: G06F 16/24561 Intermediate data storage techniques for performance improvement

Abstract

A system, method, and computer program product are provided for immediately updating a materialized view in a relational database management system. The method includes the steps of creating a materialized view defined over a base table, executing an update statement on the base table, computing a delta of the base table by comparing images of the base table before execution of the update statement and after execution of the update statement, computing a delta of the materialized view based on the delta of the base table, and updating the materialized view using the delta of the materialized view.

Claims

1. A method for immediately updating a materialized view in a relational database management system, comprising:

creating a materialized view defined over a base table;

executing an update, insert, or delete statement on the base table;

computing a delta of the base table by comparing images of the base table before execution of the statement and after execution of the statement, and providing row annotations denoting whether corresponding rows of the delta of the base table originated in the image of the base table before execution of the statement, or in the image of the base table after execution of the statement;

computing a delta of the materialized view based on the delta of the base table, comprising computing aggregations of the row annotations to identify row deletions or insertions; and

updating the materialized view using the delta of the materialized view.

2. The method of claim 1, wherein the step of updating the materialized view comprises:

processing a propagate-apply instruction on the materialized view using the delta of the materialized view.

3. The method of claim 2, wherein the propagate-apply instruction is a merge instruction.

4. The method of claim 2, wherein the propagate-apply instruction is processed responsive to processing a trigger associated with the base table.

5. The method of claim 4, further comprising:

determining whether the trigger exists for the base table upon executing the statement on the base table; and

if the trigger does not exist, creating the trigger and the propagate-apply instruction.

6. The method of claim 1, wherein the materialized view is further defined over one or more additional base tables.

7. A system for immediately updating a materialized view in a relational database management system, comprising:

a memory configured to store modules comprising;

a first creating module for creating a materialized view defined over a base table,

an executing module for executing an update, insert, or delete statement on the base table,

a first computing module for computing a delta of the base table by comparing images of the base table before execution of the statement and after execution of the statement, and providing row annotations denoting whether corresponding rows of the delta of the base table originated in the image of the base table before execution of the statement, or in the image of the base table after execution of the statement,

a second computing module for computing a delta of the materialized view based on the delta of the base table, comprising computing aggregations of the row annotations to identify row deletions or insertions, and

an updating module for updating the materialized view using the delta of the materialized view; and

one or more processors configured to process the modules.

8. The system of claim 7, wherein the step of updating module comprises:

a processing module for processing a propagate-apply instruction on the materialized view using the delta of the materialized view.

9. The system of claim 8, wherein the propagate-apply instruction is a merge instruction.

10. The system of claim 8, wherein the propagate-apply instruction is processed responsive to processing a trigger associated with the base table.

11. The system of claim 10, further comprising:

a determining module for determining whether the trigger exists for the base table upon executing the statement on the base table; and

a second creating module for creating the trigger and the propagate-apply instruction if the trigger does not exist.

12. The system of claim 7, wherein the materialized view is further defined over one or more additional base tables.

13. A computer-usable medium having computer program logic recorded thereon for immediately updating a materialized view in a relational database management system, execution of which, by a computing device, causes the computing device to perform operations comprising:

creating a materialized view defined over a base table;

executing an update, insert, or delete statement on the base table;

computing a delta of the base table by comparing images of the base table before execution of the statement and after execution of the statement, and providing row annotations denoting whether corresponding rows of the delta of the base table originated in the image of the base table before execution of the statement, or in the image of the base table after execution of the statement;

computing a delta of the materialized view based on the delta of the base table, comprising computing aggregations of the row annotations to identify row deletions or insertions; and

updating the materialized view using the delta of the materialized view.

14. The computer-usable medium of claim 13, wherein updating the materialized view comprises:

processing a propagate-apply instruction on the materialized view using the delta of the materialized view.

15. The computer-usable medium of claim 14, wherein the propagate-apply instruction is a merge instruction.

16. The computer-usable medium of claim 14, wherein the propagate-apply instruction is processed responsive to processing a trigger associated with the base table.

17. The computer-usable medium of claim 16, the operations further comprising:

determining whether the trigger exists for the base table upon executing the statement on the base table; and

creating the trigger and the propagate-apply instruction if the trigger does not exist.

18. The computer-usable medium of claim 13, wherein the materialized view is further defined over one or more additional base tables.