Re: de-dup process

Biggest problem we've faced in coming up with a solution is none of the solutions so far scale. In other words, things are fine if we have a 20 million row table with 2-3 million duplicates - runs in 10-15 minutes. However, trying it for 100+ million row table - it runs for hrs!    

  We've even had another tool (Informatica) select out the ROWIDs of the duplicates into a separate table then we are using PL/SQL cursor to delete those rows from the large table, but this doesn't scale either!    

  I'm currently looking at some of the suggestions I got from this list and seeing if any of them will work with us.   

Thanks for all who replied and I'll let you know how it goes!   

Mladen Gogala <mgogala_at_verizon.net> wrote:   

On 12/12/2006 08:42:38 PM, tboss_at_bossconsulting.com wrote:
> >From asktom, the best way I've found is to use Tom's little code snippet below:
>
> delete from table your_huge_table
> where rowid in
> (select rid
> from
> (select rowid rid,
> row_number() over
> (partition by varchar_that_defines_duplicates
> order by rowid ) rn
> from your_huge_table
> )
> where rn <> 1
> )
> /

Good luck with that if cardinality is +60M rows. I would also add a condition like WHERE ROWID in (SELECT ROW_ID from EXCEPTIONS) to your query. You only need to populate exceptions table with duplicates and if those duplicates are a small percentage of the total number of records, your task will be done two order of magnitude faster then without the exceptions table.

-- 
Mladen Gogala
http://www.mladen-gogala.com

--
http://www.freelists.org/webpage/oracle-l




 	
---------------------------------
Everyone is raving about the all-new Yahoo! Mail beta.
--
http://www.freelists.org/webpage/oracle-l