Update... I modified my query and had success - took 12 minutes to run though.
> while ( (nRows = stmt.executeUpdate( "DELETE CDS_HISTORY WHERE MSGDATE <= > '2014-07-13 22:21:37.048000' LIMIT 10000" )) > 0 ) > Can you think of a better way? On Tuesday, July 15, 2014 12:03:16 PM UTC-4, Kenton Garner wrote: > > H2 Version: 1.4.179 > Java 7 > URL: > jdbc:h2:../dbFile;COMPRESS=TRUE;AUTOCOMMIT=ON;MVCC=TRUE;DB_CLOSE_ON_EXIT=FALSE;MV_STORE=FALSE;AUTO_SERVER=TRUE;AUTO_SERVER_PORT=8700 > Query: DELETE CDS_HISTORY WHERE MSGDATE <= '2014-07-13 22:21:37.048000 > Expected number of rows to delete 2.1 Million. > > I assume the transaction log is causing the issue, but that is just a > guess. Note: I currently have auto commit enabled. > These rows contain a relatively small amount of data, but I have plains to > do a similar delete with a large amount of data. > > I Oracle PL/SQL I would probably get a cursor and loop committing (n) > number of deletes at a time. > But I have never tried this with JDBC and I would expect looping on the > resultSet will be really slow. > > Do you have any suggestions? > > Attached JdbcSQLException... > > Caused by: org.h2.jdbc.JdbcSQLException: Out of memory.; SQL statement: >> DELETE CDS_HISTORY WHERE MSGDATE <= '2014-07-13 22:21:37.048000' >> [90108-179] >> at >> org.h2.message.DbException.getJdbcSQLException(DbException.java:345) >> at org.h2.message.DbException.get(DbException.java:168) >> at org.h2.message.DbException.convert(DbException.java:289) >> at org.h2.table.RegularTable.removeRow(RegularTable.java:407) >> at org.h2.command.dml.Delete.update(Delete.java:94) >> at org.h2.command.CommandContainer.update(CommandContainer.java:78) >> at org.h2.command.Command.executeUpdate(Command.java:253) >> at >> org.h2.jdbc.JdbcStatement.executeUpdateInternal(JdbcStatement.java:131) >> at org.h2.jdbc.JdbcStatement.executeUpdate(JdbcStatement.java:116) >> at >> com.issinc.cds.sbrecv.history.RecvHistoryDBStore.deleteOlderThanMsgDate(RecvHistoryDBStore.java:450) >> ... 1 more >> Caused by: java.lang.OutOfMemoryError: Java heap space >> at java.util.HashMap.resize(HashMap.java:584) >> at java.util.HashMap.addEntry(HashMap.java:883) >> at java.util.HashMap.put(HashMap.java:509) >> at java.util.HashSet.add(HashSet.java:217) >> at org.h2.index.PageDataIndex.remove(PageDataIndex.java:359) >> at org.h2.table.RegularTable.removeRow(RegularTable.java:389) >> ... 7 more >> >> > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
