如何删除数据库中的冗余数据（翻译）
作为数据库的开发者，我们经常面临着要找出及删除数据库中冗余数据的任务，如果数据库中有大量的冗余数据（占总数的百分比太多），数据的精确性和可靠性将受到影响，同时也影响着数据库的性能，那么如何解决这个问题呢？下面我将探讨关于这个问题的这个解决方案，oracle也为我们提供了一个解决方案，但是Ｏｒａｃｌｅ提供的解决方案不够完美，遇到大批量数据那个解决方案工作起来很慢
应该怎么删除冗余数据呢？在这里我们应用一个ＰＬ／ＳＱｌ方案（一个自定义的存储过程）或者一个ＳＱＬ语句的解决方案（使用一个分析的函数ＲＡＮＫ（）和一个嵌套的子查询）来消除冗余数据然后控制应该保留的记录
什么是冗余数据？冗余数据就是一个数据表中，这个表中的行包含了一些相同的值，这些值理论上来说应该是唯一的（这些值一般来说能确定一条记录）例如，像社会保险号，姓与名的集合．那么我们把这么含有相同信息的行中包含的数据叫做冗余数据，现在所有的数据库表中都有主键约束，主键中记录了一行记录中的唯一值，从数据库的角度来看，每一行都是唯一的，但是从我们用户角度看来，这些记录都是相同的记录，因为它们都包含相同的键值（Ｆｉｒｓｔ Ｎａｍｅ ＋ Ｌａｓｔ Ｎａｍｅ），即使他们有不同的主键IDLast Name;;;;First Name City;Phone---- --------------- ---------- --------------- ----------1005 Krieger Jeff;;;;San Ramon;;;;92529971001012 Krieger Jeff;;;;San Ramon;;;;92529971001017 Krieger Jeff;;;;San Ramon;;;;9252997100
那么这些冗余数据是怎么出现的那？通常有两种情况：１.从不同的表中加载或者合并数据
通过图形化的用户接口来输入数据，然后由计算机来生成一个唯一的键，并做为这一条记录的主键
那么怎样找到冗余数据呢？让我们来创建一个叫作Ｃｕｓｔｏｍｅｒ 的表并向其中加入冗余数据，看表１，正如你所看到的，我们并没有在这个表上做什么限制来防止冗余数据，下面这么代码创建了一个唯一约束，来防止冗余数据的生成SQLListing 1. 创建Ｃｕｓｔｏｍｅｒ表这个表中我们故意加入了冗余数据DROP TABLE Customers CASCADE CONSTRAINTS;
CREATE TABLE Customers( Id INTEGER NOT NULL, LastName VARCHAR2(15) NOT NULL, FirstName VARCHAR2(10), Address VARCHAR2(20), City VARCHAR2(15), State CHAR(2), Zip VARCHAR2(10), Phone VARCHAR2(10), RecDate DATE, CONSTRAINT Customers_PK PRIMARY KEY (ID)) TABLESPACE TALLYDATA;
INSERT INTO Customers VALUES (1000, 'Bradley', 'Tom', '2450 3rd Str. #12', 'San Francisco', 'CA', '94114', '4156679230', TO_DATE('01-JAN-2000', 'DD-MON-YYYY'));
INSERT INTO Customers VALUES (1001, 'Stone', 'Tony', '12 Main St. Apt. 3', 'Oakland', 'CA', '94342', '5104562881', TO_DATE('12-MAR-2001', 'DD-MON-YYYY'));
INSERT INTO Customers VALUES (1002, 'Chang', 'Jim', '425 26th Ave.', 'Seattle', 'WA', '98103', '8182454400', TO_DATE('15-JUN-2000', 'DD-MON-YYYY'));
INSERT INTO Customers VALUES (1003, 'Loney', 'Julie', '12 Keith St.', 'Castro Valley', 'CA', '94546', '5103300721', TO_DATE('22-NOV-2000', 'DD-MON-YYYY'));
INSERT INTO Customers VALUES (1004, 'King', 'Chuck', '100 Broadway St.', 'Pleasant Hill', 'CA', '95114', '9254247701', TO_DATE('14-APR-2001', 'DD-MON-YYYY'));
INSERT INTO Customers VALUES (1005, 'Krieger', 'Jeff', '120 Mercury Rd.', 'San Ramon', 'CA', '95114', '9252997100', TO_DATE('02-FEB-2001', 'DD-MON-YYYY'));
INSERT INTO Customers VALUES (1006, 'Monroe', 'John', '122 West Ave.', 'Phoenix', 'AZ', '85004', '9252997100', TO_DATE('02-OCT-2000', 'DD-MON-YYYY'));
INSERT INTO Customers VALUES (1007, 'Lord', 'Don', '573 Hill View', 'Atlanta', 'GA', '30303', '3036578900', TO_DATE('12-DEC-2000', 'DD-MON-YYYY'));
INSERT INTO Customers VALUES (1008, 'Griffith', 'David', '10 Fulton Rd. Apt.4', 'San Francisco', 'CA', '94121', '7236578900', TO_DATE('15-DEC-1999', 'DD-MON-YYYY'));
INSERT INTO Customers VALUES (1009, 'Simon', 'Michael', '573 Hill View', 'Santa Monica', 'CA', '90402', '8185689200', TO_DATE('22-MAY-1999', 'DD-MON-YYYY'));
INSERT INTO Customers VALUES (1010, 'Simon', 'Michael', '573 Hill View', 'Santa Monica', 'CA', '90402', '8185689200', TO_DATE('02-OCT-1999', 'DD-MON-YYYY'));
INSERT INTO Customers VALUES (1011, 'Stone', 'Tony', '12 Main St. Apt. 3', 'Oakland', 'CA', '94342', '5104562881', TO_DATE('07-DEC-1999', 'DD-MON-YYYY'));
INSERT INTO Customers VALUES (1012, 'Krieger', 'Jeff', '120 Mercury Rd.', 'San Ramon', 'CA', '95114', '9252997100', TO_DATE('15-JUN-1999', 'DD-MON-YYYY'));
INSERT INTO Customers VALUES (1013, 'Blue', 'Don', '12250 Saturn Rd.', 'Pleasanton', 'CA', '95434', '9252332400', TO_DATE('09-SEP-1999', 'DD-MON-YYYY'));
INSERT INTO Customers VALUES (1014, 'Stone', 'Tony', '12 Main St. Apt. 3', 'Oakland', 'CA', '94342', '5104562881', TO_DATE('11-APR-2000', 'DD-MON-YYYY'));
INSERT INTO Customers VALUES (1015, 'Mason', 'Paul', '53 Orange Way', 'Las Vegas', 'NV', '89101', '5104562881', TO_DATE('04-JUL-2000', 'DD-MON-YYYY'));
INSERT INTO Customers VALUES (1016, 'Stone', 'Tony', '12 Main St. Apt. 3', 'Oakland', 'CA', '94342', '5104562881', TO_DATE('30-DEC-2000', 'DD-MON-YYYY'));
INSERT INTO Customers VALUES (1017, 'Krieger', 'Jeff', '120 Mercury Rd.', 'San Ramon', 'CA', '95114', '9252997100', TO_DATE('11-JAN-2001', 'DD-MON-YYYY'));
INSERT INTO Customers VALUES (1018, 'Blake', 'Becky', '1099 Venus St.', 'Salt Lake City', 'UT', '84106', '8016543501', TO_DATE('12-JUN-2001', 'DD-MON-YYYY'));
INSERT INTO Customers VALUES (1019, 'Stone', 'Tony', '12 Main St. Apt. 3', 'Oakland', 'CA', '94342', '5104562881', TO_DATE('03-NOV-2000', 'DD-MON-YYYY'));
INSERT INTO Customers VALUES (1020, 'Hill', 'Larry', '2220 Bench St.', 'Santa Rosa', 'CA', '94533', '7072279800', TO_DATE('24-AUG-2000', 'DD-MON-YYYY'));
COMMIT;看下面的代码我在姓，和名这两个字段上加上唯一约束，（当然你可以在创建表的时候加上这一约束，来防止冗余数据）ALTER TABLE Customers ADD CONSTRAINT Customers_LastFirst UNIQUE (LastName, FirstName);
Ｃｕｓｔｏｍｅｒ表中的冗余键是ＬａｓｔＮａｍｅ和ＦｉｒｓｔＮａｍｅ的集合，我们把含有冗余键的数据进行分组并进行统计．
SELECT LastName, FirstName, COUNT(*)FROM Customers GROUP BY LastName, FirstName ORDER BY LastName, FirstName;
Ｌｉｓｔｉｎｇ ２显示了这条语句的输出，我们可以看到有三行的输出大于１，这也就意味着表中含有３组冗余数据．Listing 2. 找出冗余
LASTNAME;;;;;FIRSTNAME;COUNT(*)--------------- ---------- ----------BlakeBecky;;;;1Blue;Don 1Bradley Tom 1ChangJim 1Griffith;;;;;David;;;;1Hill;Larry;;;;1King;Chuck;;;;1Krieger Jeff;;;;;3LoneyJulie;;;;1Lord;Don 1MasonPaul;;;;;1Monroe; John;;;;;1SimonMichael;;2StoneTony;;;;;5
14 rows selected.
我们在语句中加入Ｈａｖｉｎｇ（）语句来过滤出非冗余数据．SELECT LastName, FirstName, COUNT(*) FROM Customers GROUP BY LastName, FirstName HAVING COUNT(*) > 1;
SQLListing 3. 过滤冗余加入Ｈａｖｉｎｇ（）语句来过滤出非冗余数据．LASTNAME;;;;;FIRSTNAME;COUNT(*)--------------- ---------- ----------Krieger Jeff;;;;;3SimonMichael;;2StoneTony;;;;;5
3 rows selected.Ｌｉｓｔｉｎｇ ３显示了以上代码的输入，尽管如此，这些查询结果并没有显示出能标识每一行的字段，我们将上一语句做为一个嵌套查询来显示标识这些记录的ＩＤSELECT ID, LastName, FirstName FROM Customers WHERE (LastName, FirstName) IN (SELECT LastName, FirstName FROM Customers GROUP BY LastName, FirstName HAVING COUNT(*) > 1) ORDER BY LastName, FirstName;
Ｌｉｓｔｉｎｇ ４显示出了以上代码的结果，这些查询显示了有三组冗余，共有十行，我们应该保留这些组中的1005，1009，1001这些记录然后删除1012，1017，1010，1011，1016，1019，1014这些冗余的条目．SQLListing 4. 找出唯一的键语句的输出ID LASTNAME;;;;;FIRSTNAME----- --------------- ----------1005 Krieger Jeff1012 Krieger Jeff1017 Krieger Jeff1009 SimonMichael1010 SimonMichael1001 StoneTony1011 StoneTony1016 StoneTony1019 StoneTony1014 StoneTony
10 rows selected.
Ｏｒａｃｌｅ公司给出的一个解决方案Ｏｒａｃｌｅ 公司给我们提供一个见删除冗余数据的一个方案，这个方案使用了Ｏｒａｃｌｅ公司自己的一个集合函数ＭＩＮ（）或者ＭＡＸ（）来解决这一问题ＭＩＮ（）函数可以得到每一组中（冗余的非冗余的），应保留的所有值．（正如我们所见，输入出不包含那些大ＩＤ的冗余值
SELECT MIN(ID) AS ID, LastName, FirstName FROM Customers GROUP BY LastName, FirstName;这一条命令的输出Listing 5. Output of MIN() query这一条命令显示了所有的非冗余的数据，其它的行则应该被删除ID LASTNAME;;;;;FIRSTNAME----- --------------- ----------1018 BlakeBecky1013 Blue;Don1000 Bradley Tom1002 ChangJim1008 Griffith;;;;;David1020 Hill;Larry1004 King;Chuck1005 Krieger Jeff1003 LoneyJulie1007 Lord;Don1015 MasonPaul1006 Monroe; John1009 SimonMichael1001 StoneTony
14 rows selected.
这样你就可以删除那些不在这个表中的所有的行，同样将上一条语句作为一个子查询，构造一个语句
DELETE FROM Customers WHERE ID NOT IN (SELECT MIN(ID) FROM Customers GROUP BY LastName, FirstName);
尽管如此，理论是可行的，但是这个方案并不是那么有效，因为这样一来，ＤＢＭＳ要完成两个表的扫描来完成这项任务，对于大量的数据来说，这简直是不可行的，为了测试他的性能，我创建了Ｃｕｓｔｏｍｅｒ表，大约有5000，00０行，45，000冗余行，（９％）以上这个命令运行了一个小时，没有输出结果，它耗尽了我的耐心，所以我杀死了这个进程这个方案的令外这个方案还有一个缺点，你不能控制每一个组中你要保留的行
一种ＰＬ／ＳＱｌ解决方案：使用存储过程删除冗余数据，叫做ＤｅｌｅＤｕｐｌｉｃａｔｅ的存储过程，这个过程的结构很清晰的．SQLListing 6. The DeleteDuplicate stored procedure
它将这些冗余行选择一到一个游标中，然后从表中取出每一个冗余行来进行与游标中的行进行比对，然后决定是否删除CREATE OR REPLACE PROCEDURE DeleteDuplicates( pCommitBatchSize IN INTEGER := 5000) IS
CURSOR csr_Duplicates IS SELECT ID, LastName, FirstName FROM Customers WHERE (LastName, FirstName) IN (SELECT LastName, FirstName FROM Customers GROUP BY LastName, FirstName HAVING COUNT(*) > 1) ORDER BY LastName, FirstName;
／＊保存上一次的姓和名＊／vLastName Customers.LastName%TYPE := NULL;vFirstName Customers.FirstName%TYPE := NULL;
vCounter INTEGER := 0;
BEGIN
FOR vDuplicates IN csr_Duplicates LOOP IF vLastName IS NULL OR (vDuplicates.LastName != vLastName OR NVL(vDuplicates.FirstName, ' ') != NVL(vFirstName, ' '))THEN／＊第一次取出行或者是一个新行 保存它的姓和名的值＊／ vLastName := vDuplicates.LastName; vFirstName := vDuplicates.FirstName;
ELSE ／＊冗余数据，删除它＊／ DELETE FROM Customers WHERE ID = vDuplicates.ID;
vCounter := vCounter + 1;
／＊提交结果＊／ /* Commit every pCommitBatchSize rows */ IF MOD(vCounter, pCommitBatchSize) = 0 THEN COMMIT; END IF;
END IF; END LOOP;
IF vCounter > 0 THEN COMMIT; END IF;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(vCounter) || ' duplicates have been deleted.');
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error ' || TO_CHAR(SQLCODE) || ': ' || SQLERRM); ROLLBACK;
END DeleteDuplicates;
它将冗余数据选择到一个游标中，并根据（ＬａｓｔＮａｍｅ，ＦｉｒｓｔＮａｍｅ）来分组（在我们这个方案中），然后打开游标然后循环地取出每一行，然后用与先前的取出的键值进行比较，如果这是第一次取出这个值，或者这个值不是冗余键，那么跳过这个记录然后取下一个，不然的话，这就是这个组中的冗余记录，所以删掉它．让我们运行一下这个存储过程BEGIN DeleteDuplicates;END;/
SELECT LastName, FirstName, COUNT(*) FROM Customers GROUP BY LastName, FirstName HAVING COUNT(*) > 1;
最后一个查询语句没有返回值，所以冗余数据没有了从表中取冗余数据的过程完全是由定义在csr_Duplicates 这个游标中的ＳＱＬ语句来实现的，ＰＬ／ＳＱｌ只是用来实现删除冗余数，那么能不能完全用ＳＱＬ语句来实现呢？
二．ＳＱＬ解决方案，使用ＲＡＮＫ（）删除冗余数据Ｏｒａｃｌｅ8i分析函数ＲＡＮＫ（）来枚举每一个组中的元素，在我们的方案中， 我们应用这个方案，我们使用这个函数动态的把冗余数据连续的排列起来加上编号，组由Ｐａｒｔｉｎｔｉｏｎ ｂｙ 这个语句来分开，然后用Ｏｒｄｅｒ ｂｙ 进行分组SELECT ID, LastName, FirstName, RANK() OVER (PARTITION BY LastName, FirstName ORDER BY ID) SeqNumber FROM Customers ORDER BY LastName, FirstName;
SQLListing 7. Output of single SQL statement that uses RANK()显示的是根据记录的条数的个数来显示尤其对于冗余数据ID LASTNAME;;;;;FIRSTNAMESEQNUMBER----- --------------- ---------- ----------1018 BlakeBecky;;;;11013 Blue;Don 11000 Bradley Tom 11002 ChangJim 11008 Griffith;;;;;David;;;;11020 Hill;Larry;;;;11004 King;Chuck;;;;11005 Krieger Jeff;;;;;11012 Krieger Jeff;;;;;21017 Krieger Jeff;;;;;31003 LoneyJulie;;;;11007 Lord;Don 11015 MasonPaul;;;;;11006 Monroe; John;;;;;11009 SimonMichael;;11010 SimonMichael;;21001 StoneTony;;;;;11011 StoneTony;;;;;21014 StoneTony;;;;;31016 StoneTony;;;;;41019 StoneTony;;;;;5
我们可以看一到，ＳｅｑＮｕｍｂｅｒ这一列中的数值，冗余数据是根据ＩＤ号由小到大进行的排序，所有的冗余数据的ＳｑｌＮｕｍｂｅｒ都大于一，所有的非冗余数据都等于一，所以我们取自己所需，删除那么没用的SELECT ID, LastName, FirstName FROM (SELECT ID, LastName, FirstName, RANK() OVER (PARTITION BY LastName, FirstName ORDER BY ID) AS SeqNumber FROM Customers) WHERE SeqNumber > 1;
SQLListing 8. 冗余键的键值有七行必须被删除ID LASTNAME;;;;;FIRSTNAME----- --------------- ----------1012 Krieger Jeff1017 Krieger Jeff1010 SimonMichael1011 StoneTony1014 StoneTony1016 StoneTony1019 StoneTony
7 rows selected.
这显示有七行需要删除，还是用上一个表我测试了一下这个代码，它用了７７秒种就删除了所有的数据准备好了用Ｓｑｌ语句来删除冗余数据，版本一它执行了135秒
DELETE FROM CUSTOMERS WHERE ID IN (SELECT ID FROM (SELECT ID, LastName, FirstName, RANK() OVER (PARTITION BY LastName, FirstName ORDER BY ID) AS SeqNumber FROM Customers) WHERE SeqNumber > 1);我们可以看到最后的两行语句对表中的数据进行了排序，这不是有效的，所以我们来优化一下最后一个查询语句，把Ｒａｎｋ（）函数应用到只含有冗余数据的组，而不是所有的列下面这个语句是比较有效率的，虽然它不像上一个查询那样精简SELECT ID, LastName, FirstName FROM (SELECT ID, LastName, FirstName, RANK() OVER (PARTITION BY LastName, FirstName ORDER BY ID) AS SeqNumber FROM (SELECT ID, LastName, FirstName FROM Customers WHERE (LastName, FirstName) IN (SELECT LastName, FirstName FROM Customers GROUP BY LastName, FirstName HAVING COUNT(*) > 1))) WHERE SeqNumber > 1;选择冗余数据只用了２６秒钟，这样就提高了67％的性能，这样就提高了将这个作为子查询的删除查询的效率，DELETE FROM Customers WHERE ID IN (SELECT ID FROM (SELECT ID, LastName, FirstName, RANK() OVER (PARTITION BY LastName, FirstName ORDER BY ID) AS SeqNumber FROM (SELECT ID, LastName, FirstName FROM Customers WHERE (LastName, FirstName) IN (SELECT LastName, FirstName FROM Customers GROUP BY LastName, FirstName HAVING COUNT(*) > 1))) WHERE SeqNumber > 1);现在只用了47秒钟的就完成的上面的任务，比起上一个136秒，这是一个很大的进步，相比之下，存储过程用了56秒，这样存储过程有些慢了使用ＰＬ／ＳＱＬ语句我们和我们以上的代码，会得到更好的更精确的代码，和提高你代码的执行效率，虽然对于从数据库中枚举数据ＰＬ／ＳＱＬ对于Ｓｑｌ两者没有什么差别，但是对于数据的比较上，ＰＬ／ＳＱＬ就比ＳＱＬ要快很多，但是如果冗余数据量比较小的话，我们尽量使用ＳＱＬ而不使用ＰＬ／ＳＱＬ
如果你的数据表没有主键的话，那么你可以参考其它技术
Ｒａｎｋ（）其它的方法使用Ｒａｎｋ（）函数你可以对选择你所保留的数据，（或者是小ＩＤ的或者是大ＩＤ 的，就由ＲＥＣＤａｔｅ这个列来决定这种情况下，你可以把ＲＥｃｄａｔｅ加入到（Ｏｒｄｅｒ ｂｙ ）子句中，倒序或者正序
这是一种保留最大Ｉｄ的一种解决方案
DELETE FROM Customers WHERE ID IN (SELECT ID FROM (SELECT ID, LastName, FirstName, RANK() OVER (PARTITION BY LastName, FirstName ORDER BY RecDate DESC, ID) AS SeqNumber FROM (SELECT ID, LastName, FirstName, RecDate FROM Customers WHERE (LastName, FirstName) IN (SELECT LastName, FirstName FROM Customers GROUP BY LastName, FirstName HAVING COUNT(*) > 1))) WHERE SeqNumber > 1);
这种技术保证了你可以控制每一个表中的保留的组，假设你有一个数据库，有一个促销或者有一个折扣信息，比如一个团体可以使用这种促销５次，或者个人可以使用这个折扣三次，为了指出要保留的组的个数，你可以在where 和having子句中进行设置，那么你将删除所有大于你设置有数的冗余组DELETE FROM Customers WHERE ID IN (SELECT ID FROM (SELECT ID, LastName, FirstName, RANK() OVER (PARTITION BY LastName, FirstName ORDER BY ID) AS SeqNumber FROM (SELECT ID, LastName, FirstName FROM Customers WHERE (LastName, FirstName) IN (SELECT LastName, FirstName FROM Customers GROUP BY LastName, FirstName HAVING COUNT(*) > 3))) WHERE SeqNumber > 3);As you can see, using the RANK() function allows you to eliminate duplicates in asingle SQL statement and gives you more capabilities by extending the power of yourqueries.正如你所见使用Ｒａｎｋ（）可以消除冗余数据而且能给你很大的可伸展性
网公网安备