RE: Duplicate rows

From: David Kurtz <info_at_go-faster.co.uk>
Date: Wed, 2 Jun 2004 12:50:33 +0100
Message-ID: <CKEAJBMGFEOCDBFILPJDIEJEDPAA.info@go-faster.co.uk>

Presumably you have a delete statement that looks something like

DELETE FROM <table> t1
WHERE EXISTS(

	SELECT 'x'
	FROM 	<table> t2
	WHERE t1.<key> = t2.<key>
	AND   t1.rowid < t2.rowid
	AND   rownum = 1)

;

A non-unique index on the key fields should improve the performace of this query.

How did the non-unique data get in there in the first place? Is there any condition on t1 that you could use to cut down the number of rows that the driving query will return? If your are trying to delete 5000 out of millions of rows then this isn't going to be quick either.

David Kurtz
Go-Faster Consultancy Ltd.
tel: +44 (0)7771 760660
fax: +44 (0)7092 348865
mailto:david.kurtz_at_go-faster.co.uk
web: www.go-faster.co.uk
PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Computer Centre - NIIPL
Sent: 02 June 2004 10:57
To: ORALCE LIST
Subject: Duplicate rows

Dear All,

One table is having more than 5000 duplicate record. We would like to remove all the duplicate records.

Using rowid method, it takes much time to execute.

Is there any other way to remove all the duplicate rows in a faster manner.

Regards,
Balu
This mail is scaned by eScan Anti Virus Software

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Please see the official ORACLE-L FAQ: http://www.orafaq.com

Received on Wed Jun 02 2004 - 06:47:59 CDT