Materialized views ddl and dml stati (and the infamous NEEDS_COMPILE)

introduction

Employing materialized views (or snapshot or mview for short) for producing simple sum-up reports is really cool stuff. You just launch some statement like below and will be served with a well performing collection of data that is more or less current (that is why i call that simple). Oracle will care for the refresh of the mview by self-registering a job on calling dbms_refresh.refresh('some_mv');, that starts immediately and takes the interval from the statement. Handing in the force keyword just chooses a fast refresh method iff possible and a complete refresh method otherwise. There are more defaults applied by leaving other keywords unset, the refresh mode for example, but this is not the point of the article. It is:

Why does that … mview always show a state of NEEDS_COMPILE although no ddl action has happend along the objects dependency chain lately?

create materialized view some_mv
  build immediate
  refresh force next (sysdate + 1/24/12)
as
select X.ts, Y.name from (
  select to_char(max(A.ts), 'dd.mm.yyyy hh24:mi') as ts, B.blubb
    from tab1 A, tab1_1 B where A.id = B.id
    group by B.blubb
) X, tab2 Y
  where X.blubb = Y.id;