Using estimated cost to schedule an order for refreshing a set of materialized views (MVS)

• US 7,890,497 B2
• Filed: 03/18/2005
• Issued: 02/15/2011
• Est. Priority Date: 04/14/2004
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Witkowski, Andrew, Folkert, Nathaniel Keith, Gupta, Abhinav
• Primary Examiner(s): LeRoux; Etienne P
• Assistant Examiner(s): Witzenburg; Bruce A
• Application Number:
  US11/084,331
• Publication Number:
  US 20050235004A1
• Time in Patent Office: 2,160 Days
• Field of Search: 707/602, 707/688, 707/717, 707/718, 707/719
• US Class Current: 707/718
• 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:

storing a plurality of materialized views;

for each materialized view of said plurality of materialized views;

submitting to an optimizer at least one set of one or more refresh expressions for refreshing said each materialized view, wherein said at least one set of one or more refresh expressions refreshes said each materialized view according to a particular refresh technique, wherein each refresh expression of said at least one set of one or more refresh expressions is a DML statement; and

said optimizer generating, for each refresh expression of said at least one set of one or more refresh expressions;

a query execution plan and a query execution cost for executing said query execution plan;

wherein for at least one materialized view of said plurality of materialized views, submitting to said optimizer at least one set of one or more refresh expressions includes submitting a plurality of sets of one or more refresh expressions, each set of one or more refresh expressions of said plurality of sets of one or more refresh expressions refreshing said at least one materialized view according to a respective refresh technique, wherein the respective refresh technique of said each set of one or more refresh expressions is different than the respective refresh technique of any other set of one or more refresh expressions of said plurality of sets of one or more refresh expressions;

wherein said plurality of sets of one or more refresh expressions includes at least two different sets of one or more refresh expressions that refresh said at least one materialized view according to at least two different incremental refresh techniques;

based on the query execution costs estimated by said optimizer, estimating a cost of each of a plurality of schedules for refreshing the plurality of materialized views;

choosing a preferred schedule from the plurality of schedules based on the estimated costs of the plurality of schedules; and

refreshing the plurality of materialized views according to the preferred schedule;

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

2. The method of claim 1, wherein the choosing of the preferred schedule is performed by at least choosing a first one or more preferred materialized views to refresh at a first level;

and after choosing the one or more preferred materialized views to refresh at the first level choosing a second one or more preferred materialized views to refresh at a second level.

3. The method of claim 1, wherein the preferred schedule has an estimated preferred cost that is a lowest cost estimated during the estimating of the cost of each of the plurality of schedules.

4. The method of claim 1, wherein each schedule of the plurality of schedules includes an order in which the plurality of materialized views are refreshed with respect to one another and one of a plurality of techniques for refreshing each of the plurality of materialized views, and the estimated cost of the schedule is the estimated cost of applying each technique associated with each materialized view in the order of the schedule.

5. The method of claim 4, wherein at least one of the plurality of schedules includes a plurality of refresh techniques applied to a plurality of materialized views.

6. The method of claim 1, wherein estimating the cost of each of said plurality of schedules includes estimating a cardinality of a materialized view associated with an empty container.

7. The method of claim 1, wherein the preferred schedule is represented as a refresh graph.

8. The method of claim 7, wherein the method further comprises if the graph includes a cycle, at least one edge associated with the cycle is removed to therein remove the cycle from the graph.

9. The method of claim 8, further comprising adding a dummy node to the graph, wherein each node of the cycle has one incoming edge that originates from the dummy node.

10. The method of claim 8, further comprising:

adding a dummy node to the graph, and

for each incoming edge to the cycle, adding a corresponding incoming edge to the dummy node.

11. The method of claim 8, further comprising selecting the at least one edge based on a heuristic rule.

12. The method of claim 8, further comprising selecting the at least one edge based on a count of a number of nodes that are converted into source nodes by the removal of the at least one edge.

13. The method of claim 8, further comprising for each node of the cycle searching for source nodes not previously included in the graph.

14. The method of claim 7, wherein the graph includes a plurality of nodes representing the plurality of materialized views, and the method further comprises removing one of the plurality of nodes from the graph after one of the plurality of materialized views, which corresponds to the one of the plurality of nodes, is scheduled.

15. The method of claim 1, wherein said each set of one or more refresh expressions of said plurality of sets of one or more refresh expressions refreshes said at least one materialized view using a refresh technique from the set of refresh techniques comprising conventional fast refresh, PCT fast refresh with TRUNCATE, and PCT fast refresh with DELETE.

16. The method of claim 1, wherein said each set of one or more refresh expressions of said plurality of sets of one or more refresh expressions refreshes said at least one materialized view from the same set of base tables.

17. The method of claim 1, wherein said each set of one or more refresh expressions of said plurality of sets of one or more refresh expressions refreshes said at least one materialized view according to an incremental refresh technique.

18. The method of claim 1, wherein said query execution cost includes an estimation of CPU time required for executing said query execution plan.

33. The method of claim 1, wherein for at least one materialized view of said plurality of materialized views, the respective at least one set of one or more refresh expressions includes a plurality of DML statements.

19. A machine-readable non-transitory storage medium storing one or more sequences of instructions, which when executed by one or more processors, cause the one or more processors to perform steps comprising:

storing a plurality of materialized views;

for each materialized view of said plurality of materialized views;

submitting to an optimizer at least one set of one or more refresh expressions for refreshing said each materialized view, wherein said at least one set of one or more refresh expressions refreshes said each materialized view according to a particular refresh technique, wherein each refresh expression of said at least one set of one or more refresh expressions is a DML statement; and

said optimizer generating, for each refresh expression of said at least one set of one or more refresh expressions;

a query execution plan and a query execution cost for executing said query execution plan;

wherein for at least one materialized view of said plurality of materialized views, submitting to said optimizer at least one set of one or more refresh expressions includes submitting a plurality of sets of one or more refresh expressions, each set of one or more refresh expressions of said plurality of sets of one or more refresh expressions refreshing said at least one materialized view according to a respective refresh technique, wherein the respective refresh technique of said each set of one or more refresh expressions is different than the respective refresh technique of any other set of one or more refresh expressions of said plurality of sets of one or more refresh expressions;

wherein said plurality of sets of one or more refresh expressions includes at least two different sets of one or more refresh expressions that refresh said at least one materialized view according to at least two different incremental refresh techniques;

based on the query execution costs estimated by said optimizer, estimating a cost of each of a plurality of schedules for refreshing the plurality of materialized views;

choosing a preferred schedule from the plurality of schedules based on the estimated costs of the plurality of schedules; and

refreshing the plurality of materialized views according to the preferred schedule.

20. The machine readable storage medium of claim 19, wherein the instructions that cause choosing of the preferred schedule comprise instructions which, when executed by the one or more processors, cause the one or more processors to perform the steps of:

choosing a first one or more preferred materialized views to refresh at a first level; and

after choosing the one or more preferred materialized views to refresh at the first level choosing a second one or more preferred materialized views to refresh at a second level.

21. The machine-readable storage medium of claim 19, wherein the preferred schedule has an estimated preferred cost that is a cost that is a lowest cost estimated during the estimating of the cost of each of the plurality of schedules.

22. The machine-readable storage medium of claim 19, wherein each schedule of the plurality of schedules includes an order in which the plurality of materialized views are refreshed with respect to one another and one of a plurality of techniques for refreshing each of the plurality of materialized views, and the estimated cost of the schedule is the estimated cost of applying each technique associated with each materialized view in the order of the schedule.

23. The machine-readable storage medium of claim 22, wherein at least one of the plurality of schedules includes a plurality of refresh techniques applied to a plurality of materialized views.

24. The machine-readable storage medium of claim 19, wherein the instructions that cause estimating the cost of each of said plurality of schedules include instructions which, when executed by the one or more processors, cause the one or more processors to perform the step of estimating a cardinality of a materialized view associated with an empty container.

25. The machine-readable storage medium of claim 19, wherein the preferred schedule is represented as a refresh graph.

26. The machine-readable storage medium of claim 25, wherein the one or more sequences of instructions further comprise instructions which, when executed by the one or more processors, cause the one or more processors to perform the step of, if the graph includes a cycle, then removing at least one edge associated with the cycle to remove the cycle from the graph.

27. The machine-readable storage medium of claim 26, wherein the one or more sequences of instructions further comprise instructions which, when executed by the one or more processors, cause the one or more processors to perform the step of adding a dummy node to the graph, wherein each node of the cycle has one incoming edge that originates from the dummy node.

28. The machine-readable storage medium of claim 25, wherein the one or more sequences of instructions further comprise instructions which, when executed by the one or more processors, cause the one or more processors to perform the step of

adding a dummy node to the graph, wherein

for each incoming edge to the cycle, the dummy node has a corresponding incoming edge.

29. The machine-readable storage medium of claim 25, wherein the one or more sequences of instructions further comprise instructions which, when executed by the one or more processors, cause the one or more processors to perform the step of selecting the at least one edge based on a heuristic rule.

30. The machine-readable storage medium of claim 25, wherein the one or more sequences of instructions further comprise instructions which, when executed by the one or more processors, cause the one or more processors to perform the step of selecting the at least one edge based on a count of a number of nodes that are converted into source nodes by the removal of the at least one edge.

31. The machine-readable storage medium of claim 25, wherein the one or more sequences of instructions further comprise instructions which, when executed by the one or more processors, cause the one or more processors to perform the step of, for each node of the cycle, searching for source nodes not previously included in the graph.

32. The machine-readable storage medium of claim 24, wherein the graph includes a plurality of nodes representing the plurality of materialized views, and the one or more sequences of instructions further comprise instructions which, when executed by the one or more processors, cause the one or more processors to perform the step of removing one of the plurality of nodes from the graph after one of the plurality of materialized views, which corresponds to the one of the plurality of nodes, is scheduled.

34. The machine-readable storage medium of claim 19, wherein for at least one materialized view of said plurality of materialized views, the respective at least one set of one or more refresh expressions includes a plurality of DML statements.

35. The machine-readable storage medium of claim 19, wherein said each set of one or more refresh expressions of said plurality of sets of one or more refresh expressions refreshes said at least one materialized view using a refresh technique from the set of refresh techniques comprising conventional fast refresh, PCT fast refresh with TRUNCATE, and PCT fast refresh with DELETE.

36. The machine-readable storage medium of claim 19, wherein said each set of one or more refresh expressions of said plurality of sets of one or more refresh expressions refreshes said at least one materialized view from the same set of base tables.

37. The machine-readable storage medium of claim 19, wherein said each set of one or more refresh expressions of said plurality of sets of one or more refresh expressions refreshes said at least one materialized view according to an incremental refresh technique.

38. The machine-readable storage medium of claim 19, wherein said query execution cost includes an estimation of CPU time required for executing said query execution plan.