Choosing whether to use a delayed index maintenance depending on the portion of the materialized view (MV) changed

• US 7,734,602 B2
• Filed: 03/18/2005
• Issued: 06/08/2010
• 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
• Primary Examiner(s): LeRoux; Etienne P
• Assistant Examiner(s): Witzenburg; Bruce A
• Application Number:
  US11/083,804
• Publication Number:
  US 20050235003A1
• Time in Patent Office: 1,908 Days
• Field of Search: 707/1, 707/3, 707/100, 707/101, 707/200
• US Class Current: 707/696
• CPC Class Codes: G06F 16/22 Indexing; Data structures therefor; Storage structures
• CPC Class Codes: G06F 16/2272 Management thereof

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 indexes of a materialized view in a database system, the method comprising:

determining whether an incremental refresh of the materialized view satisfies one or more criteria, wherein an index on the materialized view is refreshable by incrementally refreshing the index as the incremental refresh of the materialized view is being performed or by delayed maintenance of the index until after the materialized view is refreshed;

wherein the index is not any materialized view that is referable as a table in database statements that are executable by the database system;

when the one or more criteria are satisfied,

disabling incremental updating of the index, refreshing the materialized view, and, after refreshing the materialized view, performing the delayed maintenance of the index;

when the one or more criteria are not satisfied, incrementally refreshing the index as the incremental refresh of the materialized view is being performed;

wherein the method is performed by one or more computer systems.

2. The method of claim 1, wherein the one or more criteria include at least a threshold of an amount that is changed of a partition of the materialized view that affects the index.

3. The method of claim 2, wherein the threshold is 10%, and satisfying the one or more criteria includes at least changing more than 10% of a partition that affects the index.

4. The method of claim 2, wherein the threshold is a percentage selected from the range of 10% to 15%, and satisfying the one or more criteria includes at least changing more than the percentage selected of a partition that affects the index.

5. The method of claim 1, wherein when the one or more criteria are satisfied, the index is marked as unusable prior to incrementally refreshing the materialized view.

6. The method of claim 1, wherein when the one or more criteria are satisfied, the index is disabled prior to incrementally refreshing the materialized view.

7. The method of claim 1, wherein the delayed maintenance of the index is performed after committing all changes to rows of a partition that affect the index of the materialized view.

8. The method of claim 1, wherein performing the delayed maintenance of the index further comprises rebuilding the index.

9. A machine-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 a method for refreshing indexes of a materialized view in a database system, the method comprising:

determining whether an incremental refresh of the materialized view satisfies one or more criteria, wherein an index on the materialized view is refreshable by incrementally refreshing the index as the incremental refresh of the materialized view is being performed or by delayed maintenance of the index until after the materialized view is refreshed;

wherein the index is not any materialized view that is referable as a table in database statements that are executable by the database system;

when the one or more criteria are satisfied,

disabling incremental updating of the index, refreshing the materialized view, and, after refreshing the materialized view, performing the delayed maintenance of the index;

when the one or more criteria are not satisfied, incrementally refreshing the index as the incremental refresh of the materialized view is being performed.

10. The machine-readable storage medium of claim 9, wherein the one or more criteria include at least a threshold of an amount that is changed of a partition of the materialized view that affects the index.

11. The machine-readable storage medium of claim 10, wherein the threshold is 10% and satisfying the one or more criteria includes at least changing more than 10% of a partition that affects the index.

12. The machine-readable storage medium of claim 10, wherein the threshold is a percentage selected from the range of 10% to 15%, and satisfying the one or more criteria includes at least changing more than the percentage selected of a partition that affects the index.

13. The machine-readable storage medium of claim 9, wherein when the one or more criteria are satisfied, the index is marked as unusable prior to incrementally refreshing the materialized view.

14. The machine-readable storage medium of claim 9, wherein when the one or more criteria are satisfied, the index is disabled prior to incrementally refreshing the materialized view.

15. The machine-readable storage medium of claim 9, wherein the delayed maintenance of the index is performed after committing all changes to rows of a partition that affect the index of the materialized view.

16. The machine-readable storage medium of claim 9, wherein performing the delayed maintenance of the index further comprises rebuilding the index.