基于Java 1.8与OJDBC6，如何高效对比两个Oracle 11g数据库数据？

Great question—validating data consistency between two Oracle 11g databases while keeping overhead low is a common challenge, especially when working with older JDBC drivers like OJDBC6. Let’s break down your options, from database-native optimizations to Java tweaks and tooling, to get you the most efficient solution.

1. Database-Level Comparison (Most Efficient) #

The best way to avoid JDBC ResultSet overhead is to let Oracle handle the comparison directly. Use the MINUS operator with a database link to query across your source and target databases—this keeps all processing on the database side, no need to pull massive datasets into your Java app.

Step 1: Create a Database Link from Source to Target #

First, set up a DB link so your source database can query the target directly:

CREATE DATABASE LINK target_db_link
CONNECT TO target_user IDENTIFIED BY target_password
USING '(DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = target_host)(PORT = target_port))
    (CONNECT_DATA = (SID = target_sid))
)';

Step 2: Use MINUS to Find Missing Target Data #

Run this query on the source database to get all records present in the source but missing from the target. Replace schema.table with your actual table details, and include all columns you need to validate (or just the primary key if you only care about existence):

-- For existence check (primary key only)
SELECT id FROM source_schema.your_table
MINUS
SELECT id FROM target_schema.your_table@target_db_link;

-- For full data consistency check (all columns)
SELECT id, col1, col2, col3 FROM source_schema.your_table
MINUS
SELECT id, col1, col2, col3 FROM target_schema.your_table@target_db_link;

This returns exactly the records missing from the target. It’s far faster than pulling all data into Java because Oracle optimizes set operations natively.

2. Java-Based Optimizations (If You Must Use JDBC) #

If you need to handle the logic in Java, you can drastically reduce ResultSet.next() overhead with these tweaks:

Batch Fetching to Reduce Network Roundtrips #

OJDBC6 defaults to a small fetch size (often 10 rows), which means lots of back-and-forth between your app and the database. Increase the fetch size to pull more rows per network call:

try (Connection conn = DriverManager.getConnection(sourceUrl, sourceUser, sourcePass);
     Statement stmt = conn.createStatement()) {
    stmt.setFetchSize(1000); // Adjust based on your row size and memory
    ResultSet rs = stmt.executeQuery("SELECT id FROM source_schema.your_table");
    
    // Batch collect primary keys
    List<Long> sourceIds = new ArrayList<>();
    while (rs.next()) {
        sourceIds.add(rs.getLong("id"));
        // Process in batches to avoid memory overload
        if (sourceIds.size() % 1000 == 0) {
            checkMissingTargetIds(sourceIds);
            sourceIds.clear();
        }
    }
    // Process remaining ids
    if (!sourceIds.isEmpty()) {
        checkMissingTargetIds(sourceIds);
    }
}

Batch Query the Target Database #

Instead of checking each id individually against the target, batch your queries to minimize target database hits. Use an IN clause (or Oracle’s TABLE(CAST(MULTISET(...)) for very large batches):

private void checkMissingTargetIds(List<Long> sourceIds) throws SQLException {
    try (Connection targetConn = DriverManager.getConnection(targetUrl, targetUser, targetPass);
         PreparedStatement pstmt = targetConn.prepareStatement(
             "SELECT id FROM target_schema.your_table WHERE id IN (" +
             String.join(",", Collections.nCopies(sourceIds.size(), "?")) +
             ")"
         )) {
        // Set parameters
        for (int i = 0; i < sourceIds.size(); i++) {
            pstmt.setLong(i + 1, sourceIds.get(i));
        }
        ResultSet targetRs = pstmt.executeQuery();
        
        // Track which ids exist in target
        Set<Long> existingTargetIds = new HashSet<>();
        while (targetRs.next()) {
            existingTargetIds.add(targetRs.getLong("id"));
        }
        
        // Find missing ids
        for (Long id : sourceIds) {
            if (!existingTargetIds.contains(id)) {
                System.out.println("Missing in target: " + id);
                // Log or collect missing records as needed
            }
        }
    }
}

Hash-Based Comparison for Full Data Checks #

If you need to validate more than just existence, compute a hash of each row in the database (instead of pulling all columns) to reduce data transfer:

-- Source query: compute hash of row data
SELECT id, DBMS_CRYPTO.HASH(UTL_RAW.CAST_TO_RAW(col1 || '|' || col2 || '|' || col3), 2) AS row_hash
FROM source_schema.your_table;

Do the same for the target, then compare hashes in Java. This cuts down on the amount of data you need to transfer and process.

3. Tooling Alternatives (No Code Needed) #

If you’d rather avoid writing code entirely, these tools can handle the comparison efficiently:

• Oracle SQL Developer: Built-in Database Diff tool (under Tools > Database Diff). You can configure it to compare specific tables, and filter results to only show records missing from the target. It uses database-level operations under the hood, so it’s fast.
• Toad for Oracle: Offers a robust Data Compare feature that lets you set up source/target connections, select tables, and define exactly what to check (including missing target records).
• Quest Data Compare for Oracle: A dedicated tool for cross-database data validation, with options to schedule comparisons and generate detailed reports.

Key Notes #

• Consistency First: Run comparisons during low-traffic windows or when both databases are in a read-only state to avoid false positives from in-flight transactions.
• Large Tables: For very large tables, split your comparison into chunks (e.g., by primary key ranges) to avoid overwhelming the database or your app’s memory.
• OJDBC Version: While OJDBC6 works with Oracle 11g, upgrading to OJDBC7 or later can bring performance improvements, but the fetch size tweak is the biggest win if you can’t upgrade.

内容的提问来源于stack exchange，提问作者Kevin Kovack