Using estimated cost to refresh a set of materialized views (MVS)

• US 20050234971A1
• Filed: 03/18/2005
• Published: 10/20/2005
• Est. Priority Date: 04/14/2004
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Gupta, Abhinav, Witkowski, Andrew, Folkert, Nathaniel Keith
• Granted Patent: US 8,478,742 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2393 Updating materialised views
• CPC Class Codes: G06F 16/24539 using cached or materialised query results

Abstract

In an embodiment, estimated costs are used to pick the best refresh technique (e.g., method) for a given MV. In another embodiment, estimated costs are used to schedule the refresh of a set MVs in an order such that overall time to refresh is reduced, or in an order that uses the smallest overall time, for refreshing when compared to other alternative refresh schedules. In another embodiment, the estimated cost of refreshing each of a set of MVs is used to select the set of CPU resources to allocate to refreshing each MV. Based on the estimated costs, the allocation of CPU resources is chosen to be more optimal (e.g., use less CPU time) than other available sets of CPU resources. In an embodiment, when refreshing an MV, delayed index maintenance is chosen if a significant portion of the base table and/or the partitions of the table has changed.

Claims

1. A method for refreshing materialized views comprising:

estimating a cost of a plurality of sets of refresh techniques for refreshing one or more materialized views, wherein the plurality of sets of refresh techniques includes a plurality of refresh techniques;

choosing a particular set of the plurality of sets based on the estimating; and

refreshing the plurality of materialized views according to the particular set.

2. The method of claim 1, wherein at least one of the materialized views is partitioned.

3. The method of claim 1, wherein the plurality of techniques includes at least partition tracking using delete.

4. The method of claim 1, wherein the plurality of techniques includes at least partition tracking using truncate.

5. The method of claim 1, wherein the plurality of techniques includes at least an incremental refresh.

6. The method of claim 1, wherein the plurality of techniques includes at least a complete refresh.

7. The method of claim 1, wherein the plurality of techniques includes at least

partition tracking using delete,

partition tracking using truncate,

an incremental refresh, and

a rebuilding of the materialized view.

8. The method of claim 1 further comprising determining which of the plurality techniques are possible techniques for the materialized view;

and wherein

the estimating includes at least estimating a cost associated with the possible techniques; and

the choosing includes at least selecting one of the possible techniques based on the cost estimated.

9. The method of claim 1, further the estimating includes estimating a cost associated with a row-by-row delete.

10. The method of claim 1, wherein the estimating includes at least estimating a cost of computing changes to portions of the materialized view.

11. The method of claim 1, wherein the estimating includes at least estimating a cost recording in a log a row at a time deletion of rows of a partition of the materialized view.

12. A machine-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 a method for refreshing materialized views, the method comprising:

estimating a cost of a plurality of sets of refresh techniques for refreshing one or more materialized views, wherein the plurality of sets of refresh techniques includes a plurality of refresh techniques;

choosing a particular set of the plurality of sets based on the estimating; and

refreshing the plurality of materialized views according to the particular set.

13. The machine-readable medium of claim 12, wherein at least one of the materialized views is partitioned.

14. The machine-readable medium of claim 12, wherein the plurality of techniques includes at least partition tracking using delete.

15. The machine-readable medium of claim 12, wherein the plurality of techniques includes at least partition tracking using truncate.

16. The machine-readable medium of claim 12, wherein the plurality of techniques includes at least an incremental refresh.

17. The machine-readable medium of claim 12, wherein the plurality of techniques includes at least a complete refresh.

18. The machine-readable medium of claim 12, wherein the plurality of techniques includes at least partition tracking using delete,

partition tracking using truncate,

an incremental refresh, and

a rebuilding of the materialized view.

19. The machine-readable medium of claim 12, wherein the method further comprises determining which of the plurality techniques are possible techniques for the materialized view;

and wherein

the estimating includes at least estimating the a cost associated with the possible techniques; and

the choosing includes at least selecting one the possible techniques based on the cost estimated.

20. The machine-readable medium of claim 12, wherein the estimating includes estimating a cost associated with a row-by-row delete.

21. The machine-readable medium of claim 12, wherein the estimating includes at least estimating a cost of computing changes to portions of the materialized view.

22. The machine-readable medium of claim 12, wherein the estimating includes at least estimating a cost recording in a log a row at a time deletion of rows of a partition of the materialized view.