Re: PK constraint and underlying index behaviour

Not the first time someone has been bitten by this either: http://evdbt.com/singing-the-nocoug-blues/

On 7/8/2022 2:02 AM, Pap wrote:
> Thank you Jonathan.
> So basically if the automatic index is unique it gets attached to the
> constraints by Oracle internally and thus will be dropped along with
> it. Non unique ones stays intact.
> Thank you.
>
> On Fri, 8 Jul 2022, 12:03 pm Jonathan Lewis, <jlewisoracle_at_gmail.com>
> wrote:
>
>
> 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