Selective materialized view refresh

Patent metadata

Field	Value
Publication number	US-10120899-B2
Application number	US-201313826501-A
Country	US
Kind code	B2
Filing date	Mar 14, 2013
Priority date	Mar 14, 2013
Publication date	Nov 6, 2018
Grant date	Nov 6, 2018

Abstract

Techniques are provided for selectively refreshing a materialized view. A portion of a materialized view is refreshed (or updated) while other portions of the materialized view are not refreshed. Thus, the other portions of the materialized view may contain “stale” data that does not accurately reflect current data in the base table(s) upon which the materialized view is based. In one technique, a user indicates the portion of the materialized view that is to be refreshed. In another technique, a user indicates a plurality of portions of a materialized view and it is automatically determined which portion should be refreshed first. Change data that indicates information about changes made to different portions of one or more base tables and/or read data that indicates information about reads to different portions of the materialized view may be taken into account to determine which portion of a materialized view to refresh.

First claim

What is claimed is:

1. One or more storage media storing instructions which, when
executed by one or more processors, cause:
determining to perform a refresh operation with respect to a materialized view that is associated with one or more base tables and that comprises a plurality of portions that includes a first portion and a second portion that is different than the first portion;
identifying change data that indicates information about changes to the one or more base tables;
wherein the change data indicates:
(1) for the first portion, a first number of changes to the one or more base tables or a first number of data items, in the one or more base tables, that were changed, and
(2) for the second portion, a second number of changes to the one or more base tables or a second number of data items, in the one or more base tables, that were changed;

wherein the changes affect the first portion of the materialized view and the second portion of the materialized view;
wherein determining to perform the refresh operation is based on the change data;
based on (1) the first number of changes or the first number of data items and (2) the second number of changes or the second number of data items, identifying the first portion but not the second portion for updating, wherein the first portion corresponds to one or more first portions of the one or more base tables that are associated with the materialized view;
wherein the second portion corresponds to one or more second portions of the one or more base tables that are associated with the materialized view;
wherein the one or more first portions include first changes that are not yet reflected in the materialized view and the one or more second portions include second changes that are not yet reflected in the materialized view;
in response to determining to perform the refresh operation to update the materialized view, performing the refresh operation by updating the first portion of the materialized view without updating the second portion of the materialized view,
wherein, after the refresh operation is performed, the second changes that are included in the one or more second portions before the refresh operation are not reflected in the materialized view.

2. The one or more storage media of claim 1 , wherein the instructions, when executed by the one or more processors, further cause:
identifying read data that indicates information about one or more reads to each portion of the plurality of portions;
wherein identifying the first portion is also based on the read data.

3. The one or more storage media of claim 1 , wherein the instructions, when executed by the one or more processors, further cause:
receiving, from a user, input that indicates a particular portion of the materialized view;
wherein determining to update is performed in response to receiving the input.

4. The one or more storage media of claim 3 , wherein the first portion is a partition of the materialized view or a portion, of the materialized view, that is defined by a predicate and that does not correspond to only a single partition of the materialized view.

5. The one or more storage media of claim 3 , wherein:
the instructions, when executed by the one or more processors, further cause causing, to be communicated, data that indicates a recommendation of which portion of the materialized view to update first relative to other portions of the materialized view;
the data indicates the first portion;
causing is performed prior to receiving the input.

6. The one or more storage media of claim 3 , wherein:
the input indicates a plurality of distinct portions of the materialized view;
the plurality of distinct portions at least includes the first portion and a third portion;
the instructions, when executed by the one or more processors, further cause:
determining, based on one or more criteria, which distinct portion of the plurality of distinct portions to update first;
determining to update the first portion prior to updating the third portion;

updating the first portion comprises updating the first portion prior to updating the third portion.

7. The one or more storage media of claim 6 , wherein the one or more criteria indicates an amount of activity related to one or more portions of the one or more base tables that are associated with the materialized view.

8. The one or more storage media of claim 1 , wherein the first portion is a partition of the materialized view.

9. The one or more storage media of claim 8 , wherein the partition is a logical partition and is not related to physical properties of the materialized view.

10. The one or more storage media of claim 8 , wherein the partition is a physical partition and is one of a plurality of partitions of the materialized view.

11. The one or more storage media of claim 1 , wherein the instructions, when executed by the one or more processors, further cause:
determining an access frequency of each portion of the plurality of portions of the materialized view;
causing, to be communicated, data that indicates, for each portion of the plurality of portions, the access frequency for said each portion;
receiving input that selects at least one of the plurality of portions.

12. The one or more storage media of claim 1 , wherein updating the first portion of the materialized view comprises:
determining a revised version of a defining query that, when executed, causes the materialized view to be generated based on the one or more base tables, wherein the revised version of the defining query includes a predicate that corresponds to the first portion, wherein the defining query does not include the predicate;
executing the revised version of the defining query against the one or more base tables to generate results that are used to update the first portion of the materialized view.

13. The one or more storage media of claim 1 , wherein:
at least one base table of the one or more base tables comprises a plurality of partitions;
updating the first portion of the materialized view comprises executing a query that targets only a strict subset of the plurality of partitions;
executing the query causes results to be returned;
the results are used to update the first portion of the materialized view.

14. The one or more storage media of claim 1 , wherein updating the first portion of the materialized view comprises:
identifying, in a log file that is associated with a base table of the one or more base tables, a plurality of log entries, each of which corresponds to a change to the base table;
determining that a first strict subset of the plurality of log entries corresponds to the first portion of the materialized view, wherein a second strict subset of the plurality of log entries do not correspond to the first portion of the materialized view;
applying changes reflected in the log entries of the first strict subset to the first portion of the materialized view without applying changes reflected in the second strict subset to the first portion of the materialized view.

15. One or more storage media storing instructions which, when executed by one a particular or more processors, cause:
determining to perform a refresh operation with respect to a materialized view that is based on one or more base tables and that comprises a plurality of portions that includes a first portion and a second portion;
receiving user input that explicitly specifies the first portion of the materialized view, wherein the user input does not specify one or more other portions in the plurality of portions and the user input does

Assignees

• Oracle Int Corp

Inventors

• Ding Luping
• Yu Tsae-Feng

Classifications

• G06F16/24539 Primary

  using cached or materialised query results · CPC title

• G06F16/2393

  Updating materialised views · CPC title

• G06F17/30457 Primary

  Physics · mapped topic

• G06F17/30383

  Physics · mapped topic