This technique is used when large volumes of data must be loaded and maximum performance is paramount. The graphic below represents the process. The exchange is a logical operation: Typically, the exchange step looks like this: For example, new data can be inserted into LOAD before any indexes have been created on this table. If the volume of data is large, creating indexes at the end of the load is very efficient and avoids the need to bear the higher cost of index maintenance during the load.
The performance benefit is especially impessive if data is loaded at very high rates in parallel. The exact steps you need to execute for a partition exchange load will vary depending on the type of partitioning you use, whether there are local or global indexes on the table and what constraints are being used.
When new data is loaded into a table, optimizer statistics must be updated to take this new data into account. I'm also going to assume that the statistics on SALES are up-to-date prior to the partition exchange load.
Once the exchange has taken place, Q2 will need fresh statistics and a synopsis and it might also need extended statistics and histograms if SALES has them. The database takes care of this automatically, so there's no requirement to create histograms and extended column statistics on LOAD prior to the exchange because they are created for you when statistics are gathered on SALES. There is nevertheless a scenario where you might want to gather statistics on LOAD prior to the exchange.
This is easy to do because any statistics gathered on LOAD will be associated with Q2 after the exchange. However, bear in mind that this will ultimately mean that statistics for the new data will be gathered twice: If you want to know more about extended statistics and column usage then check out this post. It covers how you can seed column usage to identify where there's a benefit in using extended statistics.
Note that some column usage information is always captured to help identify columns that can benefit from histograms. This happens even if you don't choose to seed column usage. This means that a synopsis will be ready to be used as soon as the exchange has taken place without requiring statistics to be gathered on Q2 post-exchange.
This is how to prepare the LOAD table before the exchange: This isn't quite the end of the story though. Statistics on Q2 will be gathered again after the exchange when statistics are gathered on SALES unless you have created appropriate histograms and extended statistics on LOAD before the exchange.
Statistics on Q2 will be gathered post-exchange if SALES has column usage information indicating that there are columns in Q2 that don't have a histogram but might benefit from having one. Also, as mentioned above, extended statistics will be maintained automatically too. For Oracle Database 11g, statistics will always be gathered on Q2 once the exchange has completed.
Oracle Database 12c will complete this step more quickly if you implemented "4", above. If, in the past, you have used partition exchange load and gathered statistics in an ad-hoc manner then you should probably check that the histograms you have match your expectations when comparing table-level histograms with histograms on partitions and sub-partitions. Composite Partitioned Tables If you are using a composite partitioned table, partition exchange load works in the same way as described above.