| Lesson 7 | Exchanging a Partition and a Table |
| Objective | Describe 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.
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.
- Name the partition that gets exchanged here. After the exchange, this partition will contain all the data that was in the table.
- Name the table that will be exchanged. After the exchage is done, this table will contain all the data that was in the partition.
- 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.
- 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.
- Rejected rows goes into the table listed here. Use when you validate (WITH VALIDATION) if you wish to save the rejected data.
- 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 INCLUDING INDEXES WITH VALIDATION EXCEPTIONS INTO tablenameName the partition that gets exchanged here. After the exchange, this partition will contain all the data that was in the table
Exchange Partition
The next lesson covers how to create a partition-wise query or join.