Skip to Main Content
  • Questions

  • Hash partition the index on existing primary key column

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, kumaresan.

Asked: August 30, 2016 - 10:24 pm UTC

Last updated: October 15, 2020 - 4:45 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi,

I have a query on the hash partitioning the index, and below are the details.

There is a table in production with 60 million records and it has got a event_id as primary key.

I am planning to hash partition this column and as we aware oracle won't allow to create another index when the primary key column has got its own local index.

In this case, if I need to create a hash partition with 64 partitions then should I need to remove the primary key index and apply the hash partition index DDL statement? If yes, the column won't be a primary key anymore right?


I am new to this index level concept, could you please advise me on this?


Regards,
Kumar.



Hi Conner,

Please find the details.
is the current *table* partitioned ? - No, it is not partitioned
if so, on what column and in what way (range/list/hash) - NA
is the current *primary key index* partitioned ? - No, it is not partitioned. I am planning to partition the primary key index
if so, is it range/hash/list, and is it global or local ? -- NA
And ... what is your motivation for hash partitioning ? What problem are you aiming to solve ? --> To evenly distribute concurrent DML across multiple partitions.

Kumar.

and Connor said...

An example of what you could do here is:

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

Table created.

SQL>
SQL> create unique index IX on T ( object_id );

Index created.

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

Table altered.

--
-- now disable the constraint in order to change the index
--
SQL>
SQL> alter table T modify constraint T_PK disable drop index;

Table altered.

SQL>
SQL> create unique index IX2 on T ( object_id )
  2  global partition by hash (object_id )
  3  partitions 8;

Index created.

SQL>
SQL> alter table T modify constraint T_PK enable;

Table altered.



On 12c, you can do this with minimal disruption because you can have multiple indexes on the same columns, etc

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

Table created.

SQL>
SQL> create unique index IX on T ( object_id );

Index created.

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

Table altered.

SQL>
SQL> create index IX2 on T ( object_id )
  2  global partition by hash (object_id )
  3  partitions 8
  4  invisible;

Index created.

SQL>
SQL> alter index IX invisible;

Index altered.

SQL>
SQL> alter index IX2 visible;

Index altered.

SQL>
SQL> alter table T modify constraint T_PK using index IX2;

Table altered.

SQL>
SQL> drop index ix;

Index dropped.


Rating

  (5 ratings)

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

Comments

Thank you.

A reader, September 11, 2016 - 9:22 pm UTC

Thanks a lot. Really interesting..

Your method works!

KHALID RAHIM, September 22, 2016 - 4:36 pm UTC

I had a similar issue as Kumar, Your method worked for me.
kr

ORA-01408: such column list already indexed

Andy Klock, September 10, 2018 - 2:20 pm UTC

Hi TOMs, I really like this idea a lot. But, getting an ORA-01408. What am I doing wrong? Thanks!

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0
PL/SQL Release 12.2.0.1.0 - Production                                                    0
CORE    12.2.0.1.0      Production                                                                0
TNS for Linux: Version 12.2.0.1.0 - Production                                            0
NLSRTL Version 12.2.0.1.0 - Production                                                    0

SQL> create table t1 (id number);

Table created.

SQL> create unique index t1_uniq on t1 (id);

Index created.

SQL> alter table t1 add constraint t1_pk primary key (id);

Table altered.

SQL> create unique index t1_ind_hash on t1 (id) global partition by hash (id) partitions 2;
create unique index t1_ind_hash on t1 (id) global partition by hash (id) partitions 2
                                       *
ERROR at line 1:
ORA-01408: such column list already indexed


RE: ORA-01408: such column list already indexed

Andy Klock, September 10, 2018 - 3:33 pm UTC

Nevermind. I'd forgotten the INVISIBLE keyword :) 

SQL>  create unique index t1_ind_hash on t1 (id) global partition by hash (id) partitions 2 invisible;

Index created.


As always, thanks for all the help!
Chris Saxon
September 11, 2018 - 3:04 pm UTC

:)

A reader, October 14, 2020 - 2:34 pm UTC

Hi,

What if below conditions met?

is the current *table* partitioned ? - yes, it is partitioned
if so, on what column and in what way (range/list/hash) - event_id (PK of the table) hash partitioned
is the current *primary key index* partitioned ? - No, it is not partitioned. I am planning to partition the primary key index
Connor McDonald
October 15, 2020 - 4:45 am UTC 

SQL> create table t
  2  partition by hash (object_id)
  3  partitions 4
  4  as select * from dba_objects
  5  where object_id is not null;

Table created.

SQL>
SQL> alter table t add constraint pk primary key ( object_id );

Table altered.

SQL> create index new_ix on t ( object_id ) local invisible;

Index created.

SQL> alter table t disable constraint pk drop index;

Table altered.

SQL> alter index new_ix visible;

Index altered.

SQL> alter table t enable constraint pk;

Table altered.

SQL> select index_name
  2  from user_indexes
  3  where table_name = 'T';

INDEX_NAME
------------------------------
NEW_IX

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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.