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.
The first thing that springs to mind is to use an a discriminator column (@DiscriminatorColumn) to define which of two trade tables the record is written to. This is also dependent upon how much you query the history trade records; how often they get updated etc (moving from trade_current to trade_history would involve a delete and re-add I believe).
We don't have requirement to move data from one trade table to another. Lets say we have 2 tables - Trade_Actual, Trade_Dummy. The trades which go in Trade_Actual are never need to be deleted. The trades (in the range of 200K) which goes in Trade_Dummy needs to be deleted everyday. Ok, I'll add an additional attribute in Trade class (say isDummy) to identify whether it should go in Trade_Actual or Trade_Dummy. My question is, how can we configure in hibernate (based on column isDummy) to persist trade in either Trade_Actual or Trade_Dummy.