Solved

Materialized-View changes its compile state to error after refresh

Posted on 2004-08-18
3
3,603 Views
Last Modified: 2008-01-09
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
Comment
Question by:ChiefMav
  • 2
3 Comments
 
LVL 11

Accepted Solution

by:
cjjclifford earned 500 total points
Comment Utility
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
 
LVL 11

Expert Comment

by:cjjclifford
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now