Oracle Database Discussions

Hi, all.

The db is 11.2.0.3 on a linux machine.

There is an "UNIQUE" index "NEW_HIS3_PK" (seq, trigger_date).
Both seq and trigger_date have NOT NULL constraints.

I issued the following command. 

ALTER TABLE HOHO.NEW_HIS3 ADD (
  CONSTRAINT NEW_HIS3_PK PRIMARY KEY (SEQ, TRIGGER_DATE) USING INDEX NEW_HIS3_PK);

And, I saw a lot of db file scattered read on obj#=800108 (table).

*** 2012-11-28 15:06:03.658
WAIT #47385252424096: nam='db file scattered read' ela= 53422 file#=6 block#=9715972 blocks=124 obj#=800108 tim=1354082763658581
WAIT #47385252424096: nam='db file scattered read' ela= 11463 file#=6 block#=9749892 blocks=128 obj#=800108 tim=1354082763672781

Adding primary key constraint does table full scan in stead of using "the existing unique index"

Is this normal?

I think that adding pk constraint should use the existing unque index instead of full table scan.

And what does it mean ,"USING INDEX NEW_HIS3_PK" clause?

Thanks in advance.
Best Regards.

Edited by: 869578 on 2012. 11. 27 오후 11:56