怎样删除Oracle数据库中的冗余数据(一)

会计考友 · 发表于 2012-8-4 14:06:19

作为数据库的开发者，我们经常面临着要找出及删除数据库中冗余数据的任务，如果数据库中有大量的冗余数据（占总数的百分比太多），数据的精确性和可靠性将受到影响，同时也影响着数据库的性能，那么如何解决这个问题呢？下面我将探讨关于这个问题的这个解决方案，oracle也为我们提供了一个解决方案，但是Ｏｒａｃｌｅ提供的解决方案不够完美，遇到大批量数据那个解决方案工作起来很慢应该怎么删除冗余数据呢？　　在这里我们应用一个ＰＬ／ＳＱｌ方案（一个自定义的存储过程）或者一个ＳＱＬ语句的解决方案（使用一个分析的函数ＲＡＮＫ（）和一个嵌套的子查询）来消除冗余数据然后控制应该保留的记录
　　什么是冗余数据？
　　冗余数据就是一个数据表中，这个表中的行包含了一些相同的值，这些值理论上来说应该是唯一的（这些值一般来说能确定一条记录）例如，像社会保险号，姓与名的集合．那么我们把这么含有相同信息的行中包含的数据叫做冗余数据，现在所有的数据库表中都有主键约束，主键中记录了一行记录中的唯一值，从数据库的角度来看，每一行都是唯一的，但是从我们用户角度看来，这些记录都是相同的记录，因为它们都包含相同的键值（Ｆｉｒｓｔ　Ｎａｍｅ　＋　Ｌａｓｔ　Ｎａｍｅ），即使他们有不同的主键
　　ID Last Name First Name City Phone
　　---- --------------- ---------- --------------- ----------
　　1005 Krieger Jeff San Ramon 9252997100
　　1012 Krieger Jeff San Ramon 9252997100
　　1017 Krieger Jeff San Ramon 9252997100
　　那么这些冗余数据是怎么出现的那？通常有两种情况：１.从不同的表中加载或者合并数据通过图形化的用户接口来输入数据，然后由计算机来生成一个唯一的键，并做为这一条记录的主键那么怎样找到冗余数据呢？让我们来创建一个叫作Ｃｕｓｔｏｍｅｒ　的表并向其中加入冗余数据，看表１，正如你所看到的，我们并没有在这个表上做什么限制来防止冗余数据，下面这么代码创建了一个唯一约束，来防止冗余数据的生成
　　SQL
　　Listing 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)
　　CONSTRAINT Customers_PK
　　PRIMARY KEY (ID))
　　TABLESPACE TALLYDATA;
　　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(*)
　　--------------- ---------- ----------
　　Blake Becky 1
　　Blue Don 1
　　Bradley Tom 1
　　Chang Jim 1
　　Griffith David 1
　　Hill Larry 1
　　King Chuck 1
　　Krieger Jeff 3
　　Loney Julie 1
　　Lord Don 1
　　Mason Paul 1
　　Monroe John 1
　　Simon Michael 2
　　Stone Tony 5
　　14 rows selected.

　　

会计考友 · 发表于 2012-8-4 14:06:20

我们在语句中加入Ｈａｖｉｎｇ（）语句来过滤出非冗余数据．</p>　　SELECT LastName, FirstName, COUNT(*)
　　FROM Customers
　　GROUP BY LastName, FirstName
　　HAVING COUNT(*) > 1;
　　SQL
　　Listing 3. 过滤冗余
　　加入Ｈａｖｉｎｇ（）语句来过滤出非冗余数据．
　　LASTNAME FIRSTNAME COUNT(*)
　　--------------- ---------- ----------
　　Krieger Jeff 3
　　Simon Michael 2
　　Stone Tony 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这些冗余的条目．
　　SQL
　　Listing 4. 找出唯一的键
　　语句的输出
　　ID LASTNAME FIRSTNAME
　　----- --------------- ----------
　　1005 Krieger Jeff
　　1012 Krieger Jeff
　　1017 Krieger Jeff
　　1009 Simon Michael
　　1010 Simon Michael
　　1001 Stone Tony
　　1011 Stone Tony
　　1016 Stone Tony
　　1019 Stone Tony
　　1014 Stone Tony
　　10 rows selected.
　　Ｏｒａｃｌｅ公司给出的一个解决方案
　　Ｏｒａｃｌｅ　公司给我们提供一个见删除冗余数据的一个方案，这个方案使用了Ｏｒａｃｌ
　　ｅ公司自己的一个集合函数ＭＩＮ（）或者ＭＡＸ（）来解决这一问题ＭＩＮ（）函数可以得
　　到每一组中（冗余的非冗余的），应保留的所有值．（正如我们所见，输入出不包含那些大Ｉ
　　Ｄ的冗余值
　　SELECT MIN(ID) AS ID, LastName, FirstName
　　FROM Customers
　　GROUP BY LastName, FirstName;
　　这一条命令的输出

　

会计考友 · 发表于 2012-8-4 14:06:21

　Listing 5. Output of MIN() query这一条命令显示了所有的非冗余的数据，其它的行则应该被删除</p>　　ID LASTNAME FIRSTNAME
　　----- --------------- ----------
　　1018 Blake Becky
　　1013 Blue Don
　　1000 Bradley Tom
　　1002 Chang Jim
　　1008 Griffith David
　　1020 Hill Larry
　　1004 King Chuck
　　1005 Krieger Jeff
　　1003 Loney Julie
　　1007 Lord Don
　　1015 Mason Paul
　　1006 Monroe John
　　1009 Simon Michael
　　1001 Stone Tony
　　14 rows selected.
　　这样你就可以删除那些不在这个表中的所有的行，同样将上一条语句作为一个子查询，构造一
　　个语句
　　DELETE FROM Customers
　　WHERE ID NOT IN
　　(SELECT MIN(ID)
　　FROM Customers
　　GROUP BY LastName, FirstName);
　　尽管如此，理论是可行的，但是这个方案并不是那么有效，因为这样一来，ＤＢＭＳ要完成两个表的扫描来完成这项任务，对于大量的数据来说，这简直是不可行的，为了测试他的性能，我创建了Ｃｕｓｔｏｍｅｒ表，大约有5000，00０行，45，000冗余行，（９％）以上这个命令运行了一个小时，没有输出结果，它耗尽了我的耐心，所以我杀死了这个进程这个方案的令外这个方案还有一个缺点，你不能控制每一个组中你要保留的行
　　一种ＰＬ／ＳＱｌ解决方案：使用存储过程删除冗余数据，叫做ＤｅｌｅＤｕｐｌｉｃａｔｅ的存储过程，这个过程的结构很清晰的．
　　SQL
　　Listing 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;

　

会计考友 · 发表于 2012-8-4 14:06:22

　／＊保存上一次的姓和名＊／</p>　　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;

		自动登录	找回密码
密码			立即注册

[考试辅导] 怎样删除Oracle数据库中的冗余数据(一)