rob@XE:11g2> set timing on

rob@XE:11g2> DROP TABLE test_dedup2;

Table dropped.

Elapsed: 00:00:02.46

 

rob@XE:11g2> CREATE TABLE test_dedup2 AS SELECT * FROM test_dedup1;

Table created.

Elapsed: 00:00:02.07

rob@XE:11g2> CREATE INDEX ix2_dedup_conc ON test_dedup2(alpha_code, num_code);

Index created.

Elapsed: 00:00:01.76

rob@XE:11g2> DELETE FROM test_dedup2

  2  WHERE ROWID IN

  3    (SELECT bad_clone FROM

  4       (SELECT

  5          ROWID AS bad_clone,

  6          ROW_NUMBER() OVER

  7            (PARTITION BY alpha_code||num_code ORDER BY alpha_code||num_code)

  8             AS sibling_id

  9        FROM test_dedup2

 10       )

 11     WHERE sibling_id > 1

 12    );

170261 rows deleted.

Elapsed: 00:00:14.39

rob@XE:11g2> SELECT COUNT(*), alpha_code||num_code

 2   FROM test_dedup2

 3   GROUP BY alpha_code||num_code

 4   HAVING COUNT(*) > 1;

no rows selected

Elapsed: 00:00:00.61

rob@XE:11g2> set timing off