高效删除Oracle中重复数据的方法(国外英语资料).docVIP

高效删除Oracle中重复数据的方法(国外英语资料)Method for efficiently deleting duplicate data in OracleIn the operation of the database in the process we may encounter this kind of situation, the data in the table may be repeated, so we bring a lot of inconvenience to the operation of the database in the process, so how to delete these useless duplicate data?Deduplication can provide more backup capacity, achieve longer data retention, but also can realize the continuous verification of data backup, data recovery to improve service levels, to facilitate the realization of data recovery etc.. Duplicate data may have two cases: the first table only has some fields, the second is the two line, the record is exactly the same. Oracle database deduplication technology has the following advantages: greater backup capacity, data can be sustained verification, a higher level of data recovery services, and easy to achieve backup data disaster tolerance.Delete some fields and repeat the dataLets talk about how to query the duplicated data first.The following statement can query that the data is repeated:Select field 1, field 2, count (*) from, table name group, by field 1, field 2, having, count (*) 1Change the number to =, you can query the data without duplication.To delete these duplicate data, you can use the following statement to deleteDelete from table name a, where field 1, field 2 in(select field 1, field 2, count (*) from, table name group, by field 1, field 2, having, count (*) 1)The above statement is very simple, that is, the data you want to delete is deleted. However, the efficiency of such deletion execution is very low, and for large data volumes, the database may be hung. So I suggest inserting the duplicate data from the query into a temporary table, and then deleting it, so you dont have to do a query when youre deleting. As follows:CREATE TABLE temporary table AS(select field 1, field 2, count (*) from, table name group, by field 1, field 2, having, count (*) 1)The above s