ora-12014 while creating a materialized view

[manjunathk]

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

[ckwan]

Probably u should try to use the ROWID to create the MV.

[julian]

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?

[slimdave]

what're the issues with rowid, Julian?

[julian]

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.

[slimdave]

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

[manjunathk]

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

[slimdave]

have you considered using query rewrite, Manjunath?