Has anyone got a working system using the new 8.1 contraint_exclusion partitioning on OLTP tables?
Here is my situation.
The
main system I administer is required by law to keep 7 years of
data. Management wants to keep all 7 years online so the user
community can access it whenever they want to. I am looking very
hard at partitioning the data since the tables are already becoming
very difficult to deal with at the 2-3 year point.
The main table of our system has a record number and a
status. This is my problem table. The record number is a
combination of a 2 digit year and a serial column. I have been
thinking of partitioning the table based upon those two fields.
>From my research, 95% + of the records are in 2 status that are
considered to be closed, the other 5% are the records that are being
actively manipulated by the
end user.
I have been thinking of partitioning our main table like this:
main_table_live (constraint status not in ('C','D);
main_table_2003 (constraint rec_number < 040000000 and status in ('C','D'));
main_table_2004 (constraint rec_number < 050000000 and rec_number > 039999999 and status in ('C','D'));
main_table_2005 (constraint rec_number < 060000000 and rec_number > 049999999 and status in ('C','D'))
main_table_2006 (constraint rec_number < 070000000 and rec_number > 059999999 and status in ('C','D'))
main_table_2007 (constraint rec_number < 080000000 and rec_number > 069999999 and status in ('C','D'))
The problem I am having is how can you safely move records
between main_table_live and one of the other partition tables?
Obviously, this will have to happen as our users work the data and the
records enter into on of the 2 closed statuses ('C' or 'D'). When
the status is changed to C or D, I need to first move the record and
run an update against the moved record to make sure all fields are
updated and our auditing triggers are fired.
Also, is there a way to make foreign keys work with the
partitioned table (i.e. can I create a foriegn key from a normal
unpartitioned table to a table that has been partitioned where the key
data may be in multiple partitions)?
Thanks for any help,
Chris
- [ADMIN] constraint_exclusion on OLTP tables Chris Hoover
