Oracle Data deduplication

first, the total data deduplication method

The idea is to first create a temporary table, and then insert the table data after distinct into the temporary table, then empty the original table data, and then the temporary table data inserted into the original table, and finally delete the temporary table.

The following SQL statements can be used for full deduplication in a table.

--code

CREATE table "#temp" as (selectdistinct * from table name); --Create a temporary table and insert the distinct data into the temporary table

TRUNCATE TABLE name; --Empty the original table data

INSERT into table name (SELECT * from "#temp"); --inserting temporary table data into the original table

DROP TABLE "#temp"; --Delete temporary table

Second, some data deduplication method

We can consider creating temporary tables that will need to be judged by repeating the fields, rowid inserting the temporary table, and then deleting the time in the comparison.

!== = B. field 2); commit;

Example: Ideas

1, according to the above thinking operation

2. Delete the formal table and insert the temporary table data into the formal table

--To filter duplicate data based on Max (A.ROWID), get a temporary table with no duplication of data
CREATE TABLE Temp Table Asselect A.ip,a.port,max (a.rowid) Dataid from Ipresulta GROUP by A.ip,a.port;
--delete duplicate data from the official table and keep only the latest data  ! == b.ip anda.port= b.port);
--Delete temporary tables and submit drop table temporary tables; commit;

‘

Citation information:

Data deduplication method in Oracle Database: Partial deduplication + full de-weight

Oracle Data deduplication