0354

recommend materialized views based on historical queries, or based on theoretical scenarios. They can be run from the Oracle Enterprise Manager Grid Control (OEM) or by calling the dbms_advisor package.

Create Materialized Views

Materialized views are created using a create materialized view statement, which is similar to a create table statement. This can be performed using SQL*Plus or OEM. The following listing shows a simple example of how to create the comm_prod_mv materialized view mentioned earlier and Table 9-16 provides an explanation of the syntax.

 1 create materialized view comm_prod_mv
 2    tablespace comm_prod_mv_ts
 3    storage (initial 50k next 50k)
 4    refresh complete next sysdate + 7
 5    enable query rewrite
 6 as select sales_rep_id, prod_id, comm_date, count(*), sum(comm_amt)
 7    from commission
 8    group by sales_rep_id, prod_id, comm_date;

In the next three sections, we will be discussing some higher-level concepts: Real Application Clusters, Automatic Storage Management, and Grid Computing. But first, a progress check.

Lines	Important Points
2–3	Specify the tablespace and storage parameters.
4	Specifies how and when to refresh the data. In this case, the materialized view will be populated immediately and be completely refreshed every seven days thereafter.
5	Specifies that query rewrite is to be enabled.
6–8	Specify the query that will act as the source of the data.
TABLE 9-16. Explanation of Materialized View Creation Syntax

Team Fly

0354