Copy a partition
689919Apr 27 2009 — edited Apr 27 2009I have a table that stores daily account balances. The table is partitioned by day. The stats are something like this:
On a daily basis, only balances that have changed are stored: 5 mn records per day.
At the end of the month, all balances are stored : About 50 mn records.
This table (FACT_DLY_BAL) stores data for only 60 days. Older data is archived onto tape storage.
The requirement is to have another table (FACT_MTH_END_BAL) that will store only month end balances (i.e. balance on 4/30, 3/31, 2/28 etc). This table is also partitioned by day. The partitions p_20090331 on FACT_DLY_BAL should be identical to the partition p_20090331 on FACT_MTH_END_BAL.
Partitions in the FACT_DLY_BAL are loaded using partition exchange from a "work table" that is loaded as part of the ETL process and after indexes are validated, stats generated.
My question is: Is there a way to copy the p_20090331 partition from FACT_DLY_BAL to FACT_MTH_END_BAL while retaining indexes, constraints ? We would want to copy the statistics for this partition as well.
Any insight is much appreciated...
Thanks!