This discussion is archived
10 Replies Latest reply: Aug 16, 2013 4:42 AM by 84b09b47-0add-4854-b184-296b3903b009 RSS

Non Unique Index using a Non Unique field

84b09b47-0add-4854-b184-296b3903b009 Newbie
Currently Being Moderated

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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points