MERGE INTO DCC_LOAN_CARDINFO_ts T1
USING (SELECT ORG,
CARD_NO,
CUST_NAME,
CKIND,
MAIN_FLAG,
CARD_STATUS,
CRDG1,
CRDG2,
OPCR_DATE,
EXPD,
ORIGIN_CODE,
ACCT_NO,
ID_NO,
BANKNO,
CARD_BLANK_CODE,
YX_OPER_NO,
YX_DEPTNO,
Z_CUST_NO,
CARD_FACE,
BRAND_NO,
APP_NO,
CUST_ORG,
ACTIVE_FLAG,
ACTIVE_DATE,
BLANK_CODE_DATE
FROM DCC_LOAN_CARDINFO_TEMP) T2
ON (T1.ORG = T2.ORG AND T1.CARD_NO = T2.CARD_NO)
WHEN MATCHED THEN
UPDATE
SET T1.CUST_NAME = T2.CUST_NAME,
T1.CKIND = T2.CKIND,
T1.MAIN_FLAG = T2.MAIN_FLAG,
T1.CARD_STATUS = T2.CARD_STATUS,
T1.CRDG1 = T2.CRDG1,
T1.CRDG2 = T2.CRDG2,
T1.OPCR_DATE = T2.OPCR_DATE,
T1.EXPD = T2.EXPD,
T1.ORIGIN_CODE = T2.ORIGIN_CODE,
T1.ACCT_NO = T2.ACCT_NO,
T1.ID_NO = T2.ID_NO,
T1.BANKNO = T2.BANKNO,
T1.CARD_BLANK_CODE = T2.CARD_BLANK_CODE,
T1.YX_OPER_NO = T2.YX_OPER_NO,
T1.YX_DEPTNO = T2.YX_DEPTNO,
T1.Z_CUST_NO = T2.Z_CUST_NO,
T1.CARD_FACE = T2.CARD_FACE,
T1.BRAND_NO = T2.BRAND_NO,
T1.APP_NO = T2.APP_NO,
T1.CUST_ORG = T2.CUST_ORG,
T1.CARD_CLS_DTE = T1.CARD_CLS_DTE,
T1.ACTIVE_FLAG = T2.ACTIVE_FLAG,
T1.ACTIVE_DATE = T2.ACTIVE_DATE,
T1.BLANK_CODE_DATE = T2.BLANK_CODE_DATE
WHEN NOT MATCHED THEN
INSERT
(T1.ORG,
T1.CARD_NO,
T1.CUST_NAME,
T1.CKIND,
T1.MAIN_FLAG,
T1.CARD_STATUS,
T1.CRDG1,
T1.CRDG2,
T1.OPCR_DATE,
T1.EXPD,
T1.ORIGIN_CODE,
T1.ACCT_NO,
T1.ID_NO,
T1.BANKNO,
T1.CARD_BLANK_CODE,
T1.YX_OPER_NO,
T1.YX_DEPTNO,
T1.Z_CUST_NO,
T1.CARD_FACE,
T1.BRAND_NO,
T1.APP_NO,
T1.CUST_ORG,
T1.CARD_CLS_DTE,
T1.ACTIVE_FLAG,
T1.ACTIVE_DATE,
T1.BLANK_CODE_DATE)
VALUES
(T2.ORG,
T2.CARD_NO,
T2.CUST_NAME,
T2.CKIND,
T2.MAIN_FLAG,
T2.CARD_STATUS,
T2.CRDG1,
T2.CRDG2,
T2.OPCR_DATE,
T2.EXPD,
T2.ORIGIN_CODE,
T2.ACCT_NO,
T2.ID_NO,
T2.BANKNO,
T2.CARD_BLANK_CODE,
T2.YX_OPER_NO,
T2.YX_DEPTNO,
T2.Z_CUST_NO,
T2.CARD_FACE,
T2.BRAND_NO,
T2.APP_NO,
T2.CUST_ORG,
TO_DATE('18990101', 'yyyymmdd'),
T2.ACTIVE_FLAG,
T2.ACTIVE_DATE,
T2.BLANK_CODE_DATE)
t1、t2表记录均为400多万条，执行计划如下：
description object name cost cardinality cpu cost time IO cost
MERGE STATEMENT, GOAL = ALL_ROWS 65993 4111890 8253454865 1188 63384
MERGE DCC_LOAN_CARDINFO_TS
VIEW
HASH JOIN OUTER 65993 4111890 8253454865 1188 63384
TABLE ACCESS FULL DCC_LOAN_CARDINFO_TEMP 11532 4111890 2899976616 208 10615
TABLE ACCESS FULL DCC_LOAN_CARDINFO_TS 12304 4127820 3259479831 222 11274
大概需要2小时才能完成，哪位大侠帮忙优化下，谢谢了
收起