Re: Newbie: Double Keys

John Blackburn (jb2_at_qdot.qld.gov.au) wrote:
: 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

--
ASSUMING ORACLE7,

Wouldn't it be easier to run the utlexcpt.sql script (found in VAX
ora_rdbms subdirectory or UNIX $ORACLE_HOME/rdbms/admin subdirectory)?

This creates a table called 
EXCEPTIONS
Name                      Null? Type
------------------------- ----- ----
ROW_ID                          ROWID
OWNER                           VARCHAR2(30)
TABLE_NAME                      VARCHAR2(30)
CONSTRAINT                      VARCHAR2(30)

Then

ALTER TABLE FOO ADD CONSTRAINT XYZ UNIQUE (BAR)
EXCEPTIONS INTO EXCEPTIONS;

The constraint cannot be created because there are duplicate values. Those 
rows in violation are written to the EXCEPTION table

Now

SELECT * 
FROM FOO WHERE ROWID IN (SELECT ROW_ID FROM EXCEPTIONS);

You can now make all your corrections to the FOO table and try again.

Be sure to drop and recreate the exceptions table or at least truncate it 
before running another such command!!

Murray Kaiser                  | Usual gutless disclaimer.. 
Nova Scotia Power              | Opinions are mine and not necessarily
(Murray.Kaiser_at_NSPower.NS.CA)  | shared by my employer