Re: PK constraint and underlying index behaviour

It's expected Behaviour.

The note at https://jonathanlewis.wordpress.com/2012/04/19/drop-constraint/ doesn't address this directly, but gives you some important background. Do read the comments as well.

Regards
Jonathan Lewis

On Fri, 8 Jul 2022 at 06:15, Pap <oracle.developer35_at_gmail.com> wrote:

> Creating index and constraint separately outside create table statement is
> working fine.
>
> I was stumped about the behaviour while creating constraint as part of
> create table statement only and then disabling+re-enabling the constraint
> making the index global . So wanted to check if it's expected or some
> specific settings making this happen?
>
> On Thu, 7 Jul 2022, 9:47 pm Pap, <oracle.developer35_at_gmail.com> wrote:
>
>> 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