60 Planning for Read-Only Materialized Views

If possible, each master table should have a primary key.

Where a primary key is not possible, each master table must have a set of columns that can be used as a unique identifier for each row of the table. If the tables that you plan to use in your replication environment do not have a primary key or a set of unique columns, then alter these tables accordingly. In addition, if you plan to create any primary key materialized views based on a master table, then that master must have a primary key.

There are several considerations for data types and master tables.

You can create read-only materialized views based on master tables with columns that use the following data types:

• VARCHAR2

• NVARCHAR2

• NUMBER

• DATE

• TIMESTAMP

• TIMESTAMP WITH TIME ZONE

• TIMESTAMP LOCAL TIME ZONE

• INTERVAL YEAR TO MONTH

• INTERVAL DAY TO SECOND

• RAW

• ROWID

• CHAR

• NCHAR

• CLOB with BASICFILE storage

• NCLOB with BASICFILE storage

• BLOB with BASICFILE storage

• XMLType stored as CLOB

• User-defined types that do not use type inheritance or type evolution

• Oracle-supplied types that do not use type inheritance or type evolution

You cannot reference LOB columns in a WHERE clause of a materialized view's defining query.

You can create materialized views that use user-defined types, including column objects, object tables, REFs, varrays, and nested tables.

You cannot create materialized views based on master tables with columns that use the following data types:

• FLOAT

• BINARY_FLOAT

• BINARY_DOUBLE

• LONG

• LONG RAW

• CLOB with SECUREFILE storage

• NCLOB with SECUREFILE storage

• BLOB with SECUREFILE storage

• BFILE

• XMLType stored object relationally or as binary XML

• Expression type

• User-defined types that use type inheritance or type evolution

• Oracle-supplied types that use type inheritance or type evolution

You should convert LONG data types to LOBs with BASICFILE storage.

Materialized views cannot be based on certain types of tables.

You cannot create materialized views based on these types of tables:

• Tables that have been compressed with the table compression feature

• Tables with virtual columns

• Temporary tables

• Tables in a flashback data archive

When you are planning your replication environment, you must decide whether the databases participating in the replication environment will be master databases or materialized view databases.

Consider the characteristics and advantages of both types of databases when you are deciding whether a particular database in your environment should be a master database or a materialized view database. One replication environment can support both master databases and materialized view databases.

Master databases have several advantages.

Master databases have the following advantages:

• Support for highly available data access by remote databases

• Provide support data manipulation language (DML) changes

• Can provide failover protection

Materialized view databases have certain advantages.

Materialized view databases have the following advantages:

• Support disconnected computing

• Can contain a subset of its master database's data

Most problems encountered with materialized view replication result from not preparing the environment properly.

Ensure that the following prerequisites are met before creating your materialized view environment:

• Ensure that the required schemas exist.

• Ensure that the required database links exist.

• Ensure that the required privileges are granted.

• Ensure that the sufficient job processes exit.

• Required Schemas at Materialized View Database
  A schema containing a materialized view in a remote database must correspond to the schema that contains the master table in the master database.
• Required Database Links for Materialized Views
  The defining query of a materialized view can use one or more database links to reference remote table data.
• Required Privileges
  Both the creator and the owner of the materialized view must be able to issue the defining SELECT statement of the materialized view.
• Sufficient Job Processes
  It is important that you have allocated sufficient job processes to handle the automation of your replication environment. The job processes automatically refresh materialized views.

Create a materialized view log on a master table so that materialized views based on the master table can be fast refreshed.

1. Connect to the master database as a user with the required privileges to create a materialized view log on the intended table.
2. Run the CREATE MATERIALIZED VIEW LOG statement.

   When you create a materialized view log on an object table, you must log the object identifier by specifying the WITH OBJECT ID clause, but you can also specify that the primary key is logged if the object identifier is primary key-based.

CREATE MATERIALIZED VIEW LOG ON hr.employees;

CREATE TYPE oe.category_typ AS OBJECT
   (category_name           VARCHAR2(50), 
    category_description    VARCHAR2(1000), 
    category_id             NUMBER(2));
/

CREATE TABLE oe.categories_tab_sys OF oe.category_typ 
    (category_id    PRIMARY KEY)
    OBJECT ID SYSTEM GENERATED;

CREATE TABLE oe.categories_tab_pkbased OF oe.category_typ 
    (category_id    PRIMARY KEY)
    OBJECT ID PRIMARY KEY;

CREATE MATERIALIZED VIEW LOG ON oe.categories_tab_sys
   WITH OBJECT ID;

CREATE MATERIALIZED VIEW LOG ON oe.categories_tab_pkbased
   WITH OBJECT ID, PRIMARY KEY;

When you create a materialized view log, you can add columns to the log to enable fast refreshes of materialized views.

1. Connect to the master database as a user with the required privileges to create or alter a materialized view log on the intended table.
2. Do one of the following:

   • Run the CREATE MATERIALIZED VIEW LOG statement and specify the columns to log.

   • Run the ALTER MATERIALIZED VIEW LOG statement and specify the columns to log.

CREATE MATERIALIZED VIEW LOG ON oe.orders 
  WITH PRIMARY KEY (customer_id,order_total);

ALTER MATERIALIZED VIEW LOG ON oe.orders ADD (customer_id,order_total);

ALTER MATERIALIZED VIEW LOG ON oe.customers ADD (cust_address.postal_code);