i create a materialized view fast refresh. it takes almost 45 min to create but it did not refresh in 24 hours. i tried it on with index and without index. i check the log of all table max record in log table is 2 lac. query is as under please suggest what changes needed
CREATE MATERIALIZED VIEW LOG ON a WITH ROWID, SEQUENCE (COLUMN USED FROM THIS TABLE)
/
CREATE MATERIALIZED VIEW LOG ON P WITH ROWID, SEQUENCE (COLUMN USED FROM THIS TABLE)
/
CREATE MATERIALIZED VIEW LOG ON PG WITH ROWID, SEQUENCE (COLUMN USED FROM THIS TABLE)
/
CREATE MATERIALIZED VIEW LOG ON PN WITH ROWID, SEQUENCE (COLUMN USED FROM THIS TABLE))
/
CREATE MATERIALIZED VIEW LOG ON AP WITH ROWID
/
CREATE MATERIALIZED VIEW C_INFO
NOLOGGING
BUILD IMMEDIATE
refresh fast with rowid
on demand
AS
SELECT
A.ROWID ACTROWID , P.ROWID PREMROWID,
PG.ROWID PGROWID,AP.ROWID APROWID, PN.ROWID PNROWID,
...
FROM A, P, pg, ap, pn
WHERE
p.id = pg.id (+)
and pg.columname (+)= 'Value'
...
Best Answer
Your materialized is not defined with a NEXT clause, therefore it will only refresh when you ask for it explicitely. You can use either DBMS_MVIEW.REFRESH directly or create a refresh group with DBMS_REFRESH.
In order to automate the refresh, you could program a job with DBMS_SCHEDULER or DBMS_JOB (dbms_job is deprecated in 11g).
You could also define your MV with a NEXT clause, for example this will refresh the MV every hour: