Solved

Materialized-View changes its compile state to error after refresh

Posted on 2004-08-18
3
3,643 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

Suggested Solutions

Title # Comments Views Activity
Difference in number of minutes between 2 timestamps 16 49
Clone Oracle 12c Database 5 52
Can i Import Access Table Into Oracle Using Toad 36 166
help on oracle query 5 43
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

815 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

8 Experts available now in Live!

Get 1:1 Help Now