优化Oracle中基于Java实现的copyTable函数性能

我需要将非Oracle数据库（比如Gauss）的表数据复制到Oracle库中，为此编写了Java方法并封装成Oracle存储过程，功能正常但面对大表时担忧性能。现有疑问：

• 是否必须逐行执行列赋值循环？
• 批量读取源表是否可行且有益？
  注：无法使用INSERT...SELECT，因源库非Oracle。

现有Java方法及Oracle函数、调用方式如下：

public static int copyTable (String cmdSelect, String cmdInsert, String sourceURL) throws SQLException {
    int rowCount = 0;
    try {
        Connection conOra = DriverManager.getConnection("jdbc:default:connection:");
        Connection conGauss = DriverManager.getConnection(sourceURL, "username", "password");
        PreparedStatement sthSel = conGauss.prepareStatement(cmdSelect);
        PreparedStatement sthIns = conOra.prepareStatement(cmdInsert);
        ResultSet rs = sthSel.executeQuery();
        ResultSetMetaData rsmd = rs.getMetaData();
        while ( rs.next() ) {
            for( int c = 1; c <= rsmd.getColumnCount(); c++ ) {
                sthIns.setObject(c, rs.getObject(c), rsmd.getColumnType(c), rsmd.getScale(c));
            }
            sthIns.addBatch();
            rowCount++;
            if (rowCount % 10000 == 0) {
                sthIns.executeBatch();
            }
        }
        sthIns.executeBatch();
        rs.close();
        sthSel.close();
        sthIns.close();
        conGauss.close();
        conOra.close();
    } catch (SQLException e) {
        throw e;
    }
    return rowCount;
}

CREATE OR REPLACE FUNCTION copyTable(cmdSelect VARCHAR2, cmdInsert VARCHAR2, sourceURL VARCHAR2) RETURN NUMBER AS
LANGUAGE JAVA NAME 'Gauss.copyTable(java.lang.String, java.lang.String, java.lang.String) return int';

DECLARE
    cmdSelect VARCHAR2(1000) := 'SELECT NRID, NEID, NRNAME, NR_NAME, NBTYPE, NETYPE, GNODEBID, INVALIDTIME, OBJECTSTATUS FROM D_NR';
    cmdInsert VARCHAR2(1000) := 'INSERT INTO D_NR_COPY (NRID, NEID, NRNAME, NR_NAME, NBTYPE, NETYPE, GNODEBID, INVALIDTIME, OBJECTSTATUS) VALUES (?,?,?,?,?,?,?,?,?)';
    ret INTEGER;
BEGIN
    ret := copyTable(cmdSelect, cmdInsert, 'some URL');
    DBMS_OUTPUT.PUT_LINE ( 'ret = ' || ret );
    COMMIT;
END;

嘿，这个场景我之前处理过，针对大表的性能瓶颈，咱们可以从这几个方向优化：

1. 批量读取源表：非常必要且收益极大 #

默认情况下，JDBC的ResultSet是逐行从源数据库拉取数据，这对大表来说会产生成千上万次的网络请求，是性能的最大杀手之一。你只需要给源库的PreparedStatement设置fetchSize，就能让驱动一次性读取多行数据到本地内存，大幅减少网络交互：

PreparedStatement sthSel = conGauss.prepareStatement(cmdSelect);
sthSel.setFetchSize(10000); // 数值可以根据内存情况调整，比如10000、20000都试试

这个设置对Gauss这类数据库同样有效，能直接把读取性能提升数倍，一定要加上！

2. 列赋值循环：难以完全避免，但可小幅优化 #

逐行列赋值的循环其实很难绕开——毕竟你需要把ResultSet的每列数据映射到插入语句的参数中，但可以做一些小调整减少开销：

• 你已经在循环外获取了ResultSetMetaData，这点做得很好，避免了重复调用rsmd.getColumnCount()；
• 如果能确认源表和目标表的列类型完全兼容（比如都是VARCHAR2、NUMBER对应正确），可以简化赋值逻辑：

  sthIns.setObject(c, rs.getObject(c));
  

  去掉类型和scale的指定，能减少一点类型转换的开销，但如果有类型不匹配的风险，保留原来的写法更稳妥。

3. 调整批量插入的批次大小 #

你当前每10000行执行一次批量插入，这个数值可以根据实际测试调整。Oracle的JDBC驱动对批量操作有优化，你可以试试把批次调到20000或50000（只要内存够），找到最适合你环境的数值。另外，也可以给插入的PreparedStatement显式设置批次大小：

sthIns.setBatchSize(10000);

和你当前的rowCount % 10000 == 0逻辑配合，效果会更稳定。

4. 其他细节优化 #

• 关闭自动提交：你用的是Oracle内部连接jdbc:default:connection:，它会继承PL/SQL的事务上下文，自动提交是关闭的，这点没问题；如果是外部连接的话，一定要记得conOra.setAutoCommit(false)，避免每次插入都触发提交。
• 资源关闭的优化：可以改用Java的try-with-resources语法，自动关闭连接、Statement、ResultSet，避免手动关闭的遗漏，同时代码更简洁：

  try (Connection conOra = DriverManager.getConnection("jdbc:default:connection:");
       Connection conGauss = DriverManager.getConnection(sourceURL, "username", "password");
       PreparedStatement sthSel = conGauss.prepareStatement(cmdSelect);
       PreparedStatement sthIns = conOra.prepareStatement(cmdInsert);
       ResultSet rs = sthSel.executeQuery()) {
      // 业务逻辑
  } catch (SQLException e) {
      throw e;
  }
  

5. 超大数据量的备选方案 #

如果数据量特别大（比如千万级以上），可以考虑先把源数据导出成CSV文件，再用Oracle的SQL*Loader或者外部表来加载——这种方式通常比JDBC批量插入快得多。不过这需要额外的步骤（导出、传输文件），如果你希望完全在存储过程里完成，还是优先用前面的JDBC优化方案。

• 批量读取源表（设置fetchSize）是提升大表性能的核心优化，一定要做；
• 列赋值循环无法完全避免，但可以通过简化逻辑、缓存元数据来减少开销；
• 调整批次大小、优化资源管理也能进一步提升效率。

内容的提问来源于stack exchange，提问作者Wernfried Domscheit