Team,
Was reading this content from this blog post
https://connor-mcdonald.com/2016/09/06/partitioning-an-existing-index one question: with the system generated unique index on PK, does the database drop that index while modifiying the constraint?
in the below demo, during the modify constraint -does the index T_PK is getting dropped? any reason - why it is not available after the modify constraint statement?
demo@ORA12C> create table t
2 as
3 select *
4 from all_objects;
Table created.
demo@ORA12C> alter table t add constraint t_pk
2 primary key(object_id);
Table altered.
demo@ORA12C> select index_name
2 from user_indexes
3 where table_name ='T';
INDEX_NAME
----------
T_PK
demo@ORA12C> create unique index t_idx2 on t(object_id)
2 global partition by hash(object_id)
3 ( partition p1,
4 partition p2 )
5 online invisible;
Index created.
demo@ORA12C> select index_name
2 from user_indexes
3 where table_name ='T';
INDEX_NAME
----------
T_PK
T_IDX2
demo@ORA12C> alter table t modify constraint t_pk using index t_idx2;
Table altered.
demo@ORA12C> select index_name
2 from user_indexes
3 where table_name ='T';
INDEX_NAME
----------
T_IDX2
demo@ORA12C> alter index t_pk invisible;
alter index t_pk invisible
*
ERROR at line 1:
ORA-01418: specified index does not exist
demo@ORA12C> alter index t_idx2 visible;
Index altered.
demo@ORA12C> drop index t_pk;
drop index t_pk
*
ERROR at line 1:
ORA-01418: specified index does not exist
demo@ORA12C>
July 03, 2018 - 12:35 am UTC
When you did this:
alter table t add constraint t_pk primary key(object_id);
you (in effect) never asked for an index to be created, you simply asked for a constraint. So in that circumstance, the index is "ours" not "yours". So when you modified the contraint to use the other index, the original index (which *you* never created anyway) is no longer required to implement the constraint.