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

我使用以下代码在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);
}

最佳答案

如果我正确理解您的代码，那么您正在将文本追加到details clob列中。

在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进行其他更新。

关于java - 在oracle中更新列Clob时如何提高性能？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/6842694/