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]