One thing to watch with this is that it deletes all records that are duplicated whilst doing the delete using the exceptions table's row_id.
Cheers
Keith
-----Original Message-----
From: Ramekar Mahesh
[mailto:oracledba-ezmlmshield-x44406178.[Email address protected]
Sent: Tuesday, 2 August 2005 11:54 AM
To: LazyDBA Discussion
Subject: RE: Deleting duplicate rows !!!
Looking at the 3 mil records and 1.5 duplicates. I think following process may be efficient.
1. create exceptions table as below ( if table exists then truncate)
SQL> desc exceptions
Name Null? Type
----------------------------------------- -------- ----------------------------
ROW_ID ROWID
OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
CONSTRAINT VARCHAR2(30)
2. try to create a primary key as
alter table t2 add constraint pk primary key (n) exception into exceptions;
alter table t2 add constraint pk primary key (n) exception into exceptions
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
3. It creates a record of all exception causing rows... into exceptions table.
4. create temptable as select * from originaltable where rowid in ( select row_id from exceptions );
now temptable will have all duplicate rows.
5. delete from originalatble where rowid in ( select row_id from exceptions ); - no dup anymore.
6. create/enable primary key alter table t2 add constraint pk primary key (n) ;
6. delete duplicates rows of temptable as per previous mails ( create necessary indexes on temptable)
7. insert into originalatble select * from temptable;
8. drop temtable;
Just a thought...
-----Original Message-----
From: Patterson Joel [mailto:oracledba-ezmlmshield-x49908069.[Email address protected]
Sent: Friday, 29 July 2005 10:49 PM
To: LazyDBA Discussion
Subject: RE: Deleting duplicate rows !!!
All true. Including of course that by deleting both rows, you have indeed deleted the duplicate rows. It is just one solution of many, -- some better than others.
-----Original Message-----
From: vera mitaroff [mailto:oracledba-ezmlmshield-x10251854.[Email address protected]
Sent: Friday, July 29, 2005 7:00 AM
To: LazyDBA Discussion
Subject: AW: Deleting duplicate rows !!!
This is true. Thank's.
You can find double keys with 'having count(*)', but it is impossible to delete them. (incorrect syntax...) Following exaample is possible to execute:
-- Find double Keys:
select department_id, count(*) from dep2 group by department_id having count(*) > 1;
-- delete double key's
delete from dep2 dp1
where rowid > (select MIN(rowid) from dep2 dp2
where dp1.department_id = dp2.department_id);
:-))
Vera
-----Urspr|ngliche Nachricht-----
Von: Lloyd Ruskin
[mailto:oracledba-ezmlmshield-x40544413.[Email address protected]
Gesendet: Freitag, 29. Juli 2005 11:33
An: LazyDBA Discussion
Betreff: RE: Deleting duplicate rows !!!
I think this will delete both rows.
You need to use the pseudo column RWID and delete where ROWID>1
Lloyd
-----Original Message-----
From: vera mitaroff [mailto:oracledba-ezmlmshield-x74660649.[Email address protected]
Sent: 29 July 2005 10:28
To: LazyDBA Discussion
Subject: WG: Deleting duplicate rows !!!
You can find ( and delete) this rows with following queriy:
'Pos_sendungsnr' ist the unique - Key in this example:
-- Control:
select pos_sendungsnr from eu_faktura_header group by pos_sendungsnr having
count(*) > 1
-- Delete:
Delete from eu_faktura_header
group by pos_sendungsnr
having count(*) > 1
Good luck!
Vera
-----Urspr|ngliche Nachricht-----
Von: VGaddipati
[mailto:oracledba-ezmlmshield-x12795000.[Email address protected]
Gesendet: Freitag, 29. Juli 2005 11:09
An: LazyDBA Discussion
Betreff: Deleting duplicate rows !!!
Hi folks,
we have a huge table with 3 million rows. After an import (disabling the constraints on the table) we ended up having 4.5 million rows with lots of duplicate rows.
Is there any way to delete these duplicate rows from the table ?
Kind Regards,
Venugopal R.
Technical Architect (Database).
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com By using this list you agree to these terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com By using this list you agree to these terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com By using this list you agree to these terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page