Data Warehouse Best Practice (1) -- Partition exchange load

--From Oracle DW Best Practices White Paper

Steps:
1. Create external table for the flat file data coming from the online system
2. Using CTAS statement, create a non-partitioned table that has the same column structure as Sales table
3. Build any indexes that are on the Sales table on the temp_sales table
4. Gather optimizer statistics on the temp_sales
5. Issue the exchange partition command:
ALTER table Sales EXCHANGE partition May_24_2009 with table temp_sales including indexes without validation;

The last step does not physically move data, it simply updates the data dictionary to reset a pointer from the partition to the table and vice versa. So it is a sub-second operation and far less likely to impact performance than any traditional data-movement approaches such as INSERT.