Using CTAS & Exchange Partition Replace IAS for Copying Partition on Exadata
- by Bandari Huang
Usage Scenario:
Copy data&index from one partition to another partition in a partitioned table.
Solution:
Create a partition definition
Copy data from one partition to another partiton by 'Insert as select (IAS)'
Create a nonpartitioned table by 'Create table as select (CTAS)'
Convert a nonpartitioned table into a partition of partitoned table by exchangng their data segments.
Rebuild unusable index
Exchange Partition Convertion
Mutual convertion between a partition (or subpartition) and a nonpartitioned table
Mutual convertion between a hash-partitioned table and a partition of a composite *-hash partitioned table
Mutual convertiton a [range | list]-partitioned table into a partition of a composite *-[range | list] partitioned table.
Exchange Partition Usage Scenario
High-speed data loading of new, incremental data into an existing partitioned table in DW environment
Exchanging old data partitions out of a partitioned table, the data is purged from the partitioned table without actually being deleted and can be archived separately
Exchange Partition Syntax
ALTER TABLE schema.table EXCHANGE [PARTITION|SUBPARTITION] [partition|subprtition] WITH TABLE schema.table [INCLUDE|EXCLUDING] INDEX [WITH|WITHOUT] VALIDATION UPDATE [INDEXES|GLOBAL INDEXES]
INCLUDING | EXCLUDING INDEXES Specify INCLUDING INDEXES if you want local index partitions or subpartitions to be exchanged with the corresponding table index (for a nonpartitioned table) or local indexes (for a hash-partitioned table). Specify EXCLUDING INDEXES if you want all index partitions or subpartitions corresponding to the partition and all the regular indexes and index partitions on the exchanged table to be marked UNUSABLE. If you omit this clause, then the default is EXCLUDING INDEXES.
WITH | WITHOUT VALIDATION Specify WITH VALIDATION if you want Oracle Database to return an error if any rows in the exchanged table do not map into
partitions or subpartitions being exchanged. Specify WITHOUT VALIDATION if you do not want Oracle Database to check the proper mapping of rows in the exchanged table. If you omit this clause, then the default is WITH VALIDATION.
UPADATE INDEX|GLOBAL INDEX Unless you specify UPDATE INDEXES, the database marks UNUSABLE the global indexes or all global index partitions on the table whose partition is being exchanged. Global indexes or global index partitions on the table being exchanged remain invalidated. (You cannot use UPDATE INDEXES for index-organized tables. Use UPDATE GLOBAL INDEXES instead.)
Exchanging Partitions&Subpartitions Notes
Both tables involved in the exchange must have the same primary key, and no validated foreign keys can be referencing either of the tables unless the referenced
table is empty.
When exchanging partitioned index-organized tables:
– The source and target table or partition must have their primary key set on the same columns, in the same order.
– If key compression is enabled, then it must be enabled for both the source and the target, and with the same prefix length.
– Both the source and target must be index organized.
– Both the source and target must have overflow segments, or neither can have overflow segments. Also, both the source and target must have mapping
tables, or neither can have a mapping table.
– Both the source and target must have identical storage attributes for any LOB columns.
