java如何在oracle中更新列Clob的同时提高性能？

Question

我使用以下代码更新了oracle中的Clob列，它似乎还可以正常工作，经过性能测试，它报告需要消耗200ms以上的时间，而字符串长度超过130000。有什么好办法可以改进吗

  private void updateClobDetailsField(Map<Integer, String> idToDetails){
     long s1 = System.currentTimeMillis();
     Connection conn = null;
     PreparedStatement pStmt = null;
     ResultSet rset = null;
     Map<Integer, Clob> idToDetailsClob = new HashMap<Integer, Clob>();
     int BATCH_SIZE = CMType.BATCH_UPDATE_MAXSIZE;
     try
     {
         conn = getConnection();
         ServerAdapter adapter = ServerAdapter.getServerAdapter();

         List<Integer> IDList = new ArrayList<Integer>();
         for(Integer id : idToDetails.keySet()){
             IDList.add(id);
         }

         List<Integer> tempIDList = new ArrayList<Integer>(IDList);
         while(!tempIDList.isEmpty()){
              int size = tempIDList.size() < BATCH_SIZE ? tempIDList.size() : BATCH_SIZE;
              List<Integer> currentBatch = tempIDList.subList(0, size);
              String inClause = SQLHelper.prepareInClause("ID",currentBatch.size());
              pStmt = conn.prepareStatement("SELECT ID, DETAILS FROM PROGRAM_HISTORY WHERE " + inClause);
              for(int i = 0; i < currentBatch.size(); i++){
                  pStmt.setInt(i+1, (currentBatch.get(i)));
              }
              rset = pStmt.executeQuery();
              while(rset.next()){
                  int id = rset.getInt(1);
                  Clob detailsClob = rset.getClob(2);
                  Writer writer = adapter.getCharacterOutputStream(detailsClob);
                  String details = idToDetails.get(id);
                  if (details != null) {
                      writer.write(details);
                  }
                  writer.flush();
                  writer.close();
                  idToDetailsClob.put(id, detailsClob);
              }
              currentBatch.clear();
              BaseSQLHelper.close(pStmt, rset);
         }

         int counter = 0;
         pStmt = conn.prepareStatement("UPDATE PROGRAM_HISTORY SET DETAILS = ? WHERE ID = ?");

         for(int i=0; i<IDList.size(); i++){
             int index = 1;
             Clob detailsClob = (Clob) idToDetailsClob.get(IDList.get(i));
             pStmt.setClob(index++, detailsClob);
             pStmt.setInt(index++, IDList.get(i));
             pStmt.addBatch();
             counter++;
             if(counter % BATCH_SIZE == 0) {
                 pStmt.executeBatch();
                 pStmt.clearBatch();
                 counter = 0;
             }
         }
         if(IDList.size() % BATCH_SIZE > 0) {
             pStmt.executeBatch();
         }                         
     }
     catch (SQLException se)
     {
         se.printStackTrace();
     }
     catch (IOException se)
     {
         se.printStackTrace();
     }
     finally
     {
         cleanup(conn, pStmt, null);
     }
     System.out.println(System.currentTimeMillis()-s1);
}

Answer 1

使用可更新的结果集执行查询，以便在滚动时更新数据，而无需执行单独的更新语句

您需要create your prepared statement将resultSetConcurrency设置为ResultSet。CONCUR_可更新。查看关于处理流的oracle documentation，了解处理clob数据的各种方法

Answer 2

如果我正确理解了您的代码，那么您正在向detailsclob列追加文本

在PL/SQL中这样做会更快，因为您不必通过网络获取clob。例如，您可以准备以下语句：

DECLARE
   l_details CLOB;
BEGIN
   SELECT details INTO l_details FROM program_history WHERE ID = ?;
   dbms_lob.append(l_details, ?);
END;

并绑定currentBatch.get(i)和idToDetails.get(id)

请注意，您不需要使用PL/SQL进行额外的更新