作為資料庫的開發者，我們經常面臨着要找出及删除資料庫中備援資料的任務，如果資料庫中有大量的備援資料（占總數的百分比太多），資料的精确性和可靠性将受到影響，同時也影響着資料庫的性能，那麼如何解決這個問題呢？下面我将探讨關于這個問題的這個解決方案，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'));
values (1001, 'stone', 'tony', '12 main st. apt. 3',
'oakland', 'ca', '94342', '5104562881',
to_date('12-mar-2001', 'dd-mon-yyyy'));
values (1002, 'chang', 'jim', '425 26th ave.',
'seattle', 'wa', '98103', '8182454400',
to_date('15-jun-2000', 'dd-mon-yyyy'));
values (1003, 'loney', 'julie', '12 keith st.',
'castro valley', 'ca', '94546', '5103300721',
to_date('22-nov-2000', 'dd-mon-yyyy'));
values (1004, 'king', 'chuck', '100 broadway st.',
'pleasant hill', 'ca', '95114', '9254247701',
to_date('14-apr-2001', 'dd-mon-yyyy'));
values (1005, 'krieger', 'jeff', '120 mercury rd.',
'san ramon', 'ca', '95114', '9252997100',
to_date('02-feb-2001', 'dd-mon-yyyy'));
values (1006, 'monroe', 'john', '122 west ave.',
'phoenix', 'az', '85004', '9252997100',
to_date('02-oct-2000', 'dd-mon-yyyy'));
values (1007, 'lord', 'don', '573 hill view',
'atlanta', 'ga', '30303', '3036578900',
to_date('12-dec-2000', 'dd-mon-yyyy'));
values (1008, 'griffith', 'david', '10 fulton rd. apt.4',
'san francisco', 'ca', '94121', '7236578900',
to_date('15-dec-1999', 'dd-mon-yyyy'));
values (1009, 'simon', 'michael', '573 hill view',
'santa monica', 'ca', '90402', '8185689200',
to_date('22-may-1999', 'dd-mon-yyyy'));
values (1010, 'simon', 'michael', '573 hill view',
to_date('02-oct-1999', 'dd-mon-yyyy'));
values (1011, 'stone', 'tony', '12 main st. apt. 3',
to_date('07-dec-1999', 'dd-mon-yyyy'));
values (1012, 'krieger', 'jeff', '120 mercury rd.',
to_date('15-jun-1999', 'dd-mon-yyyy'));
values (1013, 'blue', 'don', '12250 saturn rd.',
'pleasanton', 'ca', '95434', '9252332400',
to_date('09-sep-1999', 'dd-mon-yyyy'));
values (1014, 'stone', 'tony', '12 main st. apt. 3',
to_date('11-apr-2000', 'dd-mon-yyyy'));
values (1015, 'mason', 'paul', '53 orange way',
'las vegas', 'nv', '89101', '5104562881',
to_date('04-jul-2000', 'dd-mon-yyyy'));
values (1016, 'stone', 'tony', '12 main st. apt. 3',
to_date('30-dec-2000', 'dd-mon-yyyy'));
values (1017, 'krieger', 'jeff', '120 mercury rd.',
to_date('11-jan-2001', 'dd-mon-yyyy'));
values (1018, 'blake', 'becky', '1099 venus st.',
'salt lake city', 'ut', '84106', '8016543501',
to_date('12-jun-2001', 'dd-mon-yyyy'));
values (1019, 'stone', 'tony', '12 main st. apt. 3',
to_date('03-nov-2000', 'dd-mon-yyyy'));
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
having count(*) > 1;
listing 3. 過濾備援
加入Ｈａｖｉｎｇ（）語句來過濾出非備援資料．
3 rows selected.
Ｌｉｓｔｉｎｇ ３顯示了以上代碼的輸入，盡管如此，這些查詢結果并沒有顯示出能辨別每一行的字段，我們将上一語句做為一個嵌套查詢來顯示辨別這些記錄的ＩＤ
select id, lastname, firstname
where (lastname, firstname) in
(select lastname, firstname
from customers
group by lastname, firstname
having count(*) > 1)
Ｌｉｓｔｉｎｇ ４顯示出了以上代碼的結果，這些查詢顯示了有三組備援，共有十行，我們應該保留這些組中的1005，1009，1001這些記錄然後删除1012，1017，1010，1011，1016，1019，1014這些備援的條目．
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
group by lastname, firstname;
這一條指令的輸出
listing 5. output of min() query
這一條指令顯示了所有的非備援的資料，其它的行則應該被删除
1018 blake becky
1013 blue don
1000 bradley tom
1002 chang jim
1008 griffith david
1020 hill larry
1004 king chuck
1003 loney julie
1007 lord don
1015 mason paul
1006 monroe john
這樣你就可以删除那些不在這個表中的所有的行，同樣将上一條語句作為一個子查詢，構造一個語句
delete from customers
where id not in
(select min(id)
group by lastname, firstname);
盡管如此，理論是可行的，但是這個方案并不是那麼有效，因為這樣一來，ＤＢＭＳ要完成兩個表的掃描來完成這項任務，對于大量的資料來說，這簡直是不可行的，為了測試他的性能，我建立了Ｃｕｓｔｏｍｅｒ表，大約有5000，00０行，45，000備援行，（９％）以上這個指令運作了一個小時，沒有輸出結果，它耗盡了我的耐心，是以我殺死了這個程序
這個方案的令外這個方案還有一個缺點，你不能控制每一個組中你要保留的行
一種ＰＬ／ＳＱｌ解決方案：使用存儲過程删除備援資料，叫做ＤｅｌｅＤｕｐｌｉｃａｔｅ的存儲過程，這個過程的結構很清晰的．
listing 6. the deleteduplicate stored procedure
它将這些備援行選擇一到一個遊标中，然後從表中取出每一個備援行來進行與遊标中的行進行比對，然後決定是否删除
create or replace procedure deleteduplicates(
pcommitbatchsize in integer := 5000) is
cursor csr_duplicates is
select id, 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
dbms_output.put_line('error ' ||
to_char(sqlcode) || ': ' || sqlerrm);
rollback;
end deleteduplicates;
它将備援資料選擇到一個遊标中，并根據（ＬａｓｔＮａｍｅ，ＦｉｒｓｔＮａｍｅ）來分組（在我們這個方案中），然後打開遊标然後循環地取出每一行，然後用與先前的取出的鍵值進行比較，如果這是第一次取出這個值，或者這個值不是備援鍵，那麼跳過這個記錄然後取下一個，不然的話，這就是這個組中的備援記錄，是以删掉它．
讓我們運作一下這個存儲過程
deleteduplicates;
end;
/
最後一個查詢語句沒有傳回值，是以備援資料沒有了從表中取備援資料的過程完全是由定義在csr_duplicates 這個遊标中的ＳＱＬ語句來實作的，ＰＬ／ＳＱｌ隻是用來實作删除備援數，那麼能不能完全用ＳＱＬ語句來實作呢？
二．ＳＱＬ解決方案，使用ＲＡＮＫ（）删除備援資料
Ｏｒａｃｌｅ8i分析函數ＲＡＮＫ（）來枚舉每一個組中的元素，在我們的方案中， 我們應用這個方案，我們使用這個函數動态的把備援資料連續的排列起來加上編号，組由Ｐａｒｔｉｎｔｉｏｎ ｂｙ 這個語句來分開，然後用Ｏｒｄｅｒ ｂｙ 進行分組
select id, lastname, firstname,
rank() over (partition by lastname,
firstname order by id) seqnumber
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
我們可以看一到，ＳｅｑＮｕｍｂｅｒ這一列中的數值，備援資料是根據ＩＤ号由小到大進行的排序，所有的備援資料的ＳｑｌＮｕｍｂｅｒ都大于一，所有的非備援資料都等于一，是以我們取自己所需，删除那麼沒用的
from
(select id, lastname, firstname,
rank() over (partition by lastname,
firstname order by id) as seqnumber
from customers)
where seqnumber > 1;
listing 8. 備援鍵的鍵值
有七行必須被删除
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 customers
where (lastname, firstname) in
(select lastname, firstname
group by lastname, firstname
having count(*) > 1)))
where seqnumber > 1;
選擇備援資料隻用了２６秒鐘，這樣就提高了67％的性能，這樣就提高
了将這個作為子查詢的删除查詢的效率，
(select id
from
(select id, lastname, firstname
where (lastname, firstname) in
(select lastname, firstname
from customers
group by lastname, firstname
having count(*) > 1)))
where seqnumber > 1);
現在隻用了47秒鐘的就完成的上面的任務，比起上一個136秒，這是一個很大的進步，相比之下，存儲過程用了56秒，這樣存儲過程有些慢了使用ＰＬ／ＳＱＬ語句我們和我們以上的代碼，會得到更好的更精确的代碼，和提高你代碼的執行效率，雖然對于從資料庫中枚舉資料ＰＬ／ＳＱＬ對于Ｓｑｌ兩者沒有什麼差别，但是對于資料的比較上，ＰＬ／ＳＱＬ就比ＳＱＬ要快很多，但是如果備援資料量比較小的話，我們盡量使用ＳＱＬ而不使用ＰＬ／ＳＱＬ
如果你的資料表沒有主鍵的話，那麼你可以參考其它技術
Ｒａｎｋ（）其它的方法
使用Ｒａｎｋ（）函數你可以對選擇你所保留的資料，（或者是小ＩＤ的或者是大ＩＤ 的，就由ＲＥＣＤａｔｅ這個列來決定這種情況下，你可以把ＲＥｃｄａｔｅ加入到（Ｏｒｄｅｒ ｂｙ ）子句中，倒序或者正序
這是一種保留最大Ｉｄ的一種解決方案
firstname order by recdate desc, id) as seqnumber
(select id, lastname, firstname, recdate
這種技術保證了你可以控制每一個表中的保留的組，假設你有一個資料庫，有一個促銷或者有一個折扣資訊，比如一個團體可以使用這種促銷５次，或者個人可以使用這個折扣三次，為了指出要保留的組的個數，你可以在where 和having子句中進行設定，那麼你将删除所有大于你設定有數的備援組
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.
正如你所見使用Ｒａｎｋ（）可以消除備援資料而且能給你很大的可伸展性
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)