ORACLE11GR2 中使用 IGNORE_ROW_ON_DUPKEY_INDEX （转载）

我们经常需要把部分在线系统表的数据进行部分备份，例如门诊医生站和门诊收费的中间表outp_orders_costs
创建备份表并 把数据备份过来：

 

时间A 的时候

 

				 create table OUTP_ORDERS_COSTS_BAK as
				   select * from OUTP_ORDERS_COSTS ;

				 alter table OUTP_ORDERS_COSTS_BAK
				  add constraint PK_OUTP_ORDERS_COSTS_BAK primary key (SERIAL_NO, ORDER_CLASS, ORDER_NO, ITEM_NO);

				 create index IN1_2_OUTP_ORDERS_COSTS_BAK on OUTP_ORDERS_COSTS_BAK (VISIT_DATE, VISIT_NO);

 

 

原始表只保留部分数据： 

 

				 truncate table OUTP_ORDERS_COSTS ;


				 insert /*+ append */ into OUTP_ORDERS_COSTS
				  select * from OUTP_ORDERS_COSTS_BAK where visit_date < sysdate -

假设系统又运行了很长时间，OUTP_ORDERS_COSTS 又多了很多数据，有需要清理。

 

如果直接insert into  OUTP_ORDERS_COSTS_BAK  因为时间A 中的部分数据在OUTP_ORDERS_COSTS 和OUTP_ORDERS_COSTS_BAK 都存在，因此可能报错，例如：

 

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

 

以前只有写SQL 把这些数据剥离出来， 例如加个where子句，把两个表的交集剃掉。例如：

 

 

				 into OUTP_ORDERS_COSTS_BAK
				     select *
				      from OUTP_ORDERS_COSTS
				     where (SERIAL_NO, ORDER_CLASS, ORDER_NO, ITEM_NO) not in
				           (select SERIAL_NO, ORDER_CLASS, ORDER_NO, ITEM_NO
				               from OUTP_ORDERS_COSTS_BAK);

 

在ORACLE11GR2 之后，可以这样：

 

 

				 insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(OUTP_ORDERS_COSTS_BAK,PK_OUTP_ORDERS_COSTS_BAK)*/
				    into OUTP_ORDERS_COSTS_BAK
				      select * from OUTP_ORDERS_COSTS ;

 

  处理起来更简单。

  

IGNORE_ROW_ON_DUPKEY_INDEX  针对的只要是唯一索引即可，不一定要求一定是主键。虽然主键一般会有一个唯一性索引。

 

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

 

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

这个提示，是更适合在增量备份中使用。

 

转载自：http://blog.itpub.net/9036/viewspace-1393307/