There is nevertheless a scenario where you might want to gather statistics on LOAD prior to the exchange.For example, if it’s likely that Q2 will be queried before statistics have been gathered on SALES then you might want to be sure that statistics are available on Q2 as soon as the exchange completes.
I expect you'll have guessed from the title of this post that I’m going to assume that from now on!
I'm also going to assume that the statistics on SALES are up-to-date prior to the partition exchange load.
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.
SQL grant execute on utl_file to ggs_owner; Grant succeeded.
We can then confirm that the Golden Gate user we have just created is able to connect to the Oracle database $ ggsci Oracle Golden Gate Command Interpreter for Oracle Version 10.4.0.19 Build 002 AIX 5L, ppc, 64bit (optimized), Oracle 11 on Sep 17 2009 Copyright (C) 1995, 2009, Oracle and/or its affiliates. GGSCI (devu007) 1 DBLOGIN USERID ggs_owner, PASSWORD ggs_owner Successfully logged into database.
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.
In the example above, the global-level statistics for SALES must be refreshed to reflect the data incorporated into the table when LOAD is exchanged with Q2.
To make this step as efficient as possible SALES must use incremental statistics maintenance.