Oracle9i Data Warehousing Guide Release 1 (9.0.1) Part Number A90237-01 |
|
This chapter introduces you to the use of materialized views and discusses:
Typically, data flows from one or more online transaction processing (OLTP) databases into a data warehouse on a monthly, weekly, or daily basis. The data is normally processed in a staging file before being added to the data warehouse. Data warehouses commonly range in size from tens of gigabytes to a few terabytes. Usually, the vast majority of the data is stored in a few very large fact tables.
One technique employed in data warehouses to improve performance is the creation of summaries. Summaries are special kinds of aggregate views that improve query execution times by precalculating expensive joins and aggregation operations prior to execution and storing the results in a table in the database. For example, you can create a table to contain the sums of sales by region and by product.
The summaries or aggregates that are referred to in this book and in literature on data warehousing are created in Oracle using a schema object called a materialized view. Materialized views can perform a number of roles, such as improving query performance or providing replicated data.
Prior to Oracle8i, organizations using summaries spent a significant amount of time creating summaries manually, identifying which summaries to create, indexing the summaries, updating them, and advising their users on which ones to use. The introduction of summary management in Oracle8i eases the workload of the database administrator and means the end user no longer has to be aware of the summaries that have been defined. The database administrator creates one or more materialized views, which are the equivalent of a summary. The end user queries the tables and views in the database. The query rewrite mechanism in the Oracle server automatically rewrites the SQL query to use the summary tables. This mechanism reduces response time for returning results from the query. Materialized views within the data warehouse are transparent to the end user or to the database application.
Although materialized views are usually accessed through the query rewrite mechanism, an end user or database application can construct queries that directly access the summaries. However, serious consideration should be given to whether users should be allowed to do this because any change to the summaries will affect the queries that reference them.
In data warehouses, you can use materialized views to precompute and store aggregated data such as the sum of sales. Materialized views in these environments are often referred to as summaries, because they store summarized data. They can also be used to precompute joins with or without aggregations. A materialized view eliminates the overhead associated with expensive joins and aggregations for a large or important class of queries.
In distributed environments, you can use materialized views to replicate data at distributed sites and to synchronize updates done at those sites with conflict resolution methods. The materialized views as replicas provide local access to data that otherwise would have to be accessed from remote sites. Materialized views are also useful in remote data marts.
See Also:
Oracle9i Replication and Oracle9i Heterogeneous Connectivity Administrator's Guide for details on distributed and mobile computing |
You can also use materialized views to download a subset of data from central servers to mobile clients, with periodic refreshes and updates between clients and the central servers.
This chapter focuses on the use of materialized views in data warehouses.
See Also:
Oracle9i Replication and Oracle9i Heterogeneous Connectivity Administrator's Guide for details on distributed and mobile computing |
Use materialized views in data warehouses to increase the speed of queries on very large databases. Queries to large databases often involve joins between tables, aggregations such as SUM
, or both. These operations are expensive in terms of time and processing power. The type of materialized view you create determines how the materialized view is refreshed and used by query rewrite.
You can use materialized views in a number of ways, and you can use almost identical syntax to perform a number of roles. For example, a materialized view can replicate data, a process formerly achieved by using the CREATE
SNAPSHOT
statement. Now CREATE
MATERIALIZED
VIEW
is a synonym for CREATE
SNAPSHOT
.
Materialized views improve query performance by precalculating expensive join and aggregation operations on the database prior to execution and storing the results in the database. The query optimizer automatically recognizes when an existing materialized view can and should be used to satisfy a request. It then transparently rewrites the request to use the materialized view. Queries go directly to the materialized view and not to the underlying detail tables. In general, rewriting queries to use materialized views rather than detail tables improves response.
When using query rewrite, create materialized views that satisfy the largest number of queries. For example, if you identify 20 queries that are commonly applied to the detail or fact tables, then you might be able to satisfy them with five or six well-written materialized views. A materialized view definition can include any number of aggregations (SUM
, COUNT(x)
, COUNT(*)
, COUNT(DISTINCT x)
, AVG
, VARIANCE
, STDDEV
, MIN
, and MAX
). It can also include any number of joins. If you are unsure of which materialized views to create, Oracle provides a set of advisory procedures in the DBMS_OLAP
package to help in designing and evaluating materialized views for query rewrite. These functions are also known as the Summary Advisor or the Advisor.
If a materialized view is to be used by query rewrite, it must be stored in the same database as the fact or detail tables on which it relies. A materialized view can be partitioned, and you can define a materialized view on a partitioned table. You can also define one or more indexes on the materialized view.
Unlike indexes, materialized views can be accessed directly using a SELECT
statement.
Note: The techniques shown in this chapter illustrate how to use materialized views in data warehouses. Materialized views can also be used by Oracle Replication. See Oracle9i Replication for further information. |
Summary management consists of:
DBMS_OLAP
package. Collectively, these functions are called the Summary Advisor, and are also available as part of Oracle Enterprise Manager.
Many large decision support system (DSS) databases have schemas that do not closely resemble a conventional data warehouse schema, but that still require joins and aggregates. The use of summary management features imposes no schema restrictions, and can enable some existing DSS database applications to improve performance without the need to redesign the database or the application.
Figure 8-2 illustrates the use of summary management in the warehousing cycle. After the data has been transformed, staged, and loaded into the detail data in the warehouse, you can invoke the summary management process. First, use the Advisor to plan how you will use summaries. Then, create summaries and design how queries will be rewritten.
Understanding the summary management process during the earliest stages of data warehouse design can yield large dividends later in the form of higher performance, lower summary administration costs, and reduced storage requirements.
Hierarchies describe the business relationships and common access patterns in the database. An analysis of the dimensions, combined with an understanding of the typical work load, can be used to create materialized views.
Some basic data warehousing terms are defined here:
Dimension tables usually change slowly over time and are not modified on a periodic schedule. They are used in long-running decision support queries to aggregate the data returned from the query into appropriate levels of the dimension hierarchy.
The vast majority of data in a data warehouse is stored in a few very large fact tables that are updated periodically with data from one or more operational online transaction processing (OLTP) databases.
Fact tables include measures such as sales, units, and inventory.
fact.sales
.
fact.revenues
- fact.expenses
.
fact_a.revenues
- fact_b.expenses
.
Fact tables also contain one or more foreign keys that organize the business transactions by the relevant business entities such as time, product, and market. In most cases, these foreign keys are non-null, form a unique compound key of the fact table, and each foreign key joins with exactly one row of a dimension table.
Summary management can perform many useful functions, including query rewrite and materialized view refresh, even if your data warehouse design does not follow these guidelines. However, you will realize significantly greater query execution performance and materialized view refresh performance benefits and you will require fewer materialized views if your schema design complies with these guidelines.
A materialized view definition includes any number of aggregates, as well as any number of joins. In several ways, a materialized view behaves like an index:
In the case of normalized or partially normalized dimensions (a dimension that is stored in more than one table), identify how these tables are joined. Note whether the joins between the dimension tables can guarantee that each child-side row joins with one and only one parent-side row. In the case of denormalized dimensions, determine whether the child-side columns uniquely determine the parent-side (or attribute) columns. These relationships can be enabled with constraints, using the NOVALIDATE
and RELY
options if the relationships represented by the constraints are guaranteed by other means. Note that if the joins between fact and dimension tables do not support this relationship, you still gain significant performance advantages from defining the dimension with the CREATE
DIMENSION
statement. Another alternative, subject to some restrictions, is to use outer joins in the materialized view definition (that is, in the CREATE
MATERIALIZED
VIEW
statement).
You must not create dimensions in any schema that does not satisfy these relationships. Incorrect results can be returned from queries otherwise.
Before starting to define and use the various components of summary management, you should review your schema design to abide by the following guidelines wherever possible:
Guideline 1: |
Dimensions should either be denormalized (each dimension contained in one table) or the joins between tables in a normalized or partially normalized dimension should guarantee that each child-side row joins with exactly one parent-side row. The benefits of maintaining this condition are described in "Creating Dimensions". |
|
You can enforce this condition by adding |
Guideline 2: |
If dimensions are denormalized or partially denormalized, hierarchical integrity must be maintained between the key columns of the dimension table. Each child key value must uniquely identify its parent key value, even if the dimension table is denormalized. Hierarchical integrity in a denormalized dimension can be verified by calling the |
Guideline 3: |
Fact and dimension tables should similarly guarantee that each fact table row joins with exactly one dimension table row. This condition must be declared, and optionally enforced, by adding |
Guideline 4: |
Incremental loads of your detail data should be done using the SQL*Loader direct-path option, or any bulk loader utility that uses Oracle's direct-path interface. This includes |
Guideline 5: |
Range/composite partition your tables by a monotonically increasing the time column if possible (preferably of type |
Guideline 6: |
After each load and before refreshing your materialized view, use the |
Guideline 7: |
If a time dimension appears in the materialized view as a time column, partition and index the materialized view in the same manner as you have the fact tables. Include a local concatenated index on all the materialized view keys. |
Guidelines 1 and 2 are more important than guideline 3. If your schema design does not follow guidelines 1 and 2, it does not then matter whether it follows guideline 3. Guidelines 1, 2, and 3 affect both query rewrite performance and materialized view refresh performance.
If you are concerned with the time required to enable constraints and whether any constraints might be violated, use the ENABLE
NOVALIDATE
with the RELY
clause to turn on constraint checking without validating any of the existing constraints. The risk with this approach is that incorrect query results could occur if any constraints are broken. Therefore, as the designer, you must determine how clean the data is and whether the risk of wrong results is too great.
The SELECT
clause in the materialized view creation statement defines the data that the materialized view is to contain. Only a few restrictions limit what can be specified. Any number of tables can be joined together. However, they cannot be remote tables if you wish to take advantage of query rewrite. Besides tables, other elements such as views, inline views (subqueries in the FROM
clause of a SELECT
statement), subqueries, and materialized views can all be joined or referenced in the SELECT
clause.
The types of materialized views are:
In data warehouses, materialized views normally contain aggregates as shown in Example 8-1 below. For fast refresh to be possible, the SELECT
list must contain all of the GROUP
BY
columns (if present), and there must be a COUNT(*)
and a COUNT(column)
on any aggregated columns. Also, materialized view logs must be present on all tables referenced in the query that defines the materialized view. The valid aggregate functions are: SUM
, COUNT(x)
, COUNT(*)
, AVG
, VARIANCE
, STDDEV
, MIN
, and MAX
, and the expression to be aggregated can be any SQL value expression.
Fast refresh for a materialized view containing joins and aggregates is possible after any type of DML to the base tables (direct load or conventional INSERT
, UPDATE
, or DELETE
). It can be defined to be refreshed ON
COMMIT
or ON
DEMAND
. A REFRESH ON
COMMIT
, materialized view will be refreshed automatically when a transaction that does DML to one of the materialized views commits. The time taken to complete the commit may be slightly longer than usual when this method is chosen. This is because the refresh operation is performed as part of the commit process. Therefore, this method may not be suitable if many users are concurrently changing the tables upon which the materialized view is based.
Here are some examples of materialized views with aggregates. Note that materialized view logs are only created because this materialized view will be fast refreshed.
CREATE MATERIALIZED VIEW LOG ON products WITH SEQUENCE, ROWID (prod_id, prod_name, prod_desc, prod_subcategory, prod_subcat_desc, prod_ category, prod_cat_desc, prod_weight_class, prod_unit_of_measure, prod_pack_ size, supplier_id, prod_status, prod_list_price, prod_min_price) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW LOG ON sales WITH SEQUENCE, ROWID (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount, cost) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW product_sales_mv PCTFREE 0 TABLESPACE demo STORAGE (initial 8k next 8k pctincrease 0) BUILD IMMEDIATE REFRESH FAST ENABLE QUERY REWRITE AS SELECT p.prod_name, SUM(amount) AS dollar_sales, COUNT(*) AS cnt, COUNT(amount) AS cnt_amt FROM sales s, products p WHERE s.prod_id = p.prod_id GROUP BY prod_name;
The statement above creates a materialized view product_sales_mv
that computes total number and value of sales for a product. It is derived by joining the tables sales
and products
on the column prod_id
. The materialized view is populated with data immediately because the build method is immediate and it is available for use by query rewrite. In this example, the default refresh method is FAST
, which is allowed because the appropriate materialized view logs have been created on tables product
and sales
.
CREATE MATERIALIZED VIEW store_sales_mv PCTFREE 0 TABLESPACE mviews STORAGE (initial 16k next 16k pctincrease 0) BUILD DEFERRED REFRESH COMPLETE ON DEMAND ENABLE QUERY REWRITE AS SELECT s.store_name, SUM(dollar_sales) AS sum_dollar_sales FROM store s, fact f WHERE f.store_key = s.store_key GROUP BY s.store_name;
Example 8-2 creates a materialized view store_sales_mv
that computes the sum of sales by store
. It is derived by joining the tables store
and fact
on the column store_key
. The materialized view does not initially contain any data, because the build method is DEFERRED
. A complete refresh is required for the first refresh of a build deferred materialized view. When it is refreshed and once populated, this materialized view can be used by query rewrite.
CREATE MATERIALIZED VIEW LOG ON fact WITH ROWID (store_key, time_key, dollar_sales, unit_sales) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW sum_sales PARALLEL BUILD IMMEDIATE REFRESH FAST ON COMMIT AS SELECT f.store_key, f.time_key, COUNT(*) AS count_grp, SUM(f.dollar_sales) AS sum_dollar_sales, COUNT(f.dollar_sales) AS count_dollar_sales, SUM(f.unit_sales) AS sum_unit_sales, COUNT(f.unit_sales) AS count_unit_sales FROM fact f GROUP BY f.store_key, f.time_key;
This example creates a materialized view that contains aggregates on a single table. Because the materialized view log has been created, the materialized view is fast refreshable. If DML is applied against the fact table, then the changes will be reflected in the materialized view when the commit is issued.
Table 8-1 illustrates the aggregate requirements for materialized views.
Note that COUNT(*)
must always be present. Oracle recommends that you include the optional aggregates in column Z
in the materialized view in order to obtain the most efficient and accurate fast refresh of the aggregates.
Oracle9i enables a single materialized view to contain multiple aggregate groups. A materialized view holding multiple aggregate groups supports On-Line Analytical Processing (OLAP) needs well. OLAP environments require fast response time for analytical queries under multiuser workloads. Typically, OLAP queries compare aggregates at different levels of granularity. For efficient processing of these queries, it is common to precompute all possible levels of aggregation and store them in materialized views.
When a single materialized view stores all the levels of aggregation needed in an OLAP environment, it enables efficient creation and data refresh.
Materialized views for OLAP environments have the following characteristics:
GROUPING
SETS
, ROLLUP
, or CUBE
in the GROUP
BY
clause of the query definition. These grouping features are described in Chapter 18, "SQL for Aggregation in Data Warehouses".
SELECT
list includes a GROUPING_ID
function using all the GROUP
BY
expressions as its arguments.
Below is an example of a materialized view suited to OLAP needs, containing multiple aggregate groups. The materialized view is created using the GROUPING
SETS
extension to the GROUP
BY
clause. The example presents a retail database with a sample schema and some materialized views to illustrate how materialized views with aggregation for OLAP can be created.
/*the following tables and their columns*/ STORE (store_key, store_name, store_city, store_state, store_country) TIME (time_key, time_day, time_week, time_month) FACT (store_key, prod_key, time_key, dollar_sales) CREATE MATERIALIZED VIEW sales_mv REFRESH FAST ON DEMAND ENABLE QUERY REWRITE AS SELECT store_country, store_state, store_city, prod_category, prod_subcategory, prod_name, GROUPING_ID(store_country, store_state, store_city, prod_category, prod_subcategory, prod_name) gid SUM(dollar_sales) s_sales, COUNT(dollar_sales) c_sales, COUNT(*) c_star FROM sales s, product p, store st WHERE s.store_id = st.store_id and s.prod_id = p.prod_id GROUP BY GROUPING SETS ((store_country, store_state, store_city), (store_country, prod_category, prod_subcategory, prod_name), (prod_category, prod_subcategory, prod_name),(store_country, prod_category));
This is a materialized view that stores aggregates at four different levels. Queries can be rewritten to use this materialized view if they require one or more these groupings.
The creation and fast refresh of such a materialized view is very efficient as all the joins are factored out (and hence, computed only once) and some groupings can be derived from other groupings, rather than going to the joined base data. For example, group (store_country
, prod_category
) can be computed from (store_country
, prod_category
, prod_subcategory
, prod_name
). In addition to creation and refresh efficiency, a single database object containing all the required groupings can be easier to manage than many materialized views each holding just one aggregate group.
If an OLAP environment's queries cover the full range of aggregate groupings possible in its data set, it may be best to materialize the whole hierarchical cube. This means that each dimension's aggregation hierarchy is precomputed in combination with each of the other dimensions. Naturally, precomputing a full hierarchical cube requires more disk space and higher creation and refresh times than a small set of aggregate groups. The trade-off in processing time and disk space versus query performance needs to be factored in before deciding to create it. Example 8-5 is an example of a hierarchical materialized view:
CREATE MATERIALIZED VIEW sales_hierarchical_cube_mv REFRESH FAST ON DEMAND ENABLE QUERY REWRITE AS SELECT store_country, store_state, store_city, prod_category, prod_subcategory, prod_name, time_month, time_week, time_day, GROUPING_ID(store_country, store_state, store_city, prod_category, prod_subcategory, prod_name, time_month, time_week, time_day) gid SUM(dollar_sales) s_sales, COUNT(dollar_sales) c_sales, COUNT(*) c_star FROM sales s, product p, store st, time t WHERE s.store_id = st.store_id and s.prod_id = p.prod_id and s.time_id = t.time_id GROUP BY ROLLUP(store_country, store_state, store_city), ROLLUP(prod_category, prod_subcategory, prod_name), ROLLUP(time_month, time_week, time_day);
The materialized view sales_hierarchical_cube_mv
above is a superset of the materialized view sales_mv
. sales_hierarchical_cube_mv
in Example 8-4 contains the many groupings generated by the concatenated ROLLUP
s in its GROUP
BY
clause.
Materialized views with multiple aggregate groups will give their best performance when partitioned appropriately. The most effective partitioning scheme for these materialized views is to use composite partitioning. For the top level partitioning, use LIST
partitioning with the GROUPING_ID
column. For the subpartitioning, use whichever column best fits the data distribution characteristics.
By partitioning the materialized views this way, you enable partition pruning for queries rewritten against this materialized view: only relevant aggregate groups will be accessed, greatly reducing the query processing cost.
Some materialized views contain only joins and no aggregates, such as in Example 8-6, where a materialized view is created that joins the fact
table to the store
table. The advantage of creating this type of materialized view is that expensive joins will be precalculated.
Fast refresh for a materialized view containing only joins is possible after any type of DML to the base tables (direct-path or conventional INSERT
, UPDATE
, or DELETE
).
A materialized view containing only joins can be defined to be refreshed ON COMMIT
or ON
DEMAND
. If it is ON
COMMIT
, the refresh is performed at commit time of the transaction that does DML on the materialized view's detail table. Oracle does not allow self-joins in materialized join views.
If you specify REFRESH
FAST
, Oracle performs further verification of the query definition to ensure that fast refresh can be performed if any of the detail tables change. These additional checks are:
SELECT
list of the materialized view query definition.
WHERE
clause. However, if there are outer joins, the WHERE
clause cannot have any selections. Further, if there are outer joins, all the joins must be connected by AND
s and must use the equality (=) operator.
If some of the above restrictions are not met, you can create the materialized view as REFRESH
FORCE
to take advantage of fast refresh when it is possible. If the materialized view is created as ON
COMMIT
, Oracle performs all of the fast refresh checks. If one of the tables did not meet all of the criteria, but the other tables did, the materialized view would still be fast refreshable with respect to the other tables for which all the criteria are met.
A materialized view log should contain the rowid of the master table. It is not necessary to add other columns.
To speed up refresh, you should create indexes on the materialized view's columns that store the rowids of the fact table.
CREATE MATERIALIZED VIEW LOG ON fact WITH ROWID; CREATE MATERIALIZED VIEW LOG ON time WITH ROWID; CREATE MATERIALIZED VIEW LOG ON store WITH ROWID; CREATE MATERIALIZED VIEW detail_fact_mv PARALLEL BUILD IMMEDIATE REFRESH FAST AS SELECT f.rowid "fact_rid", t.rowid "time_rid", s.rowid "store_rid", s.store_key, s.store_name, f.dollar_sales, f.unit_sales, f.time_key FROM fact f, time t, store s WHERE f.store_key = s.store_key(+) AND f.time_key = t.time_key(+);
In this example, in order to perform a fast refresh, UNIQUE
constraints should exist on s.store_key
and t.time_key
. You should also create indexes on the columns fact_rid
, time_rid
, and store_rid
, as illustrated below. This will improve the refresh performance.
CREATE INDEX mv_ix_factrid ON detail_fact_mv(fact_rid);
Alternatively, if the example shown above did not include the columns time_rid
and store_rid
, and if the refresh method was REFRESH
FORCE
, then this materialized view would be fast refreshable only if the fact table was updated but not if the tables time or store were updated.
CREATE MATERIALIZED VIEW detail_fact_mv PARALLEL BUILD IMMEDIATE REFRESH FORCE AS SELECT f.rowid "fact_rid", s.store_key, s.store_name, f.dollar_sales, f.unit_sales, f.time_key FROM fact f, time t, store s WHERE f.store_key = s.store_key(+) AND f.time_key = t.time_key(+);
A nested materialized view is a materialized view whose definition is based on another materialized view. A nested materialized view can reference other relations in the database in addition to referencing materialized views.
In a data warehouse, you typically create many aggregate views on a single join (for example, rollups along different dimensions). Incrementally maintaining these distinct materialized aggregate views can take a long time, because the underlying join has to be performed many times. By using nested materialized views, the join is performed just once (while maintaining the materialized view containing joins only). Incremental maintenance of single-table aggregate materialized views is very fast due to the self-maintenance refresh operations on this class of views.
You can create a materialized view containing joins only or a single-table aggregate materialized view on a single table on top of the following:
All the underlying objects (materialized views or tables) on which the materialized view is defined must have a materialized view log. All the underlying objects are treated as if they were tables. All the existing options for materialized views containing joins only and single-table aggregate materialized views can be used. Thus, ON
COMMIT
REFRESH
is supported for these types of nested materialized views.
Using the tables and their columns from Example 8-4, the following materialized views illustrate how nested materialized views can be created.
/* create the materialized view logs */ CREATE MATERIALIZED VIEW LOG ON fact WITH ROWID; CREATE MATERIALIZED VIEW LOG ON store WITH ROWID; CREATE MATERIALIZED VIEW LOG ON time WITH ROWID; /*create materialized join view join_fact_store_time as fast refreshable at COMMIT time */ CREATE MATERIALIZED VIEW join_fact_store_time REFRESH FAST ON COMMIT AS SELECT s.store_key, s.store_name, f.dollar_sales, t.time_key, t.time_day, f.prod_key, f.rowid frid, t.rowid trid, s.rowid srid FROM fact f, store s, time t WHERE f.time_key = t.time_key AND f.store_key = s.store_key;
To create a nested materialized view on the table join_fact_store_time
, you would have to create a materialized view log on the table. Because this will be a single-table aggregate materialized view on join_fact_store_time
, you need to log all the necessary columns and use the INCLUDING
NEW
VALUES
clause.
/* create materialized view log on join_fact_store_time */ CREATE MATERIALIZED VIEW LOG ON join_fact_store_time WITH rowid (store_name, time_day, dollar_sales) INCLUDING new values; /* create the single-table aggregate materialized view sum_sales_store_time on
join_fact_store_time as fast refreshable at COMMIT time. */ CREATE MATERIALIZED VIEW sum_sales_store_time REFRESH FAST ON COMMIT AS SELECT COUNT(*) cnt_all, SUM(dollar_sales) sum_sales, COUNT(dollar_sales) cnt_sales, store_name, time_day FROM join_fact_store_time GROUP BY store_name, time_day;
This schema can be diagrammatically represented as in Figure 8-3.
Materialized views with joins and aggregates can be nested if they are refreshed as COMPLETE
REFRESH
. Thus, you can arbitrarily nest materialized views having joins and aggregates. No FAST
REFESH
is possible for these materialized views.
Note that the ON
COMMIT
REFRESH
clause is not available for complex materialized views. Because you have to invoke the refresh functions manually, ordering has to be taken into account. This is because the refresh for a materialized view that is built on other materialized views will use the current state of the other materialized views, whether they are fresh or not. You can find the dependent materialized views for a particular object using the PL/SQL function GET_MV_DEPENDENCIES
in the DBMS_MVIEW
package.
You should keep a couple of points in mind when deciding whether to use nested materialized views.
REFRESH
FAST
clause, then you can define a nested materialized view.
REFRESH
FAST
and nested if all the materialized views that they depend on are either materialized join views or single-table aggregate materialized views.
Here are some guidelines on how to use nested materialized views:
ON
COMMIT
or ON
DEMAND
. The choice would depend on the application using the materialized views. If you expect the materialized views to always remain fresh, then all the materialized views should have the ON
COMMIT
refresh option. If the time window for refresh does not permit refreshing all the materialized views at commit time, then the appropriate materialized views could be created with (or altered to have) the ON
DEMAND
refresh option.
Only nested materialized join views and nested single-table aggregate materialized views can use fast refresh. If you want complete refresh for all of your materialized views, then you can still nest these materialized views.
Some restrictions exist on the way you can nest materialized views. Oracle allows nesting a materialized view only when all the immediate dependencies of the materialized view do not have any dependencies among themselves. Thus, in the dependency tree, a materialized view can never be a parent as well as a grandparent of an object. For example, Figure 8-4 shows an impermissible materialized view because MV2
is both a parent and grandparent of Table2
.
Nested materialized views incur the space overhead of materializing the join and having a materialized view log. In contrast, materialized aggregate views do not have demanding space requirements for the materialized join view and its log, but they have relatively long refresh times due to multiple computations of the same join.
A materialized view can be created with the CREATE
MATERIALIZED
VIEW
statement or using Oracle Enterprise Manager. Example 8-8 creates the materialized view store_sales_mv
.
CREATE MATERIALIZED VIEW store_sales_mv PCTFREE 0 TABLESPACE mviews STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0) PARALLEL BUILD IMMEDIATE REFRESH COMPLETE ENABLE QUERY REWRITE AS SELECT s.store_name, SUM(dollar_sales) AS sum_dollar_sales FROM store s, fact f WHERE f.store_key = s.store_key GROUP BY s.store_name;
It is not uncommon in a data warehouse to have already created summary or aggregation tables, and you might not wish to repeat this work by building a new materialized view. In this case, the table that already exists in the database can be registered as a prebuilt materialized view. This technique is described in "Registering Existing Materialized Views".
Once you have selected the materialized views you want to create, follow the steps below for each materialized view.
CREATE
MATERIALIZED
VIEW
statement to create and, optionally, populate the materialized view. If a user-defined materialized view already exists, then use the ON
PREBUILT
TABLE
clause in the CREATE
MATERIALIZED
VIEW
statement. Otherwise, use the BUILD
IMMEDIATE
clause to populate the materialized view immediately, or the BUILD
DEFERRED
clause to populate the materialized view later. A BUILD
DEFERRED
materialized view is disabled for use by query rewrite until the first REFRESH
, after which it will be automatically enabled, provided the ENABLE
QUERY
REWRITE
clause has been specified.
Oracle9i SQL Reference for descriptions of the SQL statements
See Also:
CREATE
MATERIALIZED
VIEW
, ALTER
MATERIALIZED
VIEW
, and DROP
MATERIALIZED
VIEW
The name of a materialized view must conform to standard Oracle naming conventions. However, if the materialized view is based on a user-defined prebuilt table, then the name of the materialized view must exactly match that table name.
If you already have a naming convention for tables and indexes, you might consider extending this naming scheme to the materialized views so that they are easily identifiable. For example, instead of naming the materialized view sum_of_sales
, it could be called sum_of_sales_mv
to denote that this is a materialized view and not a table or view.
Unless the materialized view is based on a user-defined prebuilt table, it requires and occupies storage space inside the database. Therefore, the storage needs for the materialized view should be specified in terms of the tablespace where it is to reside and the size of the extents.
If you do not know how much space the materialized view will require, then the DBMS_OLAP.ESTIMATE_SIZE
package, which is described in Chapter 16, "Summary Advisor", can estimate the number of bytes required to store this materialized view. This information can then assist the design team in determining the tablespace in which the materialized view should reside.
Two build methods are available for creating the materialized view, as shown in the following table. If you select BUILD
IMMEDIATE
, the materialized view definition is added to the schema objects in the data dictionary, and then the fact or detail tables are scanned according to the SELECT
expression and the results are stored in the materialized view. Depending on the size of the tables to be scanned, this build process can take a considerable amount of time.
An alternative approach is to use the BUILD
DEFERRED
clause, which creates the materialized view without data, thereby enabling it to be populated at a later date using the DBMS_MVIEW.REFRESH
package described in Chapter 14, "Maintaining the Data Warehouse".
Build Method | Description |
---|---|
|
Create the materialized view and then populate it with data. |
|
Create the materialized view definition but do not populate it with data. |
Before creating a materialized view, you can verify what types of query rewrite are possible by calling the procedure DBMS_MVIEW.EXPLAIN_MVIEW
. Once the materialized view has been created, you can use DBMS_MVIEW.EXPLAIN_REWRITE
to find out if (or why not) it will rewrite a specific query.
Even though a materialized view is defined, it will not automatically be used by the query rewrite facility. You must set the QUERY_REWRITE_ENABLED
initialization parameter to TRUE
before using query rewrite. You also must specify the ENABLE
QUERY
REWRITE
clause if the materialized view is to be considered available for rewriting queries.
If this clause is omitted or specified as DISABLE
QUERY
REWRITE
when the materialized view is created, the materialized view can subsequently be enabled for query rewrite with the ALTER
MATERIALIZED
VIEW
statement.
If you define a materialized view as BUILD
DEFERRED
, it is not eligible for query rewrite until it is populated with data.
Query rewrite is not possible with all materialized views. If query rewrite is not occurring when expected, check to see if your materialized view satisfies all of the following conditions.
ROWNUM
, SYSDATE
, non-repeatable PL/SQL functions, and so on).
RAW
or LONG
RAW
datatypes or object REF
s.
UNION
, MINUS
, and so on). However, a materialized view can have multiple query blocks (for example, inline views in the FROM
clause and subselects in the WHERE
or HAVING
clauses).
PREBUILT
, the precision of the columns must agree with the precision of the corresponding SELECT
expressions unless overridden by the WITH
REDUCED
PRECISION
clause.
SYS
.
SELECT
and GROUP
BY
lists, if present, must be the same in the query of the materialized view.
AVG(AVG(x))
or AVG(x)
+ AVG(x)
are not allowed.
CONNECT
BY
clauses are not allowed.
When you define a materialized view, you can specify two refresh options: how to refresh and what type of refresh. If unspecified, the defaults are assumed as ON DEMAND
and FORCE
.
The two refresh execution modes are: ON
COMMIT
and ON
DEMAND
. Depending on the materialized view you create, some of the options may not be available.
When a materialized view is maintained using the ON
COMMIT
method, the time required to complete the commit may be slightly longer than usual. This is because the refresh operation is performed as part of the commit process. Therefore this method may not be suitable if many users are concurrently changing the tables upon which the materialized view is based.
If you anticipate performing insert, update or delete operations on tables referenced by a materialized view concurrently with the refresh of that materialized view, and that materialized view includes joins and aggregation, Oracle recommends you use ON
COMMIT
fast refresh rather than ON
DEMAND
fast refresh.
If you think the materialized view did not refresh, check the alert log or trace file.
If a materialized view fails during refresh at COMMIT
time, you must explicitly invoke the refresh procedure using the DBMS_MVIEW
package after addressing the errors specified in the trace files. Until this is done, the view will no longer be refreshed automatically at commit time.
You can specify how you want your materialized views to be refreshed from the detail tables by selecting one of four options: COMPLETE
, FAST
, FORCE
, and NEVER
.
Whether the fast refresh option is available depends upon the type of materialized view. You can call the procedure DBMS_MVIEW.EXPLAIN_MVIEW
to determine whether fast refresh is possible. Fast refresh is available for both general classes of materialized views:
The defining query of the materialized view is restricted as follows:
Not contain references to non-repeating expressions like SYSDATE
and ROWNUM
.
Not contain references to RAW
or LONG
RAW
data types.
Defining queries for materialized views with joins only and no aggregates have these restrictions on fast refresh:
GROUP
BY
clauses or aggregates.
WHERE
clause of the query contains outer joins, then unique constraints must exist on the join columns of the inner join table.
WHERE
clause. However, if there are outer joins, the WHERE
clause cannot have any selections. Furthermore, if there are outer joins, all the joins must be connected by AND
s and must use the equality (=) operator.
FROM
list must appear in the SELECT
list of the query.
FROM
list of the query.
Defining queries for materialized views with joins and aggregates have these restrictions on fast refresh:
Fast refresh is supported for both ON
COMMIT
and ON
DEMAND
materialized views, however the following restrictions apply:
SUM
, COUNT
, AVG
, STDDEV
, VARIANCE
, MIN
and MAX
are supported for fast refresh.
COUNT(*)
must be specified.
AGG(expr)
, the corresponding COUNT(expr)
must be present.
VARIANCE(expr)
or STDDEV(expr
) is specified, COUNT(expr)
and SUM(expr)
must be specified. Oracle recommends that SUM(expr *expr)
be specified. See Table 8-1 for further details.
SELECT
list must contain all GROUP
BY
columns.
COMPATIBILITY
parameter must be set to 9.0 if the materialized aggregate view has inline views, outer joins, self joins or grouping sets and FAST
REFRESH
is specified during creation. Note that all other requirements for fast refresh specified above must also be satisfied.
FROM
clause can be fast refreshed provided the views can be completely merged. For information on which views will merge, refer to the Oracle9i Database Performance Guide and Reference.
WHERE
clause.
AND
s and must use the equality (=) operator.
CUBE
, ROLLUP
, Grouping Sets, or concatenation of them, the following restrictions apply:
SELECT
list should contain grouping distinguisher that can either be a GROUPING_ID
function on all GROUP
BY
expressions or GROUPING
functions one for each GROUP
BY
expression. For example, if the GROUP
BY
clause of the materialized view is "GROUP
BY
CUBE(a, b)
", then the SELECT
list should contain either "GROUPING_ID(a, b)
" or "GROUPING(a)
AND
GROUPING(b)
" for the materialized view to be fast refreshable.
GROUP
BY
should not result in any duplicate groupings. For example, "GROUP BY a, ROLLUP(b, a)
" is not fast refreshable because it results in duplicate groupings "(a, b), (a, b), AND (a)
".
An ORDER
BY
clause is allowed in the CREATE
MATERIALIZED
VIEW
statement. It is used only during the initial creation of the materialized view. It is not used during a full refresh or a fast refresh.
To improve the performance of queries against large materialized views, store the rows in the materialized view in the order specified in the ORDER
BY
clause. This initial ordering provides physical clustering of the data. If indexes are built on the columns by which the materialized view is ordered, accessing the rows of the materialized view using the index often reduces the time for disk I/O due to the physical clustering.
The ORDER
BY
clause is not considered part of the materialized view definition. As a result, there is no difference in the manner in which Oracle detects the various types of materialized views (for example, materialized join views with no aggregates). For the same reason, query rewrite is not affected by the ORDER
BY
clause. This feature is similar to the CREATE
TABLE
... ORDER
BY
capability that exists in Oracle.
Materialized view logs are required if you want to use fast refresh. They are defined using a CREATE
MATERIALIZED
VIEW
LOG
statement on the base table that is to be changed. They are not created on the materialized view. For fast refresh of materialized views, the definition of the materialized view logs must specify the ROWID
clause. In addition, for aggregate materialized views, it must also contain every column in the table referenced in the materialized view, the INCLUDING
NEW
VALUES
clause and the SEQUENCE
clause.
An example of a materialized view log is shown below where one is created on the table sales
.
CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount, cost) INCLUDING NEW VALUES;
The keyword SEQUENCE
is new for Oracle9i and Oracle recommends that this clause be included in your materialized view log statement unless you are sure that you will never perform a mixed DML operation (a combination of INSERT
, UPDATE
, or DELETE
operations on multiple tables).
The boundary of a mixed DML operation is determined by whether the materialized view is ON
COMMIT
or ON
DEMAND
.
ON
COMMIT
, the mixed DML statements occur within the same transaction because the refresh of the materialized view will occur upon commit of this transaction.
ON
DEMAND
, the mixed DML statements occur between refreshes. An example of a materialized view log is shown below where one is created on the table sales that includes the SEQUENCE
keyword.
CREATE MATERIALIZED VIEW LOG ON sales WITH SEQUENCE, ROWID (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount, cost) INCLUDING NEW VALUES;
A materialized view can also be created using Oracle Enterprise Manager by selecting the materialized view object type. There is no difference in the information required if this approach is used. However, you must complete three property sheets and you must ensure that the option Enable
Query
Rewrite
on the General
sheet is selected.
When using certain materialized views, you must ensure that your NLS parameters are the same as when you created the materialized view. Materialized views with this restriction are:
SELECT
list of a materialized view, or inside an aggregate of a materialized aggregate view. This restriction does not apply to expressions that involve only numeric data, for example, a+b
where a
and b
are numeric fields.
Some data warehouses have implemented materialized views in ordinary user tables. Although this solution provides the performance benefits of materialized views, it does not:
Because of these limitations, and because existing materialized views can be extremely large and expensive to rebuild, you should register your existing materialized view tables with Oracle whenever possible. You can register a user-defined materialized view with the CREATE
MATERIALIZED
VIEW
... ON
PREBUILT
TABLE
statement. Once registered, the materialized view can be used for query rewrites or maintained by one of the refresh methods, or both.
The contents of the table must reflect the materialization of the defining query at the time you register it as a materialized view, and each column in the defining query must correspond to a column in the table that has a matching datatype. However, you can specify WITH
REDUCED
PRECISION
to allow the precision of columns in the defining query to be different from that of the table columns.
The table and the materialized view must have the same name, but the table retains its identity as a table and can contain columns that are not referenced in the defining query of the materialized view. These extra columns are known as unmanaged columns. If rows are inserted during a refresh operation, each unmanaged column of the row is set to its default value. Therefore, the unmanaged columns cannot have NOT
NULL
constraints unless they also have default values.
Unmanaged columns are not supported by single-table aggregate materialized views or materialized views containing joins only.
Materialized views based on prebuilt tables are eligible for selection by query rewrite provided the parameter QUERY_REWRITE_INTEGRITY
is set to at least the level of STALE_TOLERATED
or TRUSTED
.
When you drop a materialized view that was created on a prebuilt table, the table still exists--only the materialized view is dropped.
When a prebuilt table is registered as a materialized view and query rewrite is desired, the parameter QUERY_REWRITE_INTEGRITY
must be set to at least STALE_TOLERATED
because, when it is created, the materialized view is marked as unknown. Therefore, only stale integrity modes can be used.
The following example illustrates the two steps required to register a user-defined table. First, the table is created, then the materialized view is defined using exactly the same name as the table. This materialized view sum_sales_tab
is eligible for use in query rewrite.
CREATE TABLE sum_sales_tab PCTFREE 0 TABLESPACE mviews STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0) AS SELECT f.store_key SUM(dollar_sales) AS dollar_sales, SUM(unit_sales) AS unit_sales, SUM(dollar_cost) AS dollar_cost FROM fact f GROUP BY f.store_key; CREATE MATERIALIZED VIEW sum_sales_tab ON PREBUILT TABLE WITHOUT REDUCED PRECISION ENABLE QUERY REWRITE AS SELECT f.store_key, SUM(dollar_sales) AS dollar_sales, SUM(unit_sales) AS unit_sales, SUM(dollar_cost) AS dollar_cost FROM fact f GROUP BY f.store_key;
In some cases, user-defined materialized views are refreshed on a schedule that is longer than the update cycle. For example, a monthly materialized view might be updated only at the end of each month, and the materialized view values always refer to complete time periods. Reports written directly against these materialized views implicitly select only data that is not in the current (incomplete) time period. If a user-defined materialized view already contains a time dimension:
If the user-defined materialized view does not contain a time dimension, then:
Because of the large volume of data held in a data warehouse, partitioning is an extremely useful option when designing a database.
Partitioning the fact tables improves scalability, simplifies system administration, and makes it possible to define local indexes that can be efficiently rebuilt. Partitioning the fact tables provides greater opportunities for fast refresh of the materialized view, when the partition maintenance operation occurs. Partitioning the fact tables also improves the opportunity of fast refreshing the materialized view when the partition maintenance operation occurs.
Partitioning a materialized view also has benefits for refresh, because the refresh procedure can use parallel DML to maintain the materialized view.
See Also:
Chapter 5, "Parallelism and Partitioning in Data Warehouses" for further details about partitioning |
It is possible and advantageous to track freshness to a finer grain than the entire materialized view. The ability to identify which rows in a materialized view are affected by a certain detail table partition, is known as Partition Change Tracking (PCT). When one or more of the detail tables are partitioned, it may be possible to identify the specific rows in the materialized view that correspond to a modified detail partition(s); those rows become stale when a partition is modified while all other rows remain fresh.
Partition Change Tracking can be used to identify which materialized view rows correspond to a particular detail table partition is used to support fast refresh after partition maintenance operations on detail tables. For instance, if a detail table partition is truncated or dropped, the affected rows in the materialized view are identified and deleted. Identifying which materialized view rows are fresh or stale, rather than considering the entire materialized view as stale, allows query rewrite to use those rows that are fresh while in QUERY_REWRITE_INTEGRITY
=ENFORCED
or TRUSTED
modes.
To support PCT, a materialized view must satisfy the following requirements:
DBMS_MVIEW.PMARKER
function.
GROUP
BY
clause is used, the partition key column or the partition marker must be present in the GROUP
BY
clause.
COMPATIBILITY
initialization parameter must be a minimum of 9.0.0.0.0.
Partition change tracking requires sufficient information in the materialized view to be able to correlate each materialized view row back to its corresponding detail row in the source partitioned detail table. This can be accomplished by including the detail table partition key columns in the select list and, if GROUP
BY
is used, in the GROUP
BY
list. Depending on the desired level of aggregation and the distinct cardinalities of the partition key columns, this has the unfortunate effect of significantly increasing the cardinality of the materialized view. For example, say a popular metric is the revenue generated by a product during a given year. If the sales
table were partitioned by time_id
, it would be a required field in the SELECT
clause and the GROUP
BY
clause of the materialized view. If there were 1000 different products sold each day, it would substantially increase the number of rows in the materialized view.
In many cases, the advantages of PCT will be offset by this restriction for highly aggregated materialized views. The DBMS_MVIEW.PMARKER
function is designed to significantly reduce the cardinality of the materialized view (see Example 8-9 for an example). The function returns a partition identifier that uniquely identifies the partition for a specified row within a specified partition table. The DBMS_MVIEW.PMARKER
function is used instead of the partition key column in the SELECT
and GROUP
BY
clauses.
Unlike the general case of a PL/SQL function in a materialized view, use of the DBMS_MVIEW.PMARKER
does not prevent rewrite with that materialized view even when the rewrite mode is QUERY_REWRITE_INTEGRITY=ENFORCED
.
The following example uses the Sales
History
Schema and the three detail tables sales
, products
, and times
to create two materialized views. For this example, sales
is a partitioned table using the time_id
column and products
is partitioned by the prod_category
column. times
is not a partitioned table.
The first materialized view is for the yearly sales revenue per product.
The second materialized view is for monthly customer sales. As customers tend to purchase in bulk, sales average just two orders per customer per month. Therefore, the impact of including the time_id
in the materialized view will not unacceptably increase the number of rows stored. However, most orders are large and contain many different products. With approximately 1000 different products sold each day, including the time_id
in the materialized view would substantially increase the cardinality. This materialized view uses the DBMS_MVIEW.PMARKER
function.
The detail tables must have materialized view logs for FAST
REFRESH
.
CREATE MATERIALIZED VIEW LOG ON SALES WITH ROWID (prod_id, time_id, quantity_sold, amount) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW LOG ON PRODUCTS WITH ROWID (prod_id, prod_name, prod_desc) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW LOG ON TIMES WITH ROWID (time_id, calendar_month_name, calendar_year) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW cust_mth_sales_mv BUILD DEFERRED REFRESH FAST ON DEMAND ENABLE QUERY REWRITE AS SELECT s.time_id, p.prod_id, SUM(s.quantity_sold), SUM(s.amount), p.prod_name, t.calendar_month_name, COUNT(*), COUNT(s.quantity_sold), COUNT(s.amount) FROM sales s, products p, times t WHERE s.time_id = t.time_id AND s.prod_id = p.prod_id GROUP BY t.calendar_month_name, s.prod_id, p.prod_name, s.time_id;
cust_mth_sales_mv
includes the partition key column from table sales
(time_id
) in both its select and group by lists. This enables PCT on table sales
for materialized view cust_mth_sales_mv
. However, the GROUP
BY
and SELECT
lists include PRODUCTS.PROD_ID
rather the partition key column (PROD_CATEGORY
) of the products
table. Therefore, PCT is not enabled on table products
for this materialized view. In other words, any partition maintenance operation to the sales
table will allow a PCT fast refresh of cust_mth_sales_mv
. However, PCT fast refresh is not possible after any kind of modification to the products
table. To correct this, the GROUP
BY
and SELECT
lists must include column PRODUCTS.PROD_CATEGORY
. Following a partition maintenance operation, such as a drop partition, it is recommended a PCT fast refresh be performed on any materialized view that is referencing the table upon which the partition operations are undertaken.
CREATE MATERIALIZED VIEW prod_yr_sales_mv BUILD DEFERRED REFRESH FAST ON DEMAND ENABLE QUERY REWRITE AS SELECT DBMS_MVIEW.PMARKER(s.rowid), DBMS_MVIEW.PMARKER(p.rowid), s.prod_id, SUM(s.amount), SUM(s.quantity_sold), p.prod_name, t.calendar_year, COUNT(*), COUNT(s.amount), COUNT(s.quantity_sold) FROM sales s, products p, times t WHERE s.time_id = t.time_id AND s.prod_id = p.prod_id GROUP BY DBMS_MVIEW.PMARKER (s.rowid), DBMS_MVIEW.PMARKER (p.rowid), t.calendar_year, s.prod_id, p.prod_name;
prod_yr_sales_mv
includes the DBMS_MVIEW.PMARKER
function on the sales
and products
tables in both its SELECT
and GROUP
BY
lists. This enables partition change tracking on both the sales
table and the products
table with significantly less cardinality impact than grouping by the respective partition key columns. In this example, the desired level of aggregation for the prod_yr_sales_mv
is to group by times.calendar_year
. Using the DBMS_MVIEW.PMARKER
function, the materialized view cardinality is increased only by a factor of the number of partitions in the sales
table times, the number of partitions in the products
table. This would generally be significantly less than the cardinality impact of including the respective partition key columns.
A subsequent INSERT
statement adds a new row to the sales_part3
partition of table SALES
. At this point, because cust_mth_sales_mv
and prod_yr_sales_mv
have partition change tracking available on table sales
, Oracle can determine that those rows in these materialized views corresponding to sales_part3
are stale, while all other rows in these materialized views are unchanged in their freshness state. An INSERT
INTO
products
statement is not tracked for materialized view cust_mth_sales_mv
. Therefore, cust_mth_sales_mv
becomes completely stale when the products
table is modified in this way.
Partitioning a materialized view involves defining the materialized view with the standard Oracle partitioning clauses, as illustrated in the example below. This example creates a materialized view called part_sales_mv
, which uses three partitions, may be fast refreshed, and is eligible for query rewrite.
CREATE MATERIALIZED VIEW part_sales_mv PARALLEL PARTITION by RANGE (time_key) (PARTITION month1 VALUES LESS THAN (TO_DATE('31-12-1997', 'DD-MM-YYYY')) PCTFREE 0 PCTUSED STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0) TABLESPACE sf1, PARTITION month2 VALUES LESS THAN (TO_DATE('31-01-1998', 'DD-MM-YYYY')) PCTFREE 0 PCTUSED STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0) TABLESPACE sf2, PARTITION month3 VALUES LESS THAN (TO_DATE('28-02-1998', 'DD-MM-YYYY')) PCTFREE 0 PCTUSED STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0) TABLESPACE sf3) BUILD DEFERRED REFRESH FAST ENABLE QUERY REWRITE AS SELECT f.store_key, f.time_key, SUM(f.dollar_sales) AS sum_dol_sales, SUM(f.unit_sales) AS sum_unit_sales FROM fact f GROUP BY f.time_key, f.store_key;
Alternatively, a materialized view can be registered to a partitioned prebuilt table as illustrated in the following example:
CREATE TABLE part_fact_tab(time_key, store_key, sum_dollar_sales, sum_unit_sale) PARALLEL PARTITION by RANGE (time_key) ( PARTITION month1 VALUES LESS THAN (TO_DATE('31-12-1997', 'DD-MM-YYYY')) PCTFREE 0 PCTUSED 99 STORAGE (INITITAL 64k NEXT 16k PCTINCREASE 0) TABLESPACE sf1, PARTITIION month2 VALUES LESS THAN (TO_DATE('31-01-1998', 'DD-MM-YYYY')) PCTFREE 0 PCTUSED 99 STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0) TABLESPACE sf2, PARTITION month3 VALUES LESS THAN (TO_DATE('28-02-1998', DD-MM-YYYY')) PCTFREE 0 PCTUSED 99 STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0) TABLESPACE sf3) AS SELECT f.time_key, f.store_key, SUM(f.dollar_sales) AS sum_dollar_sales, SUM(f.unit_sales) AS sum_unit_sales FROM fact f GROUP BY f.time_key, f.store_key; CREATE MATERIALIZED VIEW part_fact_tab ON PREBUILT TABLE ENABLE QUERY REWRITE AS SELECT f.time_key, f.store_key, SUM(f.dollar_sales) AS sum_dollar_sales, SUM(f.unit_sales) AS sum_unit_sales FROM fact f GROUP BY f.time_key , f.store_key;
In this example, the table part_fact_tab
has been partitioned over three months and then the materialized view was registered to use the prebuilt table. This materialized view is eligible for query rewrite because the ENABLE
QUERY REWRITE
clause has been included.
When the data warehouse or data mart contains a time dimension, it is often desirable to archive the oldest information and then reuse the storage for new information. This is called the rolling window scenario. If the fact tables or materialized views include a time dimension and are horizontally partitioned by the time attribute, then management of rolling materialized views can be reduced to a few fast partition maintenance operations provided the unit of data that is rolled out equals, or is at least aligned with, the range partitions.
If you plan to have rolling materialized views in your warehouse, you should determine how frequently you plan to perform partition maintenance operations, and you should plan to partition fact tables and materialized views to reduce the amount of system administration overhead required when old data is aged out.
You are not restricted to using range partitions. For example, a composite partition using both a time value and a key value could result in a good partition solution for your data.
See Also:
Chapter 14, "Maintaining the Data Warehouse", for further details regarding |
The two most common operations on a materialized view are query execution and fast refresh, and each operation has different performance requirements. Query execution might need to access any subset of the materialized view key columns, and might need to join and aggregate over a subset of those columns. Consequently, query execution usually performs best if a single-column bitmap index is defined on each materialized view key column.
In the case of materialized views containing only joins using fast refresh, Oracle recommends that indexes be created on the columns that contain the rowids to improve the performance of the refresh operation.
If a materialized view using joins and aggregates is fast refreshable, then an index is automatically created unless USING
NO
INDEX
is specified in the CREATE
MATERIALIZED
VIEW
statement.
Dependencies related to materialized views are automatically maintained to ensure correct operation. When a materialized view is created, the materialized view depends on the detail tables referenced in its definition. Any DML operation, such as a INSERT
, or DELETE
, UPDATE
, or DDL operation on any dependency in the materialized view will cause it to become invalid. To revalidate a materialized view, use the ALTER
MATERIALIZED
VIEW
COMPILE
statement.
A materialized view is automatically revalidated when it is referenced. In many cases, the materialized view will be successfully and transparently revalidated. However, if a column has been dropped in a table referenced by a materialized view or the owner of the materialized view did not have one of the query rewrite privileges and that privilege has now been granted to the owner, the statement:
ALTER MATERIALIZED VIEW mview_name ENABLE QUERY REWRITE
should be used to revalidate the materialized view.
The state of a materialized view can be checked by querying the data dictionary views USER_MVIEWS
or ALL_MVIEWS
. The column STALENESS
will show one of the values FRESH
, STALE
, UNUSABLE
, UNKNOWN
, or UNDEFINED
to indicate whether the materialized view can be used. The state is maintained automatically, but it can be manually updated by issuing an ALTER
MATERIALIZED
VIEW
<name> COMPILE
statement.
To create a materialized view in your own schema, you must have the CREATE
MATERIALIZED
VIEW
privilege and the SELECT
privilege to any tables referenced that are in another schema. To create a materialized view in another schema, you must have the CREATE
ANY
MATERIALIZED
VIEW
privilege and the owner of the materialized view needs SELECT
privileges to the tables referenced if they are from another schema.
Moreover, if you enable query rewrite on a materialized view that references tables outside your schema, you must have the GLOBAL
QUERY
REWRITE
privilege or the QUERY
REWRITE
object privilege on each table outside your schema.
If the materialized view is on a prebuilt container, the creator, if different from the owner, must have SELECT
WITH
GRANT
privilege on the container table.
If you continue to get a privilege error while trying to create a materialized view and you believe that all the required privileges have been granted, then the problem is most likely due to a privilege not being granted explicitly and trying to inherit the privilege from a role instead. The owner of the materialized view must have explicitly been granted SELECT
access to the referenced tables if the tables are in a different schema.
If the materialized view is being created with ON
COMMIT
REFRESH
specified, then the owner of the materialized view requires an additional privilege if any of the tables in the defining query are outside the owner's schema. In that case, the owner requires the ON
COMMIT
REFRESH
system privilege or the ON
COMMIT
REFRESH
object privilege on each table outside the owner's schema.
Five modifications can be made to a materialized view. You can:
FAST/FORCE/COMPLETE/NEVER
)
ON
COMMIT/ON
DEMAND
)
All other changes are achieved by dropping and then re-creating the materialized view.
The COMPILE
clause of the ALTER
MATERIALIZED
VIEW
statement can be used when the materialized view has been invalidated. This compile process is quick, and allows the materialized view to be used by query rewrite again.
See Also:
Oracle9i SQL Reference for further information about the |
Use the DROP
MATERIALIZED
VIEW
statement to drop a materialized view. For example:
DROP MATERIALIZED VIEW sales_sum_mv;
This statement drops the materialized view sales_sum_mv
. If the materialized view was prebuilt on a table, then the table is not dropped, but it can no longer be maintained with the refresh mechanism or used by query rewrite. Alternatively, you can drop a materialized view using Oracle Enterprise Manager.
You can use the DBMS_MVIEW
.EXPLAIN_MVIEW
procedure to learn what is possible with a materialized view or potential materialized view. In particular, this procedure enables you to determine:
Using this procedure is straightforward. You simply call DBMS_MVIEW.EXPLAIN_MVIEW
, passing in as a single parameter the schema and materialized view name for an existing materialized view. Alternatively, you can specify the SELECT
string for a potential materialized view. The materialized view or potential materialized view is then analyzed and the results are written into either a table called MV_CAPABILITIES_TABLE
, which is the default, or to an array called MSG_ARRAY
.
Note that you must run the utlxmv.sql
script prior to calling EXPLAIN_MVIEW
except when you are only concerned with VARRAYs
. The script is found in the admin directory. In addition, you must create MV_CAPABILITIES_TABLE
in the current schema. An explanation of the various capabilities is in Table 8-2, and all the possible messages are listed in Table 8-3.
The DBMS_MVIEW
.EXPLAIN_MVIEW
procedure has the following parameters:
STMT_ID
An optional parameter. A client-supplied unique identifier to associate output rows with specific invocations of EXPLAIN_MVIEW.
MV
The name of an existing materialized view or the query definition of a potential materialized view you want to analyze.
MSG_ARRAY
The PL/SQL varray that receives the output.
DBMS_MVIEW.EXPLAIN_MVIEW
analyzes the specified materialized view in terms of its refresh and rewrite capabilities and inserts its results (in the form of multiple rows) into MV_CAPABILITIES_TABLE
or MSG_ARRAY
.
See Also:
Oracle9i Supplied PL/SQL Packages and Types Reference for further information about the |
The following PL/SQL declarations that are made for you in the DBMS_MVIEW
package show the order and datatypes of these parameters for explaining an existing materialized view and a potential materialized view with output to a table and to a VARRAY
.
Explain an existing or potential materialized view with output to MV_CAPABILITIES_TABLE
DBMS_MVIEW.EXPLAIN_MVIEW (mv IN VARCHAR2, stmt_id IN VARCHAR2:= NULL);
Explain an existing or potential materialized view with output to a VARRAY
:
DBMS_MVIEW.EXPLAIN_MVIEW (mv IN VARCHAR2, msg_array OUT SYS.ExplainMVArrayType);
One of the simplest ways to use DBMS_MVIEW.EXPLAIN_MVIEW
is with the MV_CAPABILITIES_TABLE
, which has the following structure:
CREATE TABLE MV_CAPABILITIES_TABLE ( STMT_ID VARCHAR(30), -- client-supplied unique statement identifier MV VARCHAR(30), -- NULL for SELECT based EXPLAIN_MVIEW CAPABILITY_NAME VARCHAR(30), -- A descriptive name of particular -- capabilities, such as REWRITE. -- See Table 8-2 POSSIBLE CHARACTER(1), -- Y = capability is possible -- N = capability is not possible RELATED_TEXT VARCHAR(2000), -- owner.table.column, and so on related to -- this message RELATED_NUM NUMBER, -- When there is a numeric value -- associated with a row, it goes here. MSGNO INTEGER, -- When available, message # explaining -- why disabled or more details when -- enabled. MSGTXT VARCHAR(2000), -- Text associated with MSGNO SEQ NUMBER); -- Useful in ORDER BY clause when -- selecting from this table.
You can use the utlxmv.sql
script found in the admin directory to create MV_CAPABILITIES_TABLE
.
First, create the materialized view. Alternatively, you can use EXPLAIN_MVIEW
on a potential materialized view using its SELECT
statement.
CREATE MATERIALIZED VIEW cal_month_sales_mv BUILD IMMEDIATE REFRESH FORCE ENABLE QUERY REWRITE AS SELECT t.calendar_month_desc, SUM(s.amount) AS dollars FROM sales s, times t WHERE s.time_id = t.time_id GROUP BY t.calendar_month_desc;
Then, you invoke EXPLAIN_MVIEW
with the materialized view to explain.
EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW ('SH.CAL_MONTH_SALES_MV'); SELECT capability_name, possible, SUBSTR(related_text,1,8) AS rel_text, SUBSTR(msgtxt,1,60) AS msgtxt FROM MV_CAPABILITIES_TABLE ORDER BY seq;
You need to use the SEQ
column in an ORDER
BY
clause so the rows will display in a logical order. If a capability is not possible, N
will appear in the P
column and an explanation in the MSGTXT
column. If a capability is not possible for more than one reason, a row is displayed for each reason.
CAPABILITY_NAME P REL_TEXT MSGTXT --------------- - -------- ------ PCT N REFRESH_COMPLETE Y REFRESH_FAST N REWRITE Y PCT_TABLE N SALES no partition key or PMARKER in select list PCT_TABLE N TIMES relation is not a partitioned table REFRESH_FAST_AFTER_INSERT N SH.TIMES mv log must have new values REFRESH_FAST_AFTER_INSERT N SH.TIMES mv log must have ROWID REFRESH_FAST_AFTER_INSERT N SH.TIMES mv log does not have all necessary columns REFRESH_FAST_AFTER_INSERT N SH.SALES mv log must have new values REFRESH_FAST_AFTER_INSERT N SH.SALES mv log must have ROWID REFRESH_FAST_AFTER_INSERT N SH.SALES mv log does not have all necessary columns REFRESH_FAST_AFTER_ONETAB_DML N DOLLARS SUM(expr) without COUNT(expr) REFRESH_FAST_AFTER_ONETAB_DML N see the reason why REFRESH_FAST_AFTER_INSERT is disabled REFRESH_FAST_AFTER_ONETAB_DML N COUNT(*) is not present in the select list REFRESH_FAST_AFTER_ONETAB_DML N SUM(expr) without COUNT(expr) REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled REFRESH_FAST_AFTER_ANY_DML N SH.TIMES mv log must have sequence REFRESH_FAST_AFTER_ANY_DML N SH.SALES mv log must have sequence REFRESH_PCT N PCT is not possible on any of the detail tables in the materialized view REWRITE_FULL_TEXT_MATCH Y REWRITE_PARTIAL_TEXT_MATCH Y REWRITE_GENERAL Y REWRITE_PCT N PCT is not possible on any detail tables
See Also:
Chapter 14, "Maintaining the Data Warehouse" and Chapter 22, "Query Rewrite" for further details about PCT |
Table 8-2 lists explanations for values in the CAPABILITY_NAME
column.
Table 8-3 lists the semantics for RELATED_TEXT
and RELATED_NUM
columns.
The motivation for using materialized views is to improve performance, but the overhead associated with materialized view management can become a significant system management problem. Materialized view management activities include:
After the initial effort of creating and populating the data warehouse or data mart, the major administration overhead is the update process, which involves:
The update process must generally be performed within a limited period of time known as the update window. The update window depends on the update frequency (such as daily or weekly) and the nature of the business. For a daily update frequency, an update window of two to six hours might be typical.
You need to know your update window for the following activities:
A popular and efficient way to load data into a warehouse or data mart is to use SQL*Loader with the DIRECT
or PARALLEL
option or to use another loader tool that uses the Oracle direct-path API.
See Also:
Oracle9i Database Utilities for the restrictions and considerations when using SQL*Loader with the |
Loading strategies can be classified as one-phase or two-phase. In one-phase loading, data is loaded directly into the target table, quality assurance tests are performed, and errors are resolved by performing DML operations prior to refreshing materialized views. If a large number of deletions are possible, then storage utilization can be adversely affected, but temporary space requirements and load time are minimized. The DML that may be required after one-phase loading causes multitable aggregate materialized views to become unusable in the safest rewrite integrity level.
In a two-phase loading process:
INSERT
AS
SELECT
with the PARALLEL
or APPEND
hint.
NOVALIDATE
option.
Immediately after loading the detail data and updating the indexes on the detail data, the database can be opened for operation, if desired. You can disable query rewrite at the system level with ALTER
SYSTEM
SET
QUERY_REWRITE_ENABLED
= FALSE
until all the materialized views are refreshed.
If QUERY_REWRITE_INTEGRITY
=STALE_TOLERATED
, access to the materialized view can be allowed at the session level to any users who do not require the materialized views to reflect the data from the latest load by using ALTER
SESSION
SET
QUERY_REWRITE_INTEGRITY
=TRUE
. This scenario does not apply when QUERY_REWRITE_INTEGRITY
is either ENFORCED
or TRUSTED
because the system ensures in these modes that only materialized views with updated data participate in a query rewrite.
|
![]() Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|