US 7,734,602 B2 · Choosing whether to use a delayed index maintenance depending on the portion of the materialized view (MV) changed

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.

Claims16

Dependent claims

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.

3 more elements

Dependencies are inferred from the claim text.

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

Life of this patent

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

Life of this patent

Abstract

Claims16