So I was apprised of some goings-on wherein our data warehouse tables were going to (finally) be moving from separate monthly tables (e.g. FOO_200607, FOO_200608, FOO_200609) to a single table with range partitions on the year and month fields and local indexes.

The developers were going to just do direct insert into the new table (presumably holding off on building indexes and constraints until after), but I immediately saw my chance to finally use Oracle's partition exchange. Paritition exchange basically allows you to do a logical swap of a single partition with a single table. The hitch is that the partitioned table and the to-be-swapped table have to be virtually identical in column types, sizes and constraints. The constraints part means you can't have a NOT NULL constraint on a column in one table and not in the other. If you use INCLUDING INDEXES in your exchange command, then the indexes need to be identical as well in terms of columns indexed.

I didn't even bother trying to do the "insert way," but I rest assured that I'm saving a LOT of time doing partition exchange. Equally important is the space saved. If I were to simply copy the data via insert statements, I would basically need to use twice as much tablespace, and in this case that is a LOT. Partition exchange involves none of that. I just do the swap and them I am free to drop the monthly tables (which should contain zero rows now). Anyway the big migration is part of a crazy Sunday SUNday SUNDAY of zaps and releases coming up this weekend. All the time saved for something like this is a huge win considering everything else that we need to get done in a Sunday night.

And one famous DBA told me that partition exchange is clutch for doing dataloads into a NOLOGGING stage table, then exchanging that table into your normal partitioned table. That avoids ugliness with NOLOGGING and redo logs. I currently bump up against this all the time doing RMAN duplication recovery because all of our DW tablespaces and tables were created with NOLOGGING. Yes it seemed like a good idea when we just cared about load times. But now I know that it shrinks my recovery window on any given day and also prevents us from trying to use DataGuard. We'll be going back to LOGGING next year after we have time to test and time it.

I can't remember if the DBA was tkyte, HJR or maybe hali from #oracle. All three rock.