Partitioning, rolling windows for data for DW

[bberg@rtnyc.com]

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]

[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 ...

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

[marist89]

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.

[bberg@rtnyc.com]

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?

[marist89]

No, by default row movement is not enabled. For the row movement clause, checkout
http://technet.oracle.com/docs/produ...titi.htm#11851

For merging partitions, check out
http://technet.oracle.com/docs/produ...2a.htm#2056688
and
http://technet.oracle.com/docs/produ...titi.htm#12492

For splitting, check out
http://technet.oracle.com/docs/produ...2a.htm#2056642
and
http://technet.oracle.com/docs/produ...rtiti.htm#6541