1

There is 6GB table (oracle 12), which changes approximately every 1 second (delete,insert,update).

I have restful application which uses hibernate to call database with some filters to get data from this DB table. there are cases when "user" wants 1GB data from table as csv or xlsx report, selecting and fetching data from database takes too long. so I thought, maybe its better to take this whole DB table in to java object (in to heap) and make search and exports directly from this object.

but there is a problem: my java object should be synchronized with oracle Db table. What is the best way to listen/track to oracle table changes from java?

one way that I know is oracle util UTL_HTTP which can call web service directly from oracle, and using this util inform a service about data changes.

Can hibernate also listen to oracle table changes which is not initiated from the same service? I mean, is it possible that APP1 listen to some oracle table_1, and when APP2 changes some data in to oracle table_1, APP1 to know this automatically. if it can, please provide some examples.

also, I wonder what do you think about importing whole table data in to a service, and make filters in this object, will it be lightweight? (I know that ~6GB memory will always in use)

JiboOne
  • 1,438
  • 4
  • 22
  • 55
  • You could look into using an in-memory database. 6GB is not so large that a gussied-up database server could not handle it. – Tim Biegeleisen Oct 29 '18 at 14:04
  • 1
    Rolling your own cache for a large table which is changing constantly seems like a bad idea. I think a better idea would be to address the special cases (such as export to CSV) with special services which can handle large amounts of data in a performative fashion. – APC Oct 29 '18 at 14:16
  • You have to investigate oracle docs. e.g. https://docs.oracle.com/cd/E14526_01/coh.350/e14527/datachanges.htm#COHTU652 So the best approach will be to use cache which describes in Oracle documentation. also I found the way to solve you problem in sc : https://stackoverflow.com/questions/12618915/how-to-implement-a-db-listener-in-java – Egor Kravchenko Oct 29 '18 at 14:50
  • When you say that querying the database takes too long, are you certain that you've optimised the query sufficiently, added the correct indexes etc? – Michael Oct 29 '18 at 14:52
  • create an oracle external table as .csv format, and do a insert ... select into it. – OldProgrammer Oct 29 '18 at 16:09

0 Answers0