Tuesday, February 16, 2021

Data Compare for Oracle - Comparison Keys

The Data Compare for Oracle is designed to compare the data in "matching" rows in two tables that may be in two different databases or even the same database. What does "matching" rows mean? Matching rows means rows from both tables, that can be uniquely paired to each other based on the value of a certain column or a combination of columns. 

The most common scenario is that in which you would pair the rows from both sides based on the value of the Primary Key. This works great when you are comparing tables from identical or similar schemas / databases, but that is not always the case. Regardless, after pairing the tables (either automatically or manually), the Data Compare for Oracle analyses the tables and tries to identify an identical unique key that exist on both tables. The unique key can be the Primary Key, a Unique Constraint or a Unique Index. If it finds one, that is the key that will be used to pair the rows to each other, and if it does not find one it marks the table pair as un-comparable. Whether a useful comparison key was automatically identified or not you can still drill down on that table pair and either pick a different comparison key from the list of identified unique keys, or create a custom comparison key. 


When you drill down on a table pair you will see a window with 3 tabs one of which is called "Unique Keys". The "Unique Keys" tab is divided in two main sections - the top section contains two boxes with the list of unique keys that are potential candidates to be used as the comparison key for each table in the pair, whereas, the bottom section contains a box with the pair of unique keys that have been selected as the key to be used for the data comparison operation.

If the key that has been selected is what you want then you may proceed. However, you can also pick one of the other keys or,  create a custom key based on one or more columns of the table. Note that, a user-defined key or a custom key exists only inside data compare. No index or constraint is created on the Oracle table.

Data Compare picks the comparison key in the following order:
  1. User-Defined Key
  2. Primary Key
  3. Unique Constraint (the 1st in alpha order)
  4. Unique Index (the 1st in alpha order)
A user-defined key or a custom key always take precedence over the primary key, unique constraints or unique indexes that might exist on a table. When custom keys are selected, you should validate them by clicking on the Validate button. Custom keys are validated as following:
  • Columns in the custom keys must have the same name.
  • Columns in the custom keys must have the same data type. Length, scale, precision, nullability and other column properties are not considered.
  • The uniqueness of the key is checked only if the option “Check custom key uniqueness”, in the Application Settings form, is on.
To select a key for data comparison, simply double-click on it.

Download Data Compare for Oracle and try now. 

0 comments:

Post a Comment