3

I have one table(1 cr records) in Oracle 12g which is getting updated(from other/ETL source) everyday at 4:00 AM which is my user master table. I am using this table to authenticate my user.

So simple way is get it from database but instead I am thinking to move/update the table into Redis everyday after ETL operation so that I don't have to connect DB for each authentication.

One way is write a scheduler/Cron job in Java which will run after ETL operation to copy table record and insert into Redis.

Is there any better way to do so? like use oRedis to update Redis cache directly from Oracle database or something else?

PS: Right now I am using Redis with Java(redisson) for caching.

I am using Netflix Zuul filter for the authentication so each request will be authenticated at Zuul filter so it seems costly operation to connect database for each request.

Puneet Purohit
  • 1,243
  • 6
  • 23
  • 42

2 Answers2

3

One way is write a scheduler/Cron job in Java which will run after ETL operation to copy table record and insert into Redis.

Oracle supports the stored procedures which can be written in Java https://docs.oracle.com/cd/B19306_01/java.102/b14187/chfive.htm In this case your code will be closer to data, thus it may work much faster than just a desktop java app.

Then, you may need to schedule daily synchronization using Oracle DBMS_SCHEDULER https://docs.oracle.com/database/121/ARPLS/d_sched.htm#ARPLS72235

The fastest access to oracle table is access by rowid https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns008.htm thus, the period of synchronization depends on how often rows are inserting. You need to access your table only for daily/hourly data using rowid.

Please note, that rowid might be changed during database migration/movement/major maintenance actions. What can cause an Oracle ROWID to change?

lazylead
  • 1,453
  • 1
  • 14
  • 26
0

Since you already know the time of ETL update to your table, I do think whatever you have proposed is already optimal. But here's a different perspective on the same problem. See if this flavour suits. :)

Oracle RAC supports reliable CQN (Continuous Query Notifications) termed in Oracle world as DCN. This can either be an Object Change event listener or Query change event listener. If you register a listener to either of those, along with the rowids option, your registered component will get the rowids which you can directly fetch via select to get the updated rows. This was your select will be much faster (assuming you are not already using a Date range partition on an update_date column and selecting those).

Refs: https://docs.oracle.com/cd/E11882_01/java.112/e16548/dbchgnf.htm#JJDBC28815

Ed Bighands
  • 159
  • 8