Skip to Main Content
  • Questions
  • Indexes when partitioning a table with alter table

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, raja.

Asked: September 09, 2020 - 4:53 am UTC

Last updated: September 09, 2020 - 10:41 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hello Tom,

If we partition a non-partitioned table using alter command, does indexes on non-partitioned table will also be partitioned are it will work in a different way?

Thanks in advance

and Chris said...

I'm guessing you mean 12.2s alter table ... modify partition command.

If so, use the update indexes clause to state whether they're local or global. For global indexes, you can use a different partitioning scheme to the base table:

create table t (
  c1 int, c2 int, c3 int
);

create index i1 on t ( c1 );
create index i2 on t ( c2 );
create index i3 on t ( c3 );

alter table t
  modify partition by range ( c1 ) 
  interval ( 100 ) (
    partition p0 values less than ( 101 )
  ) update indexes (
    i1 local,
    i2 global,
    i3 global 
      partition by hash ( c3 ) 
      partitions 4
  );
  
select index_name, partitioned 
from   user_indexes
where  table_name = 'T';

INDEX_NAME    PARTITIONED   
I1            YES            
I2            NO             
I3            YES 

select index_name, partition_name 
from   user_ind_partitions;

INDEX_NAME    PARTITION_NAME   
I1            P0                
I3            SYS_P2660         
I3            SYS_P2661         
I3            SYS_P2662         
I3            SYS_P2663         

Rating

  (1 rating)

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

Comments

on update indexes clause.

Rajeshwaran, Jeyabal, September 09, 2020 - 11:10 am UTC

It is also worth to look at the "update indexes" clause - which is an optional part of the ddl. incase if that is not mentioned, there are set of rules the database apply to convert existing index as either local (prefixed or non-prefixed) or global ones -

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/vldbg/evolve-nopartition-table.html#GUID-5FDB7D59-DD05-40E4-8AB4-AF82EA0D0FE5

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.