Skip to Main Content
  • Questions
  • Move subpartitioned indexes to another tablespace

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Stefano.

Asked: March 14, 2018 - 6:13 pm UTC

Last updated: June 22, 2023 - 2:35 pm UTC

Version: 11.2.0.4

Viewed 10K+ times! This question is

You Asked

Hi.
Tried to look around but haven't found a valid solution for my setup.

I have partitioned indexes with subpartitions like this:

CREATE INDEX "MYINDEX" ON "MYTABLE" ("ID1", "MYDATE") 
  TABLESPACE "MYIDXTBS"  LOCAL
 (PARTITION "P_2018_01" 
PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "MYIDXTBS" 
 ( SUBPARTITION "PART_2018_01_01" 
  TABLESPACE "MYIDXTBS"  , 
  SUBPARTITION "PART_2018_01_02" 
  TABLESPACE "MYIDXTBS"  , 
  SUBPARTITION "PART_2018_01_03" 
  TABLESPACE "MYIDXTBS"  , 
  SUBPARTITION "PART_2018_01_04" 
  TABLESPACE "MYIDXTBS" ) 



I need to move index subpartitions to other tablespaces

If I use

ALTER INDEX MYINDEX REBUILD PARTITION PART_2018_01_01 TABLESPACE MYIDXP20108_01 NOLOGGING;


i get

 ORA-14287 cannot REBUILD a partition of a Composite Range partitioned index


If I try to modify attributes I get

ORA-14121: MODIFY DEFAULT ATTRIBUTES may not be combined with other operations


Any idea?
Thanks

and Chris said...

That's because you need to rebuild the subpartition:

create table mytable (
  id1 int, mydate date
) partition by hash (id1)
  subpartition by hash (mydate) 
  subpartition template (
    subpartition sp1, subpartition sp2
  ) (
    partition p1
  );

CREATE INDEX "MYINDEX" ON "MYTABLE" ("ID1", "MYDATE") LOCAL;

select subpartition_name, tablespace_name from user_ind_subpartitions;

SUBPARTITION_NAME   TABLESPACE_NAME   
P1_SP1              ASSM_TBLSP        
P1_SP2              ASSM_TBLSP  

alter index myindex rebuild subpartition p1_sp1 tablespace tblsp;

select subpartition_name, tablespace_name from user_ind_subpartitions;

SUBPARTITION_NAME   TABLESPACE_NAME   
P1_SP1              TBLSP             
P1_SP2              ASSM_TBLSP  

Rating

  (3 ratings)

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

Comments

..a little extra..

Stefano Vasi, March 16, 2018 - 9:14 am UTC

Thanks for your reply. Everything is clear.
Mi real issue is that in my environment I've 4 partitioned, and subpartitioned, tables with their indexes where partitions and subpartitions name are the same.

Eg:

TABLE1
PART1
SUBPART1 (Tablespace: T1P1SP1)
SUBPART2 (Tablespace: T1P1SP2)
SUBPART3 (Tablespace: T1P1SP3)
PART2
SUBPART1 (Tablespace: T1P2SP1)
SUBPART2 (Tablespace: T1P2SP2)
SUBPART3 (Tablespace: T1P2SP3)
TABLE2
PART1
SUBPART1 (Tablespace: T2P1SP1)
SUBPART2 (Tablespace: T2P1SP2)
SUBPART3 (Tablespace: T2P1SP3)
PART2
SUBPART1 (Tablespace: COMMONTBS)
SUBPART2 (Tablespace: COMMONTBS)
SUBPART3 (Tablespace: COMMONTBS)

I need to modify, only for TABLE2 > PART2 > SUBPART1-3, with separate tablespaces (T3P2S1, T3P2S2, T3P2S3).

Altering what's in my scenario with the suggested solution will affect all subpartitions named SUBPART1,2 or 3 of the other partitions?

Is a messy world :)

Thanks again
Chris Saxon
March 16, 2018 - 1:17 pm UTC

I'm not sure what the issue is here?

Rebuilding a subpartition only affects one subpartition. So run three "alter index..." commands to move the three you want to relocate.

Stefano Vasi, March 16, 2018 - 1:24 pm UTC

Nothing wrong.
Simly I had to try it.
And it worked.

Thanks
You saved my day!
Connor McDonald
March 18, 2018 - 3:02 am UTC

glad we could help

Move Tablespace for SubPartition Indexes

Amin Adatia, June 22, 2023 - 10:18 am UTC

Is it possible to move the subpartition indexes for more than one partition at the same time using two separate jobs.
Chris Saxon
June 22, 2023 - 2:35 pm UTC

I'm not sure what you mean - please clarify

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.