關於ORA-01779問題的分析和解決

我們來首先復現一下這個問題，準備基礎的資料。
create table data (id number,credit_class varchar2(100),amount number);
create table link (credit_class varchar2(100),score number);

insert into link values('vip',100);
insert into link values('agent',10);
insert into link values('*',1);

begin
for i in 1..8 loop
insert into data values(i,'vip',110);
end loop; 
end;
/

begin
for i in 9..10 loop
insert into data values(i,'normal',9);
end loop;
end;
/

alter table data modify(id primary key);  --給表data加上主鍵，可以保證在關聯update能夠校驗唯一性。
有些資料的credit_class匹配link.credit_class,有些不匹配。我們先一股腦把資料都配上，然後再過濾。


select a.id,a.credit_class,x.credit_class ,a.amount,x.score From data a, link x
  where (a.credit_class=x.credit_class or x.credit_class ='*')   
  order by id;

        ID CREDIT_CLASS                   CREDIT_CLASS                       AMOUNT      SCORE
---------- ------------------------------ ------------------------------ ---------- ----------
         1 vip                            vip                                   110        100
         1 vip                            *                                     110          1
         2 vip                            vip                                   110        100
         2 vip                            *                                     110          1
         3 vip                            *                                     110          1
         3 vip                            vip                                   110        100
         4 vip                            *                                     110          1
         4 vip                            vip                                   110        100
         5 vip                            *                                     110          1
         5 vip                            vip                                   110        100
         6 vip                            vip                                   110        100
         6 vip                            *                                     110          1
         7 vip                            vip                                   110        100
         7 vip                            *                                     110          1
         8 vip                            vip                                   110        100
         8 vip                            *                                     110          1
         9 normal                         *                                       9          1
        10 normal                         *                                       9          1
看看id=1和id=9的資料，就能看出差別了。id=1的資料存在匹配的credit_class,而id=9卻沒有匹配的ccredit_class,所以關聯表link之後得到的score也不同。
現在的問題是如何把id=1的資料進行去重。如果存在匹配的credit_class,就修改對應的score為amount值，對於credit_class='*’ and id=1的那條記錄如何做排除是這個問題的關鍵。

嘗試了各種方法之後，發現還是把資料一分為二，creidt_class匹配的一組，credit_class不匹配的一組。根據count(id) 做group by來分組。
--存在匹配credit_class的資料為：
        ID OLD                            CREDIT_CLASS                       AMOUNT      SCORE
---------- ------------------------------ ------------------------------ ---------- ----------
         1 vip                            vip                                   110        100
         6 vip                            vip                                   110        100
         2 vip                            vip                                   110        100
         4 vip                            vip                                   110        100
         5 vip                            vip                                   110        100
         8 vip                            vip                                   110        100
         3 vip                            vip                                   110        100
         7 vip                            vip                                   110        100
8 rows selected.
--不匹配credit_class的資料為：
select *from(select x.id,a.credit_class old,x.credit_class  ,x.amount,a.score From link a, data x
  where (a.credit_class=x.credit_class or a.credit_class ='*')  )
  where id in(
  select id from (
select x.id,a.credit_class old,x.credit_class  ,a.score From link a, data x
  where (a.credit_class=x.credit_class or a.credit_class ='*')    
 )group by id   having count(*)=1
  );
得到的資料如下：
        ID OLD                            CREDIT_CLASS                       AMOUNT      SCORE
---------- ------------------------------ ------------------------------ ---------- ----------
         9 *                              normal                                  9          1
        10 *                              normal                                  9          1

得到了資料集，但是使用update語句卻是困難重重。
無論是使用Hint  /*+BYPASS_UJVC*/  還是使用各種可用的update方法。都會丟擲ORA-01779: cannot modify a column which maps to a non key-preserved table
SQL> update (
  2   select*from (
  3    select x.id,a.credit_class old,x.credit_class ,x.amount ,a.score From link a, data x
  4    where (a.credit_class=x.credit_class or a.credit_class ='*')  )
  5    where id in(
  6    select id from (
  7  select x.id,a.credit_class old,x.credit_class  ,a.score From link a, data x
  8    where (a.credit_class=x.credit_class or a.credit_class ='*')    
  9   )group by id   having count(*)=2
 10    ) and old!='*'
 11   ) set amount=score
 12  /
 ) set amount=score
       *
ERROR at line 11:
ORA-01779: cannot modify a column which maps to a non key-preserved table

最後使用的方法是透過pl/sql來完成，寫了一個小的pl/sql指令碼。
  declare
 cursor part1_cursor is select*from (
  select x.id,a.credit_class old,x.credit_class ,x.amount ,a.score From link a, data x
  where (a.credit_class=x.credit_class or a.credit_class ='*')  )
  where id in(
  select id from (
select x.id,a.credit_class old,x.credit_class  ,a.score From link a, data x
  where (a.credit_class=x.credit_class or a.credit_class ='*')    
 )group by id   having count(*)=2
  ) and old!='*'
 ;
 cursor part2_cursor is 
  select *from(select x.id,a.credit_class old,x.credit_class  ,x.amount,a.score From link a, data x
  where (a.credit_class=x.credit_class or a.credit_class ='*')  )
  where id in(
  select id from (
select x.id,a.credit_class old,x.credit_class  ,a.score From link a, data x
  where (a.credit_class=x.credit_class or a.credit_class ='*')    
 )group by id   having count(*)=1
  );
 begin
 for tmp_part1_cursor in part1_cursor loop
 dbms_output.put_line('update data set amount is '||tmp_part1_cursor.score||' for id:'||tmp_part1_cursor.id);
 update data set amount=tmp_part1_cursor.score where id=tmp_part1_cursor.id;
  rollback;
 end loop;
  for tmp_part2_cursor in part2_cursor loop
 dbms_output.put_line('update data set amount is '||tmp_part2_cursor.score||' for id:'||tmp_part2_cursor.id);
 update data set amount=tmp_part2_cursor.score where id=tmp_part2_cursor.id;
  rollback;
 end loop;
 end;
 /

指令碼執行結果如下：
update data set amount is 100 for id:1
update data set amount is 100 for id:6
update data set amount is 100 for id:2
update data set amount is 100 for id:4
update data set amount is 100 for id:5
update data set amount is 100 for id:8
update data set amount is 100 for id:3
update data set amount is 100 for id:7
update data set amount is 1 for id:9
update data set amount is 1 for id:10
PL/SQL procedure successfully completed.

 可以看到還是很快就處理完成了，而且也沒有多次更新。基本是完成了期望的結果。
透過這個問題，方法總比困難多，一種方式不合適還可以透過其它的方式實現。能夠最大限度的保證資料的準確性才是根本。