Re: PK constraint and underlying index behaviour

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