M. View Fast Refresh
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: M. View Fast Refresh

Hybrid View

  1. #1
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727

    M. View Fast Refresh

    All,

    I am creating a Materialized view using joining of two tables. When trying for the FAST refresh, getting the message

    Code:
    SQL> exec dbms_mview.refresh('crew_log','F');
    BEGIN dbms_mview.refresh('crew_log','F'); END;
    
    *
    ERROR at line 1:
    ORA-12004: REFRESH FAST cannot be used for materialized view
    "REPLDBA"."CREW_LOG"
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
    ORA-06512: at line 1
    Originally my understanding was, FAST refresh is not possible on M. Views based complex queries ( M. Views with joins and functions). But please see the following link

    http://www.oracle.com/technology//pr...ily/jul05.html

    I have Oracle 9.2.0 in both ends and the OS is IBM AIX. M. View logs as well created. If the above link is true, why I am getting the problem?

    Many Thanks
    Last edited by Thomasps; 08-05-2004 at 04:54 AM.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Use the DBMS_MVIEW.EXPLAIN_MVIEW procedure to get a list of the capabilities of the MV -- it may point to a problem with the structure, or more likely to a bug in Oracle. Some of the fast refresh code seems to be a little ropy.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  3. #3
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Thanks Slimdave. I was making two mistakes

    1. M. View log created without the option "WITH ROWID"
    2. ROWIDs were not included in the select list.

    This is my R&D result which is going to implement in UAT soon.

    Code:
    create table emp (empno number, deptno number, name varchar2(20)) ;
    
    create table dept (deptno number, deptname varchar2(20));
    
    alter table emp add constraint empno_pk primary key  (empno);
    alter table dept add constraint deptno_pk primary key  (deptno);
    
    create MATERIALIZED VIEW LOG ON emp
      WITH ROWID;
    create MATERIALIZED VIEW LOG ON dept
      WITH ROWID;
    
    CREATE MATERIALIZED VIEW emp_dep
    TABLESPACE users
    STORAGE ( INITIAL 1M NEXT 1M PCTINCREASE 0 )
    REFRESH fast   
    AS
    select e.empno,e.deptno,e.name,d.deptname,d.rowid drid,e.rowid erid,d.deptno ddeptno
    from emp e ,dept d
    where e.deptno = d.deptno;
    Thomas
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Why rowid and not primary key, if I may ask?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  5. #5
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Originally posted by slimdave
    Why rowid and not primary key, if I may ask?
    Because

    Code:
    SQL> create MATERIALIZED VIEW LOG ON emp   WITH primary key;
    
    Materialized view log created.
    
    SQL> create MATERIALIZED VIEW LOG ON dept   WITH primary key;
    
    Materialized view log created.
    
    SQL> CREATE MATERIALIZED VIEW emp_dep
    TABLESPACE    users
    STORAGE (     INITIAL 1M NEXT 1M PCTINCREASE 0 )
    REFRESH fast   
    AS
    select  e.empno,e.deptno,e.name,d.deptname,d.deptno ddeptno
    from emp    e ,dept d
    where e.deptno = d.deptno;
    from emp e ,dept d
         *
    ERROR at line 7:
    ORA-12052: cannot fast refresh materialized view THOMAS.EMP_DEP
    
    
    SQL> CREATE MATERIALIZED VIEW emp_dep
    TABLESPACE users
    STORAGE ( INITIAL 1M NEXT 1M PCTINCREASE 0 )
    REFRESH fast   
    AS
    select e.empno,e.deptno,e.name,d.deptname,d.rowid drid,e.rowid erid,d.deptno ddeptno
      from emp e ,dept d
    where e.deptno = d.deptno;
    from emp e ,dept d
         *
    ERROR at line 7:
    ORA-12032: cannot use rowid column from materialized view log on
    "THOMAS"."DEPT"
    If I can use Primary key for JOIN based M. Views, can you please correct me where I am wrong? Many Thanks

    Thomas
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    The use of primary key/rowid ought not to affect whether you can fast refresh or not, since it just controls whether the materialized view log stores changes against the rowid of the base table or the primary key. The use of primary key-based MV logs is more robust -- rowid-based logs get cleared if you move the table, and are really only there for backwards compatibility (although they're handy for DW fact tables that have no PK)
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  7. #7
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Logically agreee. Still I am not getting way to build a M. View based a complex (join multiple tables) using primary key. Thanks Slim.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

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