The effect of the exchange is to incorporate all of the data in LOAD into SALES by swapping the “identity” of LOAD with Q2.
The exchange is a logical operation: a change is made in the Oracle data dictionary and no data is moved.
It’s common to see it used in decision support systems and large operational data stores.
Make sure you’ve taken a look at Part 1, or you are at least familiar with the concept of incremental statistics so that you know what a synopsis is in the context of a partitioned table.
For example: Statistics will be gathered on Q2 and the synopsis will be created so that the global-level statistics for SALES will be updated.
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).
This post covers how you can manage optimizer statistics efficiently when you use partition exchange load (PEL).
This technique is used when large volumes of data must be loaded and maximum performance is paramount.The moment after LOAD has been exchanged with Q2 there will be no synopsis on Q2; it won’t have been created yet.Incremental statistics requires synopses to update the global-level statistics for SALES efficiently so a synopsis for Q2 will be created automatically when statistics on SALES are gathered.The data in LOAD is published to SALES “at the flick of a switch”.Typically, the exchange step looks like this: Operationally, this approach is more complex than inserting data directly into SALES but it offers some advantages.This is easy to do because any statistics gathered on LOAD will be associated with Q2 after the exchange.