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!
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
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