How to de-duplicate millions of records in oracle?

Question

I have a table (with one column called Id) and it has about 10 millions records. Some of these Ids are duplicate and I need to transfer unique Ids into another table (which will also have same one column). I am looking for a sql which can do it and quickest of performance.

Does this answer your question? [How to select unique records by SQL](https://stackoverflow.com/questions/1641718/how-to-select-unique-records-by-sql) — Joe, Oct 13 '21 at 12:34

score 0 · Answer 1 · answered Sep 30 '21 at 03:34

10million won't be an issue for the database. Here's an example of that just on my laptop

SQL> create table t as
  2  select mod(rownum,10000000) x
  3  from
  4   ( select 1 from dual connect by level <= 11000 ),
  5   ( select 1 from dual connect by level <= 1100 );

Table created.

SQL>
SQL> select count(*) from t;

  COUNT(*)
----------
  12100000

SQL> set timing on
SQL> create table t1
  2  as select distinct x from t;

Table created.

Elapsed: 00:00:03.96
SQL> select count(*) from t1;

  COUNT(*)
----------
  10000000

Elapsed: 00:00:00.23

So expect around 3-4 seconds to get the unique list.

How to de-duplicate millions of records in oracle?

1 Answers1