Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
MView delta deploy problem between SDDM and db schema (Swap target model Dictionary import - DDL Pre
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
Best 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.)
David
Answers
-
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)
-
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.)
David
-
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 )
