HI, I was coping with a design problem. It would be great if some one can help it out.
The problem statement: I have to read data from a huge oracle table (approx 3K - 5K rows) where in the rows are getting updated dynamically. I have to fetch the data (rows) from datbase and display to the user (a JSP will do).
How should I design my application so that only the differential (the updated data) is read from the table and the corresponding record is updated on the View).
My approach: I thought of assining multoiple threads a section of table (1-1K, 1001-2K etc) and these threads will be constantly reading the table and updating view. But the problem is that in this scenario, all the rows will be updated even when only a few have been updated.
Another approach I thought of was using triggers in oracle. When a row gets updated, trigger will be executed and a java module can be invoked (not sure if this can be done)and it would push the data on the view.
Please let me know if you think of an approach.
Since you are firing a trigger when ever your rows are being updated,create a temporary table in your database which will be updated by the trigger with latest data(save the primary key of the updated record).Apply some rules(may be restrict the number of rows of temporary table and clear it whenever required or else you will end up with duplicate table).Create a view combining these two tables which will always gives you the updated records.
Let me know if you have any other idea.
Your problem is not clear.
Do you want to show web user only updated records? if yes, whats the definition of updated records? Do you mean the records that were previously fetched by the user and which are updated by then?
If Yes than Trigger might be performance bottleneck in a high updation table. You can simply use a Date/Time type column "lastAccessedDate" in your table and query only those records whose "lastAccessedDate" is greater than the date/time when last fetched occurs (value will be sent by client).
Do you want to dynamically update the web page? If yes are u going to use AJAX?
May be i didnt understand your problem so if you can clarify it a little, i might able to help.