Materialized view issue

[PatM]

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

[PAVB]

Please take a look at your MV creation statement...

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

...and then take a look a Oracle documentation you have quoted...

...and cannot contain any of the following:
1) Distinct or aggregate functions
2) ...

[PatM]

Please take a look at your MV creation statement...



...and then take a look a Oracle documentation you have quoted...

Why doesn't Oracle give me an error message when I do this and why does it create the materialized view with primary key?

[PAVB]

Nothing personal but I want to see it with my eyes...
could you please run the following code and post the whole log?

Please... humor me and fully qualify everything replacing "schema" with the right schema_name.

create materialized view schema.mv_abc1
refresh on demand with rowid
as
select distinct col1, col2, col3, user, sysdate from schema.abc;

set pagesize 0
set linesize 180
set long 90000
SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','MV_ABC1','SCHEMA') FROM dual;

IF output shows MV created using PK as you claim, please post query showing you do not have a PK on base table; also post Oracle version and platform.

IF output shows MV created using rowid, you know

[PatM]

Here is the output from the SQLPlus session:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> create table gcust.abc
2 (col1 varchar2(10) not null,
3 col2 varchar2(10) not null,
4 col3 varchar2(10) not null);

Table created.

SQL> create materialized view gcust.mv_abc1
2 refresh on demand with rowid
3 as
4 select distinct col1, col2, col3, user, sysdate from gcust.abc;

Materialized view created.

SQL> set pagesize 0
SQL> set linesize 180
SQL> set long 90000
SQL>
SQL> SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','MV_ABC1','GCUST') FROM dual;

CREATE MATERIALIZED VIEW "GCUST"."MV_ABC1"
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOG
GING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TS_CUST_DATA0"
BUILD IMMEDIATE
USING INDEX
REFRESH FORCE ON DEMAND
USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS select distinct col1, col2, col3, user, sysdate from gcust.abc

================

As you can see it does not show rowid!

Further, I created a materialized view that I know is syntactically correct and ran the DBS_METADATA.GET_DDL and it shows rowid, here are the results:


SQL> create materialized view gcust.mv_abc2
2 refresh on demand with rowid
3 as
4 select col1, col2, col3 from gcust.abc;

Materialized view created.

SQL>
SQL> SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','MV_ABC2','GCUST') FROM dual;

CREATE MATERIALIZED VIEW "GCUST"."MV_ABC2"
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOG
GING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TS_CUST_DATA0"
BUILD IMMEDIATE
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TS_CUST_DATA0"
REFRESH FORCE ON DEMAND
WITH ROWID USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS select col1, col2, col3 from gcust.abc

====================================

Any ideas/information that you have would be greatly appreciated!

Thanks,
Pat

[PAVB]

You are right, it happens as you described it when done in Ora10g
but
it works as expected in Ora9i...

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> create table dbateam.abc
  2  (col1 varchar2(10) not null,
  3  col2 varchar2(10) not null,
  4  col3 varchar2(10) not null);

Table created.

SQL> create materialized view dbateam.mv_abc1
  2  refresh on demand with rowid
  3  as 
  4  select distinct col1, col2, col3, user, sysdate from dbateam.abc;

Materialized view created.

SQL> set pagesize 0
SQL> set linesize 180
SQL> set long 90000
SQL> 
SQL> SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','MV_ABC1','DBATEAM') FROM dual;

  CREATE MATERIALIZED VIEW "DBATEAM"."MV_ABC1"
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOG
GING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TSD01"
  BUILD IMMEDIATE
  USING INDEX
  REFRESH FORCE ON DEMAND
  WITH ROWID USING DEFAULT LOCAL ROLLBACK SEGMENT
  DISABLE QUERY REWRITE
  AS select distinct col1, col2, col3, user, sysdate from dbagroup.abc

SQL>

Interesting... further research is needed but we can certainly pinpoint it to some not yet discovered feature of 10g LOL

I'll let you know if I bump into something