Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from smtp-aa.freelists.org (smtp-aa.freelists.org [23.23.80.81]) by malta2546.startdedicated.com (Postfix) with ESMTPS id 3FE051002BF81C for ; Thu, 7 Jul 2022 18:18:13 +0200 (CEST) Received: from turing.freelists.org (turing.freelists.org [10.0.0.164]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits)) (No client certificate requested) by smtp-aa.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 7D94B3F9EE; Thu, 7 Jul 2022 16:18:11 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 5CE773FADE; Thu, 7 Jul 2022 16:18:11 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1657210691; bh=lIBRM5gpplRugUPMwEVL+Nr9z0SNs/kA3FBqzgvxWsU=; h=From:Sender:Sender:From; b=Dj+aOZduOcF1IUmD++Y6j2iCsN33EjJQsi7WWmAqxVCJkjZLCrgp3//cFqOgnoeSD tG4usd2VTJtTKLjBtwEp5mIJayQVc/2P1jkvYRe0kf141u75D+XoSH1X381H2P9Xdc UeMwna27jaRaLjuXQaP4hhhu7mjMX1Z+3Bq2cktw= X-Virus-Scanned: by FreeLists at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id JwYvtbQEZoqQ; Thu, 7 Jul 2022 16:18:11 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id DFA873FAE2; Thu, 7 Jul 2022 16:18:05 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1657210688; bh=lIBRM5gpplRugUPMwEVL+Nr9z0SNs/kA3FBqzgvxWsU=; h=From:Sender:Sender:From; b=rg0/RX24NWmdGinso6UbtG8F3HI7iKnTYQaKCDU49GITzFCjxKmHFia4Fva4YpRxB WunqVm6R6mrF0m0Ztijf3C+ol/t2VulivD4M8zigh5epH4AlkysQQm/lpO6+NLSxG+ /i8vafYTDjey9pag5TRSQj5k736Bn1PrhyVCRMH0= Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 07 Jul 2022 16:18:04 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 252A33FADD for ; Thu, 7 Jul 2022 16:18:04 +0000 (UTC) Authentication-Results: turing.freelists.org; dkim=pass (2048-bit key; unprotected) header.d=gmail.com header.i=@gmail.com header.a=rsa-sha256 header.s=20210112 header.b=eTgMhBr3; dkim-atps=neutral Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id mcn_RRhxFywt for ; Thu, 7 Jul 2022 16:18:04 +0000 (UTC) Received: from mail-oa1-f53.google.com (mail-oa1-f53.google.com [209.85.160.53]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) by turing.freelists.org (Postfix) with ESMTPS id 186883FA19 for ; Thu, 7 Jul 2022 16:18:04 +0000 (UTC) Received: by mail-oa1-f53.google.com with SMTP id 586e51a60fabf-10c0052da61so15129742fac.12 for ; Thu, 07 Jul 2022 09:18:04 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:from:date:message-id:subject:to; bh=RPr44zoy6x8u8YQYAUyZwho3y7MCFqwwS546RFWJO0k=; b=XtIkr/0kt+SItVWRsqjsd6rPbrTDsFWtKLWU65ibh5nSfrPGbfnxo3ZqfvdLZZWXT8 Kuxt7Uq7IFr5Onkm8n48l0t7h0ZESlDsXqyzW2j/tC4kYbaE5Psev7IkKenKtmuyZ8OO XSNjykCJKmECrIlMczCfPm2gNBko+uhXooX/7Sh0NP3QfrYqj8U3QjHOB0T1jTRAkaf+ psUXIQivElvZ1AMQSsrYlGxyyPtOScxHY94kkHLvZtKX6q0MMk8Gb2X10oB3Ua8CWwTQ m2yD9996TRPw3FCT6zAbpRFvOnHKfeMderwov6+WB+aLaroLU5uO8JJc3ivLyVJ2KJpt qdMA== X-Gm-Message-State: AJIora/vPQcuA+2vWtVDp6XSOQON0wDnN/+vs3vjh4SespqNVB2hyKjZ KsyhLwNlag1ZOs5NMTjbvASsFzoN1F6vIfkEz7aW8ngR X-Google-Smtp-Source: AGRyM1vbKaySlfaIX1JRGAH1SIHHDPzqBog3uNNux5GX1BD233QvH8yUEmQfW6QsTkaRC0Wyyvfo+NySE6anJolgU/s= X-Received: by 2002:a05:6870:b414:b0:10b:8204:7e95 with SMTP id x20-20020a056870b41400b0010b82047e95mr3266874oap.88.1657210683197; Thu, 07 Jul 2022 09:18:03 -0700 (PDT) MIME-Version: 1.0 From: Pap Date: Thu, 7 Jul 2022 21:47:51 +0530 Message-ID: Subject: PK constraint and underlying index behaviour To: Oracle L Content-Type: multipart/alternative; boundary="000000000000170e0b05e3396e0a" X-archive-position: 82599 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: oracle.developer35@gmail.com Precedence: normal Reply-To: oracle.developer35@gmail.com List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-Subscribe: List-Owner: List-post: List-Archive: X-list: oracle-l --000000000000170e0b05e3396e0a Content-Type: text/plain; charset="UTF-8" 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 --000000000000170e0b05e3396e0a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hello, I am seeing some odd behavior. I= ts version 19C of Oracle.=C2=A0

If we create primary key constraint as part of create table sta= tement 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 dr= opped automatically. And the most odd thing is when , we re-enable the cons= traint, it again re-create the underlying index but not as local , its rath= er=C2=A0 created as a global index. Is this expected behavior?


---Creat= ing a table

CREATE TABLE= SCOTT.PART_TAB

( PART_D= ATE DATE, EID NUMBER(10),

CONSTRAINT PART_TAB_IND PRIMARY KEY (EID, PART_DATE) USING INDEX LOCAL= )

PARTITION BY RANGE (PA= RT_DATE)

(

=C2=A0PARTITION DAY_11_JUL VALUES LES= S THAN (TO_DATE(' 2019-07-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS= ', 'NLS_CALENDAR=3DGREGORIAN')),

=C2=A0PARTITION DAY_12_JUL VALUES LESS THAN (TO_DATE(&= #39; 2019-07-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C= ALENDAR=3DGREGORIAN')),

=C2=A0 PARTITION p_today VALUES LESS THAN (TO_DATE('2020-11-25 00:0= 0:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=3DGREGORIAN&= #39;))

);

Table created.

----Checking Index status ----------

SQL> select index_name, partit= ioned from user_indexes where index_name =3D 'PART_TAB_IND';
<= div dir=3D"auto">
INDEX_NAME = PARTITIONED

--------------------------------- = -------------------------------
PART_TAB_IND = YES


----- Disabling the co= nstraint----------

SQL&g= t; alter table PART_TAB DISABLE CONSTRAINT PART_TAB_IND;

Table altered.

=
----Index status ----------
=
SQL> select index_name, partitioned from use= r_indexes where index_name =3D 'PART_TAB_IND';

no rows selected

=
----- Enabling the constraint----------

SQL> alter table PART_TAB ENABLE= CONSTRAINT PART_TAB_IND;

Table altered.

----Che= cking Index status=C2=A0

SQL> select index_name, partitioned from user_indexes

=C2=A02 where index_name =3D 'PART_T= AB_IND';

INDEX_NAME = PARTITIONED

--------------------------------= - -------------------------------

PART_TAB_IND = NO






--000000000000170e0b05e3396e0a-- -- http://www.freelists.org/webpage/oracle-l