This discussion is archived
3 Replies Latest reply: Feb 19, 2013 11:15 PM by Philip Stoyanov RSS

SDDM lacks semantic analysis to support mviews on prebuilts

KevinDavis Newbie
Currently Being Moderated
In SQL*Plus:

create table a as select 1 a from dual;
create table b as select 1 a from dual;
create table c as select 1 a from dual;
create materialized view b on prebuilt table as select a from c;
create materialized view a on prebuilt table as select a from b;

this shall work.

In SDDM the corresponding relational and physical model shall not result in DDL for the mviews in the correct order.
The result is:
ORA-32334: cannot create prebuilt materialized view on a table already referenced by a MV

(Part 1 -- I need to go work out, then, I think I still have an Oberon in the fridge)
  • 1. Re: SDDM lacks semantic analysis to support mviews on prebuilts
    KevinDavis Newbie
    Currently Being Moderated
    In part 2 I conclude with an attempted SDDM dictionary import of A, B and C. Having selected my schema, then tables A, B and C. I then check that the three tables are selected and presented as new tables in the compare models panel. They are. DDL Preview only depicts DDL for table C. Ah! I then open up the materialized views node in the left panel and select materialized views A and B. I question whether these should have been selected by default. DDL Preview still only depicts DDL for table C!

    Nonetheless, let's proceed with the merge. Alas, tables KDAVIS.A, .B and .C arrive in the relational model along with materialized views KDAVIS.A and .B. Unfortunately the two mviews do not respect the user_snapshots catalog specification:

    OWNER     NAME     TABLE_NAME     PREBUILT
    KDAVIS     B     B     YES
    KDAVIS     A     A     YES

    I.e. the “Implement as materialized view” property for tables A and B should be blank and the “On Prebuilt Table” property for the mviews should be their respective tables. I expect to have over a hundred mviews in my model and I depend on the prebuilt mechanism to have fine resolution control over the data types in the table rather than using casts in the queries and so I have flexibility to drop mviews from some tables depending on how our ETL integration evolves.

    So, I now endeavor to manually convert the imported model to one that uses the prebuilt property. The resulting DDL, following the commenting out of the WITH ROWID (blank s/b an option in SDDM), is:

    -- Generated by Oracle SQL Developer Data Modeler 3.1.3.709
    -- at: 2012-10-19 11:05:07 EDT
    -- site: Oracle Database 11g
    -- type: Oracle Database 11g



    CREATE USER "KDAVIS"
    IDENTIFIED BY
    ACCOUNT UNLOCK
    ;

    CREATE TABLE "KDAVIS"."A"
    (
    "A" NUMBER
    )
    PCTFREE 10
    PCTUSED 40
    MAXTRANS 255
    TABLESPACE "USERS"
    LOGGING
    STORAGE (
    INITIAL 81920
    NEXT 1048576
    PCTINCREASE 0
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
    )
    ;




    CREATE TABLE "KDAVIS"."B"
    (
    "A" NUMBER
    )
    PCTFREE 10
    PCTUSED 40
    MAXTRANS 255
    TABLESPACE "USERS"
    LOGGING
    STORAGE (
    INITIAL 81920
    NEXT 1048576
    PCTINCREASE 0
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
    )
    ;




    CREATE TABLE "KDAVIS"."C"
    (
    "A" NUMBER
    )
    PCTFREE 10
    PCTUSED 40
    MAXTRANS 255
    TABLESPACE "USERS"
    LOGGING
    STORAGE (
    INITIAL 81920
    NEXT 1048576
    PCTINCREASE 0
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1
    FREELIST GROUPS 1
    BUFFER_POOL DEFAULT
    )
    ;




    CREATE MATERIALIZED VIEW "A"
    ON PREBUILT TABLE
    USING INDEX
    REFRESH COMPLETE ON DEMAND --WITH ROWID
    AS select a from b
    ;


    CREATE MATERIALIZED VIEW "B"
    ON PREBUILT TABLE
    USING INDEX
    REFRESH COMPLETE ON DEMAND --WITH ROWID
    AS select a from c
    ;




    -- Oracle SQL Developer Data Modeler Summary Report:
    --
    -- CREATE TABLE 3
    -- CREATE INDEX 0
    -- ALTER TABLE 0
    -- CREATE VIEW 0
    -- CREATE PACKAGE 0
    -- CREATE PACKAGE BODY 0
    -- CREATE PROCEDURE 0
    -- CREATE FUNCTION 0
    -- CREATE TRIGGER 0
    -- ALTER TRIGGER 0
    -- CREATE STRUCTURED TYPE 0
    -- CREATE COLLECTION TYPE 0
    -- CREATE CLUSTER 0
    -- CREATE CONTEXT 0
    -- CREATE DATABASE 0
    -- CREATE DIMENSION 0
    -- CREATE DIRECTORY 0
    -- CREATE DISK GROUP 0
    -- CREATE ROLE 0
    -- CREATE ROLLBACK SEGMENT 0
    -- CREATE SEQUENCE 0
    -- CREATE MATERIALIZED VIEW 2
    -- CREATE SYNONYM 0
    -- CREATE TABLESPACE 0
    -- CREATE USER 1
    --
    -- DROP TABLESPACE 0
    -- DROP DATABASE 0
    --
    -- ERRORS 0
    -- WARNINGS 0

    This elicits:

    CREATE MATERIALIZED VIEW "B"
    *
    ERROR at line 1:
    ORA-32334: cannot create prebuilt materialized view on a table already referenced by a MV



    Q.E.D.
  • 2. Re: SDDM lacks semantic analysis to support mviews on prebuilts
    Philip Stoyanov Guru
    Currently Being Moderated
    Hi Kevin,

    thanks I logged a bug about incorrect import of Mview on prebuilt table and enhancement request about dependencies tracking (or missing semantic analysis to support mviews on prebuilts).

    Philip
  • 3. Re: SDDM lacks semantic analysis to support mviews on prebuilts
    Philip Stoyanov Guru
    Currently Being Moderated
    Hi Kevin,

    you can check how dependencies are tracked now in DM 3.3 EA2.

    Philip