"rolling windows" partitioning scheme info?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: "rolling windows" partitioning scheme info?

  1. #1
    Join Date
    Oct 2000
    Posts
    10
    Does anyone know where I can find extensive info/examples of this? I've checked all my Oracle books and MetaLink with negative results. My project is employing this scheme, but I do not yet have the DDL for it available to me. I would like to better familiarize myself with it. All I do know is what's described in the Oracle Press 24x7 book, Chapter 6, top of page 259. The scheme is used to routinely purge old partitions and roll in new ones for partitioning based on windows of inclusive dates. Thanx for any help you can give me on this!

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    I recently implemented a project like this. Basically, we wanted to keep 1 year of data online with a month in each partition. However, instead of re-using partitions, I just maintained 12 partitions.

    For example, in january my table would have 12 partitions:
    PART_2001_01
    PART_2001_02
    PART_2001_03
    ...
    PART_2001_10
    PART_2001_11
    PART_2001_12

    Then, when it came time to "roll" the partitions, I dropped PART_2001_01 and added PART_2002_01. The beauty of this method is that all the partition maintenance can be done via a package.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    I found that doing this requires monthly maintenance, and because youa re altering the table, you invalidate any packages/triggers against it.

    You should test this first.

    I also recommend that you MAY not want to put the year in the partition... only put PART_JAN, PART_FEB, etc.

    This way you don't have to drop and add partitions, just clean out the partition (truncate partition) before the month cycles around again. Of course this limits you 2 11 months of history, but if you have a date column in your tables, you still could clean out only those dates from over 11 months ago.

    Decide on how much maintenance you want, and if things get invalidated.

    good luck.

  4. #4
    Join Date
    Aug 2000
    Posts
    194
    you may add a column (such as part_mnth number(2)) which is partitioned. you can write a trigger to insert/update this column whenever records are added/updated.

    note: Keeping as number field has some advantage, becaz you can easily add, substrat, mod (useful when u automate the truncation of old partitions)

    Also, u may need some sort of job queue/scheduled job to purge/truncate the old partitions whenever the date rolls back to the next year.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width