Out-of-place materialized view refresh

Patent metadata

Field	Value
Publication number	US-9454572-B2
Application number	US-201313826726-A
Country	US
Kind code	B2
Filing date	Mar 14, 2013
Priority date	Mar 14, 2013
Publication date	Sep 27, 2016
Grant date	Sep 27, 2016

Abstract

Techniques are provided for performing a refresh or update of a materialized view without modifying the materialized view. Such a refresh is referred to as an out-of-place materialized view refresh. An out-of-place materialized view refresh involves creating one or more outside tables into which data will be inserted. During the refresh, the materialized view may be accessible for query processing, even though the materialized view contains “stale” data. After the insertion, the one or more outside tables are made accessible for query processing and future queries will target the one or more outside tables instead of the materialized view.

First claim

What is claimed is:

1. A method comprising:
determining to update a materialized view that is based on one or more base tables and a defining query that is used to create the materialized view when executed against the one or more base tables;
wherein the determining comprises determining that one or more changes have been made to at least one of the one or more base tables and have not been made to the materialized view;
in response to determining to update the materialized view:
creating a database object that is separate from the materialized view and the one or more base tables;
generating first data for insertion into the database object, wherein generating the first data comprises retrieving second data from the one or more base tables upon which the materialized view is based, wherein the first data includes the second data;
inserting the first data into the database object;
causing the database object to be accessible for query processing in place of the materialized view;

after creating the database object and prior to causing the database object to be accessible for query processing in place of the materialized view:
receiving a query that targets the materialized view;
in response to receiving the query:
determining that a first portion of the materialized view corresponds to one or more first portions, of a plurality of portions of the one or more base tables, that have not been modified since a most recent update of the materialized view;
retrieving first results of the query based on analyzing the first portion;
determining that a second portion of the materialized view corresponds to one or more second portions, of the plurality of portions of the one or more base tables, that have been modified since the most recent update of the materialized view;
identifying the one or more second portions, of the one or more base tables, that correspond to the second portion of the materialized view;
retrieving second results of the query based on analyzing the one or more second portions of the one or more base tables;

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

2. The method of claim 1 , wherein causing the database object to be accessible for query processing comprises associating a name of the materialized view with the database object.

3. A method comprising:
determining to update a materialized view that is based on one or more base tables and a defining query that is used to create the materialized view when executed against the one or more base tables;
wherein the determining comprises determining that one or more changes have been made to at least one of the one or more base tables and have not been made to the materialized view;
in response to determining to update the materialized view:
creating a database object that is separate from the materialized view and the one or more base tables;
generating first data for insertion into the database object, wherein generating the first data comprises retrieving second data from the one or more base tables upon which the materialized view is based, wherein the first data includes the second data;
wherein the first data comprises a plurality of portions;
inserting the first data into the database object;
wherein inserting the first data into the database object comprises, for each portion of the plurality of portions, performing a direct path insert operation to append said each portion to the database object;
causing the database object to be accessible for query processing in place of the materialized view.

4. The method of claim 1 , wherein generating the first data comprises:
executing a second query that defines the materialized view and that is processed
against the one or more base tables upon which the materialized view is based;

wherein executing the second query causes result data to be identified;
wherein the result data is the first data.

5. The method of claim 1 , wherein generating the first data comprises:
determining which partitions of a plurality of partitions of the one or more base tables have been modified;
wherein determining which partitions comprises identifying a set of one or more partitions of the plurality of partitions;
executing a second defining query that is associated with the materialized view and that includes one or more conditions that excludes each partition of the plurality of partitions that is not in the set of one or more partitions, wherein executing the second defining query causes result data to be identified;
wherein the result data is a first subset of the first data.

6. The method of claim 5 , wherein generating the first data further comprises:
identifying, of the materialized view, one or more portions that correspond to one or more partitions, of the plurality of partitions of the one or more base tables, that have not been modified since the most recent update of the materialized view;
wherein the one or more portions are a second subset of the first data;
wherein the second subset is different than the first subset.

7. The method of claim 1 , wherein:
the materialized view is partitioned into a plurality of view partitions and the one or more base tables are partitioned into a plurality of table partitions prior to determining to update the materialized view;
generating the first data comprises:
determining which partitions of the plurality of table partitions of the one or more base tables have been modified,
wherein determining which partitions comprises identifying a set of one or more table partitions of the plurality of table partitions;
identifying one or more view partitions of the plurality of view partitions that correspond to the set of one or more table partitions;

creating the database object comprises creating one or more database objects, each corresponding to a different view partition of the one or more view partitions;
generating the first data comprises executing one or more queries, each of which is processed against one or more table partitions in the set of one or more table partitions, wherein executing the one or more queries causes result data to be identified;
the first data is at least a subset of the result data.

8. The method of claim 7 , wherein the number of the one or more database objects is less than the number of the plurality of view partitions.

9. The method of claim 1 , further comprising:
prior to determining to update the materialized view:
generating a materialized view log that is separate from the materialized view and the one or more base tables;
storing changes that are made to one or more of the one or more base tables in the materialized view log;

wherein generating the first data comprises combining third data from the materialized view with fourth data from the materialized view log.

10. The method of claim 9 , wherein combining comprises performing a full outer join operation between the third data and the fourth data.

11. One or more storage media storing instructions which, when executed by one or more processors, cause:
determining to update a materialized view that is based on one or more base tables and a defining query that is used to create the materialized view when executed against the one or more base tables;
wherein the determining comprises determining that one or more changes have been made to at least one of the one or more base tables and have not been made to the materialized view;
in response to determining to update the materialized view:
creating a database object that is separate from the materialized view and the one or more base tables;
generating first data for insertion into the database object, wherein generating th

Assignees

• Oracle Int Corp

Inventors

• Ding Luping
• Yu Tsae-Feng
• Witkowski Andrew

Classifications

• G06F16/24539 Primary

  using cached or materialised query results · CPC title

• G06F16/2445

  Data retrieval commands; View definitions · CPC title

• G06F16/2393

  Updating materialised views · CPC title

• G06F17/30383

  Physics · mapped topic

• G06F17/30457 Primary

  Physics · mapped topic