There exists a requirement to remove data in the plethora of 200K from database everyday. Our application is Java/JEE based using Oracle DB and Hibernate ORM tool.

We investigated various options like

  1. Hibernate batch processing
  2. Saved procedure
  3. Database partitioning

Our DBA indicates database partitioning is the easiest method to go, therefore we can certainly recreate and drop the partitioned table everyday. The problem is we've 2 types of data, the one that you want to remove everyday and also the other which you want to ensure that it stays. Suppose this information is saved in table "Trade". With partitioning, we've 2 tables "Trade". We've already established Hibernate based DAO layer to fetch/store trades from/to DB. Whenever we choose to partition the database, exactly how should we control the trades to use which of these two tables through hibernate. Essentially I would like , the trades have to be erased by finish during the day, to use partitioned table and also the trades I wish to keep, in primary table. Please suggest how is this possible with Hibernate. We might add one more column to recognize the trades to become erased but exactly how should we ensure these trades is going to partitioned trade table using hibernate.

I'd appreciate if a person can suggest much better approach just in case we're on wrong path.

Whenever we choose to partition the database, exactly how should we control the trades to use which of these two tables through hibernate.

That is what Hibernate Shards is perfect for.

You could utilize hibernate inheritance strategy.

Knowing at object creation that it'll be erased through the finish during the day, you may create a VolatileTrade that's a subclass of Trade (without any other attribute). Make use of the 'table per concrete class' strategy (section 9.1.5 of hibernate 3.3 reference documentation) for that mapping.

(I believe i'd do an abstract superclass Trade, and 2 concrete subclasses : PersistentTrade and VolatileTrade, to ensure that for those who have another classes you know will reference only PersistentTrade (or Volatile), you are able to constrain that inside your code. Should you have had used the Trade superclass because the PersistentTrade, you will not have the ability to enforce that.)

The volatile trade will use one table and also the 'persitent' trade will use another table.

Remember that you will not have the ability to set a fk constraint on any Trade (persistent and volatile) using their company table within the db.

Then you just need to obvious the table when you wish.

Make sure define a securing mechanism to ensure that not one other thread will attempt to create data towards the table throughout the drop and also the create (if you are using that). That will not be always easy, and doing the work rightfully might impact the performance of operation placing data within the table (as it may need obtaining the lock).

Would not it be less complicated to truncate the table ?