Forum Stats

  • 3,874,528 Users
  • 2,266,750 Discussions
  • 7,911,880 Comments

Discussions

MView delta deploy problem between SDDM and db schema (Swap target model Dictionary import - DDL Pre

xkb
xkb Member Posts: 72

Hi,

I'm having a hard time to reverse merge the difference between my model and my database schema.

The initial goal is simple :

1/ detect metadata differences

2/ have SDDM generate DDL alter code

(if possible, otherwise, recreate, reload : powerful existing feature BTW)

3/ deploy

4/ check/confirm that no more delta existing

I'm doing that by :

* menu File > Import > Dictionary

* select Connection

* select db schema

* check "Swap Target Model"

* select MY_MVIEW > Next (1 TABLE DB Object to be imported) > Finish ("Generate Design" working)

* in Compare Models window I deselect all, minus MY_MVIEW table AND also MY_VIEW Materialized View

(as they appear as 2 objects in SDDM)

* button DDL Preview

I see :

- comments created first (whereas the MVIEW is to be recreated)

that is of less importance but still blurs the readability

- MY_MVIEW is systematically recreated

(how many times ever I deploy)

I figured out :

. both SDDM objects table (relational, implemented as MVIEW) and MVIEW (physical) host the query independantly

. even if I sync them manually (copy-paste), DDL code deployed is not strictly the same as

So may it have to do with a compare dysfunction ?

SDDM is full of options do desensitize compare (exclude physical, storage, etc.) but I haven't found my way to simply compare and align MVIEWs

(and documentation is scarce on the topic)

Any hint ?

Thx

Tagged:
xkb

Best Answer

  • David Last-Oracle
    David Last-Oracle Member Posts: 1,057 Silver Trophy
    edited Feb 24, 2016 6:14PM Answer ✓

    Interesting.  It looks like you are right that it is the partitioning that is causing the problem.

    In a model, partitioning information can be held on Physical Model objects for both Tables and Materialized Views.

    In the usual case where a Table and a Materialized View are associated together (by the Implement as Materialized View property on the Physical Model Table), it is the partitioning information held on the Table that is relevant.  The information on the Table is used when generating DDL.  And in an import or synchronize, the partitioning information is added to the Table object.

    I think what is probably happening in your case is your model has some partitioning details held on the Materialized View object.

    The synchronize is associating the partition details from your database with the Table object in the model.

    As it is not associating the partition details from your database with the Materialized View object in the model, the comparison shows a difference for the Materialized View:- not partitioned in database, but partitioned in your model.  And this difference is causing the drop and recreate of your Materialized View in the DDL.

    There are various options to get round this:

    1.  You could remove the unnecessary Partitioning details held on the Materialized View object in your model.

    2.  You could unset the check box for the entry for the Materialized View(s) in the tree in the Compare Models dialog before doing the DDL Preview.  (But this also means that no DDL will be generated for any other differences in those Materialized Views.)

    3.  You could use the Properties filter to filter out the relevant properties (e.g. Partitioned, Partitioning Columns and Subpart Columns for Materialized View objects) and then select the Refresh Trees button before doing the DDL Preview.  (See screenshot below.)

    pastedImage_1.png

    David

    xkbxkb
«1

Answers

  • xkb
    xkb Member Posts: 72
    edited Feb 11, 2016 9:45AM

    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

  • David Last-Oracle
    David Last-Oracle Member Posts: 1,057 Silver Trophy
    edited Feb 11, 2016 3:14PM

    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

    xkb
  • Philip Stoyanov-Oracle
    Philip Stoyanov-Oracle Member Posts: 3,424 Employee
    edited Feb 12, 2016 9:51AM

    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

    xkb
  • xkb
    xkb Member Posts: 72
    edited Feb 19, 2016 11:30AM

    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

  • Philip Stoyanov-Oracle
    Philip Stoyanov-Oracle Member Posts: 3,424 Employee
    edited Feb 22, 2016 10:06AM

    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

  • xkb
    xkb Member Posts: 72
    edited Feb 24, 2016 6:47AM

    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

  • Philip Stoyanov-Oracle
    Philip Stoyanov-Oracle Member Posts: 3,424 Employee
    edited Feb 24, 2016 8:09AM

    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

  • xkb
    xkb Member Posts: 72
    edited Feb 24, 2016 12:00PM

    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)

  • David Last-Oracle
    David Last-Oracle Member Posts: 1,057 Silver Trophy
    edited Feb 24, 2016 6:14PM Answer ✓

    Interesting.  It looks like you are right that it is the partitioning that is causing the problem.

    In a model, partitioning information can be held on Physical Model objects for both Tables and Materialized Views.

    In the usual case where a Table and a Materialized View are associated together (by the Implement as Materialized View property on the Physical Model Table), it is the partitioning information held on the Table that is relevant.  The information on the Table is used when generating DDL.  And in an import or synchronize, the partitioning information is added to the Table object.

    I think what is probably happening in your case is your model has some partitioning details held on the Materialized View object.

    The synchronize is associating the partition details from your database with the Table object in the model.

    As it is not associating the partition details from your database with the Materialized View object in the model, the comparison shows a difference for the Materialized View:- not partitioned in database, but partitioned in your model.  And this difference is causing the drop and recreate of your Materialized View in the DDL.

    There are various options to get round this:

    1.  You could remove the unnecessary Partitioning details held on the Materialized View object in your model.

    2.  You could unset the check box for the entry for the Materialized View(s) in the tree in the Compare Models dialog before doing the DDL Preview.  (But this also means that no DDL will be generated for any other differences in those Materialized Views.)

    3.  You could use the Properties filter to filter out the relevant properties (e.g. Partitioned, Partitioning Columns and Subpart Columns for Materialized View objects) and then select the Refresh Trees button before doing the DDL Preview.  (See screenshot below.)

    pastedImage_1.png

    David

    xkbxkb
  • xkb
    xkb Member Posts: 72
    edited Mar 8, 2016 6:25AM

    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 )

This discussion has been closed.