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

Question

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"?

Solution 1

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.

Solution 2

A refresh updates the data that the materialized view holds. This cannot be done if the materialized view's definition is invalid.

A compile validates the definition of the materialized view, i.e. that the SQL is valid and that the objects it relies on exist.

Comments

[jpmc26]

Does recompiling also refresh the data, since the query may now have different results due to changes in other objects?