1 Reply Latest reply on Oct 24, 2013 3:22 PM by Aasubram-Oracle

    Primary key index DDL Twice??!!

    Aasubram-Oracle

      Hello All,

       

      When selecting the dummy_table in the connections side panel in SQL Developer 3.2 and clicking on the SQL tab the primary key index DDL is generated twice - once in the table DDL and then a duplicate CREATE INDEX statement.

       

      Example script:

      create type dummy_type as object

        (

        id_num  integer

        );

      /

      create table dummy_table of dummy_type

        (

        constraint dummy_table_pk primary key ( id_num ) using index

        );

       

      SQL Developer Output with DUMMY_TABLE_PK defined twice:

       

        CREATE TABLE "AIMS"."DUMMY_TABLE" OF "AIMS"."DUMMY_TYPE"

         ( CONSTRAINT "DUMMY_TABLE_PK" PRIMARY KEY ("ID_NUM")

        USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255

        STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

        TABLESPACE "AIMS_DATA"  ENABLE

         ) OIDINDEX  ( PCTFREE 10 INITRANS 2 MAXTRANS 255

        STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

        TABLESPACE "AIMS_DATA" )

      PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

        STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

        TABLESPACE "AIMS_DATA" ;

       

        CREATE UNIQUE INDEX "AIMS"."DUMMY_TABLE_PK" ON "AIMS"."DUMMY_TABLE" ("ID_NUM")

        PCTFREE 10 INITRANS 2 MAXTRANS 255

        STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

        TABLESPACE "AIMS_DATA" ;

        CREATE UNIQUE INDEX "AIMS"."SYS_C0082553" ON "AIMS"."DUMMY_TABLE" ("SYS_NC_OID$")

        PCTFREE 10 INITRANS 2 MAXTRANS 255

        STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE

       

      Is this an expected behavior? Why do we see this? Thanks for your help in advance.