Question Table Partitioning

ron

Member
OE 11.6.3.017 on 64-bit Solaris

We have a 2 TB DB (actually 6 DBs that 'act' as one) - and we are in the process of migrating from 10.1C to 11.6.

I just read Tom Kincaid's blog about partitioning - in particular in an environment where very large tables are involved. He used an example of migrating chunks of about 3 months of data each time. My question is: does this mean ending-up with a multitude of partitions - each containing data for a 3-month period? Or can one simply move the partition (date in this case) boundary by 3-months to migrate another 3 months of data without creating a new partition?

Ron.
 

ForEachInvoiceDelete

Active Member
Not currently at work (week off, hurray!) so cant get you exact examples, but you can set up a SQL rule to move data between table partitions based on criteria. Link below for 11.7 but i did this with 11.6.3 so you should be fine.

OpenEdge 11.7 Documentation

1. Add a new integer field to your table with an index.

2. Set up a rule to move data into your new partition based on the integer field.

3. Write ABL code to alter the integer field in records you want moved.

Code:
for each Table exclusive-lock where Table.Intfield = 0
and table.datefield > today - 90.

    assign table.intfield = 1.

end.

would move the last 90 days worth of data to the new partition. You can start/stop and alter ABL code however you like to move the data.
 

ron

Member
Thanks very much for the replies.

I was aware of the technique you mention - I saw it demonstrated at a Progress conference. Unfortunately we use an application where the licence precludes us from making any schema changes. I have made the vendor aware of the issue but they have not agreed to add the extra field to any tables.

So - I thought that I might be able to achieve a similar result using partitioning.
 

ForEachInvoiceDelete

Active Member
Got any unused fields that have an index? ;)

I can take a look next week to see if this is possible without schema changing. If you can modify an existing partition policy it should be.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
You can do table partitioning without making schema changes. You install the prodct, enable the DB for partitioning, define partition policies on one or more tables, create the necessary storage areas, etc. You don't have to add new fields; that was done for the online D&L technique.

But make sure you know up front why you want to partition, i.e. what you hope to gain from it: reduced file contention? query performance? easier and faster purging of old data? There are a lot of options with TP and how you configure it should be determined by what you hope to gain. And then you should test it thoroughly to determine that it actually pays off for you. There are some whitepapers and some past conference presentations that go through these concepts so you should seek them out. You should also be aware of the gotchas with this feature. It isn't completely transparent to the application. Depending on how it is written, you may have some code that needs to be changed before you can safely use TP.

If you are going to partition a large (i.e. fast-growing) table by date, I would think 3-month increments are too short. You're going to spend a lot of time on administration and you're going to have a lot of partitions. If I were doing it I'd start out with partition policies one year apart. But you will need to test and see what works for you. Good luck and let us know how it goes.
 
Top