Materialized view issue
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Materialized view issue

Hybrid View

  1. #1
    Join Date
    Jun 2007
    Posts
    3

    Materialized view issue

    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

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Please take a look at your MV creation statement...

    Quote Originally Posted by PatM
    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...

    Quote Originally Posted by PatM
    ...and cannot contain any of the following:
    1) Distinct or aggregate functions
    2) ...
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jun 2007
    Posts
    3

    Yes I know but..

    Quote Originally Posted by PAVB
    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?

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Jun 2007
    Posts
    3

    Here are the results

    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

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    You are right, it happens as you described it when done in Ora10g
    but
    it works as expected in Ora9i...

    Code:
    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
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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