[SQL] Duplicated records Access<>Oracle

Hi,

I know it might be a many times question asked but I would like to find the way to get the list of records duplicated in an Oracle Table. The objective is to choose the good one to keep on the database by hands ...

In fact I have found a solution, looking tips on the net ... but the result is really slower than Access done ... And I'm very disapointed with that, because I have just done a small test : exported my oracle table in TXT format, imported in Access, and doing a Duplicated record found ... no index were specified in Access ...

The SQL command used by access is :
SELECT DISTINCTROW C.NAME, C.ZIP, C.NUMBER, C.FIRSTNAME, C.CITY FROM CLIENTS C
WHERE (((C.NAME) In (SELECT [NAME] FROM [CLIENTS] As Tmp GROUP BY [NAME],[ZIP] HAVING Count(*)>1 And [ZIP] = [C].[ZIP]))) ORDER BY C.NAME, C.ZIP;
(I changed the name of the table under Access by 'C' and add 'CLIENT C' to be clear)

With 29090 records access give me the result in 30/35 seconds ... With my Oracle command about 5 min ...

I tried to test the Access command with Oracle 8 but Oracle do like this SQL command ...

Do you have any idea to find in an Oracle Table records with same NAME, and ZIP, resulting in my preference a table with the 2 NUMBER fields of each records wich are duplicated ... AND really quick ... Or what can I do to make my oracle DataBase really quicker for this ... tunning of the database, tips ... any idea are welcome :)

Thanks per advance for your answers ... and sorry for my bad english ... french is my best language :) Received on Tue Oct 26 1999 - 14:00:06 CDT