[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3875
  • Last Modified:

Materialized-View changes its compile state to error after refresh

Hi,

I've made a Materialized View (Oracle 8i) like this:

CREATE MATERIALIZED VIEW MY_MVIEW
REFRESH COMPLETE
START WITH to_date(to_char(sysdate, 'dd-mon-yy')||' 23:30','DD-MON-YY HH24:MI')
NEXT trunc(SYSDATE + 1) + 47/48
WITH PRIMARY KEY
DISABLE QUERY REWRITE
AS
SELECT ...

Btw.: It contains a UNION and subselects.

After its creation the entry in the table DBA_MVIEWS looks good --> Compile State: VALID, Rewrite Enabled: N.

Then I simulate the refresh with SQL> execute dbms_refresh.refresh("MY_MVIEW");
and now the DBA_MVIEW tells me: Compile State: ERROR, Rewrite Enabled: Y!!!

ALTER MATERIALIZED VIEW MY_VIEW COMPILE doesn't help, in fact it creates the same result.

Actually I'm still able to use the MView for SELECTs. But I have to be sure that 'Rewrite Enabled' (I guess this means Query Rewrite?) is Disabled!

I'm quite new in using MViews, so maybe someone can help me with that?
0
ChiefMav
Asked:
ChiefMav
  • 2
1 Solution
 
cjjcliffordCommented:
Hi,

I ran into this before where I had a materialized view that I was trying not to be fast refreshed on commit. I created the materialized view to refresh periodically (similar to your view, although I was using fast refresh), and this worked fine. However, there was a condition that if certain tasks were performed by users (through a management GUI of the application product) the view had to be updated immediately, so as to reflect these changes (the changes were to one of the master tables) - In the code we had a DBMS_MVIEW.REFRESH() called in this case...

We started getting the same problems as you see - it seems that when a manual refresh is done at the same time as a scheduled refresh it invalidates the underlying structures of the view, and in addition, the underlying job to refresh this view becomes broken also...

What we did initially was to create a job that attempted a fast refresh, and if this failed several times in a row, perform a complete refresh - this sorta worked, but we saw some rediculous refresh times, so we eventually decided to make the view REFRESH ON COMMIT, and took the hit of longer commit times on the master tables (which in turn became a big problem that need tuning... commits into master tables results in rows in view logs necessary for fast commit, for any column being updated, not just columns of interest for the mview, so changes to the master table that didn't affect the mview still caused the REFRESH work, which was causing big problems.... we split some master tables, and for others we created triggers to check for changes to columns of interest, and flush these to a new secondary table, which the mview was redefined to use instead...

btw, selecting from the mview after it gets broken works as the select goes to the underlying table - the only thing is this table will not get updated since the mview isn't getting refreshed!

hopefully some of this info is useful...

lots of fun!
0
 
cjjcliffordCommented:
PS - we were using the MVIEW to perform pre-calculated joins of 4 large (100K-10M rows) tables, that were selected and joined regularily in response to GUI client requests - without the mview there would be dozens of large sorts going on in the DB, which was a real performance problem (toppled the production DB once too!!!)
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now