ORACLE11GR2 中使用 IGNORE_ROW_ON_DUPKEY_INDEX

guoge發表於2015-01-07
OracleIndex

我們經常需要把部分線上系統表的資料進行部分備份，例如門診醫生站和門診收費的中間表outp_orders_costs

建立備份表並 把資料備份過來：

 

時間A 的時候

點選(此處)摺疊或開啟

  1. create table OUTP_ORDERS_COSTS_BAK as
  2.    select * from OUTP_ORDERS_COSTS ;
  3.  
  4. alter table OUTP_ORDERS_COSTS_BAK
  5.   add constraint PK_OUTP_ORDERS_COSTS_BAK primary key (SERIAL_NO, ORDER_CLASS, ORDER_NO, ITEM_NO);
  6.  
  7. create index IN1_2_OUTP_ORDERS_COSTS_BAK on OUTP_ORDERS_COSTS_BAK (VISIT_DATE, VISIT_NO);



原始表只保留部分資料：

點選(此處)摺疊或開啟

  1. truncate table OUTP_ORDERS_COSTS ;
  2.  

  4.  insert /*+ append */ into OUTP_ORDERS_COSTS
  5.   select * from OUTP_ORDERS_COSTS_BAK where visit_date < sysdate -1;


 

 

假設系統又執行了很長時間，OUTP_ORDERS_COSTS 又多了很多資料，有需要清理。

 

如果直接insert into  OUTP_ORDERS_COSTS_BAK  因為時間A 中的部分資料在OUTP_ORDERS_COSTS 和OUTP_ORDERS_COSTS_BAK 都存在，因此可能報錯，例如：

點選(此處)摺疊或開啟

  1. SQL> insert /*+ append*/ into OUTP_ORDERS_COSTS_BAK
  2.   2 select * from OUTP_ORDERS_COSTS ;
  3.    insert /*+ append*/ into OUTP_ORDERS_COSTS_BAK
  4. *
  5. ERROR at line 1:
  6. ORA-00001: unique constraint (HR.PK_OUTP_ORDERS_COSTS_BAK) violated


以前只有寫SQL 把這些資料剝離出來， 例如加個where子句，把兩個表的交集剃掉。例如：


點選(此處)摺疊或開啟

  1. into OUTP_ORDERS_COSTS_BAK
  2.      select *
  3.       from OUTP_ORDERS_COSTS
  4.      where (SERIAL_NO, ORDER_CLASS, ORDER_NO, ITEM_NO) not in
  5.            (select SERIAL_NO, ORDER_CLASS, ORDER_NO, ITEM_NO
  6.                from OUTP_ORDERS_COSTS_BAK);


在ORACLE11GR2 之後，可以這樣：



點選(此處)摺疊或開啟

  1. insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(OUTP_ORDERS_COSTS_BAK,PK_OUTP_ORDERS_COSTS_BAK)*/
  2.     into OUTP_ORDERS_COSTS_BAK
  3.       select * from OUTP_ORDERS_COSTS ;


  處理起來更簡單。

 


IGNORE_ROW_ON_DUPKEY_INDEX  針對的只要是唯一索引即可，不一定要求一定是主鍵。雖然主鍵一般會有一個唯一性索引。

 

在使用上可以指定索引名，如上例，也可以指定列名，如下例。


 

點選(此處)摺疊或開啟

  1. insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(OUTP_ORDERS_COSTS_BAK(SERIAL_NO, ORDER_CLASS, ORDER_NO, ITEM_NO)))*/
  2.       into OUTP_ORDERS_COSTS_BAK
  3.      select * from OUTP_ORDERS_COSTS ;

 

這個提示，是更適合在增量備份中使用。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9036/viewspace-1393307/，如需轉載，請註明出處，否則將追究法律責任。

相關文章