软件开发 db2 9.7
SQL优化
insert into session.CORE_ACCOUNT select distinct case when b.DFANAC19 is null then a.JLADAC22 else b.DFANAC19 end,
to_char(a.JLBCDATE,'yyyy-MM-dd') from DM_CORE_BLFMCDJL a left join DM_CORE_BWFMDCIM b
on a.JLADAC22 = b.CDNOAC19 where substr(a.JLADAC22,1,2) <> '20' and JLDFFLAG = '1' and to_char(a.JLBCDATE,'yyyy-MM-dd') <= v_enddate;
insert into session.CORE_BDFMHQABTMP
select AB01AC15,AB02CCYC,AB03CHSX,AB05CSNO,AB08ACST,AB09NO16,AB10NO10,AB14BAL,AB15BAL,AB16BAL,RCSTRS1B,
case when begdate <= v_startdate then v_startdate else begdate end,
case when enddate > v_enddate then to_char(date(v_enddate) + 1 days,'yyyy-MM-dd') else enddate end
from DM_CORE_BDFMHQAB_ZIP a where a.begdate <= v_enddate and a.enddate>=v_startdate and
not exists (select 1 from session.CORE_ACCOUNT where AB01AC15 = JLADAC22); 用了not exists速度很慢 怎么优化下
DM_CORE_BDFMHQAB_ZIP 这个表里面符合条件的大概有140万 ，session.CORE_ACCOUNT 这个表里面大概有2万
to_char(a.JLBCDATE,'yyyy-MM-dd') from DM_CORE_BLFMCDJL a left join DM_CORE_BWFMDCIM b
on a.JLADAC22 = b.CDNOAC19 where substr(a.JLADAC22,1,2) <> '20' and JLDFFLAG = '1' and to_char(a.JLBCDATE,'yyyy-MM-dd') <= v_enddate;
insert into session.CORE_BDFMHQABTMP
select AB01AC15,AB02CCYC,AB03CHSX,AB05CSNO,AB08ACST,AB09NO16,AB10NO10,AB14BAL,AB15BAL,AB16BAL,RCSTRS1B,
case when begdate <= v_startdate then v_startdate else begdate end,
case when enddate > v_enddate then to_char(date(v_enddate) + 1 days,'yyyy-MM-dd') else enddate end
from DM_CORE_BDFMHQAB_ZIP a where a.begdate <= v_enddate and a.enddate>=v_startdate and
not exists (select 1 from session.CORE_ACCOUNT where AB01AC15 = JLADAC22); 用了not exists速度很慢 怎么优化下
DM_CORE_BDFMHQAB_ZIP 这个表里面符合条件的大概有140万 ，session.CORE_ACCOUNT 这个表里面大概有2万
25 同行回答
回复 19# stevenluffy
我这用的是零时表啊
declare GLOBAL TEMPORARY TABLE session.CORE_BDFMHQABTMP (
AB01AC15 CHARACTER(15),
AB02CCYC CHARACTER(3),
AB03CHSX CHARACTER(1),
AB05CSNO CHARACTER(11),
AB08ACST CHARACTER(1),
AB09NO16 CHARACTER(16),
AB10NO10 CHARACTER(10),
AB14BAL DECIMAL(15, 2),
AB15BAL DECIMAL(15, 2),
AB16BAL DECIMAL(15, 2),
RCSTRS1B CHARACTER(1),
begdate CHARACTER(10),
enddate CHARACTER(10)
) NOT LOGGED WITH REPLACE;--
declare GLOBAL TEMPORARY TABLE session.CORE_ACCOUNT (
JLADAC22 CHARACTER(22),
JLBCDATE VARCHAR(10)
) NOT LOGGED WITH REPLACE;--
CREATE TABLE LOAN.DM_CORE_BDFMHQAB_ZIP (
AB01AC15 CHARACTER(15),
BEGDATE CHARACTER(10),
ENDDATE CHARACTER(10),
AB05CSNO CHARACTER(11),
AB08ACST CHARACTER(1),
AB09NO16 CHARACTER(16),
AB10NO10 CHARACTER(10),
AB14BAL DECIMAL(15, 2),
AB15BAL DECIMAL(15, 2),
AB16BAL DECIMAL(15, 2),
RCSTRS1B CHARACTER(1),
AVERBAL DECIMAL(15, 2),
AB02CCYC CHARACTER(3),
AB03CHSX CHARACTER(1)
) 收起
我这用的是零时表啊
declare GLOBAL TEMPORARY TABLE session.CORE_BDFMHQABTMP (
AB01AC15 CHARACTER(15),
AB02CCYC CHARACTER(3),
AB03CHSX CHARACTER(1),
AB05CSNO CHARACTER(11),
AB08ACST CHARACTER(1),
AB09NO16 CHARACTER(16),
AB10NO10 CHARACTER(10),
AB14BAL DECIMAL(15, 2),
AB15BAL DECIMAL(15, 2),
AB16BAL DECIMAL(15, 2),
RCSTRS1B CHARACTER(1),
begdate CHARACTER(10),
enddate CHARACTER(10)
) NOT LOGGED WITH REPLACE;--
declare GLOBAL TEMPORARY TABLE session.CORE_ACCOUNT (
JLADAC22 CHARACTER(22),
JLBCDATE VARCHAR(10)
) NOT LOGGED WITH REPLACE;--
CREATE TABLE LOAN.DM_CORE_BDFMHQAB_ZIP (
AB01AC15 CHARACTER(15),
BEGDATE CHARACTER(10),
ENDDATE CHARACTER(10),
AB05CSNO CHARACTER(11),
AB08ACST CHARACTER(1),
AB09NO16 CHARACTER(16),
AB10NO10 CHARACTER(10),
AB14BAL DECIMAL(15, 2),
AB15BAL DECIMAL(15, 2),
AB16BAL DECIMAL(15, 2),
RCSTRS1B CHARACTER(1),
AVERBAL DECIMAL(15, 2),
AB02CCYC CHARACTER(3),
AB03CHSX CHARACTER(1)
) 收起
请问能发一下相关表的表结构么，只需要关联字段和主键。 收起