top of page
CerebroSQL

Oracle:

CREATE MATERIALIZED VIEW LOG

CREATE MATERIALIZED VIEW LOG ON [ schema. ] table
[ physical_attributes_clause
| TABLESPACE tablespace
| logging_clause
| { CACHE | NOCACHE }
]...
[ parallel_clause ]
[ table_partitioning_clauses ]
[ WITH [ { OBJECT ID
| PRIMARY KEY
| ROWID
| SEQUENCE
| COMMIT SCN
}
[ { , OBJECT ID
| , PRIMARY KEY
| , ROWID
| , SEQUENCE
| , COMMIT SCN
}
]... ]
(column [, column ]...)
[ new_values_clause ]
] [ mv_log_purge_clause ]
;

physical_attributes_clause::=
[ { PCTFREE integer
| PCTUSED integer
| INITRANS integer
| storage_clause
}...
]
logging_clause::=
{ LOGGING | NOLOGGING | FILESYSTEM_LIKE_LOGGING }
parallel_clause::=
{ NOPARALLEL | PARALLEL [ integer ] }
new_values_clause::=
{ INCLUDING | EXCLUDING } NEW VALUES
mv_log_purge_clause::=
PURGE { IMMEDIATE [ SYNCHRONOUS | ASYNCHRONOUS ]
| START WITH datetime_expr [ NEXT datetime_expr
| REPEAT INTERVAL interval_expr
]
| [ START WITH datetime_expr ] { NEXT datetime_expr
| REPEAT INTERVAL interval_expr
}
}

Example

CREATE MATERIALIZED VIEW LOG ON customers
PCTFREE 5
TABLESPACE example
STORAGE (INITIAL 10K);

CREATE MATERIALIZED VIEW LOG ON customers WITH PRIMARY KEY, ROWID;

CREATE MATERIALIZED VIEW LOG ON orders
PCTFREE 5
TABLESPACE example
STORAGE (INITIAL 10K)
PURGE REPEAT INTERVAL '5' DAY;

CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE(amount_sold, time_id, prod_id)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON order_items WITH (product_id);

CREATE MATERIALIZED VIEW LOG ON product_information
WITH ROWID, SEQUENCE (list_price, min_price, category_id), PRIMARY KEY
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW products_mv
REFRESH FAST ON COMMIT
AS SELECT SUM(list_price - min_price), category_id
FROM product_information
GROUP BY category_id;

bottom of page