Derby in-memory db: out of memory when choosing in - statements

Question

I need to use derby with huge tables in the project. To get some performance, I select document identifiers from three different tables into one huge table to search for a document type.

Basically a very simple request:

"INSERT INTO DOC_TYPES SELECT DOC_ID, 1 FROM TYPE_A_DOCUMENT" "INSERT INTO DOC_TYPES SELECT DOC_ID, 2 FROM TYPE_B_DOCUMENT" "INSERT INTO DOC_TYPES SELECT DOC_ID, 3 FROM TYPE_C_DOCUMENT" 

When I run it in normal mode, with a derby on my hard drive, it takes about 1 minute to process, and I get about 6.5 million rows in the database (huge, I know ...)

In any case, the database is still a way to slow down my taste, so I tried to run everything in memory. Unfortunately, even with a heap size of 4 GB (the full database on my hard drive never exceeds 1 GB), the operation is pretty fast in "java / lang / OutOfMemoryError".

I do not see any alternatives or workarounds to solve this problem. Derby does not support materialized representations and does it in Java (SELECT, interate resultset, INSERT) will take several hours ...

You also cannot “smash” the expression, since Derby does not seem to support LIMIT.

Any suggestions / ideas?

Regards, Michael

Answer 1

You can try to create pieces modulo id, set the second value of the modulo operation to the number of blocks (n) that you need. and repeat the comparison from 0 .. (n-1)

 INSERT INTO DOC_TYPES SELECT DOC_ID, 1 FROM TYPE_A_DOCUMENT WHERE DOC_ID % 2 = 0 INSERT INTO DOC_TYPES SELECT DOC_ID, 1 FROM TYPE_A_DOCUMENT WHERE DOC_ID % 2 = 1 

and commit the transaction for each insert.