Why use a hint in compare/repair command? (74499)

This solution delves on the virtue of using a hint in compare.

General information.

The command compare or repair (separate commands in Shareplex 7.6 or higher) or compare with “repair” option (in prior Shareplex versions) fetches qualifying rows from source and target tables and compares them to determine out of sync. If a “where” clause is used, then the only a subset of the table rows will qualify for this fetching otherwise all the rows will need to be fetched.

The fetching is done by SQL statements. The fetched rows are sorted in the respective temporary tablespace of the source and target databases for the purpose of comparing them (using Shareplex’s own algorithm) to determine the out of sync.

If the fetched rows in question are large, it helps to use hint option in compare. It should be noted, however, that the hints specified in compare or repair commands are only used when fetching the rows and not during the repair process where the DML are applied to the target table. The reason for this is that the commands are only meant to fix small out of sync on target rows and are not efficient when attempting to apply a large number of rows to target table to fix the out of sync (as there are other options like copy, etc., that will be far more efficient).

The Oracle Optimizer will normally use the best index, access path, parallelism (where applicable) when fetching such rows. At times that may not be the case. Some common examples include stale statistics for indexes due to the indexes not analyzed frequently, certain indexes being more selective than others for some type of queries, use of parallel clause to have increased number of concurrent servers that can be used for SELECT than what optimizer would use (especially when dealing with a very large number of qualifying rows), etc. In brief, compare or repair only need hint for the SELECT clause and not for any other purpose like DML or table join.

Any Oracle hint can be provided. If the hint is not appropriate, the Optimizer will simply ignore it so there will be no issues there.

If the schema/tablename differs across source and target, the source objects need to be specified (if using the index hint).

For details of syntax on how to specify hints as well as on how compare uses key when no hints are provided, please refer to Shareplex Reference Guide section titled “Alphabetical reference for Shareplex commands” for details.