The point transactions are to be retained only for three quarters(current qrtr + 2 previous qrtrs). Only the customer information is to be retained. All cusotmers who are deactivated can be removed periodically.

We are planning to retreive based on indexes and we are having multiple file systems.

I am trying to understand how we can have partitions that store records based on say the customer status(active, deactive), the status of various request placed by the cutomer(like change in personal details, redemption of loyalty points etc.) whose status gets closed on a daily basis. The processing would be real fast if I have all the open request alone in one partition. Can we have such dynamic partitions? Can we try to move closed status records to other partition on a periodic basis?is it feasible?What is the overhead?