extracting ranges of data
Posted: Thu Jun 24, 2010 8:14 pm
Hi,
Was hoping for some tips on the best approach to extract the records on a date basis and load them into appropriate Oracle partitions.
Our target tables will be partitioned by month, and in our every datastage batch we will get %98 of records going into the current month's partition and the rest will go to the earlier ones (late arriving data).
Example:
Target table targetSales with partitions 2010apr, 2010may, 2010jun, etc.
With the SYSDATE = 24/JUN/2010, I get following content in the unpartitioned sourceSales table:
ID CREATED_DATE PROCESSED_FLAG PROCESSED_DATE
33 24/JUN/2010 N 24/JUN/2010
34 12/JUN/2010 N 24/JUN/2010
35 15/APR/2010 N 24/JUN/2010
36 12/MAY/2010 N 24/JUN/2010
Since we are planning to use the PARTITION EXCHANGE function of Oracle, we have to extract all of the records belonging to the same period as any of our newly arrived records. With that done we would load them into temporary table that would be used in PARTITION EXCHANGE. That part seems easy.
The challenge is to extract the records and prepare them to be loaded into the appropriate partitions? Like so:
33,34 into partition 2010jun
35 into partition 2010apr
36 into partition 2010may
Appreciate any suggestions?
Regards,
Novak
Was hoping for some tips on the best approach to extract the records on a date basis and load them into appropriate Oracle partitions.
Our target tables will be partitioned by month, and in our every datastage batch we will get %98 of records going into the current month's partition and the rest will go to the earlier ones (late arriving data).
Example:
Target table targetSales with partitions 2010apr, 2010may, 2010jun, etc.
With the SYSDATE = 24/JUN/2010, I get following content in the unpartitioned sourceSales table:
ID CREATED_DATE PROCESSED_FLAG PROCESSED_DATE
33 24/JUN/2010 N 24/JUN/2010
34 12/JUN/2010 N 24/JUN/2010
35 15/APR/2010 N 24/JUN/2010
36 12/MAY/2010 N 24/JUN/2010
Since we are planning to use the PARTITION EXCHANGE function of Oracle, we have to extract all of the records belonging to the same period as any of our newly arrived records. With that done we would load them into temporary table that would be used in PARTITION EXCHANGE. That part seems easy.
The challenge is to extract the records and prepare them to be loaded into the appropriate partitions? Like so:
33,34 into partition 2010jun
35 into partition 2010apr
36 into partition 2010may
Appreciate any suggestions?
Regards,
Novak