• Scroll to top of this topic Scroll to Top of Page

  • Print the current topic Print Topic

  • No expanding elements on this page Show/Hide Expanders

Sorting key selection

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.

  1. 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.

  2. 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.

  3. 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
    Amazon Redshift None
    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
  4. Using clustered indexes as a sorting key can significantly improve performance for large databases.