PK constraint and underlying index behaviour

From: Pap <oracle.developer35_at_gmail.com>
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';
no rows selected

  • 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-l
Received on Thu Jul 07 2022 - 18:17:51 CEST

Original text of this message