4

I have the following materialized view - 

CREATE MATERIALIZED VIEW TESTRESULT 
ON PREBUILT TABLE WITH REDUCED PRECISION
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
AS 
SELECT...
FROM...
WHERE...

This materialized view has no backing MATERIALIZED VIEW LOG. As seen in the clause above this MV has "ON DEMAND" specifies, and according to Oracle documentation,

"[ON DEMAND] indicate[s] that the materialized view will be refreshed on demand by calling one of the three DBMS_MVIEW refresh procedures."

When I call DBMS_MVIEW.REFRESH('TESTRESULT') , what is occuring? Is it manually checking each record to see if it has been updated?

Oracle Version: 10g

contactmatt
  • 18,116
  • 40
  • 128
  • 186

1 Answers1

8

By default (and this default changes in different versions of Oracle), that will do a full, atomic refresh on the materialized view. That means that the data in the materialized view will be deleted, the underlying query will be re-executed, and the results will be loaded into the materialized view. You can make the refresh more efficient by passing in a value of FALSE for the ATOMIC_REFRESH parameter, i.e.

dbms_mview.refresh( 'TESTRESULT', atomic_refresh => false );

That will cause the materialized view to be truncated, the query re-executed, and the results inserted into the materialized view via a direct path insert. That will be more efficient than an atomic refresh but the materialized view will be empty during the refresh.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • +1 @Justin.. Also, what happens if I have created the MVIEW with `NOLOGGING`? The MVIEW I have created, keeps erring out with UNDOTBS small.. Will `ATOMIC_REFRESH` help? – Guru Jun 14 '11 at 21:22
  • @guru - If I understand correctly, you're getting an ORA-01555 error, right? That implies that the problem is that the tables you're querying are changing and your refresh is unable to produce a consistent view of the data as it existed when the refresh started. Normally, that would imply that the `UNDO_RETENTION` parameter and/or the UNDO tablespace is too small and should be increased. Potentially, you could also look to decrease the amount of time required to do the refresh which `ATOMIC_REFRESH` would accomplish. – Justin Cave Jun 14 '11 at 21:26
  • Am away from my office PC, I need to wait to get the exact ORA error. When searched, Tom Kyte too has mentioned about 01555. But here is what I do. I create a MVIEW based on a big table. It takes around 40-60 mins. The MVIEW size is 35-40 GB. When I refresh (Complete) it with `DBMS_MVIEW`, the script runs for 2-3 hrs and errors out with MVIEW REFRESH PATH size being an issue. – Guru Jun 14 '11 at 21:31