Problem: I am doing a comparison of a single object (ASSESSMENT_FACT) in 2 difference schemas. It shows what is different between the two schemas but it also shows differences that are really not differences. see attachments for more details
- I do not understand how the utility switches the order of the indexes? You will see that this is the only difference between the objects after the initial creation of the table in L2DBIDDLE. See DIFFERENCE #1.
- After the rebuild the comparison is showing differences that are really not differences. See image DIFFERENCES #2(a).
- Is it possible to change the comparison to do what other file comparisons utilities do so that the TR columns at the end of the table are not shown as differences or is this a bug? See DIFFERENCES #2(b)
compare single Schema object issue.zip (698 KB)
This table is being flagged as different by the schema compare due to the added columns.
Remember, the side-by-side viewer is just a text comparison utility that knows nothing of Oracle DDL. It has no knowledge of things like order of an index in a script.
It seems that the addition of the ALT2_ACHIEVED_DETAIL_KEY column is causing the datatype column to be a little further over. That is what’s causing all these differences.
To ignore them, go into the “comparison rules” dialog (2nd to last toolbar button in the side-by-side viewer), and checking “Ignore minor differences”. Also, go to the “Define minor” tab and check “embedded whitespace”.
-
Good question and unfortunately, I’m not able to reproduce it. I am using an “order by” clause when querying oracle about these indexes to try to prevent this sort of thing. There is a fair amount of sorting that goes on, especially in schemas with a lot of dependencies, so it’s possible that the sort has moved these around a little, even though they don’t need to be. That is not what’s causing the schema compare to flag this table as “different”, it’s only something that the side-by-side text viewer notices.
-
Whitespace. it’s not just those lines that are being flagged as different. It’s every line until you get down to storage and physical attributes. Click the last button on the side-by-side viewer’s toolbar and you’ll see:
Blue lines are similar. Red text is different. Black text is matching.
Here, the whitespace difference is more evident:
TEST_KEY INTEGER NOT NULL,
TEST_KEY INTEGER NOT NULL,
I got this text by doing a CTRL+C on the file compare viewer.
I have add DIFFERENCES #3 (a,b,c) to the docx file.
-
I initially create ASSESSMENT_FACT in L2DBIDDLE and made sure the only differences were as of DIFFERENCES #1.
-
I altered the table using the following:
alter table assessment_fact add ALT2_ACHIEVED_DETAIL_KEY NUMBER;
alter table assessment_fact add ALT2_STD_ACHIEVED_CODE VARCHAR2(4 BYTE);
-
Did the compare again and the same differences on INTEGER were found and also the differences in the TR columns. see DIFFERENCES #3
My questions:
I can understand that sorting maybe be an issue between schemas but I would think if the same code to was used to produce both sides the order would be the same?
I did the same test you did ,copy/paste the text into another file, to see what was different and saw that the datatype was moved. If the same code is being used to produce both sides of the compare I would think it would produce both side the SAME in terms of white? That is, the amount of white space before and after the datatype would be the same.
I think both the sort order and the amount of white space are bugs in the code that produce the content for the side by side viewer.
compare single Schema object issue.docx (624 KB)
One side of the comparison has a longer column name (ALT2_ACHIEVED_DETAIL_KEY) than the other. That’s why there is more indention on that side.
You can make the side by side viewer disregard the whitespace differences. To do so, follow these steps:
-
Click the 2nd to last toolbar button (“comparison rules”).
-
In the dialog that appears, check “Ignore minor differences”.
-
Go to the “Define minor” tab and check “embedded whitespace”.
-
Click OK.
As I said, I cannot reproduce the fact that the indexes are being sorted differently. Maybe it is a very minor bug, but I probably need your entire schema script to reproduce it (just the object types that are selected in your Generate Schema Script window).
Oops, all this time I thought you were getting to the side-by-side viewer from the schema compare. Disregard my comment about needing your entire schema script.
I’ve made a change in the next beta so that single object compare will generate DDL as if the longest column name in the table were 30 characters, so that should solve the whitepsace problem for your INTEGER and TR columns (actually there is a common denominator being that text exists after the data type…not null, or default value).
I found the problem with the index order. This will be fixed in the next beta. It only happens if you are logged in as a user who owns one of the tables (and the other table is in a different schema). So if you log in as a 3rd user who owns neither table, the index order should be the same.
I tried what you said about logging in as a 3rd user and it seem not to work. because in every case the indexes were in different orders. If I logged into a user that was of a different database then the one of the compare users the “single table compare” would change to be running under the connection of the “source”.
Thanks for your help on this matter. Do you know when the next beta will be out that these changes will be in?
A beta is planned for this afternoon.