Oracle Magazine, September/October 2017
DBA PERFORMANCE EXCHANGE TABLES The fastest way to load data into a partition is to exchange a table with it Its an operation in the data dictionary with no actual data movement so not only is it fast but it also doesnt cause a data outage Here is an example SQL statement that exchanges the temp_ accounts_ 2014 table with the 2014 partition of the ACCOUNTS table ORACLE MAGAZINE SEPTEMBER OCTOBER 2017 107 alter table accounts exchange partition p2014 with table temp_ accounts_ 2014 But you need to create the temp_ accounts_ 2014 table with the utmost care It has to be a faithful representation of the ACCOUNTS table but not partitioned as such For example if ACCOUNTS is just a partitioned table with no subpartitions you have to create the temp_ accounts_ 2014 table as unpartitioned Similarly if the ACCOUNTS table is composite partitioned the temp_ accounts_ 2014 table must be partitioned exactly as in the subpartition specification In Oracle Database 12c Release 2 you dont need to write complex data definition language DDL actions to account for the state of partitioning in your exchanges You can now use a special FOR EXCHANGE clause that creates the table for that very purpose eliminating all guesswork create table temp_ accounts_ 2014 tablespace ts_ 2014 for exchange with table accounts
You must have JavaScript enabled to view digital editions.
