oracle中merge执行占用空间,ORA-30926 及MERGE 临时表空占用问题

今天进行MERGE操作的时候是根据两个2个大表大约每张表10G左右，进行更新，服务器使用了PCI-E闪存盘，

速度还是相当快的，大概语句如下：

merge into TEST_EVENT a

using

TEST_EVENT_1 b

on(a.MAIN_TARGET=b.MAIN_TARGET and a.EVENT_TYPE=b.EVENT_TYPE)

when matched then

update

set

a.CARD_NO=b.CARD_NO,a.EVENT_ACCOUNT=b.EVENT_ACCOUNT,a.EXTEND_PROPERTIES=b.EXTEND_PROPERTIES

where

b.EVENT_NAME= 'WITHDRAW' AND b.EVENT_TYPE IN ('SYNCHRONIZE','ASYNCHRONIZE')

AND

b.raw_add_time>=to_date('2014-01-01','yyyy-mm-dd')

AND

b.raw_add_time

出现了2个问题如下进行分析和说明：

1、merge 链接不稳定

报错

ORA-30926: 无法在源表中获得一组稳定的行

MERGE is a deterministic statement. You therefore need to evaluate the data returned by

the USING clause to ensure that there are no duplicate values in the join. Modify

the merge statement to include a deterministic where clause.

进行举例

SQL> select * from testmerg;

ID NAME

----------- --------------------

1 gaopeng

2 gaopeng

3 gaopeng

SQL> select * from testmerg2;

ID NAME

----------- --------------------

1 yanlei

2 yanlei

2 yanlei1

SQL>

SQL> merge  into  testmerg a

2  using testmerg2 b

3  on (a.id=b.id)

4  when matched then update

5  set a.name=b.name;

merge  into  testmerg a

using te