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

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question, raja.

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

Answered by: Chris Saxon - Last updated: September 09, 2020 - 10:41 am UTC

Category: Database Development - Version: 12c

Viewed 100+ 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 we 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         

and you rated our response

  (1 rating)

Reviews

on update indexes clause.

September 09, 2020 - 11:10 am UTC

Reviewer: Rajeshwaran, Jeyabal

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.