How to create materialized views in Oracle

Creating a materialized view in Oracle involves specifying the SELECT statement for the data you want to store, along with various options for refresh and storage. Below is a step-by-step guide on how to create a materialized view in Oracle:

Steps to Create a Materialized View in Oracle

1. Ensure Necessary Privileges: Ensure you have the necessary privileges to create materialized views and the underlying objects (tables, views).

2. Basic Syntax: The basic syntax for creating a materialized view is as follows:

CREATE MATERIALIZED VIEW view_name

[BUILD IMMEDIATE | BUILD DEFERRED]

[REFRESH [FAST | COMPLETE | FORCE]]

[ON DEMAND | ON COMMIT]

[WITH PRIMARY KEY | WITH ROWID]

AS

select_statement;

3. Example:

Below is a detailed example demonstrating the creation of a materialized view with various options:

-- Create the materialized view CREATE MATERIALIZED VIEW sales_mv BUILD IMMEDIATE REFRESH FAST ON COMMIT AS SELECT product_id, SUM(quantity) as total_quantity FROM sales WHERE sale_date >= TRUNC(SYSDATE, 'MM') GROUP BY product_id;

Explanation of the Options:

• view_name: Name of the materialized view.
• BUILD IMMEDIATE | BUILD DEFERRED: Specifies when the data should be populated. BUILD IMMEDIATE populates the view at creation, while BUILD DEFERRED waits until the first refresh.
• REFRESH [FAST | COMPLETE | FORCE]:
  • FAST: Uses incremental changes (requires materialized view logs on base tables).
  • COMPLETE: Rebuilds the entire materialized view.
  • FORCE: Attempts FAST refresh but defaults to COMPLETE if conditions for FAST are not met.
• ON DEMAND | ON COMMIT:
  • ON DEMAND: Refreshes the view only when explicitly requested.
  • ON COMMIT: Automatically refreshes the view when a transaction that modified one of the underlying tables is committed.
• WITH PRIMARY KEY | WITH ROWID: Specifies how the rows in the materialized view are identified. WITH PRIMARY KEY uses primary keys from the base tables, while WITH ROWID uses the rowids.

Materialized View Log:

For FAST refresh, you need to create a materialized view log on the base table:

CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID, PRIMARY KEY, SEQUENCE (product_id, quantity, sale_date) INCLUDING NEW VALUES;

Additional Examples:

1. Complete Refresh on Demand:

CREATE MATERIALIZED VIEW customer_mv

BUILD IMMEDIATE

REFRESH COMPLETE ON DEMAND

AS

SELECT customer_id, customer_name, customer_address

FROM customers;

2.     Deferred Build and Fast Refresh:

CREATE MATERIALIZED VIEW inventory_mv BUILD DEFERRED REFRESH FAST ON DEMAND AS SELECT item_id, SUM(stock_quantity) as total_stock FROM inventory GROUP BY item_id;

Refreshing the Materialized View:

To manually refresh the materialized view, use the following command:

EXEC DBMS_MVIEW.REFRESH('sales_mv');

By following these steps and using the provided examples, you can create and manage materialized views in Oracle to optimize query performance and maintain data consistency.