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)
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: