I have a global partition on an INDEX as below (the table was partitioned on different keys)
Fiscal year, period_cd, and 2 more keys as shown
CREATE INDEX kr.ufy_idx_px ON kr.LD_LDGR_ENTR_T (
u_fy, u_prd_cd, c_cd, obj_cd)
GLOBAL PARTITION BY RANGE (u_fy)
(PARTITION px11 VALUES LESS THAN (2012) ,
PARTITION px12 VALUES LESS THAN (2013) ,
PARTITION px13 VALUES LESS THAN (2014),
PARTITION px14 VALUES LESS THAN (2015),
PARTITION px15 VALUES LESS THAN (2016),
PARTITION px16 VALUES LESS THAN (2017),
PARTITION px17 VALUES LESS THAN (2018),
PARTITION px_dflt VALUES LESS THAN (MAXVALUE)) parallel (degree 8);
Very Soon Fiscal Year 2018 is going to be in play. If this were a partition on a table, midway through Fiscal 2018, I can add a new partition for 2018 and use "Exchange Partition" to move all my 2018 data into the 2018 partition. But how do I do this for an index partition?
The only thing I can think of is drop the index, recreate index along with the new partitions.
Is there an easier way to do this?
I hope you understood my question.
Thanks,
-- kr
No, there's no way to exchange an index. Doing so doesn't really make sense because then the index entries could point to non-existent rows!
Sounds to me like you want to split the maxvalue partition:
CREATE table LD_LDGR_ENTR_T (
u_fy int, u_prd_cd int, c_cd int, obj_cd int
);
CREATE INDEX ufy_idx_px ON LD_LDGR_ENTR_T (u_fy)
GLOBAL PARTITION BY RANGE (u_fy) (
PARTITION px11 VALUES LESS THAN (2012) ,
PARTITION px12 VALUES LESS THAN (2013) ,
PARTITION px13 VALUES LESS THAN (2014),
PARTITION px14 VALUES LESS THAN (2015),
PARTITION px15 VALUES LESS THAN (2016),
PARTITION px16 VALUES LESS THAN (2017),
PARTITION px17 VALUES LESS THAN (2018),
PARTITION px_dflt VALUES LESS THAN (MAXVALUE)
);
alter index ufy_idx_px split partition px_dflt at (2019) into (
partition px18, partition px_dflt
);
select partition_name, high_value
from user_ind_partitions
where index_name = 'UFY_IDX_PX';
PARTITION_NAME HIGH_VALUE
PX_DFLT MAXVALUE
PX18 2019
PX17 2018
PX16 2017
PX15 2016
PX14 2015
PX13 2014
PX12 2013
PX11 2012
This places the data in the appropriate partitions for you.