作為數據庫的開發者，我們經常面臨著要找出及刪除數據庫中冗余數據的任務，如果數據庫中有大量的冗余數據（占總數的百分比太多），數據的精確性和可靠性將受到影響，同時也影響著數據庫的性能，那麼如何解決這個問題呢？下面我將探討關於這個問題的這個解決方案，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),
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.',
&nb
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',
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',
&n
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(*)
--------------- ---------- ----------
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.
我們在語句中加入Ｈａｖｉｎｇ（）語句來過濾出非冗余數據．
SELECT LastName, FirstName, COUNT(*)
FROM Customers
GROUP BY LastName, FirstName
HAVING COUNT(*) > 1;
SQL
Listing 3. 過濾冗余
加入Ｈａｖｉｎｇ（）語句來過濾出非冗余數據．
LASTNAME FIRSTNAME COUNT(*)
--------------- ---------- ----------
KrIEger Jeff 3
Simon &nbs
您正在看的SQLserver教程是:如何刪除數據庫的冗余數據（翻譯）。p; 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;
這一條命令的輸出
Listing 5. Output of MIN() query
這一條命令顯示了所有的非冗余的數據，其它的行則應該被刪除
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冗余行，（９％）以上這個命令運行了一個小時，沒有輸出結果，它耗盡了我的耐心，所以我殺死了這個進程
這個方案的令外這個方案還有一個缺點，你不能控制每一個組中你要保留的行
您正在看的SQLserver教程是:如何刪除數據庫的冗余數據（翻譯）。一種ＰＬ／ＳＱｌ解決方案：使用存儲過程刪除冗余數據，叫做ＤｅｌｅＤｕｐｌｉｃａｔｅ的存儲過程，這個過程的結構很清晰的．
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;
／＊保存上一次的姓和名＊／
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_CH
END DeleteDuplicates;
它將冗余數據選擇到一個游標中，並根據（ＬａｓｔＮａｍｅ，ＦｉｒｓｔＮａｍｅ）來分組（在我們這個方案中），然後打開游標然後循環地取出每一行，然後用與先前的取出的鍵值進行比較，如果這是第一次取出這個值，或者這個值不是冗余鍵，那麼跳過這個記錄然後取下一個，不然的話，
這就是這個組中的冗余記錄，所以刪掉它．
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;
SQL
Listing 7. Output of single SQL statement that uses RANK()
顯示的是根據記錄的條數的個數來顯示尤其對於冗余數據
ID LASTNAME FIRSTNAME SEQNUMBER
----- --------------- ---------- ----------
1018 Blake &n
我們可以看一到，ＳｅｑＮｕｍｂｅｒ這一列中的數值，冗余數據是根據ＩＤ號由小到大進行的排序，所有的冗余數據的ＳｑｌＮｕｍｂｅｒ都大於一，所有的非冗余數據都等於一，所以我們取自己所需，刪除那麼沒用的
SELECT ID, LastName, FirstName
FROM
(SELECT ID, LastName, FirstName,
RANK() OVER (PARTITION BY LastName,
FirstName ORDER BY ID) AS SeqNumber
FROM Cust
SQL
Listing 8. 冗余鍵的鍵值
有七行必須被刪除
ID LASTNAME FIRSTNAME
----- --------------- ----------
1012 KrIEger Jeff
1017 KrIEger Jeff
1010 Simon Michael
1011 Stone Tony
1014 Stone Tony
1016 Stone Tony
1019 Stone Tony
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 a
single SQL statement and gives you more capabilitIEs by extending the power of your
querIEs.
正如你所見使用Ｒａｎｋ（）可以消除冗余數據而且能給你很大的可伸展性
