Aqua Help

Compare the data of database objects

The Compare Data action compares the output of tables, views, and materialized views. To find this action, select two objects in the Database tool window ( View | Tool Windows | Database) , right-click the selection and navigate to Tools. Also, you can double-click these objects and then compare them from the editor.

You can compare the contents of two objects that belong to different types. For example, the output of a table and a materialized view.

Table contents

Description of Tolerance and Detect column insertion is available in Controls of the Diff Viewer for contents.

By default, when you issue a query, the number of returned rows is limited to 500. This limitation is introduced to avoid an overload (for example, when your SELECT statement returns one million rows). To increase the number of compared rows, configure the Limit page size to parameter.

Compare the contents of two tables from the Database tool window

By default, when you issue a query, the number of returned rows is limited to 500. This limitation is introduced to avoid an overload (for example, when your SELECT statement returns one million rows). To increase the number of compared rows, configure the Limit page size to parameter.

  1. In the Database tool window ( View | Tool Windows | Database) , select two tables.

  2. Right-click the selection and navigate to Tools | Compare Data.

    If needed, change the value of the Tolerance parameter in the comparison dialog. The Tolerance parameter defines a maximum number of differences that are allowed between two result sets. For example, if you want to consider two rows as equal if their data differs in a single column, enter 1 in the Tolerance field.

Compare the contents of two tables from the data editor

  1. Open two tables that you want to compare. To open a table, double-click it in the Database tool window.

  2. In the editor, click the Compare Data button (the Compare Content button) and select the second table.

Controls of the Diff Viewer for contents

In the Diff Viewer for contents, you can use the same sorting functionality that is available in the data editor. For more information about sorting columns, refer to Sort data.

The primary purpose of the Diff Viewer for contents is to show the differences and similarities of data.

To highlight the differences, Aqua uses the following color coding:

Color

Description

Rows that differ

Rows that differ.

Cells that differ in a column

Cells that differ in a column.

Rows that are considered equal

Rows that are considered equal.

Detect column insertion

When the tables have a different number of columns, extra columns in the table with more columns are ignored. If the Detect column insertion option is on, the most different columns are ignored. In the following picture, the first column in the second table is the most different and so it is ignored. As a result, the second row is shown as containing the same data.

If the option is off, ignored are the last of the columns. On the following picture, the last column in the second table is ignored. So all the rows are shown as containing different data.

Tolerance

The Tolerance parameter defines how many columns might differ to consider two rows equal. For example, if you set Tolerance to one, rows that differ in one column are considered equal.

Compare table data tolerance equals to one

With tolerance set to zero, such rows are considered different.

Compare table data tolerance equals to zero

With this setting, you can also check the columns that differ when data in rows is different. Such rows in those columns are highlighted. Increase the Tolerance option if you have different data more than in one row. For example, with Tolerance set to 1, you can see that between two tables only the last_name column differs.

columns differ when rows contain different data
Last modified: 14 March 2024