A materialized view is a stored summary containing precomputed results. Materialized views allow for significantly faster data warehouse query processing. The Oracle database server automatically rewrites queries to use the summary data, rather than retrieving data from detail tables by doing expensive joins and aggregate operations. This query rewrite facility is totally transparent to the application, which is not aware of the existence of the materialized view.
The DBA's first step in creating materialized views is to define dimensions. These represent the hierarchies that are present in the real world; for instance, multiple months make up a quarter, multiple districts make up a region, etc. The CREATE MATERIALIZED VIEW statement is used to create a materialized view. This statement includes a sub query, typically a join or a data aggregation (GROUP BY), the results of which comprise the materialized view.
A materialized view is maintained by a refresh process. The refresh process can be done automatically when a commit is done on a detail table, or it can be controlled manually by the DBA. A refresh is specified as complete or incremental. A complete refresh truncates existing data, then repopulates the summary with new data from the detail tables. An incremental refresh updates only changed data.
To create a materialized view in a users own schema, the user must have the CREATE MATERIALIZED VIEW, CREATE TABLE, CREATE INDEX, and CREATE VIEW system privileges. To create a materialized view in another user's schema, a user must have the CREATE ANY MATERIALIZED VIEW system privilege.
The DBA's first step in creating materialized views is to define dimensions. These represent the hierarchies that are present in the real world; for instance, multiple months make up a quarter, multiple districts make up a region, etc. The CREATE MATERIALIZED VIEW statement is used to create a materialized view. This statement includes a sub query, typically a join or a data aggregation (GROUP BY), the results of which comprise the materialized view.
A materialized view is maintained by a refresh process. The refresh process can be done automatically when a commit is done on a detail table, or it can be controlled manually by the DBA. A refresh is specified as complete or incremental. A complete refresh truncates existing data, then repopulates the summary with new data from the detail tables. An incremental refresh updates only changed data.
To create a materialized view in a users own schema, the user must have the CREATE MATERIALIZED VIEW, CREATE TABLE, CREATE INDEX, and CREATE VIEW system privileges. To create a materialized view in another user's schema, a user must have the CREATE ANY MATERIALIZED VIEW system privilege.
No comments:
Post a Comment