CODEWITHSUNDEEP
javadatabasehibernateormpartitioning
Alex
Alex

Reputation: 31

Partitioning with Hibernate

We have a requirement to delete data in the range of 200K from database everyday. Our application is Java/Java EE 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.

Upvotes: 3

Views: 9742

Answers (2)

Thierry
Thierry

Reputation: 5440

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 ?

Upvotes: 0

Pascal Thivent
Pascal Thivent

Reputation: 570385

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.

Upvotes: 1

Related Questions