Updating using 2 tables with sql

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.For example, if SALES has a column group, "(COL1, COL2)" then Q2 will need these statistics too.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.

SALES has partitions for quarter 1 and quarter 2 (Q1 and Q2) and LOAD is exchanged with the empty Q2 partition.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.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.

Leave a Reply