Creating Materialized Views for Heterogeneous Replication Example

This example creates three materialized views for Heterogeneous Replication. These materialized views are used in subsequent examples.

Create a primary key materialized view of table customer@remote_db.

    CREATE MATERIALIZED VIEW pk_mv REFRESH COMPLETE AS
      SELECT * FROM customer@remote_db WHERE "zip" = 94555;

Create a subquery materialized view of tables orders@remote_db and customer@remote_db.

    CREATE MATERIALIZED VIEW sq_mv REFRESH COMPLETE AS
      SELECT * FROM orders@remote_db o WHERE EXISTS
        (SELECT c."c_id" FROM customer@remote_db c
           WHERE c."zip" = 94555 and c."c_id"  = o."c_id" );

Create a complex materialized view of data from multiple tables on remote_db.

    CREATE MATERIALIZED VIEW cx_mv
      REFRESH COMPLETE AS
      SELECT  c."c_id", o."o_id"
        FROM customer@remote_db c,
             orders@remote_db o,
             order_line@remote_db ol
        WHERE c."c_id" = o."c_id"
        AND o."o_id" = ol."o_id";