Create Materialized View Command
Syntax of materialized views
COMPLETE/FAST/FORCE/NEVER: Indicates how the data for a materialized view will be updated.
ENABLE/DISABLE QUERY REWRITE: Indicates whether a materialized view will be used by the cost-based optimizer.
BUILD IMMEDIATELY/DEFERRED:Indicates when the actual data for the view is assembled.
ON COMMIT/DEMAND: Indicates when the data for a materialized view is updated.
storage_clause: Indicates how the data for the view will be stored.
New and Enhanced Materialized View Catalog Views
New and enhanced catalog views are available in Oracle Database 11g.
These changes provide information on
- Partition change tracking information for materialized views in the database
- Information on freshness or staleness of individual partitions
The new data dictionary views [DBA|ALL|USER]_MVIEW_DETAIL_PARTITION and
[DBA_MVIEW_DETAIL_SUBPARTITION provide detailed information as to the staleness of individual partitions of a partitioned materialized view.
In addition to new catalog views, new columns have been added to the [DBA|ALL|USER]_MVIEWS view (NUM_PCT_TABLES, NUM_FRESH_PCT_REGIONS, and NUM_STALE_PCT_REGIONS) to indicate the total number of fresh and stale PCT regions and the number of PCT tables.
The [DBA|ALL|USER]_MVIEW_DETAIL_RELATIONS view has new columns to indicate whether the detail table is PCT-enabled and to also indicate the total number of fresh and stale PCT regions. Here is an example of the creation of a materialized view, on top of a partitioned table. We will then look at how the new PCT-related columns work. First we create the base table of the mview; then we create an mview log and then create the mview.
-- Create our demo table
CREATE table quarterly_sales (
sales_month number, sales_person_id number
,date_of_sale date, cust_id number
,quantity_sold number, amount_sold number)
PARTITION BY LIST (sales_month)(
PARTITION qtr_one VALUES ( 1, 2, 3),
PARTITION qtr_two VALUES ( 4, 5, 6),
PARTITION qtr_three VALUES ( 7, 8, 9),
PARTITION qtr_four VALUES (10, 11, 12),
PARTITION others VALUES (DEFAULT));
-- Create the Mview log
CREATE MATERIALIZED VIEW LOG
ON quarterly_sales WITH ROWID(sales_month)
INCLUDING NEW VALUES;
-- Create the Mview
DROP MATERIALIZED VIEW mv_sales_sum;
CREATE MATERIALIZED VIEW mv_sales_sum
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
SELECT cust_id, sales_month, SUM(quantity_sold) as tot_qty_sold
,COUNT(quantity_sold) AS cnt_qty_sold
,SUM(amount_sold) as tot_amt_sold
,COUNT(amount_sold) AS cnt_amt_sold
GROUP BY cust_id, sales_month;
-- Now, check the PCT info in user_mviews
select mview_name, num_pct_tables, num_fresh_pct_regions,