Using JDBC to asynchronously read large Oracle table

Question

What strategies can be used to read every row in a large Oracle table, only once, but as fast as possible with JDBC & Java ?

Consider that each row has non-trivial amounts of data (30 columns, including large text in some columns).

Some strategies I can think of are:

• Single thread and read table. (Too slow, but listed for clarity)
• Read the id's into ConcurrentLinkedQueue, use threads to consume queue and query by id in batches.
• Read id's into a JMS queue, use workers to consume queue and query by id in batches.

What other strategies could be used ?

For the purpose of this question assume processing of rows to be free.

Answer 1

First thing is how fast do you need it to be? One should be mindful to not get into the "premature optimization" zone.

The first suggestion for clarity is to increase the jdbc_batch_size. If your oracle database sits on a different host then you will see an increase in performance on all your suggestions.

You can implement paging to split your dataset into multiple pages, then using your suggestions

• Read the entire row/page into a ConcurrentLinkedQueue, use threads to consume queue (you avoid having to re-query based on id at the expense of using more memory)
• A similar approach can be taken for the JMS suggestion

Another suggestion, although I am not sure whether this will work, is to use the Java Streams API and process the ResultSet. The code for this will probably be more concise so that would be a plus. The only downside is that you might not get ResultSet to process in parallel (see this and this for some insight).