During Data comparison, Detailed data comparison and Data synchronization operations you may need to adjust the sorting keys. This can be done on a special page where you can specify the sorting key for the selected pair of data objects (tables or views). Considering data in source tables or views can be sorted in different order, Database Compare Suite requires a sorting key for each pair of tables or views in order to match rows between them.
The sorting key for a pair of tables can be selected automatically by Database Comparison Suite, if source data objects have matched a primary key or unique constraint.
If this operation succeeds, the key on the left side of the row is highlighted in green (row 1 on the image above).
If the Database Compare Suite application cannot determine the sorting key automatically, the key on the left side turns red and the label “No sorting key selected” appears (see row 2 on the image above). In this case you should adjust the sorting keys manually.
If the Database Compare Suite application determines that the tables in the pair have different sort of order, the warning message is displayed, and the key on the left side indicates this in yellow. This warning may be skipped according to your needs.
To set the sorting key for the pair of objects manually, open the expander. The available column pairs are displayed on the left side. You can pick the sorting key out of them.
To pick up the sorting pair, you should highlight it in the left panel and press the button with the right arrow at the top of the window.
To delete the sorting pair from the sorting key, you should highlight it in the right panel and press the left arrow button . This sorting pair will be displayed in the left panel again.
You can change the order of the sorting pairs in the right panel by moving the highlighted item up or down with appropriate ( and ) buttons.
To undo all the changes made during the last session, press the power button at the bottom of the window.
Please note that the column types that cannot be used as a part of the sorting key are listed in the following table
|Database platform||Types of columns, that cannot be used as a part of sorting key|
|Apache Cassandra||The user is unable to change sorting keys because of internal database platform features.|
|Azure SQL Database||text, image, ntext, xml, geometry, geography, sql variant|
|Azure SQL Data Warehouse||None|
|IBM DB2||blob, clob, dbclob, xml, user defined types|
|IBM Netezza||abstime, bytea, int2vector, name, oid, oidvector, regproc, st_geometry, text, varbinary|
|Greenplum||arrays, box, bytea, circle, int2vector, line, lseg, oidvector, path, point, polygon, text, xml|
|Microsoft SQL Server||text, image, ntext, xml, geometry, geography, sql variant, varbinary(max), varchar(max), nvarchar(max)|
|MySQL||text, blob, mediumtext, mediumblob, longtext, longblob, json, geometry, point, polygon, linestring, multipoint, multipolygon, multilinestring, geometrycollection, enum|
|Oracle||xmltype, bfile, clob, nclob, long, long raw, blob, user defined types, sdo_geometry|
|PostgreSQL||bytea, text, json, jsonb, xml, tsquery, tsvector, box, circle, line, lseg, point, path, polygon, int2vector, oidvector, arrays, ranges|
|Sybase ASE||image, text, unitext|
|Sybase IQ||binary, long binary, long nvarchar, long varbit, long varchar, st_geometry, timestamp with time zone, uniqueidentifier, varbinary, varbit, xml|
|Teradata||clob, json, xml, blob, array|
|Vertica||long varbinary, long varchar|
Using clustered indexes as a sorting key can significantly improve performance for large databases.