Data Compare for Oracle - Where Clause

---

Download xSQL Compare Bundle, starting from $0!

---

Comparing data between two databases sounds simple and conceptually is very simple - basically you compare a data element from database 1 with the corresponding data element from table 2. However, the goal is to compare ALL data in database 1 with all data in database 2 and that is where things get complicated very quickly. Let's take a simple scenario, assume we just need to compare DB1.Table1 to DB2.Table1. Let's further assume that DB1.Table1 contains ~10 million rows and DB2.Table1 contains approximately the same amount of rows. In order to compare the data in those two tables, Data Compare for Oracle needs to transfer ~20 million rows (~10 million from each side) from the servers where those databases are hosted into the machine that is performing the data compare. As you can imagine this is an expensive operation that takes significant amount of time. 

Now, in most common scenarios you know that over 90% of those rows are going to be the same and you don't really need to compare them, so if you could avoid transferring all those rows and comparing them, you would save significant amount of time and resources. To accomplish that, Data Compare for Oracle provides a "where clause" option that allows you to compare only the rows you want. When defining the where clause, by default, the same filter is applied to both tables. If you wish to apply a different filters for each of the tables, you can un-check "Use the same where clause" and define a separate clause for each side. 

Important notes to keep in mind:

1. Do not include the "where" keyword in the where clause. Simply enter the SQL condition, in the same way you would write it in any Oracle tools.
2. Validate the where clause before comparing the data. This ensures that the clause is syntactically correct.
3. We strongly recommend basing the where clause on the columns participating in the comparison key. You may include additional columns, but all data key columns must be included in the where clause. Not having a data key column could produce false missing records during the comparison.

Download the Data Compare for Oracle and try now.