Incremental refresh of materialized views with joins and aggregates after arbitrary DML operations to multiple tables

• US 6,882,993 B1
• Filed: 01/28/2002
• Issued: 04/19/2005
• Est. Priority Date: 01/28/2002
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Gupta, Abhinav, Lawande, Shilpa, Dageville, Benoit
• Primary Examiner(s): Mizrahi, Diane D.
• Application Number:
  US10/059,616
• Time in Patent Office: 1,177 Days
• Field of Search: 707 1- 10, 707100-1041, 707200-205
• US Class Current: 707/714
• CPC Class Codes: G06F 16/2393 Updating materialised views
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A method is provided for incrementally refreshing a materialized view after multiple operations on a row of a base table of the materialized view, by determining an equivalent operation for the multiple operations and refreshing the materialized view according to the equivalent operation. The method is applicable to a materialized view based on multiple base tables on which multiple operations have been performed. The step of determining the equivalent operation can include identifying rows for which an earliest operation is a DELETE operation, or rows for which a latest operation is an INSERT operation, or a combination of the two. The step of refreshing the materialized view includes performing an inverse operation of the equivalent operation to determine a pre-update state of the row, and refreshing the materialized view based on the pre-update state. Additional embodiments are provided which enhance the performance of materialized view refresh queries.

Claims

1. A method for refreshing a materialized view after a plurality of operations on a row of a base table of the materialized view, the method comprising the steps of:

determining an equivalent operation that has the same affect on the row that the plurality of operations have on the row; and

refreshing the materialized view based on the equivalent operation.

2. The method of claim 1 wherein the step of determining an equivalent operation comprises:

identifying rows for which an earliest operation from the plurality of operations is a delete operation; and

determining the equivalent operation on the rows based on the earliest operation.

3. The method of claim 1 wherein the step of determining an equivalent operation comprises:

identifying rows for which a latest operation from the plurality of operations is an insert operation; and

determining the equivalent operation on the rows based on the latest operation.

4. The method of claim 1 wherein the step of determining an equivalent operation comprises:

identifying rows for which an earliest operation from the plurality of operations is a delete operation;

identifying rows for which a latest operation from the plurality of operations is an insert operation; and

determining the equivalent operation on the rows based on the earliest operation and the latest operation.

5. The method of claim 1, wherein the step of refreshing the materialized view comprises:

performing an inverse operation of the equivalent operation to determine a pre-update state of the row; and

refreshing the materialized view based on the pre-update state of the row.

6. The method of claim 1 wherein:

the materialized view is based on a plurality of base tables;

operations have been performed on multiple tables of the plurality of base tables;

the step of refreshing comprises the steps of

determining a pre-update state of the row based on the equivalent operation; and

refreshing the materialized view based on the pre-update state of the row.

7. The method of claim 6 wherein the step of refreshing the materialized view comprises refreshing a materialized view defined by a query that joins two or more of the plurality of base tables.

8. The method of claim 6 wherein the step of refreshing the materialized view comprises refreshing a materialized view that aggregates values from one or more of the plurality of base tables.

9. The method of claim 6 wherein the step of refreshing the materialized view comprises refreshing a materialized view that aggregates values from two or more of the plurality of base tables.

10. The method of claim 6 wherein the step of refreshing the materialized view comprises:

ordering the multiple base tables according to the computational complexity of the step of determining the pre-update state of the row of each of the multiple base tables; and

refreshing the materialized view based on the ordering, whereby a base table being more computationally complex is applied to the materialized view before a base table being less computationally complex.

11. The method of claim 10 wherein the step of refreshing the materialized view based on the ordering comprises:

determining a number of rows that were operated on for each of the multiple base tables; and

refreshing the materialized view based on the number of rows that were operated on, whereby a base table having more rows operated on is applied to the materialized view before a base table having fewer rows operated on, if the ordering indicates equal computational complexity of two or more of the multiple tables.

12. The method of claim 1 wherein the step of refreshing the materialized view comprises:

determining a pre-update state of the row, including

defining a delete query to identify rows for which an earliest operation from the plurality of operations is a delete operation;

defining an insert query to identify rows for which a latest operation from the plurality of operations is an insert operation; and

combining the delete query and the insert query into a delta query.

13. The method of claim 12 wherein the step of determining a pre-update state further comprises materializing results of the delta query into a temporary table.

14. The method of claim 13 wherein the step of materializing the delta query into a temporary table uses a WITH command.

15. The method of claim 1 wherein the plurality of operations are described in a materialized view log that describes each of the plurality of operations that were performed independently of other of the plurality of operations and a direct load log that describes an insert operation that was performed in bulk to a plurality of rows of the base table, and wherein the step of determining the equivalent operation comprises:

creating a combined log by combining the materialized view log and the direct load log; and

determining the equivalent operation based on the combined log.

16. The method of claim 15 wherein a plurality of operations are performed on a plurality of rows of the base table, the method further comprising:

creating a first cardinality by determining from the materialized view log a number of rows of the base table that were inserted by independent operations;

creating a second cardinality by determining from the direct load log a number of rows of the table that were inserted in bulk;

creating a third cardinality by determining from the materialized view log a number of rows of the base table that were deleted by independent operations; and

wherein the step of refreshing the materialized view is based on at least one of the first, second, and third cardinalities.

17. The method of claim 1 wherein the step of refreshing the materialized view comprises:

expanding an original UNION ALL command such that at least one branch of a resulting UNION ALL command acts upon two or more tables that each have an index; and

refreshing the materialized view, at least in part, based on the indexes.

18. The method of claim 1 wherein the step of refreshing the materialized view comprises:

combining inserting a new row into the materialized view based on the equivalent operation and updating a row of the materialized view based on the equivalent operation as a single operation on the materialized view.

19. The method of claim 18 wherein the step of combining uses a MERGE command.

20. A computer-readable medium carrying one or more sequences of instructions for refreshing a materialized view after a plurality of operations on a row of a base table of the materialized view, wherein execution of the one or more sequences of instructions by one or more processors causes the one or more processors to perform the steps of:

determining an equivalent operation that has the same affect on the row that the plurality of operations have on the row; and

refreshing the materialized view based on the equivalent operation.

21. The computer-readable medium of claim 20 wherein execution of the one or more sequences of instructions by one or more processors causes the one or more processors to perform the step of determining an equivalent operation by causing the one or more processors to perform the steps of:

identifying rows for which an earliest operation from the plurality of operations is a delete operation; and

determining the equivalent operation based on the earliest operation.

22. The computer-readable medium of claim 20 wherein execution of the one or more sequences of instructions by one or more processors causes the one or more processors to perform the step of determining an equivalent operation by causing the one or more processors to perform the steps of:

identifying rows for which a latest operation from the plurality of operations is an insert operation; and

determining the equivalent operation based on the latest operation.

23. The computer-readable medium of claim 20 wherein execution of the one or more sequences of instructions by one or more processors causes the one or more processors to perform the step of determining an equivalent operation by causing the one or more processors to perform the steps of:

identifying rows for which an earliest operation from the plurality of operations is a delete operation;

identifying rows for which a latest operation from the plurality of operations is an insert operation; and

determining the equivalent operation based on the earliest operation and the latest operation.

24. The computer-readable medium of claim 20 wherein execution of the one or more sequences of instructions by one or more processors causes the one or more processors to perform the step of refreshing the materialized view by causing the one or more processors to perform the steps of:

performing an inverse operation of the equivalent operation to determine a pre-update state of the row; and

refreshing the materialized view based on the pre-update state of the row.

25. The computer-readable medium of claim 20 wherein:

the materialized view is based on a plurality of base tables;

operations have been performed on multiple tables of the plurality of base tables;

execution of the one or more sequences of instructions by one or more processors causes the one or more processors to perform the step of refreshing the materialized view by causing the one or more processors to perform the steps of;

determining a pre-update state of the row based on the equivalent operation; and

refreshing the materialized view based on the pre-update state of the row.

26. The computer-readable medium of claim 25 wherein execution of the one or more sequences of instructions by one or more processors causes the one or more processors to perform the step of refreshing the materialized view by causing the one or more processors to perform the steps of:

ordering the multiple base tables according to the computational complexity of the step of determining the pre-update state of the row of each of the multiple base tables; and

refreshing the materialized view based on the ordering, whereby a base table being more computationally complex is applied to the materialized view before a base table being less computationally complex.

27. The computer-readable medium of claim 26 wherein execution of the one or more sequences of instructions by one or more processors causes the one or more processors to perform the step of refreshing the materialized view based on the ordering by causing the one or more processors to perform the steps of:

determining a number of rows that were operated on for each of the multiple base tables; and

refreshing the materialized view based on the number of rows that were operated on, whereby a base table having more rows operated on is applied to the materialized view before a base table having fewer rows operated on, if the ordering indicates equal computational complexity of two or more of the multiple tables.

28. The computer-readable medium of claim 20 wherein execution of the one or more sequences of instructions by one or more processors causes the one or more processors to perform the step of refreshing the materialized view by causing the one or more processors to perform the steps of:

determining a pre-update state of the row, including

defining a delete query to identify rows for which an earliest operation from the plurality of operations is a delete operation;

defining an insert query to identify rows for which a latest operation from a plurality of operations is an insert operation; and

combining the delete query and the insert query into a delta query.

29. The computer-readable medium of claim 28 wherein execution of the one or more sequences of instructions by one or more processors causes the one or more processors to perform the step of determining a pre-update state by causing the one or more processors to perform the steps of:

materializing results of the delta query into a temporary table.

30. The computer-readable medium of claim 20 wherein the plurality of operations are described in a materialized view log that describes each of the plurality of operations that were performed independently of other of the plurality of operations and a direct load log that describes an insert operation that was performed in bulk to a plurality of rows of the base table, and wherein execution of the one or more sequences of instructions by one or more processors causes the one or more processors to perform the step of determining the equivalent operation by causing the one or more processors to perform the steps of:

creating a combined log by combining the materialized view log and the direct load log; and

determining the equivalent operation based on the combined log.

31. The computer-readable medium of claim 30 wherein a plurality of operations are performed on a plurality of rows of the base table, and wherein execution of the one or more sequences of instructions by one or more processors causes the one or more processors to perform the steps of:

creating a first cardinality by determining from the materialized view log a number of rows of the base table that were inserted by independent operations;

creating a second cardinality by determining from the direct load log a number of rows of the table that were inserted in bulk;

creating a third cardinality by determining from the materialized view log a number of rows of the base table that were deleted by independent operations; and

wherein the step of refreshing the materialized view is based on at least one of the first, second, and third cardinalities.

32. The computer-readable medium of claim 20 wherein execution of the one or more sequences of instructions by one or more processors causes the one or more processors to perform the step of refreshing the materialized view by causing the one or more processors to perform the step of:

combining inserting a new row into the materialized view based on the equivalent operation and updating a row of the materialized view based on the equivalent operation as a single operation on the materialized view.

33. A computer apparatus comprising:

a memory; and

one or more processors coupled to the memory and configured to execute one or more sequence of instructions for refreshing a materialized view after a plurality of operations on a row of a base table of the materialized view, wherein execution of the one or more sequences of instructions by one or more processors causes the one or more processors to perform the steps of;

determining an equivalent operation that has the same affect on the row that the plurality of operations have on the row; and

refreshing the materialized view based on the equivalent operation.