作為數據庫的開發者，我們經常面臨著要找出及刪除數據庫中冗余數據的任務，如果數據庫中有大量的冗余數據（占總數的百分比太多），數據的精確性和可靠性將受到影響，同時也影響著數據庫的性能，那么如何解決這個問題呢？下面我將探討關于這個問題的這個解決方案，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.',
           '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(*)
--------------- ---------- ----------
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           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冗余行，（９％）以上這個命令運行了一個小時，沒有輸出結果，它耗盡了我的耐心，所以我殺死了這個進程
這個方案的令外這個方案還有一個缺點，你不能控制每一個組中你要保留的行

一種ｐｌ／ｓｑｌ解決方案：使用存儲過程刪除冗余數據，叫做ｄｅｌｅｄｕｐｌｉｃａｔｅ的存儲過程，這個過程的結構很清晰的．
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_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;

sql
listing 7. output of single sql statement that uses rank()
顯示的是根據記錄的條數的個數來顯示尤其對于冗余數據
id lastname        firstname   seqnumber
----- --------------- ---------- ----------
 1018 blake           becky               1
 1013 blue            don                 1
 1000 bradley         tom                 1
 1002 chang           jim                 1
 1008 griffith        david               1
 1020 hill            larry               1
 1004 king            chuck               1
 1005 krieger         jeff                1
 1012 krieger         jeff                2
 1017 krieger         jeff                3
 1003 loney           julie               1
 1007 lord            don                 1
 1015 mason           paul                1
 1006 monroe          john                1
 1009 simon           michael             1
 1010 simon           michael             2
 1001 stone           tony                1
 1011 stone           tony                2
 1014 stone           tony                3
 1016 stone           tony                4
 1019 stone           tony                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;

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.
正如你所見使用ｒａｎｋ（）可以消除冗余數據而且能給你很大的可伸展性