Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Materialized-View changes its compile state to error after refresh

Posted on 2004-08-18
3
Medium Priority
?
3,842 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 11

Accepted Solution

by:
cjjclifford earned 1500 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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, 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 configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

636 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