Note to self: How (not) to exchange partitions

Disclaimer: This is a "note to self;" not a guide, and by no means anything close to a "complete" guide! These are just the points that I can remember RIGHT NOW! :)

I spent a complete day on diagnosing a failing exchange subpartition operation; hence this post.

I was particularly disappointed (because of the OakTable accreditations) when I noticed that "Expert Oracle Practices" (Apress - OakTable, p353) describes the required conditions for a successful partition exchange in only one -occasionally vague- sentence: "[both segments should] have the same logical shape (that is, the same columns, data types, scalability[DATA_SCALE I assume], precision, data- integrity constraints such as NULL/NOT-NULL, and so forth)" although it has to be said that this book has got far more content than any other books I've recently read, and specifically details the workflow for exchanging partitions, in page 356, under "Loading Millions of Rows."

Pro Oracle 11g Administration sheds a little bit more light (p295) by saying: "[both segments] must have the same primary-key structure defined."

Oracle Database 11g Handbook suffices to say that (p266) both segments should have "the same column structure" and has reminder that in terms of the work flow you should:

1. Load data into the stand alone table
2. Analyze the table
3. Build the indexes "to match the partitioned table’s indexes" but doesn't elaborate on what does this "matching" entails. It also adds: "the indexes [on the partitioned table, obv!] must be local, and not global, indexes."

Based on my experience (11.2.0.3) you actually CAN exchange partitions without gathering stats on the stand alone table, but I'm not sure how it would benefit anyone, mainly because it can lead to stat anomalies on partitioned table, and severely affect the execution plan; unless -for whatever reason and through whatever means- you've made sure that you're not gathering any kind of stats on the partitioned table.

Also the indexes on both sides do not need to have the same name; all that matters is that both sides have the same number of indexes, and the indexes are matching in terms of type (Bitmap, B-Tree) and the columns that the indexes are built on.

Two other points on the indexes:

1. If you are using Bitmap indexes you HAVE TO make sure the Hakan Factor on both segments involved in the exchange are the same. (details: Metalink 1389444.1, although I'm more in favour of doing an "ALTER TABLE xxx NOMINIMIZE RECORDS_PER_BLOCK" on both tables when they're empty.)
2. if there are indexes on virtual columns, the definition of the virtual columns should match as well.

On the nature of columns:

Apart from name, type, and size/precision of columns on both sides, the order of the columns should match too. The presence of virtual columns in between non-virtual ones will not cause failure in partition exchange as long as the order of non-virtual columns are the same.

Notes:

• I have not tested exchanging partitions that contain Spatial indexes.
• This post will be updated, as soon as I remember/come across other points
• Or if you'll kindly let me know, if you know of other missing bits of info :)