PK constraint and underlying index behaviour
Date: Thu, 7 Jul 2022 21:47:51 +0530
Message-ID: <CAEjw_fh4RKzVXiYUCz+=ShDv6bHPzaEW0xzbuVe1PO-S-pg5YA_at_mail.gmail.com>
Hello, I am seeing some odd behavior. Its version 19C of Oracle.
If we create primary key constraint as part of create table statement then a primary key index(which is local) gets created automatically behind the scene which is okay.
But when we disable that constraint, the underlying index gets dropped automatically. And the most odd thing is when , we re-enable the constraint, it again re-create the underlying index but not as local , its rather created as a global index. Is this expected behavior?
---Creating a table
CREATE TABLE SCOTT.PART_TAB ( PART_DATE DATE, EID NUMBER(10), CONSTRAINT PART_TAB_IND PRIMARY KEY (EID, PART_DATE) USING INDEX LOCAL) PARTITION BY RANGE (PART_DATE) (
PARTITION DAY_11_JUL VALUES LESS THAN (TO_DATE(' 2019-07-12 00:00:00',
'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION DAY_12_JUL VALUES LESS THAN (TO_DATE(' 2019-07-13 00:00:00',
'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION p_today VALUES LESS THAN (TO_DATE('2020-11-25 00:00:00',
'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
);
Table created.
----Checking Index status ----------
SQL> select index_name, partitioned from user_indexes where index_name =
'PART_TAB_IND';
INDEX_NAME PARTITIONED
- -------------------------------
PART_TAB_IND YES
- Disabling the constraint----------
SQL> alter table PART_TAB DISABLE CONSTRAINT PART_TAB_IND;
Table altered.
----Index status ----------
SQL> select index_name, partitioned from user_indexes where index_name =
'PART_TAB_IND';
- Enabling the constraint----------
SQL> alter table PART_TAB ENABLE CONSTRAINT PART_TAB_IND;
Table altered.
----Checking Index status
SQL> select index_name, partitioned from user_indexes
2 where index_name = 'PART_TAB_IND';
INDEX_NAME PARTITIONED
- -------------------------------
PART_TAB_IND NO
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jul 07 2022 - 18:17:51 CEST
