ora-12014 while creating a materialized view
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: ora-12014 while creating a materialized view

Hybrid View

  1. #1
    Join Date
    Oct 2000
    Posts
    211

    ora-12014 while creating a materialized view

    Hi,
    I am trying to create a MV, from a table int_group_master which does not have any primary key, and getting the ora-12014 error.

    SQL> CREATE MATERIALIZED VIEW MV_INT_GROUP_MASTER
    2 PCTFREE 0 TABLESPACE data_local
    3 BUILD IMMEDIATE
    4 REFRESH COMPLETE ON DEMAND
    5 AS
    6 SELECT gm_id,
    7 gm_domestic_cd,
    8 gm_desc
    9 FROM INT_GROUP_MASTER
    10 WHERE enterprisekey = 0;
    FROM INT_GROUP_MASTER
    *
    ERROR at line 9:
    ORA-12014: table 'INT_GROUP_MASTER' does not contain a primary key constraint

    How to get out of this, without creating a primary key on the base table? There are no dblinks being used and I tried fully qualifying the tablename(with the owner name), but the results are same.

    Thanks
    Manjunath

  2. #2
    Join Date
    Oct 2000
    Posts
    250
    Probably u should try to use the ROWID to create the MV.

  3. #3
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by ckwan
    Probably u should try to use the ROWID to create the MV.
    Using ROWID is not good for several reasons, it should be avoided.

    What is the reason why you do not want to create a PK on that table?
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    what're the issues with rowid, Julian?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by slimdave
    what're the issues with rowid, Julian?
    Sure!

    In order to implement fast refreshes, all tables that form part of the replicated group, should have Primary keys defined.

    Fast refreshes should be implemented as far as possible to reduce network traffic.

    You should:

    Use the rowid of rows and not the primary key;

    Define primary keys for these tables;

    Use complete refreshes.

    Each of these has disadvantages:

    Use rowid only for backward compatibility to Oracle 7 databases,
    There is NO guarantee that the ROWID of a particular ROW will be the same from one day to the next. Oracle does not provide you that guarantee,
    Move table will change rowid,
    Export/import will change rowid,
    If there are no unique columns for a specific table, then it will not be possible to define a primary key,
    Complete refreshes are heavy on the network and should be avoided as far as possible.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    that all sounds very fair. it comes down to really needing to use pk's for fast refresh, and refresh on commit. there are obviously a few decisons to be made by Manjunath on these issues, but if a complete refresh on demand suits his environment for functional reasons or through technical restrictions then rowid would seem to be his only choice.

    I have quite a few refresh complete on demand MV's -- for query rewrite aggregations on DW fact tables they are a kind of necessary evil, before Partition Change Tracking is introduced in 9i
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  7. #7
    Join Date
    Oct 2000
    Posts
    211
    Thanks, all.
    My environment is not of replication. I am utilizing the features of MVs, to reduce the workload in one of our modules. I have decided to go ahead using 'ROWID', and 'REFRESH ON DEMAND' for now.
    Thanks again
    Manjunath

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    have you considered using query rewrite, Manjunath?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

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