Learning is not a spectator sport

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
online
invisible;

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;