Re: Newbie: Double Keys

pconnors on BIX (pconnors_at_BIX.com) wrote:
> bodumag_at_dial.eunet.ch (Jesper Clausen) writes:
 

> >Hello
 

> >Try to build uniq index on a table whith "create uniq index .....",
> >get the message "Double keys exist, can't build uniq index"
 

> >Now, How do i find these double records in my table with Oracle SQL-Plus
 

> Given table FOO, with unique index BAR, do the following:
 

> select A.BAR from FOO A
> where exists (select B.BAR from FOO B
> where A.BAR = B.BAR
> and A.ROWID <> B.ROWID)
 

> if your index is more than one column include all columns in the
> where clause of the subquery.
 

> NOTE: This query can potentially take a long time.

if you don't have any index at all on the primary key, the length of time this takes to run will be an exponential factor of the number of rows.

ie. a full table scan for each row in the table

• if your table has 1000 rows, that is potentially 1000 full table scans

So create your index as non-unique before you run this statement that way it can search the index to find the duplicates

• if your table has 1000 rows, only 1 full table scan is necessary.

--
 
John Blackburn                                          Phone: +61 7 2534634
jb2_at_qdot.qld.gov.au                                     Fax:   +61 7 8541194