How to improve performance while update column Clob in oracle?

I used the following codes to update column Clob in oracle, it seems to be okay and work properly, after performance testing, it reported that need consumed more than 200ms while the length of string is more than 130000. Is it any good way to improve it?

  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)));
              }
      开发者_JAVA百科        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);
}

If I understand your code correctly, you are appending text to your details clob column.

Doing it in PL/SQL would be faster since you wouldn't have to fetch the clob across the network. For example you could prepare this statement:

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

and bind currentBatch.get(i) and idToDetails.get(id).

Notice that you don't need an additional update with PL/SQL.

Execute your query with an updatable ResultSet so that you can update the data as you scroll through without separate update statements being executed.

You need to create your prepared statement with the resultSetConcurrency set to ResultSet.CONCUR_UPDATABLE. Check out the oracle documentation on dealing with streams for the various ways you can handle the clob data.