rob@XE:11g2> set timing on

rob@XE:11g2> DROP TABLE test_dedup2;

Table dropped.

Elapsed: 00:00:02.34

 

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

Table created.

Elapsed: 00:00:01.92

rob@XE:11g2> CREATE INDEX ix2_dedup_alpha ON test_dedup2(alpha_code);

Index created.

Elapsed: 00:00:01.16

rob@XE:11g2> CREATE INDEX ix2_dedup_num ON test_dedup2(num_code);

Index created.

Elapsed: 00:00:01.26

rob@XE:11g2> DELETE FROM test_dedup2

  2  WHERE (alpha_code, num_code) IN

  3  (

  4    SELECT alpha_code, num_code

  5    FROM test_dedup2

  6    GROUP BY alpha_code, num_code

  7    HAVING COUNT(*) > 1

  8  )

  9  AND (timestamp, alpha_code, num_code) NOT IN

 10  (

 11    SELECT MAX(timestamp), alpha_code, num_code

 12    FROM test_dedup2

 13    GROUP BY alpha_code, num_code

 14    HAVING COUNT(*) > 1

 15  );

170261 rows deleted.

Elapsed: 00:13:02.90

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.59

rob@XE:11g2> set timing off