rob@XE:11g2> set timing on

rob@XE:11g2> DROP TABLE test_dedup2;

Table dropped.

Elapsed: 00:00:01.38

 

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

Table created.

Elapsed: 00:00:01.95

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

Index created.

Elapsed: 00:00:01.12

ROB@XE:11g2> DELETE FROM test_dedup2 X

 2  WHERE X.rowid <>

 3   (SELECT MAX(Y.rowid) FROM test_dedup2 Y

 4    WHERE Y.alpha_code = X.alpha_code

 5    AND Y.num_code = X.num_code);

170261 rows deleted.

Elapsed: 00:00:13.19

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

rob@XE:11g2> set timing off