Partitioning an existing index

Posted by

I had a question on AskTom recently, where due to concurrency issues, the poster of the question wanted to take their existing primary key index and hash partition it to reduce the “hotness” of the leading leaf block(s).  Their database was 11g, but I thought it would worth showing off some 12c features that would let you do this process with minimal disruption.

First we’ll create our table, with a standard primary key index

drop table t purge;

create table t as select * from dba_objects
where object_id is not null;

create unique index IX on T ( object_id );

alter table T add constraint T_PK primary key ( object_id );

Now the task is to partition the index. In 12c, you can have multiple indexes defined on the same column(s), as long as only one of those indexes is visible. So we’ll create our new partitioned index invisible in online mode.

create index IX2 on T ( object_id )
global partition by hash (object_id )
partitions 8

And here is a neat little piece of syntax. We can take an existing constraint that needs to be underpinned with an index, and tell it to use an alternate index.

alter table T modify constraint T_PK using index IX2;

Now I can flip over the visibility of the indexes – I’ve still got my old index there as a fallback should my partitioning strategy have some sort of unexpected conseqeuences.

alter index IX invisible;

alter index IX2 visible;

And finally, at some appropriate point, I can drop the old index.

drop index ix;

One comment

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.