In Oracle, a summary is created using an object known as a materialized view (MV). A materialized view precomputes and stores the result of a SQL query. Note that a summary is typically an aggregate query; however, materialized views can be created for any query. In this respect, a materialized view is quite similar to a conventional view. The difference is that by materializing the view, you save the results in the database. In the remainder of this chapter, we will use the term materialized view instead of summary.
Materialized views can also be used for replication of data. Before Oracle 8i, such materialized views were called snapshots. Materialized views include all the features that were available with Snapshots and more. In this book, we will only focus on the application of materialized views to data warehousing.
A materialized view is created using the CREATE MATERIALIZED VIEW statement. The following example creates a materialized view named MONTHLY_SALES_MV, which contains the total sales of each product for each month.
CREATE MATERIALIZED VIEW MONTHLY_SALES_MV PCTFREE 0 TABLESPACE mview <- storage parameters STORAGE (initial 64k next 64k pctincrease 0) BUILD IMMEDIATE <- when to populate it REFRESH FORCE <- how to refresh it ON DEMAND <- when to refresh it ENABLE QUERY REWRITE <- use query rewrite or not AS SELECT t.month, t.year, p.product_id, <- what it contains SUM (f.purchase_price) as sum_of_sales, COUNT (f.purchase_price) as total_sales, COUNT(*) as cstar FROM time t, product p, purchases f WHERE t.time_key = f.purchase_date AND f.product_id = p.product_id GROUP BY t.month, t.year, p.product_id;
The materialized view definition contains the following parts, highlighted with arrows in the example:
A description of how to physically store the materialized view (i.e., the storage clause)
When to populate it-immediately upon creation or later-or, is it an existing summary table that must be converted into a materialized view
How to refresh it when data in the underlying detail tables has changed
When to refresh it-at the end of each transaction or when explicitly requested
Whether to use it for query rewrite or not
A SELECT statement, which describes the contents of the materialized view
Figure 4.1 shows a materialized view being created in Oracle Enterprise Manager. All the options for the CREATE MATERIALIZED VIEW statement may be specified via this graphical interface. There is also an option to collect statistics on the materialized view upon creation. The Explain button can be used to run the EXPLAIN_MVIEW utility, which helps identify the capabilities of the materialized view. See section 4.4 for a discussion of EXPLAIN_MVIEW.
Figure 4.1: Creating a materialized view in Oracle Enterprise Manager.
We will now explain each clause of the materialized view creation statement in detail.
The materialized view has a storage specification, where you can specify the tablespace to store the data in, the initial allocation size, and the size of its extents.
In our example, the materialized view is being placed in the tablespace called "MVIEW," the first extent will be 64 K, and all subsequent extents will be 64 K.
The materialized view definition describes when you would like the materialized view to be populated with data. If you specify BUILD IMMEDIATE, as in the example code segment, the materialized view is populated immediately upon creation. If you specify BUILD DEFERRED, the materialized view will be populated when you perform the REFRESH operation. If you have an existing summary table that you would like to manage using Oracle's Summary Management, you can use the ON PREBUILT TABLE clause. This converts the existing table into a materialized view and makes it available to query rewrite, if desired.
A materialized view is not considered by query rewrite until it has been populated with data.
As new detail data is periodically loaded into the data warehouse, the materialized views have to be refreshed to reflect the changes. Four refresh options are available:
A materialized view can be completely rebuilt by specifying REFRESH COMPLETE. Or, it can be incrementally updated by specifying the REFRESH FAST option. It is usually faster to perform a fast refresh than a complete refresh; however, it is not always possible to do a fast refresh.
The example shown previously, uses REFRESH FORCE, which means Oracle will perform a fast refresh if possible and only do a COMPLETE refresh if necessary. Materialized views that use the NEVER REFRESH option will never be refreshed by any of the procedures supplied by Oracle. This option can be useful if you have legacy refresh procedures that cannot be eliminated or would like to store some historical data that must not be updated.
As the underlying detail data change, the materialized views that are based on those detail tables become stale and no longer reflect the results of summarizing all the detail data. The most common ways of refresh are ON COMMIT or ON DEMAND.
If your business is such that it requires the materialized view be kept up-to-date with the detail data at the transaction level, the materialized view can be refreshed at the end of each transaction, by specifying the ON COMMIT option.
On the other hand, with ON DEMAND refresh you must manually request that the materialized view be refreshed. ON DEMAND refresh allows you to control when the materialized view will be refreshed. This is useful in a warehouse where new data is loaded in a batch, after which the materialized views must be refreshed.
You can also specify that a materialized view be refreshed on a periodic schedule by specifying a start date and subsequent refresh intervals using the START WITH and NEXT clauses.
The facilities for refreshing materialized views will be discussed in section 4.3.
In most cases you will want queries to be transparently rewritten to use your materialized views. Whenever a materialized view is defined, if you wish it to be eligible for query rewrite, then include the clause ENABLE QUERY REWRITE, as shown in the example. If you don't want a materialized view to be used for query rewrite, use the DISABLE QUERY REWRITE clause. If you already have materialized views and would like Oracle to refresh them, but not make them eligible for query rewrite, disable query rewrite in the materialized view definition.
The details of using query rewrite will be discussed in section 4.6.
The materialized view definition includes a SELECT statement, which describes its contents. A typical query for a materialized view could include any of the following:
A WHERE clause, which joins the fact table and one or more dimension tables. In our example, the fact table, PURCHASES, is joined with the dimension tables, TIME and PRODUCTS. Selections may also be present to restrict the data in the materialized views.
One or more aggregate operators. In our example, COUNT is used to obtain the total sales. Operators that can be included are SUM, MIN, MAX, AVG, COUNT(*), COUNT, COUNT(DISTINCT x), VARIANCE, and STDDEV. Analytical functions and window aggregates discussed in Chapter 3 can also be used in a materialized view.
A GROUP BY clause. In the example, we are counting the total items sold by the columns year, month, and product_id.
The materialized view in the previous example includes aggregation and hence is called a materialized aggregate view.
A materialized view can also be used to join two or more tables without any aggregation. This type of a materialized view is called a materialized join view (MJV) and is used to precompute expensive joins. A materialized join view can be used in lieu of a bitmap join index. A bitmap join index is geared more toward star queries and will work better than an MJV if you want to compute combinations of selections against the index keys. On the other hand, a materialized join view can be used to answer a wider class of queries using query rewrite. However, an MJV will typically be much larger than a bitmap join index.
The following example, shows a materialized join view that is used to compute the join between PURCHASES and CUSTOMER tables.
CREATE MATERIALIZED VIEW customer_purchases_mv BUILD IMMEDIATE REFRESH COMPLETE AS SELECT c.sex, c.occupation, f.purchase_price FROM purchases f, customer c WHERE f.customer_id = c.customer_id
The examples in this chapter will focus on these two types of materialized views.
If you already have a data warehouse with summaries stored in user tables and do not want to completely recreate them, Summary Management can still be used to manage them and to perform query rewrite.
To register your summary table with Summary Management, create a materialized view using the ON PREBUILT TABLE clause. This converts the existing table into a materialized view. If you specify the ENABLE QUERY REWRITE clause, the materialized view can be used for query rewrite. For example, you may already have a table named monthly_customer_sales, which summarizes the amount of money each customer spent each month, as represented by the following query:
SELECT t.year, t.month, c.customer_id, SUM(f.purchase_price) as dollar_sales FROM time t, purchases f, customer c WHERE f.time_key = t.time_key AND f.customer_id = c.customer_id GROUP BY t.year, t.month, c.customer_id;
The monthly_customer_sales table is described as follows:
SQL> describe monthly_customer_sales; Name Null? Type ----------------------------------------- -------- ------------ YEAR NUMBER(4) MONTH NUMBER(2) CUSTOMER_ID VARCHAR2(10) DOLLAR_SALES NUMBER
If you'd like to use this table as a materialized view and make it eligible for query rewrite, you can issue the following statement. Note that when creating the materialized view using an existing table, the materialized view must have the same name as the table.
CREATE MATERIALIZED VIEW monthly_customer_sales ON PREBUILT TABLE ENABLE QUERY REWRITE AS SELECT t.year, t.month, c.customer_id, SUM(f.purchase_price) AS dollar_sales FROM time t, purchases f, customer c WHERE f.time_key = t.time_key AND f.customer_id = c.customer_id GROUP BY t.year, t.month, c.customer_id;
This materialized view can now be used to transparently rewrite queries and can be refreshed using Oracle's supplied refresh procedures.
A word of caution when creating materialized views with the PRE-BUILT clause-Oracle will not verify that the existing table corresponds to the query you use when defining the materialized view. You must ensure that the contents of the existing table correctly represent the summarization of the detail data. Otherwise, you may see incorrect results with query rewrite.
For query rewrite to use a materialized view with the PREBUILT option, you must set the initialization parameter QUERY_REWRITE_INTEGRITY to TRUSTED or STALE_TOLERATED, as explained in section 4.6.8.
You can partition a materialized view as you would a table, using any of the partitioning methods available in Oracle. In a warehouse, it is common to partition materialized views in the same way as the fact table. Range-list partitioning may also be a good way to partition a materialized view when it contains multiple dimensions such as time (range) and geography (list).
In the following example, the materialized view is partitioned using range partitioning.
CREATE MATERIALIZED VIEW MONTHLY_SALES_MV PARTITION by RANGE (time_key) ( partition purchases_jan2002 values less than (TO_DATE('01-02-2002', 'DD-MM-YYYY')) pctfree 0 pctused 99 storage (initial 64k next 64k pctincrease 0) tablespace purchases_jan2002 , partition purchases_feb2002 values less than (TO_DATE('01-03-2002', 'DD-MM-YYYY')) pctfree 0 pctused 99 storage (initial 64k next 64k pctincrease 0) tablespace purchases_feb2002 , partition purchases_mar2002 values less than (TO_DATE('01-04-2002', 'DD-MM-YYYY')) pctfree 0 pctused 99 storage (initial 64k next 64k pctincrease 0) tablespace purchases_mar2002 ) BUILD IMMEDIATE REFRESH FORCE ON DEMAND ENABLE QUERY REWRITE AS SELECT t.time_key, p.product_id, SUM (f.purchase_price) as sum_of_sales FROM time t, product p, purchases f WHERE t.time_key = f.purchase_date AND f.product_id = p.product_id GROUP BY t.time_key, p.product_id;
Partitioning a materialized view can speed up refresh by using parallel DML. It also enables queries that use the materialized view to take advantage of optimizations such as partition pruning.
You can also build indexes on your materialized view to improve the performance of your queries. The techniques used to index materialized views are similar to those for any table.
In the following example, a concatenated index is created on the grouping columns of the MONTHLY_SALES_MV materialized view, shown earlier in this chapter. Also, bitmap indexes are created on the grouping columns. These indexes will improve performance of queries that get rewritten to use the materialized view.
CREATE INDEX easydw.products_by_month_concat_index ON MONTHLY_SALES_MV (month, year, product_id) pctfree 5 tablespace indx storage (initial 64k next 64k pctincrease 0) ; CREATE BITMAP INDEX easydw.total_products_by_month_index ON monthly_sales_mv (month, year); CREATE BITMAP INDEX easydw.total_products_by_id_index ON monthly_sales_mv (product_id);
Oracle automatically creates an index on some materialized views to speed up fast refresh. To suppress creation of this index, use the NO INDEX clause in the CREATE MATERIALIZED VIEW statement.
Some information in the data warehouse may have restricted access, and it is important to ensure that the appropriate security policies are implemented regarding access to materialized views. You may want to allow users access to a materialized view but not allow them to see the underlying detail data. For example, you may allow a user to see the average salary by department but not to see an individual employee's salary. On the other hand, you want to ensure that materialized views and query rewrite are not used as a mechanism for bypassing security. As with all database objects, Oracle provides a privilege model for creating and altering materialized views.
To create a materialized view in a user's own schema, the user must have the CREATE MATERIALIZED VIEW privilege. To create a materialized view in another user's schema, the creator must have the CREATE ANY MATERIALIZED VIEW privilege. The owner of the schema where the materialized view will be placed must have the CREATE TABLE and CREATE INDEX privilege and must be able to execute the materialized view's defining query.
It is helpful to remember that most privilege checks for a materialized view are applied to the owner of the schema in which the materialized view is placed. The creator only needs the CREATE/ALTER (or CREATE/ALTER ANY) MATERIALIZED VIEW privilege. Also, the privilege checks are applied during creation and altering and not during query rewrite.
To enable a materialized view for query rewrite, the owner of the materialized view must have the QUERY REWRITE object privilege on any tables (referenced in the materialized view) that are outside the owner's schema. Alternatively, the owner must have the GLOBAL QUERY REWRITE system privilege. (Note that the QUERY REWRITE system privilege in Oracle 8i is now deprecated.)
To refresh a materialized view in another schema, the user issuing the refresh procedure must have the ALTER ANY MATERIALIZED VIEW privilege.
To create a materialized view with REFRESH ON COMMIT option, the owner must have the ON COMMIT object privileges on all tables (referenced in the materialized view) outside the schema or have the ON COMMIT system privilege.
In the following example, the user EASYDW is granted the following privileges to create materialized views and dimensions and to allow these to be used for query rewrite.
-- Add privileges GRANT select any table to easydw; GRANT execute any procedure to easydw; -- Add privileges for summary management GRANT create materialized view to easydw; GRANT drop materialized view to easydw; GRANT alter materialized view to easydw; GRANT create any dimension to easydw; GRANT alter any dimension to easydw; GRANT drop any dimension to easydw; GRANT global query rewrite to easydw;