We have more than 10 million work objects in our application and looking for an archival strategy to improve system performance. We have around 3 million work objects for a particular case type which are resolved and rarely being reopened. Our customer wants to archive those 3 million work objects into a new temporary table and provide an option to view them in search and reports. We are planning to create an archive table - PC_WORK_ARCHIVE, PC_DATA_WORKATTACH_ARCHIVE, PC_HISTORY_ARCHIVE,... and move those records. Whenever user tries to reopen a work object, run SQL scripts to move them back to main table. Could you please let us know if this approach is feasible, if not provide us the best possible solution.
The ideal approach is to build an archive environment and import the Purged & Archived rap in to this environment. When ever, you want the archived case, then you can export it form Archived environment and import it in the production environment.
This isn't a production ready approach. Say in a Customer service implementation, where we would need to have an over view of all the case including the resolved cases(assume its already archived) then at a moment when the agent receives a call from the customer, we will be pushed to give a solution without getting an overview of the old cases( plainly because, an agent cannot upload the archive rap to get a vision of all the data)