Skip to Main Content
  • Questions
  • How to exchange index partition, if there is such a thing

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, KHALID.

Asked: June 20, 2017 - 3:54 pm UTC

Last updated: June 21, 2017 - 1:20 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

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


and Chris said...

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.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Answered concisely and clearly

KHALID RAHIM, June 20, 2017 - 5:52 pm UTC

It became clear as day when you answered my question.
Thanks,
-- kr
Connor McDonald
June 21, 2017 - 1:20 am UTC

glad we could help

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.