Problem with materialized view
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Problem with materialized view

  1. #1
    Join Date
    Nov 2005
    Posts
    15

    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.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Did you issue the commit? What is the compile_state and staleness of the materialized view before and after you modify the emp data?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Nov 2005
    Posts
    15

    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

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Look at the user_mviews system view to get the values of compile_state and staleness for the MV
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Nov 2005
    Posts
    15
    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
  •  



Click Here to Expand Forum to Full Width