Merge与Insert select 所需的系统临时表空间页大小有何区别?
在做数据处理时,想将表A的数据更新到表B中,使用 Merge b using a语句,报SQL1585N 不存在具有足够页大小的系统临时表空间。 SQLSTATE=54048
但使用Insert into b select * from a,则可以成功插入。
现在想向专家们请教为何同一份数据会有这么大的差别,Merge使用的页大小到底怎么计算?
具体语句如下:
MERGE INTO ODS_KER_BGFMAL A USING
(select CABRNO, CAPTSN, trim(REPLACE(STRIP(CHAR(CASQNO),LEADING, '0'),'.','')) AS CASQNO,
CAAC20, CACCYC, CACHSX, CAWRST, CADATE, CBDATE, CCDATE,
trim(REPLACE(STRIP(CHAR(CASN08),LEADING, '0'),'.','')) AS CASN08, CACDFG,
CAAMT, CBAMT, CCAMT, trim(REPLACE(STRIP(CHAR(CBSN03),LEADING, '0'),'.','')) AS CBSN03,
CANTCD, CARMRK, CAAC32, CBFLNM, CABLTP, trim(REPLACE(STRIP(CHAR(CABLNO),LEADING, '0'),'.','')) AS CABLNO,
CBBLTP, trim(REPLACE(STRIP(CHAR(CBBLNO),LEADING, '0'),'.','')) AS CBBLNO, CBBRNO, trim(REPLACE(STRIP(CHAR(CATXSN),LEADING, '0'),'.','')) AS CATXSN,
trim(REPLACE(STRIP(CHAR(CBTXSN),LEADING, '0'),'.','')) AS CBTXSN, trim(REPLACE(STRIP(CHAR(CASN11),LEADING, '0'),'.','')) AS CASN11,
trim(REPLACE(STRIP(CHAR(CASN10),LEADING, '0'),'.','')) AS CASN10, trim(REPLACE(STRIP(CHAR(CASN02),LEADING, '0'),'.','')) AS CASN02,
CASTAF, CBSTAF, CARS1B from NDS_KER_BGFMAL B ) B
ON A.CASQNO= B.CASQNO AND A.CADATE= B.CADATE AND A.CARS1B= B.CARS1B when matched then update set
( A.CABRNO, A.CAPTSN, A.CASQNO, A.CAAC20, A.CACCYC, A.CACHSX, A.CAWRST, A.CADATE, A.CBDATE, A.CCDATE, A.CASN08, A.CACDFG, A.CAAMT,
A.CBAMT, A.CCAMT, A.CBSN03, A.CANTCD, A.CARMRK, A.CAAC32, A.CBFLNM, A.CABLTP, A.CABLNO, A.CBBLTP, A.CBBLNO, A.CBBRNO, A.CATXSN,
A.CBTXSN, A.CASN11, A.CASN10, A.CASN02, A.CASTAF, A.CBSTAF, A.CARS1B )
=( B.CABRNO, B.CAPTSN, B.CASQNO, B.CAAC20, B.CACCYC, B.CACHSX, B.CAWRST, B.CADATE, B.CBDATE, B.CCDATE, B.CASN08, B.CACDFG, B.CAAMT,
B.CBAMT, B.CCAMT, B.CBSN03, B.CANTCD, B.CARMRK, B.CAAC32, B.CBFLNM, B.CABLTP, B.CABLNO, B.CBBLTP, B.CBBLNO, B.CBBRNO, B.CATXSN,
B.CBTXSN, B.CASN11, B.CASN10, B.CASN02, B.CASTAF, B.CBSTAF, B.CARS1B ) when not matched then insert
(A.CABRNO, A.CAPTSN, A.CASQNO, A.CAAC20, A.CACCYC, A.CACHSX, A.CAWRST, A.CADATE, A.CBDATE, A.CCDATE, A.CASN08, A.CACDFG, A.CAAMT,
A.CBAMT, A.CCAMT, A.CBSN03, A.CANTCD, A.CARMRK, A.CAAC32, A.CBFLNM, A.CABLTP, A.CABLNO, A.CBBLTP, A.CBBLNO, A.CBBRNO, A.CATXSN,
A.CBTXSN, A.CASN11, A.CASN10, A.CASN02, A.CASTAF, A.CBSTAF, A.CARS1B ) values
( B.CABRNO, B.CAPTSN, B.CASQNO, B.CAAC20, B.CACCYC, B.CACHSX, B.CAWRST, B.CADATE, B.CBDATE, B.CCDATE, B.CASN08, B.CACDFG, B.CAAMT,
B.CBAMT, B.CCAMT, B.CBSN03, B.CANTCD, B.CARMRK, B.CAAC32, B.CBFLNM, B.CABLTP, B.CABLNO, B.CBBLTP, B.CBBLNO, B.CBBRNO, B.CATXSN,
B.CBTXSN, B.CASN11, B.CASN10, B.CASN02, B.CASTAF, B.CBSTAF, B.CARS1B ) else ignore;
insert into ODS_KER_BGFMAL (CABRNO, CAPTSN, CASQNO, CAAC20, CACCYC, CACHSX, CAWRST, CADATE, CBDATE, CCDATE, CASN08, CACDFG, CAAMT,
CBAMT, CCAMT, CBSN03, CANTCD, CARMRK, CAAC32, CBFLNM, CABLTP, CABLNO, CBBLTP, CBBLNO, CBBRNO, CATXSN,
CBTXSN, CASN11, CASN10, CASN02, CASTAF, CBSTAF, CARS1B )
select CABRNO, CAPTSN, REPLACE(STRIP(CHAR(CASQNO),LEADING, '0'),'.','') AS CASQNO,
CAAC20, CACCYC, CACHSX, CAWRST, CADATE, CBDATE, CCDATE,
REPLACE(STRIP(CHAR(CASN08),LEADING, '0'),'.','') AS CASN08, CACDFG,
CAAMT, CBAMT, CCAMT, REPLACE(STRIP(CHAR(CBSN03),LEADING, '0'),'.','') AS CBSN03,
CANTCD, CARMRK, CAAC32, CBFLNM, CABLTP, REPLACE(STRIP(CHAR(CABLNO),LEADING, '0'),'.','') AS CABLNO,
CBBLTP, REPLACE(STRIP(CHAR(CBBLNO),LEADING, '0'),'.','') AS CBBLNO, CBBRNO, REPLACE(STRIP(CHAR(CATXSN),LEADING, '0'),'.','') AS CATXSN,
REPLACE(STRIP(CHAR(CBTXSN),LEADING, '0'),'.','') AS CBTXSN, REPLACE(STRIP(CHAR(CASN11),LEADING, '0'),'.','') AS CASN11,
REPLACE(STRIP(CHAR(CASN10),LEADING, '0'),'.','') AS CASN10, REPLACE(STRIP(CHAR(CASN02),LEADING, '0'),'.','') AS CASN02,
CASTAF, CBSTAF, CARS1B from NDS_KER_BGFMAL B ;