Hi,

I have a table that does not have a primary key (it is from a vendor software package so I can not change it). There is a materialized view using the table that selects columns from the table and adds user and sysdate as columns that is created using the "with rowid" syntax. Oracle does not reject the statement, rather, it creates the view and changes the with rowid clause to with primary key (even though my underlying table does not have a primary key).

Table creation statement:
create table abc
(col1 varchar2(10) not null,
col2 varchar2(10) not null,
col3 varchar2(10) not null);

Materialized view creation statement:
create materialized view mv_abc
refresh on demand with rowid
as
select distinct col1, col2, col3, user, sysdate from abc;

Oracle creates the materialized view fine BUT changes the defintion to:
CREATE MATERIALIZED VIEW MV_ABC
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
AS
select distinct col1, col2, col3, user, sysdate from abc;

I noticed this and played around with other materialized view with rowid syntaxes and found there are other instances that behave the same way (for example using a distinct clause). Now, I know "with rowid" can not be used in a materilized view defintion for these cases (quoting Oracle documentation here):

"Specify WITH ROWID to create a rowid materialized view. Rowid materialized views are useful if the materialized view does not include all primary key columns of the master tables. Rowid materialized views must be based on a single table and cannot contain any of the following:
1) Distinct or aggregate functions
2) GROUP BY or CONNECT BY clauses
3) Subqueries
4) Joins
5) Set operations"

(end quote of Oracle doc).

The questions I have are:
1) Why doesn't Oracle reject and invalid create materialized view with rowid instead of creating the object and changing it to with primary key.

2) In my example, I use the columns of one table and add the system functions of user and sysdate. Is this a violation of the Oracle documentation that I quoted?

Any information would be appreciated!

Thanks,
Pat