GaussDB(DWS)性能调优：大宽表关联MERGE性能优化
【摘要】 大宽表关联MERGE性能优化
【前言】
本优化针对的场景需满足以下约束
- MERGE语句中因为update比较很大，导致的MERGE操作在整体操作中的耗时占比较大
- MERGE目标表不存在并发更新操作，更新操作包括MERGE、UPDATE、INSERT、DELETE、TRUNCATE、EXCHANGE、UPSERT等操作
【业务背景】
如下MERGE语句执行耗时长达1743s
MERGE INTO fin_dwb_pro.dwb_pro_ep_bpa_analysis_f T
USING (
SELECT
T.SUPPLIER_SITE_ID, T.BPA_ID, T.BPA_NUM, T.PO_ID, T.PO_TYPE_ID, T.ENABLE_FLAG, T.SHIP_TO_LOCATION, T.BILL_TO_LOCATION, T.CURRENCY_CODE AS CONTRACT_CURRENCY_CODE, T.PAY_CURRENCY_CODE, T.BPA_AMT, T.APPR_STATUS, T.APPROVE_DATE AS BPA_APPROVE_DATE, T.SUPPLIER_REMARK, T.REMARK AS BPA_REMARK, T.PAYMENT_TERMS, T.HW_CONTRACT_NUM, T.ERP_INSTANCE_ID, T.ACCEPT_TERMS, T.APPROVE_EMPLOYEE_ID, T.CHANGE_STATUS, T.AGGREMENT_TYPE, T.USE_SCOPE, T.CELL_NAME, T.PAYMENT_BACK_TO_BACK_FLAG, T.ACCEPT_BACK_TO_BACK_FLAG, T.PRE_PAYMENT_GUARANTEE_FLAG, T.PERFORMANCE_GUARANTEE_FLAG, T.SPECIAL_TAX_INFO, T.CREATE_DATE, T.ENABLE_DATE, T.DISABLE_DATE, T.AGREEMENT_NUMBER, T.SUBMIT_DATE, T.PAYMENT_TERM_TYPE_ID, T.EDITION_NUM, T.CONTRACT_BEGIN_DATE, T.CONTRACT_END_DATE, T.STATUS_LOOKUP_CODE, T.PROCESS_FLAG, T.IBUY_STATUS, T.IBUY_BPA_NUM, T.IBUY_CREATE_DATE, T.SHIP_TO_LOCATION_ID, T.REGION, T.RATE_TYPE, T.RATE_DATE, T.RATE, T.SS_ID, T.PO_H_CANCELED_FLAG, T.PO_H_CREATE_DATE, T.GLOBAL_AGREEMENT_FLAG, T.PO_LINE_ID, T.PO_L_CREATE_DATE, T.UNIT_PRICE, T.ITEM_DESCRIPTION, T.DEL_FLAG, T1.CONTRACT_KEY, T2.COMPANY_KEY, T2.OU_KEY, T3.SUPPLIER_KEY, T4.EMPLOYEE_KEY CEG_BUYER_KEY, T5.EMPLOYEE_KEY CREATE_KEY, T6.GEO_PC_KEY, T7.ITEM_CODE, T8.PUR_ITEM_CATG_KEY, T9.CODE MEASURE_UNIT
FROM fin_dwb_pro.DWB_PRO_EP_BPA_ANALYSIS_TMP T
LEFT JOIN DWRDIM_DW1.DWR_DIM_CONTRACT_D T1 ON T.CONTRACT_ID = T1.CONTRACT_ID AND T.CREATE_DATE > T1.SCD_ACTIVE_BEGIN_DATE AND T.CREATE_DATE <= T1.SCD_ACTIVE_END_DATE
LEFT JOIN DWRDIM_DW1.DWR_DIM_OU_D T2 ON T.OU_ID = T2.OU_ID AND T.CREATE_DATE > T2.SCD_ACTIVE_BEGIN_DATE AND T.CREATE_DATE <= T2.SCD_ACTIVE_END_DATE
LEFT JOIN DWRDIM_DW1.DWR_DIM_SUPPLIER_D T3 ON T.SUPPLIER_ID = T3.SUPPLIER_ID AND T.CREATE_DATE > T3.SCD_ACTIVE_BEGIN_DATE AND T.CREATE_DATE <= T3.SCD_ACTIVE_END_DATE
LEFT JOIN DWRDIM_DW1.DWR_DIM_EMPLOYEE_D T4 ON T.CEG_BUYER_EMPLOYEE_ID = T4.EMPLOYEE_ID AND T.CREATE_DATE > T4.SCD_ACTIVE_BEGIN_DATE AND T.CREATE_DATE <= T4.SCD_ACTIVE_END_DATE
LEFT JOIN DWRDIM_DW1.DWR_DIM_EMPLOYEE_D T5 ON T.CREATE_EMPLOYEE_ID = T5.EMPLOYEE_ID AND T.CREATE_DATE > T5.SCD_ACTIVE_BEGIN_DATE AND T.CREATE_DATE <= T5.SCD_ACTIVE_END_DATE
LEFT JOIN DWRDIM_DW1.DWR_DIM_REGION_RC_D T6 ON T.GEO_PC_ID = T6.GEO_PC_ID AND T.CREATE_DATE > T6.SCD_ACTIVE_BEGIN_DATE AND T.CREATE_DATE <= T6.SCD_ACTIVE_END_DATE
LEFT JOIN DWRDIM_DW1.DWR_DIM_MATERIAL_CODE_D T7 ON T.ITEM_ID = T7.ITEM_ID AND T7.DEL_FLAG = 'N'
LEFT JOIN DWRDIM_DW1.DWR_DIM_PUR_ITEM_CATEGORY_D T8 ON T.ITEM_CATEGORY_ID = T8.PUR_ITEM_CATG_ID AND T.CREATE_DATE > T8.SCD_ACTIVE_BEGIN_DATE AND T.CREATE_DATE <= T8.SCD_ACTIVE_END_DATE
LEFT JOIN dwimd_dw1.DWI_MD_CLASS T9 ON T.MEASURE_UNIT_ID = T9.CLASS_ID
) S on(T.po_line_id = S.po_line_id AND T.bpa_id = S.bpa_id)
WHEN MATCHED THEN UPDATE SET T.accept_back_to_back_flag=S.accept_back_to_back_flag,T.accept_terms=S.accept_terms,T.aggrement_type=S.aggrement_type,T.agreement_number=S.agreement_number,T.appr_status=S.appr_status,T.approve_employee_id=S.approve_employee_id,T.bill_to_location=S.bill_to_location,T.bpa_amt=S.bpa_amt,T.bpa_approve_date=S.bpa_approve_date,T.bpa_num=S.bpa_num,T.bpa_remark=S.bpa_remark,T.ceg_buyer_key=S.ceg_buyer_key,T.cell_name=S.cell_name,T.change_status=S.change_status,T.company_key=S.company_key,T.contract_begin_date=S.contract_begin_date,T.contract_currency_code=S.contract_currency_code,T.contract_end_date=S.contract_end_date,T.contract_key=S.contract_key,T.create_date=S.create_date,T.create_key=S.create_key,T.del_flag=S.del_flag,T.disable_date=S.disable_date,T.edition_num=S.edition_num,T.enable_date=S.enable_date,T.enable_flag=S.enable_flag,T.erp_instance_id=S.erp_instance_id,T.geo_pc_key=S.geo_pc_key,T.global_agreement_flag=S.global_agreement_flag,T.hw_contract_num=S.hw_contract_num,T.ibuy_bpa_num=S.ibuy_bpa_num,T.ibuy_create_date=S.ibuy_create_date,T.ibuy_status=S.ibuy_status,T.item_code=S.item_code,T.item_description=S.item_description,T.measure_unit=S.measure_unit,T.ou_key=S.ou_key,T.pay_currency_code=S.pay_currency_code,T.payment_back_to_back_flag=S.payment_back_to_back_flag,T.payment_term_type_id=S.payment_term_type_id,T.payment_terms=S.payment_terms,T.performance_guarantee_flag=S.performance_guarantee_flag,T.po_h_canceled_flag=S.po_h_canceled_flag,T.po_h_create_date=S.po_h_create_date,T.po_id=S.po_id,T.po_l_create_date=S.po_l_create_date,T.po_type_id=S.po_type_id,T.pre_payment_guarantee_flag=S.pre_payment_guarantee_flag,T.process_flag=S.process_flag,T.pur_item_catg_key=S.pur_item_catg_key,T.rate=S.rate,T.rate_date=S.rate_date,T.rate_type=S.rate_type,T.region=S.region,T.ship_to_location=S.ship_to_location,T.ship_to_location_id=S.ship_to_location_id,T.special_tax_info=S.special_tax_info,T.ss_id=S.ss_id,T.status_lookup_code=S.status_lookup_code,T.submit_date=S.submit_date,T.supplier_key=S.supplier_key,T.supplier_remark=S.supplier_remark,T.supplier_site_id=S.supplier_site_id,T.unit_price=S.unit_price,T.use_scope=S.use_scope
WHEN NOT MATCHED THEN INSERT(T.accept_back_to_back_flag,T.accept_terms,T.aggrement_type,T.agreement_number,T.appr_status,T.approve_employee_id,T.bill_to_location,T.bpa_amt,T.bpa_approve_date,T.bpa_id,T.bpa_num,T.bpa_remark,T.ceg_buyer_key,T.cell_name,T.change_status,T.company_key,T.contract_begin_date,T.contract_currency_code,T.contract_end_date,T.contract_key,T.create_date,T.create_key,T.del_flag,T.disable_date,T.edition_num,T.enable_date,T.enable_flag,T.erp_instance_id,T.geo_pc_key,T.global_agreement_flag,T.hw_contract_num,T.ibuy_bpa_num,T.ibuy_create_date,T.ibuy_status,T.item_code,T.item_description,T.measure_unit,T.ou_key,T.pay_currency_code,T.payment_back_to_back_flag,T.payment_term_type_id,T.payment_terms,T.performance_guarantee_flag,T.po_h_canceled_flag,T.po_h_create_date,T.po_id,T.po_l_create_date,T.po_line_id,T.po_type_id,T.pre_payment_guarantee_flag,T.process_flag,T.pur_item_catg_key,T.rate,T.rate_date,T.rate_type,T.region,T.ship_to_location,T.ship_to_location_id,T.special_tax_info,T.ss_id,T.status_lookup_code,T.submit_date,T.supplier_key,T.supplier_remark,T.supplier_site_id,T.unit_price,T.use_scope) VALUES( S.accept_back_to_back_flag,S.accept_terms,S.aggrement_type,S.agreement_number,S.appr_status,S.approve_employee_id,S.bill_to_location,S.bpa_amt,S.bpa_approve_date,S.bpa_id,S.bpa_num,S.bpa_remark,S.ceg_buyer_key,S.cell_name,S.change_status,S.company_key,S.contract_begin_date,S.contract_currency_code,S.contract_end_date,S.contract_key,S.create_date,S.create_key,S.del_flag,S.disable_date,S.edition_num,S.enable_date,S.enable_flag,S.erp_instance_id,S.geo_pc_key,S.global_agreement_flag,S.hw_contract_num,S.ibuy_bpa_num,S.ibuy_create_date,S.ibuy_status,S.item_code,S.item_description,S.measure_unit,S.ou_key,S.pay_currency_code,S.payment_back_to_back_flag,S.payment_term_type_id,S.payment_terms,S.performance_guarantee_flag,S.po_h_canceled_flag,S.po_h_create_date,S.po_id,S.po_l_create_date,S.po_line_id,S.po_type_id,S.pre_payment_guarantee_flag,S.process_flag,S.pur_item_catg_key,S.rate,S.rate_date,S.rate_type,S.region,S.ship_to_location,S.ship_to_location_id,S.special_tax_info,S.ss_id,S.status_lookup_code,S.submit_date,S.supplier_key,S.supplier_remark,S.supplier_site_id,S.unit_price,S.use_scope) 【性能分析】
提取语句的topSQL中的query_plan进行性能分析
1 | Row Adapter (cost=20714381.64..20714381.64 rows=1 width=2414) (actual time=1743746.966..1743746.966 rows=0 loops=1)
2 | ->Vector Streaming (type: GATHER) (cost=1756493.43..20714381.64 rows=1 width=2414) (actual time=1743746.955..1743746.955 rows=0 loops=1)
| Node/s: (GenGroup) dn_6049_6050, dn_6051_6052, dn_6053_6054, dn_6055_6056, dn_6057_6058, dn_6059_6060, dn_6085_6086, dn_6087_6088, dn_6089_6090, dn_6091_6092, dn_6093_6094, dn_6095_6096, dn_6133_6134, dn_6135_6136, dn_6137_6138, dn_6139_6140, dn_6141_6142, dn_6143_6144, dn_6145_6146, dn_6147_6148, dn_6149_6150, dn_6151_6152, dn_6153_6154, dn_6155_6156, dn_6181_6182, dn_6183_6184, dn_6185_6186, dn_6187_6188, dn_6189_6190, dn_6191_6192, dn_6217_6218, dn_6219_6220, dn_6221_6222, dn_6223_6224, dn_6225_6226, dn_6227_6228, dn_6253_6254, dn_6255_6256, dn_6257_6258, dn_6259_6260, dn_6261_6262, dn_6263_6264, dn_6265_6266, dn_6267_6268, dn_6269_6270, dn_6271_6272, dn_6273_6274, dn_6275_6276
3 | ->Vector Merge on fin_dwb_pro.dwb_pro_ep_bpa_analysis_f t (cost=1756489.43..20714352.76 rows=160448279 width=2414) (actual time=[349155.302,1537098.929]..[349266.922,1743674.630], rows=90568871)
| Merge Inserted: 761
| Merge Updated: 90568110
4 | ->Vector Streaming(type: REDISTRIBUTE) (cost=1756489.43..20714352.76 rows=160448279 width=2414) (actual time=[349142.370,366465.609]..[349257.329,787452.862], rows=90568871)
| Distribute Key: (CASE WHEN (t.ctid IS NULL) THEN t.po_line_id ELSE t.po_line_id END)
| Spawn on: (LC_DW1) dn_6049_6050, dn_6051_6052, dn_6053_6054, dn_6055_6056, dn_6057_6058, dn_6059_6060, dn_6085_6086, dn_6087_6088, dn_6089_6090, dn_6091_6092, dn_6093_6094, dn_6095_6096, dn_6133_6134, dn_6135_6136, dn_6137_6138, dn_6139_6140, dn_6141_6142, dn_6143_6144, dn_6145_6146, dn_6147_6148, dn_6149_6150, dn_6151_6152, dn_6153_6154, dn_6155_6156, dn_6181_6182, dn_6183_6184, dn_6185_6186, dn_6187_6188, dn_6189_6190, dn_6191_6192, dn_6217_6218, dn_6219_6220, dn_6221_6222, dn_6223_6224, dn_6225_6226, dn_6227_6228, dn_6253_6254, dn_6255_6256, dn_6257_6258, dn_6259_6260, dn_6261_6262, dn_6263_6264, dn_6265_6266, dn_6267_6268, dn_6269_6270, dn_6271_6272, dn_6273_6274, dn_6275_6276
5 | ->Vector Hash Left Join (6, 37) (cost=1756489.43..16707455.27 rows=160448279 width=2414) (actual time=[349099.705,362635.991]..[349217.852,366717.314], rows=90568871)
| Hash Cond: ((t.po_line_id = t.po_line_id) AND (t.bpa_id = t.bpa_id))
Max File Num: 32
Min File Num: 32
6 | ->Vector Streaming(type: REDISTRIBUTE) (cost=1665564.99..15705652.32 rows=160448279 distinct=1886663.00 width=1609) (actual time=[2.601,50612.672]..[121760.091,172138.176], rows=90568871)
| Distribute Key: t.po_line_id
| Spawn on: (LC_DW1) dn_6049_6050, dn_6051_6052, dn_6053_6054, dn_6055_6056, dn_6057_6058, dn_6059_6060, dn_6085_6086, dn_6087_6088, dn_6089_6090, dn_6091_6092, dn_6093_6094, dn_6095_6096, dn_6133_6134, dn_6135_6136, dn_6137_6138, dn_6139_6140, dn_6141_6142, dn_6143_6144, dn_6145_6146, dn_6147_6148, dn_6149_6150, dn_6151_6152, dn_6153_6154, dn_6155_6156, dn_6181_6182, dn_6183_6184, dn_6185_6186, dn_6187_6188, dn_6189_6190, dn_6191_6192, dn_6217_6218, dn_6219_6220, dn_6221_6222, dn_6223_6224, dn_6225_6226, dn_6227_6228, dn_6253_6254, dn_6255_6256, dn_6257_6258, dn_6259_6260, dn_6261_6262, dn_6263_6264, dn_6265_6266, dn_6267_6268, dn_6269_6270, dn_6271_6272, dn_6273_6274, dn_6275_6276
7 | ->Vector Hash Left Join (8, 35) (cost=1665564.99..12922877.88 rows=160448279 width=1609) (actual time=[2826.190,53453.488]..[2921.774,58760.811], rows=90568871)
| Hash Cond: (t.measure_unit_id = t9.class_id)
Max Memory Used : 218435KB
Min Memory Used : 218435KB
8 | ->Vector Hash Left Join (9, 34) (cost=955116.27..12166470.75 rows=160448279 distinct=75.00 width=1594) (actual time=[2170.646,50678.371]..[2439.436,57501.803], rows=90568871)
| Hash Cond: (t.item_category_id = t8.pur_item_catg_id)
| Join Filter: ((t.create_date > t8.scd_active_begin_date) AND (t.create_date <= t8.scd_active_end_date))
| Rows Removed by Join Filter: 9833471
Max Memory Used : 25608KB
Min Memory Used : 25608KB
9 | ->Vector Hash Left Join (10, 33) (cost=944342.30..11997855.84 rows=160448279 distinct=311.00 width=1586) (actual time=[93.396,46634.035]..[2405.133,55690.231], rows=90568871)
| Hash Cond: (t.ou_id = t2.ou_id)
| Join Filter: ((t.create_date > t2.scd_active_begin_date) AND (t.create_date <= t2.scd_active_end_date))
| Rows Removed by Join Filter: 538909189
Max Memory Used : 365KB
Min Memory Used : 365KB
10 | ->Vector Hash Left Join (11, 32) (cost=943515.87..11672183.68 rows=145862072 distinct=163.00 width=1572) (actual time=[89.473,34941.728]..[2401.847,52548.763], rows=90568871)
| Hash Cond: (t.geo_pc_id = t6.geo_pc_id)
| Join Filter: ((t.create_date > t6.scd_active_begin_date) AND (t.create_date <= t6.scd_active_end_date))
| Rows Removed by Join Filter: 1062195979
Max Memory Used : 946KB
Min Memory Used : 946KB
11 | ->Vector Hash Left Join (12, 30) (cost=936516.64..10945959.32 rows=132601884 distinct=188.00 width=1565) (actual time=[83.913,23058.232]..[2396.142,47362.634], rows=90568871)
| Hash Cond: (t.supplier_id = t3.supplier_id)
| Join Filter: ((t.create_date > t3.scd_active_begin_date) AND (t.create_date <= t3.scd_active_end_date))
| Rows Removed by Join Filter: 384059448
| Skew Join Optimized by Statistic
Max Memory Used : 15896KB
Min Memory Used : 15595KB
12 | ->Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN) (cost=928824.67..10754480.30 rows=120547167 distinct=80.00 width=1556) (actual time=[2.391,12026.312]..[39.946,42622.088], rows=90568871)
| Distribute Key: t.supplier_id
| Skew Filter(type: ROUNDROBIN): ((t.supplier_id = 10011176698::numeric) OR (t.supplier_id = 10011176686::numeric))
| Spawn on: (LC_DW1) dn_6049_6050, dn_6051_6052, dn_6053_6054, dn_6055_6056, dn_6057_6058, dn_6059_6060, dn_6085_6086, dn_6087_6088, dn_6089_6090, dn_6091_6092, dn_6093_6094, dn_6095_6096, dn_6133_6134, dn_6135_6136, dn_6137_6138, dn_6139_6140, dn_6141_6142, dn_6143_6144, dn_6145_6146, dn_6147_6148, dn_6149_6150, dn_6151_6152, dn_6153_6154, dn_6155_6156, dn_6181_6182, dn_6183_6184, dn_6185_6186, dn_6187_6188, dn_6189_6190, dn_6191_6192, dn_6217_6218, dn_6219_6220, dn_6221_6222, dn_6223_6224, dn_6225_6226, dn_6227_6228, dn_6253_6254, dn_6255_6256, dn_6257_6258, dn_6259_6260, dn_6261_6262, dn_6263_6264, dn_6265_6266, dn_6267_6268, dn_6269_6270, dn_6271_6272, dn_6273_6274, dn_6275_6276
13 | ->Vector Hash Left Join (14, 28) (cost=928824.67..8651183.63 rows=120547167 width=1556) (actual time=[1103.322,40127.916]..[1156.438,345831.209], rows=90568871)
| Hash Cond: (t.ceg_buyer_employee_id = t4.employee_id)
| Join Filter: ((t.create_date > t4.scd_active_begin_date) AND (t.create_date <= t4.scd_active_end_date))
| Rows Removed by Join Filter: 385616801
| Skew Join Optimized by Statistic
Max Memory Used : 24493KB
Min Memory Used : 24087KB
14 | ->Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN) (cost=916197.12..8409975.06 rows=109588334 distinct=28.00 width=1548) (actual time=[955.105,35793.047]..[1027.700,344491.622], rows=90568871)
| Distribute Key: t.ceg_buyer_employee_id
| Skew Filter(type: ROUNDROBIN): ((t.ceg_buyer_employee_id = (-999998)::numeric) OR (t.ceg_buyer_employee_id = 1100011722256::numeric))
| Spawn on: (LC_DW1) dn_6049_6050, dn_6051_6052, dn_6053_6054, dn_6055_6056, dn_6057_6058, dn_6059_6060, dn_6085_6086, dn_6087_6088, dn_6089_6090, dn_6091_6092, dn_6093_6094, dn_6095_6096, dn_6133_6134, dn_6135_6136, dn_6137_6138, dn_6139_6140, dn_6141_6142, dn_6143_6144, dn_6145_6146, dn_6147_6148, dn_6149_6150, dn_6151_6152, dn_6153_6154, dn_6155_6156, dn_6181_6182, dn_6183_6184, dn_6185_6186, dn_6187_6188, dn_6189_6190, dn_6191_6192, dn_6217_6218, dn_6219_6220, dn_6221_6222, dn_6223_6224, dn_6225_6226, dn_6227_6228, dn_6253_6254, dn_6255_6256, dn_6257_6258, dn_6259_6260, dn_6261_6262, dn_6263_6264, dn_6265_6266, dn_6267_6268, dn_6269_6270, dn_6271_6272, dn_6273_6274, dn_6275_6276
15 | ->Vector Hash Left Join (16, 26) (cost=916197.12..6497887.18 rows=109588334 width=1548) (actual time=[1087.808,37867.443]..[1126.501,344054.415], rows=90568871)
| Hash Cond: (t.create_employee_id = t5.employee_id)
| Join Filter: ((t.create_date > t5.scd_active_begin_date) AND (t.create_date <= t5.scd_active_end_date))
| Rows Removed by Join Filter: 346831887
| Skew Join Optimized by Statistic
Max Memory Used : 24495KB
Min Memory Used : 24088KB
16 | ->Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN) (cost=902975.27..6276929.99 rows=99625758 distinct=25.00 width=1540) (actual time=[966.899,32828.811]..[1000.207,342188.371], rows=90568871)
| Distribute Key: t.create_employee_id
| Skew Filter(type: ROUNDROBIN): ((t.create_employee_id = (-999999)::numeric) OR (t.create_employee_id = (-999998)::numeric) OR (t.create_employee_id = 1100011722256::numeric) OR (t.create_employee_id = 3600023077206::numeric) OR (t.create_employee_id = 3600012434881::numeric) OR (t.create_employee_id = 3600012395770::numeric) OR (t.create_employee_id = 1100011926419::numeric) OR (t.create_employee_id = 3600012506305::numeric))
| Spawn on: (LC_DW1) dn_6049_6050, dn_6051_6052, dn_6053_6054, dn_6055_6056, dn_6057_6058, dn_6059_6060, dn_6085_6086, dn_6087_6088, dn_6089_6090, dn_6091_6092, dn_6093_6094, dn_6095_6096, dn_6133_6134, dn_6135_6136, dn_6137_6138, dn_6139_6140, dn_6141_6142, dn_6143_6144, dn_6145_6146, dn_6147_6148, dn_6149_6150, dn_6151_6152, dn_6153_6154, dn_6155_6156, dn_6181_6182, dn_6183_6184, dn_6185_6186, dn_6187_6188, dn_6189_6190, dn_6191_6192, dn_6217_6218, dn_6219_6220, dn_6221_6222, dn_6223_6224, dn_6225_6226, dn_6227_6228, dn_6253_6254, dn_6255_6256, dn_6257_6258, dn_6259_6260, dn_6261_6262, dn_6263_6264, dn_6265_6266, dn_6267_6268, dn_6269_6270, dn_6271_6272, dn_6273_6274, dn_6275_6276
17 | ->Vector Hash Left Join (18, 24) (cost=902975.27..4507534.69 rows=99625758 width=1540) (actual time=[1083.229,14636.814]..[1105.600,21496.719], rows=90568871)
| Hash Cond: (t.contract_id = t1.contract_id)
| Join Filter: ((t.create_date > t1.scd_active_begin_date) AND (t.create_date <= t1.scd_active_end_date))
| Rows Removed by Join Filter: 85901
| Skew Join Optimized by Statistic
Max Memory Used : 276283KB
Min Memory Used : 274324KB
18 | ->Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN) (cost=53608.03..3150606.00 rows=90568871 distinct=1.00 width=1529) (actual time=[2.962,12025.407]..[132.938,19271.441], rows=90568871)
| Distribute Key: t.contract_id
| Skew Filter(type: ROUNDROBIN): (t.contract_id = (-999998)::numeric)
| Spawn on: (LC_DW1) dn_6049_6050, dn_6051_6052, dn_6053_6054, dn_6055_6056, dn_6057_6058, dn_6059_6060, dn_6085_6086, dn_6087_6088, dn_6089_6090, dn_6091_6092, dn_6093_6094, dn_6095_6096, dn_6133_6134, dn_6135_6136, dn_6137_6138, dn_6139_6140, dn_6141_6142, dn_6143_6144, dn_6145_6146, dn_6147_6148, dn_6149_6150, dn_6151_6152, dn_6153_6154, dn_6155_6156, dn_6181_6182, dn_6183_6184, dn_6185_6186, dn_6187_6188, dn_6189_6190, dn_6191_6192, dn_6217_6218, dn_6219_6220, dn_6221_6222, dn_6223_6224, dn_6225_6226, dn_6227_6228, dn_6253_6254, dn_6255_6256, dn_6257_6258, dn_6259_6260, dn_6261_6262, dn_6263_6264, dn_6265_6266, dn_6267_6268, dn_6269_6270, dn_6271_6272, dn_6273_6274, dn_6275_6276
19 | ->Vector Hash Left Join (20, 22) (cost=53608.03..1693013.60 rows=90568871 width=1529) (actual time=[237.730,8866.586]..[340582.474,345448.035], rows=90568871)
| Hash Cond: (t.item_id = t7.item_id)
| Skew Join Optimized by Statistic
Max File Num: 32
Min File Num: 0
20 | ->Vector Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN) (cost=0.00..1613463.25 rows=90568871 distinct=31321.00 width=1512) (actual time=[2.002,7126.060]..[55.370,335585.193], rows=90568871)
| Distribute Key: t.item_id
| Skew Filter(type: ROUNDROBIN): (t.item_id = (-999999)::numeric)
| Spawn on: (LC_DW1) dn_6049_6050, dn_6051_6052, dn_6053_6054, dn_6055_6056, dn_6057_6058, dn_6059_6060, dn_6085_6086, dn_6087_6088, dn_6089_6090, dn_6091_6092, dn_6093_6094, dn_6095_6096, dn_6133_6134, dn_6135_6136, dn_6137_6138, dn_6139_6140, dn_6141_6142, dn_6143_6144, dn_6145_6146, dn_6147_6148, dn_6149_6150, dn_6151_6152, dn_6153_6154, dn_6155_6156, dn_6181_6182, dn_6183_6184, dn_6185_6186, dn_6187_6188, dn_6189_6190, dn_6191_6192, dn_6217_6218, dn_6219_6220, dn_6221_6222, dn_6223_6224, dn_6225_6226, dn_6227_6228, dn_6253_6254, dn_6255_6256, dn_6257_6258, dn_6259_6260, dn_6261_6262, dn_6263_6264, dn_6265_6266, dn_6267_6268, dn_6269_6270, dn_6271_6272, dn_6273_6274, dn_6275_6276
21 | ->CStore Scan on fin_dwb_pro.dwb_pro_ep_bpa_analysis_tmp t (cost=0.00..155870.85 rows=90568871 width=1512) (actual time=[1.732,1869.514]..[73.136,2622.847], rows=90568871)
22 | ->Vector Streaming(type: PART LOCAL PART BROADCAST) (cost=0.00..49584.77 rows=15449322 distinct=321861.00 width=29) (actual time=[0.301,124.500]..[14.864,166.537], rows=15449369)
| Skew Filter(type: BROADCAST): (t7.item_id = (-999999)::numeric)
| Spawn on: (LC_DW1) dn_6049_6050, dn_6051_6052, dn_6053_6054, dn_6055_6056, dn_6057_6058, dn_6059_6060, dn_6085_6086, dn_6087_6088, dn_6089_6090, dn_6091_6092, dn_6093_6094, dn_6095_6096, dn_6133_6134, dn_6135_6136, dn_6137_6138, dn_6139_6140, dn_6141_6142, dn_6143_6144, dn_6145_6146, dn_6147_6148, dn_6149_6150, dn_6151_6152, dn_6153_6154, dn_6155_6156, dn_6181_6182, dn_6183_6184, dn_6185_6186, dn_6187_6188, dn_6189_6190, dn_6191_6192, dn_6217_6218, dn_6219_6220, dn_6221_6222, dn_6223_6224, dn_6225_6226, dn_6227_6228, dn_6253_6254, dn_6255_6256, dn_6257_6258, dn_6259_6260, dn_6261_6262, dn_6263_6264, dn_6265_6266, dn_6267_6268, dn_6269_6270, dn_6271_6272, dn_6273_6274, dn_6275_6276
23 | ->CStore Scan on dwrdim_dw1.dwr_dim_material_code_d t7 (cost=0.00..35704.51 rows=15449322 width=29) (actual time=[1.805,22.649]..[5.004,49.338], rows=15449322)
| Distribute Key: t7.item_id
| Filter: ((t7.del_flag)::text = 'N'::text)
| Pushdown Predicate Filter: ((t7.del_flag)::text = 'N'::text)
24 | ->Vector Streaming(type: PART REDISTRIBUTE PART BROADCAST) (cost=0.00..823554.27 rows=99121801 distinct=398106.00 width=40) (actual time=[0.190,489.568]..[0.459,615.218], rows=99121848)
| Distribute Key: t1.contract_id
| Skew Filter(type: BROADCAST): (t1.contract_id = (-999998)::numeric)
| Spawn on: (LC_DW1) dn_6049_6050, dn_6051_6052, dn_6053_6054, dn_6055_6056, dn_6057_6058, dn_6059_6060, dn_6085_6086, dn_6087_6088, dn_6089_6090, dn_6091_6092, dn_6093_6094, dn_6095_6096, dn_6133_6134, dn_6135_6136, dn_6137_6138, dn_6139_6140, dn_6141_6142, dn_6143_6144, dn_6145_6146, dn_6147_6148, dn_6149_6150, dn_6151_6152, dn_6153_6154, dn_6155_6156, dn_6181_6182, dn_6183_6184, dn_6185_6186, dn_6187_6188, dn_6189_6190, dn_6191_6192, dn_6217_6218, dn_6219_6220, dn_6221_6222, dn_6223_6224, dn_6225_6226, dn_6227_6228, dn_6253_6254, dn_6255_6256, dn_6257_6258, dn_6259_6260, dn_6261_6262, dn_6263_6264, dn_6265_6266, dn_6267_6268, dn_6269_6270, dn_6271_6272, dn_6273_6274, dn_6275_6276
25 | ->CStore Scan on dwrdim_dw1.dwr_dim_contract_d t1 (cost=0.00..648026.04 rows=99121801 width=40) (actual time=[0.323,109.245]..[0.993,164.938], rows=99121801)
| Distribute Key: t1.contract_key
26 | ->Vector Streaming(type: PART REDISTRIBUTE PART BROADCAST) (cost=0.00..44963.47 rows=7606966 distinct=30516.00 width=44) (actual time=[0.200,47.883]..[0.627,95.708], rows=7608141)
| Distribute Key: t5.employee_id
| Skew Filter(type: BROADCAST): ((t5.employee_id = (-999999)::numeric) OR (t5.employee_id = (-999998)::numeric) OR (t5.employee_id = 1100011722256::numeric) OR (t5.employee_id = 3600023077206::numeric) OR (t5.employee_id = 3600012434881::numeric) OR (t5.employee_id = 3600012395770::numeric) OR (t5.employee_id = 1100011926419::numeric) OR (t5.employee_id = 3600012506305::numeric))
| Spawn on: (LC_DW1) dn_6049_6050, dn_6051_6052, dn_6053_6054, dn_6055_6056, dn_6057_6058, dn_6059_6060, dn_6085_6086, dn_6087_6088, dn_6089_6090, dn_6091_6092, dn_6093_6094, dn_6095_6096, dn_6133_6134, dn_6135_6136, dn_6137_6138, dn_6139_6140, dn_6141_6142, dn_6143_6144, dn_6145_6146, dn_6147_6148, dn_6149_6150, dn_6151_6152, dn_6153_6154, dn_6155_6156, dn_6181_6182, dn_6183_6184, dn_6185_6186, dn_6187_6188, dn_6189_6190, dn_6191_6192, dn_6217_6218, dn_6219_6220, dn_6221_6222, dn_6223_6224, dn_6225_6226, dn_6227_6228, dn_6253_6254, dn_6255_6256, dn_6257_6258, dn_6259_6260, dn_6261_6262, dn_6263_6264, dn_6265_6266, dn_6267_6268, dn_6269_6270, dn_6271_6272, dn_6273_6274, dn_6275_6276
27 | ->CStore Scan on dwrdim_dw1.dwr_dim_employee_d t5 (cost=0.00..28719.48 rows=7606966 width=44) (actual time=[0.140,6.417]..[0.416,16.607], rows=7606966)
| Distribute Key: t5.employee_key
28 | ->Vector Streaming(type: PART REDISTRIBUTE PART BROADCAST) (cost=0.00..42586.30 rows=7606966 distinct=30516.00 width=44) (actual time=[0.207,53.357]..[0.402,98.422], rows=7607389)
| Distribute Key: t4.employee_id
| Skew Filter(type: BROADCAST): ((t4.employee_id = (-999998)::numeric) OR (t4.employee_id = 1100011722256::numeric))
| Spawn on: (LC_DW1) dn_6049_6050, dn_6051_6052, dn_6053_6054, dn_6055_6056, dn_6057_6058, dn_6059_6060, dn_6085_6086, dn_6087_6088, dn_6089_6090, dn_6091_6092, dn_6093_6094, dn_6095_6096, dn_6133_6134, dn_6135_6136, dn_6137_6138, dn_6139_6140, dn_6141_6142, dn_6143_6144, dn_6145_6146, dn_6147_6148, dn_6149_6150, dn_6151_6152, dn_6153_6154, dn_6155_6156, dn_6181_6182, dn_6183_6184, dn_6185_6186, dn_6187_6188, dn_6189_6190, dn_6191_6192, dn_6217_6218, dn_6219_6220, dn_6221_6222, dn_6223_6224, dn_6225_6226, dn_6227_6228, dn_6253_6254, dn_6255_6256, dn_6257_6258, dn_6259_6260, dn_6261_6262, dn_6263_6264, dn_6265_6266, dn_6267_6268, dn_6269_6270, dn_6271_6272, dn_6273_6274, dn_6275_6276
29 | ->CStore Scan on dwrdim_dw1.dwr_dim_employee_d t4 (cost=0.00..28719.48 rows=7606966 width=44) (actual time=[0.140,6.230]..[0.310,16.281], rows=7606966)
| Distribute Key: t4.employee_key
30 | ->Vector Streaming(type: PART REDISTRIBUTE PART BROADCAST) (cost=0.00..25032.19 rows=5506269 distinct=30158.00 width=44) (actual time=[0.199,43.236]..[0.358,2362.927], rows=5506880)
| Distribute Key: t3.supplier_id
| Skew Filter(type: BROADCAST): ((t3.supplier_id = 10011176698::numeric) OR (t3.supplier_id = 10011176686::numeric))
| Spawn on: (LC_DW1) dn_6049_6050, dn_6051_6052, dn_6053_6054, dn_6055_6056, dn_6057_6058, dn_6059_6060, dn_6085_6086, dn_6087_6088, dn_6089_6090, dn_6091_6092, dn_6093_6094, dn_6095_6096, dn_6133_6134, dn_6135_6136, dn_6137_6138, dn_6139_6140, dn_6141_6142, dn_6143_6144, dn_6145_6146, dn_6147_6148, dn_6149_6150, dn_6151_6152, dn_6153_6154, dn_6155_6156, dn_6181_6182, dn_6183_6184, dn_6185_6186, dn_6187_6188, dn_6189_6190, dn_6191_6192, dn_6217_6218, dn_6219_6220, dn_6221_6222, dn_6223_6224, dn_6225_6226, dn_6227_6228, dn_6253_6254, dn_6255_6256, dn_6257_6258, dn_6259_6260, dn_6261_6262, dn_6263_6264, dn_6265_6266, dn_6267_6268, dn_6269_6270, dn_6271_6272, dn_6273_6274, dn_6275_6276
31 | ->CStore Scan on dwrdim_dw1.dwr_dim_supplier_d t3 (cost=0.00..14994.71 rows=5506269 width=44) (actual time=[2.097,7.887]..[7.258,17.175], rows=5506269)
| Distribute Key: t3.supplier_key
32 | ->CStore Scan on dwrdim_dw1.dwr_dim_region_rc_d t6 (cost=0.00..6912.91 rows=331488 distinct=465.00 width=40) (actual time=[0.266,0.806]..[2.009,2.719], rows=331488)
33 | ->CStore Scan on dwrdim_dw1.dwr_dim_ou_d t2 (cost=0.00..800.11 rows=101088 distinct=401.00 width=47) (actual time=[0.199,0.312]..[0.347,0.573], rows=101088)
34 | ->CStore Scan on dwrdim_dw1.dwr_dim_pur_item_category_d t8 (cost=0.00..8628.63 rows=8238096 distinct=160273.00 width=40) (actual time=[0.937,9.451]..[235.083,2298.600], rows=8238096)
35 | ->Vector Streaming(type: BROADCAST) (cost=0.00..684726.46 rows=98773488 distinct=2057781.00 width=28) (actual time=[0.501,104.548]..[88.117,214.225], rows=98773488)
| Spawn on: (LC_DW1) dn_6049_6050, dn_6051_6052, dn_6053_6054, dn_6055_6056, dn_6057_6058, dn_6059_6060, dn_6085_6086, dn_6087_6088, dn_6089_6090, dn_6091_6092, dn_6093_6094, dn_6095_6096, dn_6133_6134, dn_6135_6136, dn_6137_6138, dn_6139_6140, dn_6141_6142, dn_6143_6144, dn_6145_6146, dn_6147_6148, dn_6149_6150, dn_6151_6152, dn_6153_6154, dn_6155_6156, dn_6181_6182, dn_6183_6184, dn_6185_6186, dn_6187_6188, dn_6189_6190, dn_6191_6192, dn_6217_6218, dn_6219_6220, dn_6221_6222, dn_6223_6224, dn_6225_6226, dn_6227_6228, dn_6253_6254, dn_6255_6256, dn_6257_6258, dn_6259_6260, dn_6261_6262, dn_6263_6264, dn_6265_6266, dn_6267_6268, dn_6269_6270, dn_6271_6272, dn_6273_6274, dn_6275_6276
36 | ->CStore Scan on dwimd_dw1.dwi_md_class t9 (cost=0.00..1383.87 rows=2057781 width=28) (actual time=[1.131,1.853]..[3.674,5.038], rows=2057781)
| Distribute Key: t9.class_id
37 | ->Vector Partition Iterator (cost=0.00..235504.55 rows=102554546 distinct=1089830.00 width=805) (actual time=[176.368,161078.641]..[4211.289,279179.837], rows=102554546)
| Iterations: 12
38 | ->Partitioned CStore Scan on fin_dwb_pro.dwb_pro_ep_bpa_analysis_f t (cost=0.00..235504.55 rows=102554546 width=805) (actual time=[43317.779,160995.034]..[70998.801,279009.407], rows=102554546)
| Distribute Key: t.po_line_id
| Partitions Selected by Static Prune: 1..12从语句中看到耗时主要在表fin_dwb_pro.dwb_pro_ep_bpa_analysis_f的Merge动作上，分析Merge的详细构成
Merge Inserted: 761
Merge Updated: 90568110
发现大部分是UPDATE动作
【优化建议】
把MERGE语句拆分为UPDATE和INSERT两个部分的结果集独立往目标表插入
1) MERGE语句的USING部分中间结果集落中间表-- 耗时46s
CREATE TEMP TABLE x WITH(orientation=column) TO GROUP "LC_DW1"
AS SELECT
T.SUPPLIER_SITE_ID, T.BPA_ID, T.BPA_NUM, T.PO_ID, T.PO_TYPE_ID, T.ENABLE_FLAG, T.SHIP_TO_LOCATION, T.BILL_TO_LOCATION, T.CURRENCY_CODE AS CONTRACT_CURRENCY_CODE, T.PAY_CURRENCY_CODE, T.BPA_AMT, T.APPR_STATUS, T.APPROVE_DATE AS BPA_APPROVE_DATE, T.SUPPLIER_REMARK, T.REMARK AS BPA_REMARK, T.PAYMENT_TERMS, T.HW_CONTRACT_NUM, T.ERP_INSTANCE_ID, T.ACCEPT_TERMS, T.APPROVE_EMPLOYEE_ID, T.CHANGE_STATUS, T.AGGREMENT_TYPE, T.USE_SCOPE, T.CELL_NAME, T.PAYMENT_BACK_TO_BACK_FLAG, T.ACCEPT_BACK_TO_BACK_FLAG, T.PRE_PAYMENT_GUARANTEE_FLAG, T.PERFORMANCE_GUARANTEE_FLAG, T.SPECIAL_TAX_INFO, T.CREATE_DATE, T.ENABLE_DATE, T.DISABLE_DATE, T.AGREEMENT_NUMBER, T.SUBMIT_DATE, T.PAYMENT_TERM_TYPE_ID, T.EDITION_NUM, T.CONTRACT_BEGIN_DATE, T.CONTRACT_END_DATE, T.STATUS_LOOKUP_CODE, T.PROCESS_FLAG, T.IBUY_STATUS, T.IBUY_BPA_NUM, T.IBUY_CREATE_DATE, T.SHIP_TO_LOCATION_ID, T.REGION, T.RATE_TYPE, T.RATE_DATE, T.RATE, T.SS_ID, T.PO_H_CANCELED_FLAG, T.PO_H_CREATE_DATE, T.GLOBAL_AGREEMENT_FLAG, T.PO_LINE_ID, T.PO_L_CREATE_DATE, T.UNIT_PRICE, T.ITEM_DESCRIPTION, T.DEL_FLAG, T1.CONTRACT_KEY, T2.COMPANY_KEY, T2.OU_KEY, T3.SUPPLIER_KEY, T4.EMPLOYEE_KEY CEG_BUYER_KEY, T5.EMPLOYEE_KEY CREATE_KEY, T6.GEO_PC_KEY, T7.ITEM_CODE, T8.PUR_ITEM_CATG_KEY, T9.CODE MEASURE_UNIT
FROM fin_dwb_pro.DWB_PRO_EP_BPA_ANALYSIS_TMP T
LEFT JOIN DWRDIM_DW1.DWR_DIM_CONTRACT_D T1 ON T.CONTRACT_ID = T1.CONTRACT_ID AND T.CREATE_DATE > T1.SCD_ACTIVE_BEGIN_DATE AND T.CREATE_DATE <= T1.SCD_ACTIVE_END_DATE
LEFT JOIN DWRDIM_DW1.DWR_DIM_OU_D T2 ON T.OU_ID = T2.OU_ID AND T.CREATE_DATE > T2.SCD_ACTIVE_BEGIN_DATE AND T.CREATE_DATE <= T2.SCD_ACTIVE_END_DATE
LEFT JOIN DWRDIM_DW1.DWR_DIM_SUPPLIER_D T3 ON T.SUPPLIER_ID = T3.SUPPLIER_ID AND T.CREATE_DATE > T3.SCD_ACTIVE_BEGIN_DATE AND T.CREATE_DATE <= T3.SCD_ACTIVE_END_DATE
LEFT JOIN DWRDIM_DW1.DWR_DIM_EMPLOYEE_D T4 ON T.CEG_BUYER_EMPLOYEE_ID = T4.EMPLOYEE_ID AND T.CREATE_DATE > T4.SCD_ACTIVE_BEGIN_DATE AND T.CREATE_DATE <= T4.SCD_ACTIVE_END_DATE
LEFT JOIN DWRDIM_DW1.DWR_DIM_EMPLOYEE_D T5 ON T.CREATE_EMPLOYEE_ID = T5.EMPLOYEE_ID AND T.CREATE_DATE > T5.SCD_ACTIVE_BEGIN_DATE AND T.CREATE_DATE <= T5.SCD_ACTIVE_END_DATE
LEFT JOIN DWRDIM_DW1.DWR_DIM_REGION_RC_D T6 ON T.GEO_PC_ID = T6.GEO_PC_ID AND T.CREATE_DATE > T6.SCD_ACTIVE_BEGIN_DATE AND T.CREATE_DATE <= T6.SCD_ACTIVE_END_DATE
LEFT JOIN DWRDIM_DW1.DWR_DIM_MATERIAL_CODE_D T7 ON T.ITEM_ID = T7.ITEM_ID AND T7.DEL_FLAG = 'N'
LEFT JOIN DWRDIM_DW1.DWR_DIM_PUR_ITEM_CATEGORY_D T8 ON T.ITEM_CATEGORY_ID = T8.PUR_ITEM_CATG_ID AND T.CREATE_DATE > T8.SCD_ACTIVE_BEGIN_DATE AND T.CREATE_DATE <= T8.SCD_ACTIVE_END_DATE
LEFT JOIN dwimd_dw1.DWI_MD_CLASS T9 ON T.MEASURE_UNIT_ID = T9.CLASS_ID
;2) 创建和MERGE目标表相同结构的中间表 --耗时1s
CREATE TEMP TABLE y(like fin_dwb_pro.dwb_pro_ep_bpa_analysis_f including all);3）把MERGE逻辑等待改写的结果写入临时表pg_temp.y中 --耗时309s
改写逻辑中写入临时表pg_temp.y的数据包括三部分
1. USING部分结果集跟MERGE目标表MATCHED部分
2. USING部分结果集跟MERGE目标表NOT MATCHED部分
3. MERGE目标表和USING部分结果集没有MATCHED部分
INSERT INTO pg_temp.y (accept_back_to_back_flag,accept_terms,aggrement_type,agreement_number,appr_status,approve_employee_id,bill_to_location,bpa_amt,bpa_approve_date,bpa_id,bpa_num,bpa_remark,ceg_buyer_key,cell_name,change_status,company_key,contract_begin_date,contract_currency_code,contract_end_date,contract_key,create_date,create_key,del_flag,disable_date,edition_num,enable_date,enable_flag,erp_instance_id,geo_pc_key,global_agreement_flag,hw_contract_num,ibuy_bpa_num,ibuy_create_date,ibuy_status,item_code,item_description,measure_unit,ou_key,pay_currency_code,payment_back_to_back_flag,payment_term_type_id,payment_terms,performance_guarantee_flag,po_h_canceled_flag,po_h_create_date,po_id,po_l_create_date,po_line_id,po_type_id,pre_payment_guarantee_flag,process_flag,pur_item_catg_key,rate,rate_date,rate_type,region,ship_to_location,ship_to_location_id,special_tax_info,ss_id,status_lookup_code,submit_date,supplier_key,supplier_remark,supplier_site_id,unit_price,use_scope)
-- 1. USING部分结果集跟MERGE目标表MATCHED部分
SELECT
S.accept_back_to_back_flag,S.accept_terms,S.aggrement_type,S.agreement_number,S.appr_status,S.approve_employee_id,S.bill_to_location,S.bpa_amt,S.bpa_approve_date,S.bpa_id,S.bpa_num,S.bpa_remark,S.ceg_buyer_key,S.cell_name,S.change_status,S.company_key,S.contract_begin_date,S.contract_currency_code,S.contract_end_date,S.contract_key,S.create_date,S.create_key,S.del_flag,S.disable_date,S.edition_num,S.enable_date,S.enable_flag,S.erp_instance_id,S.geo_pc_key,S.global_agreement_flag,S.hw_contract_num,S.ibuy_bpa_num,S.ibuy_create_date,S.ibuy_status,S.item_code,S.item_description,S.measure_unit,S.ou_key,S.pay_currency_code,S.payment_back_to_back_flag,S.payment_term_type_id,S.payment_terms,S.performance_guarantee_flag,S.po_h_canceled_flag,S.po_h_create_date,S.po_id,S.po_l_create_date,S.po_line_id,S.po_type_id,S.pre_payment_guarantee_flag,S.process_flag,S.pur_item_catg_key,S.rate,S.rate_date,S.rate_type,S.region,S.ship_to_location,S.ship_to_location_id,S.special_tax_info,S.ss_id,s.status_lookup_code,S.submit_date,S.supplier_key,S.supplier_remark,S.supplier_site_id,S.unit_price,S.use_scope
FROM pg_temp.x s
INNER JOIN fin_dwb_pro.dwb_pro_ep_bpa_analysis_f T ON (T.PO_LINE_ID = S.PO_LINE_ID AND T.BPA_ID = S.BPA_ID)
UNION ALL
-- 2. USING部分结果集跟MERGE目标表NOT MATCHED部分
SELECT
S.accept_back_to_back_flag,S.accept_terms,S.aggrement_type,S.agreement_number,S.appr_status,S.approve_employee_id,S.bill_to_location,S.bpa_amt,S.bpa_approve_date,S.bpa_id,S.bpa_num,S.bpa_remark,S.ceg_buyer_key,S.cell_name,S.change_status,S.company_key,S.contract_begin_date,S.contract_currency_code,S.contract_end_date,S.contract_key,S.create_date,S.create_key,S.del_flag,S.disable_date,S.edition_num,S.enable_date,S.enable_flag,S.erp_instance_id,S.geo_pc_key,S.global_agreement_flag,S.hw_contract_num,S.ibuy_bpa_num,S.ibuy_create_date,S.ibuy_status,S.item_code,S.item_description,S.measure_unit,S.ou_key,S.pay_currency_code,S.payment_back_to_back_flag,S.payment_term_type_id,S.payment_terms,S.performance_guarantee_flag,S.po_h_canceled_flag,S.po_h_create_date,S.po_id,S.po_l_create_date,S.po_line_id,S.po_type_id,S.pre_payment_guarantee_flag,S.process_flag,S.pur_item_catg_key,S.rate,S.rate_date,S.rate_type,S.region,S.ship_to_location,S.ship_to_location_id,S.special_tax_info,S.ss_id,S.status_lookup_code,S.submit_date,S.supplier_key,S.supplier_remark,S.supplier_site_id,S.unit_price,S.use_scope
FROM pg_temp.x s
LEFT JOIN fin_dwb_pro.dwb_pro_ep_bpa_analysis_f T ON (T.po_line_id = S.po_line_id AND T.bpa_id = S.bpa_id)
WHERE t.po_line_id IS NULL AND t.bpa_id IS NULL
UNION ALL
-- 3. MERGE目标表和USING部分结果集没有MATCHED部分
SELECT
T.accept_back_to_back_flag,T.accept_terms,T.aggrement_type,T.agreement_number,T.appr_status,T.approve_employee_id,T.bill_to_location,T.bpa_amt,T.bpa_approve_date,T.bpa_id,T.bpa_num,T.bpa_remark,T.ceg_buyer_key,T.cell_name,T.change_status,T.company_key,T.contract_begin_date,T.contract_currency_code,T.contract_end_date,T.contract_key,T.create_date,T.create_key,T.del_flag,T.disable_date,T.edition_num,T.enable_date,T.enable_flag,T.erp_instance_id,T.geo_pc_key,T.global_agreement_flag,T.hw_contract_num,T.ibuy_bpa_num,T.ibuy_create_date,T.ibuy_status,T.item_code,T.item_description,T.measure_unit,T.ou_key,T.pay_currency_code,T.payment_back_to_back_flag,T.payment_term_type_id,T.payment_terms,T.performance_guarantee_flag,T.po_h_canceled_flag,T.po_h_create_date,T.po_id,T.po_l_create_date,T.po_line_id,T.po_type_id,T.pre_payment_guarantee_flag,T.process_flag,T.pur_item_catg_key,T.rate,T.rate_date,T.rate_type,T.region,T.ship_to_location,T.ship_to_location_id,T.special_tax_info,T.ss_id,T.status_lookup_code,T.submit_date,T.supplier_key,T.supplier_remark,T.supplier_site_id,T.unit_price,T.use_scope
FROM fin_dwb_pro.dwb_pro_ep_bpa_analysis_f T
WHERE (T.po_line_id IS NULL OR T.bpa_id IS NULL)
OR NOT EXISTS (SELECT 1 FROM pg_temp.x S WHERE T.po_line_id = S.po_line_id AND T.bpa_id = S.bpa_id)
;4）刷新原表数据
START TRANSACTION；
TRUNCATE fin_dwb_pro.dwb_pro_ep_bpa_analysis_f;
INSERT INTO fin_dwb_pro.dwb_pro_ep_bpa_analysis_f SELECT * FROM pg_temp.y;
COMMIMT;
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）