Partioning with Hibernate

Question

Hello,

We have a requirement to delete data in the range of 200K from database everyday. Our application is Java/JEE based using Oracle DB and Hibernate ORM tool.

We explored various options like

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

Our DBA suggests database partitioning is the best way to go, so we can easily recreate and drop the partitioned table everyday. Now the issue is we have 2 kinds of data, one which we want to delete everyday and the other which we want to keep it. Suppose this data is stored in table "Trade". Now with partitioning, we have 2 tables "Trade". We have already existing Hibernate based DAO layer to fetch/store trades from/to DB. When we decide to partition the database, how can we control the trades to go in which of the two tables through hibernate. Basically I want , the trades need to be deleted by end of the day, to go in partitioned table and the trades I want to keep, in main table. Please suggest how can this be possible with Hibernate. We may add an additional column to identify the trades to be deleted but how can we ensure these trades should go to partitioned trade table using hibernate.

I would appreciate if someone can suggest any better approach in case we are on wrong path.

Answer 1

When we decide to partition the database, how can we control the trades to go in which of the two tables through hibernate.

That's what Hibernate Shards is for.

Answer 2

You could use hibernate inheritance strategy.

If you know at object creation that it will be deleted by the end of the day, you can create a VolatileTrade that is a subclass of Trade (with no other attribute). Use the 'table per concrete class' strategy (section 9.1.5 of hibernate 3.3 reference documentation) for the mapping.

(I think i would do an abstract superclass Trade, and two concrete subclasses : PersistentTrade and VolatileTrade, so that if you have some other classes that you know will reference only PersistentTrade (or Volatile), you can constrain that in your code. If you had used the Trade superclass as the PersistentTrade, you won't be able to enforce that.)

The volatile trade will go in one table and the 'persitent' trade will go in another table.

Be aware that you won't be able to set a fk constraint on any Trade (persistent and volatile) from other table in the db.

Then you just have to clear the table when you want.

Be careful to define a locking mechanism so that no other thread will try to write data to the table during the drop and the create (if you use that). That won't be an easy task, and doing it rightfully might impact the performance of all operation inserting data in the table (as it will require acquiring the lock).

Wouldn't it be more easy to truncate the table ?

Answer 3

Thanks Thierry for proposing this solution. Since the trade creation logic is going to be same(end user won't know whether s/he is creating PersistentTrade or VolatileTrade), what advantage we have with this refactoring over horizontal partitioning.

Secondly all application logic like report generation etc. is going to be same whether it is PersistentTrade or VolatileTrade. So for all practical purposes both trades are same, only difference is, VolatileTrades need to be deleted by end of the day. Probably this solution would require more changes in existing code. It could be a good solution for building a new application with this kind of requirement.

I agree with your point on deletion. I don't think we should be deleting it from application layer. It might be a simple cron job running at the end of the day, when application is not used, to drop the entire data.