What's the difference between compiling and refreshing a materialized view?

jldugger picture jldugger · Sep 27, 2011 · Viewed 10.9k times · Source

We just ran into a problem where materialized views weren't refreshing, and giving a compile error. One of the senior developers says he just figured out how to fix it -- by telling toad to compile the materialized view.

So my question is pretty simple: what's the difference between a refresh and a "compile"?

Answer

DCookie picture DCookie · Sep 27, 2011

A Refresh of a materialized view is a data operation. The data in the MV is brought up to date as specified when the view was created, e.g., fast refresh, complete refresh, etc.

When you compile the MV, Oracle revalidates the query upon which the view is based. Your MV may be invalid due to changes in one or more of the underlying objects upon which the MV is based.