Re: Q: Oracle sqlplus way to capture all duplications and removal.

colocoloc_at_yahoo.com (ColoC) wrote i

> I have got a data duplication case in a big Oracle 8 table. Many of
> the records were processed and loaded twice, meanwhile there are much
> more good records without duplication. The table has 60 fields for
> each record.
>
> Is there a simple Oracle sqlplus way to capture all these duplications
> and remove the duplications?

The simplest and cleanest and easiest to understand SQL (that does not deal with complexities of rowids, PKs and the like) is a to create a new table with unique rows. E.g.

CREATE TABLE no_dups
NOLOGGING AS
SELECT
  DISTINCT
  col1,
  col2,
  ..
  coln
FROM dups

Okay, this assumes that we're dealing with row duplication and not with duplicate keys where the rest of the row could be different. To solve that is a tad more complex and requires a means to identify which of the rows with the same PK contains the latest/correct version of column data.

BTW, you did not mention how big the table. If is that really big (10+ million rows and more), you may want to make sure that you do have enough space for creating the 2nd table. You may also want to use parallel query.

--
Billy