Oracle Database Discussions

I have a materialized view created like this in Oracle 11.2.0.1:

create materialized view big_cust_mv
build deferred
refresh on demand
as
select * from
( select name_first, name_last, count(total_spent) as num_orders
from customers, order_summary
where customers.cid = order_summary.cid
group by name_first, name_last
) where num_orders > 10000
order by 3 desc;

After doing a dbms_mview.refresh('BIG_CUST_MV'), it works just fine.

SQL> select updatable, update_log, refresh_method, fast_refreshable, last_refresh_type, last_refresh_date, staleness from user_mviews;

U UPDATE_LOG REFRESH_ FAST_REFRESHABLE LAST_REF LAST_REFR STALENESS
- ------------------------------ -------- ------------------ -------- --------- -------------------
N FORCE NO COMPLETE 28-NOV-10 FRESH


After doing this:

SQL> delete from order_summary where cid = 133;

12147 rows deleted.

SQL> commit;

Commit complete.

It goes to NEEDS COMPILE for status. I could understand "STALE" because it is stale, but why NEEDS COMPILE? I haven't changed any of the source table definitions, just their contents.


SQL> select updatable, update_log, refresh_method, fast_refreshable, last_refresh_type, last_refresh_date, staleness from user_mviews;

U UPDATE_LOG REFRESH_ FAST_REFRESHABLE LAST_REF LAST_REFR STALENESS
- ------------------------------ -------- ------------------ -------- --------- -------------------
N FORCE NO COMPLETE 28-NOV-10 NEEDS_COMPILE

SQL> alter materialized view big_cust_mv compile;

Materialized view altered.

SQL> select updatable, update_log, refresh_method, fast_refreshable, last_refresh_type, last_refresh_date, staleness from user_mviews;

U UPDATE_LOG REFRESH_ FAST_REFRESHABLE LAST_REF LAST_REFR STALENESS
- ------------------------------ -------- ------------------ -------- --------- -------------------
N FORCE NO COMPLETE 28-NOV-10 STALE

SQL> exec dbms_mview.refresh ('BIG_CUST_MV');

PL/SQL procedure successfully completed.

SQL> select updatable, update_log, refresh_method, fast_refreshable, last_refresh_type, last_refresh_date, staleness from user_mviews;

U UPDATE_LOG REFRESH_ FAST_REFRESHABLE LAST_REF LAST_REFR STALENESS
- ------------------------------ -------- ------------------ -------- --------- -------------------
N FORCE NO COMPLETE 28-NOV-10 FRESH