Wednesday 12 March 2008

Materialized View - exchange partition - primary key

When exchanging partitions I have always not included the global primary key index on the table to be moved in. This is because the exchange partition fails if it is present. Makes sense since a primary key index has to be global and how does that fit in with having the same index in a new partition. The database has to check for uniqueness globally not locally....Anyway.... In turns out the primary key index can be present on the table to be passed in IF you specify DISABLE VALIDATE;

e.g.
ALTER TABLE sales_01_2001 ADD CONSTRAINT sales_pk_jan01
PRIMARY KEY (sales_transaction_id) DISABLE VALIDATE;


This link explains it all http://stanford.edu/dept/itss/docs/oracle/10g/server.101/b10736/refresh.htm

The key thing is once the table is exchanged in, does the global index go invalid and need rebuilding? I know it does go invalid when the index is NOT present on the table to be exchanged in. We'll see ..... I guess.