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

Answered by: Connor McDonald - Last updated: October 15, 2020 - 4:45 am UTC

Category: Database - Version: 11g

Viewed 1000+ times

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 we 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.


and you rated our response

  (5 ratings)

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

Reviews

Thank you.

September 11, 2016 - 9:22 pm UTC

Reviewer: A reader from UK

Thanks a lot. Really interesting..

Your method works!

September 22, 2016 - 4:36 pm UTC

Reviewer: KHALID RAHIM from MD

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

ORA-01408: such column list already indexed

September 10, 2018 - 2:20 pm UTC

Reviewer: Andy Klock from Boston, MA USA

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

September 10, 2018 - 3:33 pm UTC

Reviewer: Andy Klock from Boston, MA USA

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

Followup  

September 11, 2018 - 3:04 pm UTC

:)

October 14, 2020 - 2:34 pm UTC

Reviewer: A reader

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

Followup  

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 to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.