On Dec 8, 2014, at 9:35 PM, Scott Marlowe wrote:
> select a,b,c into newtable from oldtable group by a,b,c;
>
> On pass, done.
This is a bit naive, but couldn't this approach potentially be faster (depending on the system)?
SELECT a, b, c INTO duplicate_records FROM ( SELECT a, b, c, count(*) AS counted FROM source_table GROUP BY a, b, c
)q_inner WHERE q_inner.counted > 1;
DELETE FROM source_table USING duplicate_records WHERE source_table.a = duplicate_records.a AND source_table.b =
duplicate_records.bAND source_table.c = duplicate_records.c;
It would require multiple full table scans, but it would minimize the writing to disk -- and isn't a 'read' operation
usuallymuch more efficient than a 'write' operation? If the duplicate checking is only done on a small subset of
columns,indexes could speed things up too.