I am working on a software where I am supposed to handle huge volume of data (500,000 rows per table). The basic job of this software is to read data from source table, enrich the data and insert into the target table. This software should be database independent so option of using PLSQL is ruled out. I am thinking of using JDBC multithreaded data processing architecture to handle this data. The brief description of architecture is as follows.
Total data of source table will be divided into n groups. These n groups will be assigned to n threads. Individual threads will select the data enrich that and put it into BlockingQueue. At the other end of the BlockingQueue there will be some subscribers threads which will select the data from this queue and will do batchupdate on the target table.
I need your help on following points.
1) Please let me know whether the architecture mentioned above is the optimum solution.
2) How i can divide the source data in n groups in database independent way. I will prefer logic which is not dependent on any functional values of the table columns. Following options i have thought of.
(a) All the n threads will do select * from <TABLE>. Using scrollable resultset every thread will jump to the desired cursor position and start
processing the data.
(b) Create a temporary table which has one additional column which will store number from 1 to <count of rows>. Populate all the data in this table and
use it for making groups.
Please suggest me your views on the options mentioned above.