Partitioning, rolling windows for data for DW
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Partitioning, rolling windows for data for DW

  1. #1
    I have a question. We have tables which we load data into. They come from several clients, each day. It is never updated, except for a column called process_num, which ties the data for that day to the ETL program which transforms the data into the star schema.

    I do not need to save the data for more than about 2 weeks... however I do not want to run "delete" statements. What is your thought about using partitions for this?

    1. The process_num is NULL upon data load... and is updated (logically locking the records for processing)... if I have partitions for a range of process_num... will an update automatically MOVE the data into the correct partition? I'm not sure it will... This way I could simply drop the old partition?
    -- I guess I could ALSO put a "row_num" column (simply a max(col_id) in the .ctl file) and partition on that... but I don't want to delete half a day's load.

    -- Can I ALTER a range value of a partition?
    -- yes obviously I could put a LOAD date in the table, I was hoping not to have to maintain adding partitions for each week, especially if a partition doesn't fill in the beginning.. I prefer a partition to hold like 100,000 rows, and change that number as data loading grows....

    Alternatively, could I turn the "loading" tables into NOLOGGING mode... so the sql-ldr and "update process_num" statement would run faster?

    thanks for your thoughts.

    [Edited by bberg@rtnyc.com on 04-30-2001 at 09:11 AM]

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    well, if you partitionning key is process_num, you won't be able to do what you want since you can only update the partitionning key if the line stays in the same partition ...

    I do not think you can alter the range value of a partition, nevertheless you can merge and split partitions, so it would be possible to indirectly change the range value

    and yes, you can have a NOLOGGING partition ...

    hope this helps a bit

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by pipo
    well, if you partitionning key is process_num, you won't be able to do what you want since you can only update the partitionning key if the line stays in the same partition ...
    Not true in 8.1.x. You can enable row movement during the partition definition. That way, when you update the partition key, the row will move to the appropriate partition.

    As pipo says, you modify the bounds of the range partitions via the split and merge commands. Truncating the partitions is a quick operation, although you will have to be carefull about what types of indexes you put on this data.
    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."

  4. #4
    Do I need to specify anything in the partition definition to enable row movement, or is this automatic?

    You have a link to the docs on partition movement?

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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."

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