Java HotSpot Virtual Machine

I dug a little deeper on this by comment/tagging differently my queries in table and mview objects (as they are *not* in sync within SDDM)

so respectively :

SELECT -- sddm rel table qry

(...)

SELECT -- sddm phy mview qry

(...)

Now the weird part is that, only one db object is to be imported in Data Dictionary Import Wizard (only Tables tab exist, no mview tab) but two will match in the Compare Model window (table and mview).

I tried to select both or only one but the actual deployed code is always that of the MVIEW (SELECT -- sddm phy mview qry)

...and even if I sync both, the actual code deployed through DDL "Preview" (well, more than a preview indeed : there is the generated code) is the physical mview query (SELECT -- sddm phy mview qry) but it is reprocessed (e.g. : blank lines are deleted)

It hence seems that Compare will eternally consider db objects as different from sddm ones...

...and thus endlessly generate them.

Any help is welcome.

Delta/differential/gap/incremental analysis & ddl generation is one the most powerful feature of a DM tool but here I feel it lacks, if not capability, maybe some up-to-date documentation (?)

Thanks

Hi,

Normally the master version of the query is held in the Query property on the Table.  However if the Body property on the Materialized View is changed, this becomes the master.  It is this that will be used in generated DDL and when comparing properties for the Materialized View.

To sync the two up again, you should make sure the Query property on the Table contains the appropriate value, and then copy it to the Body property on the Materialized View.

Provided the value copied to the Body is different from its previous value, it should recognize that the new Body value is the same as the Table Query, and revert to using the Table's Query property.

David

Hi,

thanks for reporting the problems. David logged a bug about no DDL when select statement is changed. Some remarks:

but it is reprocessed (e.g. : blank lines are deleted)

that is an option in preferences "Preferences>Data Modeler>DDL" - "Use SQL Developer formatter" - if it's not checked then select statement will appear as it is

Now the weird part is that, only one db object is to be imported in Data Dictionary Import Wizard

There is an easy way to do it - right click on table on diagram and select "Synchronize Data Dictionary" from pop-up menu (blue arrow at the bottom).

The same blue arrows you can find on relational model tool bar (next to "Generate DDL" icon) - you ca synchronize the whole model in both directions (connection also can be changed). Also if you put some objects (tables and views) in subview  you can find the same blue arrows on context menu for that subview and can synchronize only objects in subview.

Philip

Hi Philip,

First : thanks for your concern.

I unchecked the Formatter although I don't care that much if ddl code can finally be in sync and thus not detected as changed and regenerated.

Unfortunately, that's still what happens : I checked that all code is in sync :

- sddm relational table mview query

- sddm physical mview body

- db dictionary ddl code (between "create (...) as" and the final semi-colon)

- sddm swapped import ddl preview code (in the relevant section)

Nonetheless, when I import the table, select table and/or mview in sddm, and ask for ddl preview, it still generates the MView (with the very same code).

Are you able reproduce the problem ? (if you please)

BTW : Synchronize Data Dictionary does sync but with the intermediation of the db schema so if you want to sync only relational table mview query with physical mview body without impacting your db model, it doesn't fit

(but that's a nice nice-to-have anyhow)

I'm also encountering a similar detection problem with tables generating a sequence and trigger (Identity + auto increment) but I'll open another thread so to not pollute this one.

Thanks

Hi,

I'm not sure I understand the steps and the problem.

are you saying:

1) definitions in the model and database are equal, and

2) MV definition is still generated (recreated) when compare to database

Are those MVs in the same schema in model and database? There are options that have impact on that:

- "Preferences>Data Modeler>DDL>DDL/comparison" - "Use schema property in compare functionality"

- "Preferences>Data Modeler>Model>Relational"- check-boxes for "database synchronization" - "Use source schema"

Philip

Hi Philip,

1/ & 2/ -> yes, you got me right (plus, I took care to align sddm mview & table sql query and checked ddl preview is also in sync with that, to make sure comparison doesn't occur with some other code than the one deployed)

I already had unchecked schema for DDL comparison.

I didn't know of data sync "Use source schema" check option (and I must confess although I found it in e57984 sddm 4.1 user manual, I have a hard time to consolidate all that may impact comparison and why).

Nevertheless, I unchecked it and DDL Preview still drops & recreates my MVIEW as if it weren't in sync with DB.

Do you experience a different behavior ?

Thanks

Hi,

probably something else is changed. Can you check what is reported as changed - when you step with the mouse on MV node in (comparing dialog) - details about changes are updated below (Details, Physical details, Storage details). Or you can generate a report.

So if tablespace is changed it'll try to recreate the MV with new tablespace.

Philip

As a matter of fact, the "Compare Models" dialog identifies a delta on Partitionning (no detail available) and Range Partitions (although init partition has already been deployed by the script ; for the record I use interval partitionning on this mview).

I had already unchecked all Storage/Physical properties in Compare Options, unchecked all properties in Range Partitions @ Properties Filter but it didn't change behavior.

From your previous reply I went to Prefs > Data Model > Model > Syncronization Physical > Oracle to also uncheck Import Options Partitions

...but the result is still that the MVIEW ddl is generated.

There's even the "Optional Import and Processing" / Partitions check I played with at Data Dictionary Import time (Wizard dialog) but it doesn't do much difference.

At the end, I'm a bit confused whether desactivating options in all places about physical & partitionning doesn't contribute to the phenomenon (like : oh it could be about the same here but since unchecked, just different -> generate !)

Do you have an idea of which options work together in order to skip the partitions comparison ?

(many thanks for your time)

Hi David,

Thanks for your detailed answer :

2/ is not option because I DO want to catch differences

3/ is tedious because I would have to uncheck partitionning criteria each time I sync.

1/ is just FINE !

I had indeed partitionning information on the MVIEW

Deleting it is just a 1-shot work so it has my preference.

Many thanks !

(I'll be back soon with an equivalent issue around identity sequence/trigger generation and delta detection )

Hello,

I'm allowing myself to post here since the context is the very same :

Selecting Options > Properties Filter > Materialized Views > Comment in RDBMS causes SDDM to regenerate the entire MView plus comment although comment is fine and sync'ed between physical Model and db object (i.e. within db Dictionary).

I can toggle check box and Refresh Trees to alternate that behavior (without including Comment in RDBMS, MView is considered equal and not regenerated)

1. Why SDDM won't sync comment only ? how can I do that ?
2. Why anyway will it detect a difference once I have deployed diff ddl script with the right comment ?

(I thought it could be due to comparison with the underlying table comment but I can't comment even manually on it in db which issues ORA-12098: cannot comment on the materialized view, Issue a COMMENT ON MATERIALIZED VIEW statement instead)

Thanks