-
Problem with materialized view
Hello!!!
In order to see how materialized views work , I executed the following script in SCOTT schema:
create materialized view log on emp
with rowid (job,sal)
including new values;
create materialized view emp_mv
build immediate
refresh fast on commit
as
select job , sum(sal) sum_sal
from emp
group by job;
The problem is that when i update the column sal , or insert a new record in EMP table , the materialized view emp_nv IS NOT UPDATED , although the materialized view log registers the change.
In which circumstances the desired change will take place?
Note:
The parameter 'JOB_QUEUE_PROCESSES' of the database has the value 10.
-
Did you issue the commit? What is the compile_state and staleness of the materialized view before and after you modify the emp data?
-
Problem with materialized view
Yes , I did.
As regards , the staleness of data this is not the problem because after I commit the changes I do nothing else on the database , I simply query the emp_mv materialized view.
What do you mean by the expession complile_state?
Simon
-
Look at the user_mviews system view to get the values of compile_state and staleness for the MV
-
I think I 'm very close to the solution - with your help.
After i created the materialized view log and the materialized view i noticed the following - in the user_mviews system view :
Updatable:'N'
Rewrite_enabled:'N'
Staleness:'Fresh'
Compile_state:'Valid'
After the change (update) and commit the changes i noticed the following - in the user_mviews system view :
Updatable:'N'
Rewrite_enabled:'N'
Staleness:'Unusable'
Compile_state:'Valid'
What provoked that change in user_mviews system view and what can i do to get the desired result?
Simon
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|