10 Replies Latest reply: Aug 16, 2013 6:42 AM by 84b09b47-0add-4854-b184-296b3903b009 RSS

    Non Unique Index using a Non Unique field

    84b09b47-0add-4854-b184-296b3903b009

      Hi All,

       

      Using Oracle 11g, below is the table, partitions, unique and non-unique local index:

       

      CREATE TABLE DOCA

      (

        DOCA_ID  NUMBER  NOT NULL ,

        DOCA_BKG_PAX_ID  NUMBER  NULL ,

        ROW_PURGE_DATE  DATE  NULL ,

      )

      PARTITION BY RANGE(ROW_PURGE_DATE)

      INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))

      (

        PARTITION P2007 VALUES LESS THAN (TO_DATE('01/01/2008', 'dd/mm/yyyy')),

        PARTITION P200801 VALUES LESS THAN (TO_DATE('01/02/2008', 'dd/mm/yyyy')),

      )

        TABLESPACE T0;

       

      ALTER TABLE DOCA ENABLE ROW MOVEMENT;

       

       

       

       

      CREATE UNIQUE INDEX XPKDOCA ON DOCA

      (

        DOCA_ID  ASC,

        ROW_PURGE_DATE ASC

      )

      LOCAL

      REVERSE

      TABLESPACE I0;

       

       

      ALTER TABLE DOCA

        ADD CONSTRAINT  XPKDOCA PRIMARY KEY (DOCA_ID);

       

       

       

       

      CREATE INDEX XFKDOCA_DOCA_BKG_PAX_ID ON DOCA

      (

        DOCA_BKG_PAX_ID  ASC

      )

      LOCAL

      REVERSE

      TABLESPACE I0;

       

       

      I would like to know the difference between the performance of the unique and non-unique local indexes? Please guide.

       

      Thanks.