0

I would like to recover the data from Oracle database using JDBC but using chunks. In contrast with MySQL and other databases ORacle do not allow easily to recover only a subset of the rows from a query. Any suggestion?

Should be possible to use Java 8 API to stream the JDBC.

I try to use a pagination implementation. However, pagination always download all the results into resultset and the only thing you avoid is the mapping.

I would like to retrieve 200'000'000 of records in chunk of 1'000'000.

Alexander Petrov
  • 9,204
  • 31
  • 70
ypriverol
  • 585
  • 2
  • 8
  • 28
  • Show the code on what you have tried. – M. Deinum Mar 25 '16 at 07:41
  • What do you mean with "recover only a subset of the rows"? Oracle absolutely supports point in time recovery to restore a backup to a certain point. –  Mar 27 '16 at 13:27

1 Answers1

2

I think there are two questions in your question. 1. How to deal with a very large result set(in Oracle). 2. How to do paging(In Oracle).

Oracle supports server side cursors so you dont realy need to page the result set when it is very large. It is enough to select everything you need and specify FET SIZE of 1 000 000 to your JDBC driver.

If you realy want to use pagination with Oracle( this is the other question)

you can use nested select statements and page by Row number . First you select what you desire. 

select * 
  from ( select /*+ FIRST_ROWS(n) */ 
  a.*, ROWNUM rnum 
      from ( your_query_goes_here, 
      with order by ) a 
      where ROWNUM <= 
      :MAX_ROW_TO_FETCH ) 
where rnum  >= :MIN_ROW_TO_FETCH;
Alexander Petrov
  • 9,204
  • 31
  • 70
  • I use the oracle query with interval as you suggest and it is very slow, when you want for example to loop througth 200'000'000 of entries in chunks of 1'000'000. – ypriverol Mar 28 '16 at 23:00
  • Is the query slow, or the iteration. You need to differenciate. If it is the iteration, then either your fetch size is not set correctly either you network is slow. – Alexander Petrov Mar 29 '16 at 06:50
  • The performance issue i guess is in the server side, when we move from 100'000 to 1'000'000 interval. Then, is when we observe the problems in the performance, take a couple of minutes to retrieve the data. – ypriverol Mar 29 '16 at 11:28
  • Have you considered increasing the RAM on the oracle server as well as the buffers. – Alexander Petrov Mar 29 '16 at 12:42
  • I was thinking to create a kind of temporary table for my query (like temporary view) that can be reuse during the pagination. I will increase also the RAM and buffer of the machine. – ypriverol Mar 29 '16 at 13:00
  • I think that the RAM and the Buffer should be sufficient, but lets see. – Alexander Petrov Mar 29 '16 at 13:41