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 0779910035D14F for ; Fri, 8 Jul 2022 08:33:26 +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 00DAF4274D; Fri, 8 Jul 2022 06:33:25 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id E59C246D01; Fri, 8 Jul 2022 06:33:24 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1657262004; bh=3O2tmAOOPfx33qq4d5pCol/VesdPcuAE6JjFbbGjByU=; h=From:Sender:Sender:From; b=IvoVYfEe8eGwzWydvMle8llsL97L6/RHy41BG49TKC1LKkGU0aUuRMTUbxsLT2ERQ UqreIBpgT6hSpkJ4wvSKcljfzbKXQLnIzMpV0hOffw7WixG9OOrgHORTUoKdA8htRf AXp4qPihHIfXXbCSh4qma4B8ovQPBcI5zkHw8xPI= 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 YcaceLPp6hMF; Fri, 8 Jul 2022 06:33:24 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 932A746D02; Fri, 8 Jul 2022 06:33:19 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1657262001; bh=3O2tmAOOPfx33qq4d5pCol/VesdPcuAE6JjFbbGjByU=; h=From:Sender:Sender:From; b=jvwnyiirH6oAV+7YPwhK1tC4MV95xSRW/BsQYzFh/aDZmIL1U+CEEatlkMDPd8bUZ BxbdB+u4DqsIFyy8zJjdS5qM32aj2kJhaEQcSHWVsYijB9HxUXBJzBCFEX67XWl3GA aiclq57mDmWXJqgiQ37zRzIjRAV5BLBo/b6RVjcU= Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 08 Jul 2022 06:33:18 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id E31AD46CFF for ; Fri, 8 Jul 2022 06:33:17 +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=pI/JDgyX; 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 FKf7avvgmSSC for ; Fri, 8 Jul 2022 06:33:17 +0000 (UTC) Received: from mail-oa1-f54.google.com (mail-oa1-f54.google.com [209.85.160.54]) (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 D102446CFE for ; Fri, 8 Jul 2022 06:33:17 +0000 (UTC) Received: by mail-oa1-f54.google.com with SMTP id 586e51a60fabf-1013ecaf7e0so28097731fac.13 for ; Thu, 07 Jul 2022 23:33:17 -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:references:in-reply-to:from:date :message-id:subject:to; bh=zlSFpVXTTGv76M4LjJkyzPGN7JHBu63/UcBxzOG/nhI=; b=WJ5QNsP54WfbtPE1tZzJmJHDiVsTAb0I3FyLAS72y9402wSGkDfA3DuttVgvjeJJYq ZP/UdKQX9N18gG0LDpdtBlMLGJ3tC6tu+94wQdE84XoIxDUQ1RCtdjD890dzc5FC01sb c8pP6lPRLDFFwOCezpfhckHd5p/07bOCZb8eHl9bz9w0C/wnPZkRANE6ESWyo3y8Bq2C ffi6mlaHNtZp6weNW7gKkfQXR/eiA6DcH34YPViRHVMErwl+uvWB4YU0pCALVbAmEfuO YBiuVtsndZuHVqC01t4vuMfDEsVfddG377U5QYzL3clEMGnwMGEgaK26h9sk3fbmhGP3 9POA== X-Gm-Message-State: AJIora8E6YLQdDjuLiO2hleYBzm9OImStzJUiMO8E907YAshBb9FlxfK WyBsfU8BChte7dAsu0HRwyMRtRH1dTCpMj4imGlg7H3TsKxMJw== X-Google-Smtp-Source: AGRyM1vUIo//XwwTiI7xCFR+fgzn6fPX7+s8CH9bB1S0t9KXM6yJvmdbtJrsxii7FMt9uTFND5yZUVPrJQG5wvZy6zI= X-Received: by 2002:a05:6870:910d:b0:10c:ab8:bbcb with SMTP id o13-20020a056870910d00b0010c0ab8bbcbmr5050616oae.226.1657261997188; Thu, 07 Jul 2022 23:33:17 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Jonathan Lewis Date: Fri, 8 Jul 2022 07:33:05 +0100 Message-ID: Subject: Re: PK constraint and underlying index behaviour To: Oracle L Content-Type: multipart/alternative; boundary="000000000000a4695805e3456055" X-archive-position: 82601 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: jlewisoracle@gmail.com Precedence: normal Reply-To: jlewisoracle@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 --000000000000a4695805e3456055 Content-Type: text/plain; charset="UTF-8" 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 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, 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 >> >> >> >> >> >> >> --000000000000a4695805e3456055 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

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.de= veloper35@gmail.com> wrote:
Creat= ing index and constraint separately outside create table statement is worki= ng fine.

I was stumped about the beha= viour while creating constraint as part of create table statement only and = then disabling+re-enabling the constraint making the index global . So want= ed to check if it's expected or some specific settings making this happ= en?

On Thu, 7 Jul 2022, 9:47 pm Pap, <oracle.developer35@gmail.com> wrote:
Hello, I am seeing some odd behavior. Its = version 19C of Oracle.=C2=A0

If we create primary key constraint as part of create table state= ment then a primary key index(which is local) gets created automatically be= hind the scene which is okay.

But when we disable that constraint, the underlying index gets droppe= d automatically. And the most odd thing is when , we re-enable the constrai= nt, it again re-create the underlying index but not as local , its rather= =C2=A0 created as a global index. Is this expected behavior?


---Creati= ng a table

CREATE TABLE = SCOTT.PART_TAB

( PART_DA= TE DATE, EID NUMBER(10),

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

PARTITION BY RANGE (PAR= T_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">




















--000000000000a4695805e3456055-- -- http://www.freelists.org/webpage/oracle-l