db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bergquist, Brett" <BBergqu...@canoga.com>
Subject Questions on using a View or a Restricted Table Function
Date Wed, 27 Jul 2011 23:54:52 GMT
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 right now.

View raw message