Forum Stats

  • 3,733,954 Users
  • 2,246,847 Discussions
  • 7,856,949 Comments

Discussions

How to add an index to a materialized view in Data Modeler 3.3

blama
blama Member Posts: 123

Hello everyone,

I'm looking for a how-to to add an index to a materialized view in Data Modeler 3.3.0.747, as I coudn't find a way to do it so far.

I looked here:

Relational Model

Physical Model

Oracle 11g

Materialized Views

"my_mv_name"

"INDEXES" IS NOT HERE IN THE TREE

"Tables" does not include it either

Thank you & Best regards,

Blama

Kent Graziano

Best Answer

  • David Last-Oracle
    David Last-Oracle Member Posts: 1,057 Silver Trophy
    Accepted Answer

    Hi Blama,

    You can create a Table with the same name and associate it with the Materialized View (by setting "Implement as Materialized View" in the Physical Model properties for the Table.

    If you set up the Columns and Index on the Table, the Index will be included in the DDL generated for the Materialized View.

    Regards,

    David

    Kent Graziano

Answers

  • David Last-Oracle
    David Last-Oracle Member Posts: 1,057 Silver Trophy
    Accepted Answer

    Hi Blama,

    You can create a Table with the same name and associate it with the Materialized View (by setting "Implement as Materialized View" in the Physical Model properties for the Table.

    If you set up the Columns and Index on the Table, the Index will be included in the DDL generated for the Materialized View.

    Regards,

    David

    Kent Graziano
  • blama
    blama Member Posts: 123

    Hi David,

    thanks a lot. I did so and it worked, but I found a minor bug while doing so:

    I marked the table as "Implement as Materialized View" and went to File->Export->DDL (for Oracle 11g).

    The generated code (I checked all options in "Drop Selection") includes a row:

    DROP MATERIALIZED VIEW mv_mymatview CASCADE CONSTRAINTS ;

    which produces a syntax error.

    Best regards,

    Blama

  • David Last-Oracle
    David Last-Oracle Member Posts: 1,057 Silver Trophy

    Hi Blama,

    Thanks for telling us about the CASCADE CONSTRAINTS error.

    This is already fixed in the 4.0 EA1 and EA2 "Early Adopter" versions of Data Modeler.

    Regards,

    David

    David Last-Oracle
  • blama
    blama Member Posts: 123

    Hi David,

    I just wanted to confirm that the bug is fixed for me in 4.0.0.833.

    Best regards,

    Blama

  • David Last-Oracle
    David Last-Oracle Member Posts: 1,057 Silver Trophy

    Hi Blama,

    Yes. The incorrect clause "CASCADE CONSTRAINTS" is not generated now.

    Regards,

    David

This discussion has been closed.