Partitioning Tables  «Prev  Next»

Lesson 7Exchanging a Partition and a Table
ObjectiveDescribe how to exchange a Partition with a Table

Exchanging Partition and Table

The unique thing about partitions is that you can modify the data in one partition without touching the other partitions, and without slowing the response time of queries using those other partitions.
Oracle provides a new feature, called exchanging partitions, that allows you to build all the data you need for a partition ahead of time. First, you create a table and load the data into the table. Then, you issue the appropriate command, and the data in the current partition is traded with the data in the table. This occurs without actually moving any data, changes to the internal mapping that Oracle uses to track where the partition's data is located cause the exchange. One example of using this technique occurs when you refresh a large data warehouse with current data. First, you move the table to the data warehouse using the transportable tablespace feature. Then, you can use the exchange partition feature to move the data into your partitioned table.
The larger the amount of data involved, the more economical this new feature becomes.
Look at the following MouseOver to see the syntax for this new feature.

Exchange Partition
  1. Name the partition that gets exchanged here. After the exchange, this partition will contain all the data that was in the table.
  2. Name the table that will be exchanged. After the exchage is done, this table will contain all the data that was in the partition.
  3. Include or exclude indexes that you have created on the partition and the table in the exchange using either INCLUDING INDEXES or EXCLUDING INDEXES. Default is EXCLUIDING INDEXES.
  4. You can specify either WITH VALIDATION (the default) or WITHOUT VALIDATION. Validation checks all the incoming rows against the partition range and accepts only the rows that fit.
  5. Rejected rows goes into the table listed here. Use when you validate (WITH VALIDATION) if you wish to save the rejected data.
  6. This example accomplishes the same thing as the previous example - the default parameters (EXCLUDING INDEXES and WITH VALIDATION) have been omitted.
ALTER TABLE tablename
EXCHANGE partitionname WITH TABLE tablename 
Name the partition that gets exchanged here. After the exchange, this partition will contain all the data that was in the table

Exchange Partition
Exchange Partition

Exchange Partition The next lesson covers how to create a partition-wise query or join.