Thursday, July 30, 2009

Refresh Fast

To enable refresh fast on a materialized view with joins

1. create materialized view logs on the base tables with rowids and participating columns
CREATE MATERIALIZED VIEW LOG ON FM_ADDON_SECTOR  WITH ROWID
(addon_id,departure,destination,SECTOR_NO,RETURN_COMPULSORY,STOPOVER_ONLY,
IN_BOUND_FOR_IN_DATE,CALC_METHOD)INCLUDING NEW VALUES;


2. add rowid of participating tables in the select list

select ...
,rt.rowid rtid
,oas.rowid oasid
,oadepz.rowid oadepzid
,rdepz.rowid rdepzid
,dadesz.rowid dadeszid
,rdesz.rowid rdeszid
,das.rowid dasid
,daa.rowid daaid
,oaa.rowid oaaid
,oa.rowid oaid
,da.rowid daid
....


3. Then create the materialized view with

CREATE MATERIALIZED VIEW "ROUTES"
nologging
BUILD IMMEDIATE
USING INDEX
REFRESH FAST on commit
AS SELECT ...