Some background. I am implementing a poor man's partitioning mechanism to solve a database deleting and space cleanup problem. I have records being inserted into a table at the rate of about 50/second right now and these need to stay around for a while and then are aged out. We found that deletion using the SQL DELETE statement was not efficient enough nor re-used space well enough in our situation. So what I have done is to create separate tables, one per week, and changed the insertion to insert the data into the correct week. Purging older data is done using the SQL TRUNCATE statement on tables to purge out data on a scheduled basis. The customer has the ability to specify how many weeks of data to keep, so typically on 5 weeks or so are kept even though it has the capability so keep 52 weeks.
This is working very well, especially in the fact that insertions are never being done in tables that are going to be purged and purging older data is very fast now. Other parts of the system need to have a read-only logical view that hides this partitioning. So I have two ideas that can be done. The first is to use a View that performs a union of all of these 53 week tables (a very big View DDL statement :) ). I have this solution up and running but I have a problem in that while the purge is going on a query on the View can fail because of deadlocks between the purge facility truncating tables and the view accessing the tables when purge and querying done on the view simultaneously. The second is to use a restricted table function to present the logical view. This seems nice in that it can dynamically create a SQL SELECT of only the tables of interest based upon the configuration of how many weeks of data to keep. Using the restricted table function capability I can also pass the filtering criteria as the WHERE clause to this statement. I have this solution up and running and have solved the locking problem by having a lock table that is acquired in exclusive mode by the purge and shared mode by the restricted table function. So the purge will wait while a query is being done and the query waits while the purge is being done. So the question is which is going to perform better? I don't know much how the View capability is implemented so I don't know about its table locks, etc. So I am wondering if anyone here has some guidance of suggestions knowing the internals of Derby. This is using Derby 10.8.1.2 right now.
