On Wed, May 27, 2009 at 4:52 PM, Kevin Barnard
<kevin.barnard@laser2mail.com> wrote:
> I am making the move to partition a table. I am trying to figure out the
> best way to migrate data to the partitions. I would prefer to not have down
> time. Does anybody have advice to give on this? Is there any easy way to
> determine what records are in the master table and which ones are in child
> tables? I can think of a few migration ideas doing this.
If you continue to query the master table, as you move data to the
child tables, you won't really have to do much to your app. This is
the benefit of the table inheritance.
What we did was update our app to insert new data into the proper
sub-table. Then we had a program running that would move one hunk of
data at a time to from the master table to the proper sub-table. The
programs querying the master table were unchanged, and just did the
right thing. Once all data was moved, we did a TRUNCATE on the master
table, and updated the queries we could so they would access the
proper child table directly. Some queries still needed to scan the
whole table so were left to query the master table.
Once you start playing with it (and the constraint exclusion) it
really becomes obvious that it is not all that complicated and
magical.