Solved

Materialized-View changes its compile state to error after refresh

Posted on 2004-08-18
3
3,626 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
ID: 11840606
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
ID: 11840626
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

919 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

21 Experts available now in Live!

Get 1:1 Help Now