I have a database (db.h2.db, db.trace.db) which contains only one table "tab" with two fields (the database has been created using MV_STORE=FALSE): key VARCHAR(100) not null, value VARCHAR not null and one non-unique index on key
My h2 lib is h2-1.4.188.jar I want to retrieve all rows from the table ordered by key: SELECT key,value from tab order by key My table has about 100 million rows, the average key length is about 10 characters, the average value length is about 300 characters. I run the query in Java using a prepared statement and then iterating over the result of preparedStatement.executeQuery() after calling connection.setAutoCommit(false) and preparedStatement.setFetchSize(100) When I run this (after opening the database read-only, transactions off etc) I get an out of memory error when allowing for 20G(!!) of heapspace (-Xmx20000M). I can watch in jconsole who the heap space keeps growing and growing with CPU usage around 70% until GC cannot free any memory any more. This is odd since it looks as if the executeQuery method would try to actually put the complete result into memory instead of simply walking the index and just fetching 100 rows or at least much less than the whole result. When I run explain on the query it shows: SELECT KEY, VALUE FROM PUBLIC.TAB /* PUBLIC.TABINDEXONKEY */ ORDER by 1 /* index sorted */ What is going on here? -- 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 https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
