Using Partition Exchange in Datawarehouse Environment

Our business needs:
- current data (3 months) should be on faster filesystem [RAID 1+0]
- older than 2 months should be on slower filesystem RAID 5
- older than 1 year data should be taken to Tape

So we have planed as below:
1. Current data should be kept on a Partitioned table [daily ranged partition]
So, that - each day partition will not going to contain garbage data[other day data]
- ETL processing and application design becomes simple
- tables can be managed easily.
2. Daily partition should be created on a seperate tablespace [or 10 day data will be kept on single tablespace]
3. Older partitions will be converted to stand alone tables and converted to TTS along with their tablespaces
4. Older partitions will be dropped from the current partitioned table
5. Newly created TTS with converted standalone tables will be removed from the PROD DB and moved to the slower filesystem
6. Finally these standalone tables will be reattached to the ARCHIVE database [as standalone table in partitioned view or as a partition of a table]

We have several indexes on the current partitioned table and we wanted to check the scrnerios specially related to converting a partition to a table.
So, we went through below steps:
1. create a date ranged partition
CREATE TABLE part_tab (id NUMBER, names varchar2(10) ,dates DATE)
PARTITION BY RANGE (dates)
(PARTITION PART_01_JUN_2011 VALUES LESS THAN (TO_DATE('2011-06-02', 'YYYY-MM-DD')),
PARTITION PART_02_JUN_2011 VALUES LESS THAN (TO_DATE('2011-06-03', 'YYYY-MM-DD')),
PARTITION PART_03_JUN_2011 VALUES LESS THAN (TO_DATE('2011-06-04', 'YYYY-MM-DD')),
PARTITION PART_04_JUN_2011 VALUES LESS THAN (TO_DATE('2011-06-05', 'YYYY-MM-DD')),
PARTITION PART_05_JUN_2011 VALUES LESS THAN (TO_DATE('2011-06-06', 'YYYY-MM-DD')),
PARTITION PART_MAX VALUES LESS THAN (MAXVALUE)
);
-- create two local indexes (one unique and other normal)
create unique index idx_part_tab_unq on part_tab(id,dates) local;
create index idx_part_tab_names on part_tab(names) local;
-- create one global index
create index idx_part_tab_id on part_tab(id);

2. check the status of the patition table and its indexes
select table_name,partition_name from user_tab_partitions where table_name='PART_TAB';
select index_name,status from user_ind_partitions where index_name=upper('idx_part_tab_unq');
select index_name,status from user_ind_partitions where index_name=upper('idx_part_tab_names');
select table_name,index_name,status from user_indexes where index_name=upper('idx_part_tab_id');

3. inset sample data
insert into part_tab values(1,'aa','1-Jun-2011');
insert into part_tab values(2,'bb','1-Jun-2011');
insert into part_tab values(3,'cc','2-Jun-2011');
insert into part_tab values(4,'dd','2-Jun-2011');
insert into part_tab values(5,'ee','3-Jun-2011');
insert into part_tab values(6,'ff','3-Jun-2011');
insert into part_tab values(7,'gg','4-Jun-2011');
insert into part_tab values(8,'hh','4-Jun-2011');
insert into part_tab values(9,'ii','5-Jun-2011');
insert into part_tab values(10,'jj','5-Jun-2011');
commit;

--check data , there should be 2 rows in each partition
select * from part_tab partition(PART_01_JUN_2011);
select * from part_tab partition(PART_02_JUN_2011);
select * from part_tab partition(PART_03_JUN_2011);
select * from part_tab partition(PART_04_JUN_2011);
select * from part_tab partition(PART_05_JUN_2011);

4. Now create the target non partitioned table with same structure and indexes [similar indexes like all local indexes, no global indexes and no extra indexes]
create table TAB_01_JUN_2011 as select * from part_tab partition(PART_01_JUN_2011) where 1=2;
create unique index idx_TAB_01_JUN_2011_unq on TAB_01_JUN_2011(id,dates);
create index idx_TAB_01_JUN_2011_names on TAB_01_JUN_2011(names);

5. check target tables indexes
select table_name,index_name,status from user_indexes where index_name in('IDX_TAB_01_JUN_2011_UNQ','IDX_TAB_01_JUN_2011_NAMES');

6. check target tables data [should be empty]
select * from TAB_01_JUN_2011;

7. now exchange the partition

ALTER TABLE part_tab
EXCHANGE PARTITION PART_01_JUN_2011 WITH TABLE TAB_01_JUN_2011
INCLUDING INDEXES
WITHOUT VALIDATION
update global indexes;

8. Now check the source partition[should be empty now]
select * from part_tab partition(PART_01_JUN_2011);

9. now check the target table [should have all the rows of the source partition]
select * from TAB_01_JUN_2011;


10. check whether indexes are ok on both
insert into TAB_01_JUN_2011 select * from TAB_01_JUN_2011; --ORA-00001, means unique index is ok
insert into part_tab select * from part_tab;--ORA-00001, means unique index is ok

select * from part_tab where names='cc';--no error and plan show that it is using index
select * from TAB_01_JUN_2011 where names='aa';--no error and plan show that it is using index
select * from part_tab where id=8;--no error and plan show that it is using index

11. check the status of partitioned table & indexes and target table and indexes
select table_name,partition_name from user_tab_partitions where table_name='PART_TAB';
select partition_name from user_ind_partitions where index_name=upper('idx_part_tab_unq');
select index_name,status from user_ind_partitions where index_name=upper('idx_part_tab_names');
select table_name,index_name,status from user_indexes where index_name=upper('idx_part_tab_id');
select table_name,index_name,status from user_indexes where index_name in('IDX_TAB_01_JUN_2011_UNQ','IDX_TAB_01_JUN_2011_NAMES');

12. alter table part_tab drop partition PART_01_JUN_2011 update global indexes;
select test_num_rows from dual;

13. now check whether any proceudre,function or other objects becomes invalid
--if soem object becomes invalid, create necessary scripts to recompile just it after partition exchange;
select object_name,object_type,status from user_objects where status<>'VALID';

14. Tables/partitons older than one year on the ARCHIVE DB may again converted to standalone tables and TTS and then table the whole TTS set to tape.

From above we have found that:
- Global index on the source partitioned table becomes invalid if we donot user update global indexes
- Objects(finctions & procedures) referencing to the target partitioned table becomes invalid

So, at the end we decided to go according to the plan with below conditions:
- There should not be any global index and "update global indexes" is a time consuming operation for a large table
- There should not be any object which directly references to partitions current partitioned tables