數倉性能調優：大寬表關聯MERGE性能優化

摘要：本文主要為大家講解在數倉性能調優過程中，關於大寬表關聯MERGE性能優化過程。

本文分享自華為雲社區《GaussDB(DWS)性能調優：大寬表關聯MERGE性能優化》，作者：譡里個檔。

【業務背景】

如下MERGE語句執行耗時長達2034s

MERGE INTO sdifin.hah_ae_line_sr_t_02_8663 Event_1u18olr USING (
 WITH Event_1ix1dzn AS (
 SELECT
 "sr38","sr39","sr40","sr41","sr42","sr43","sr44","sr45","sr46","sr47",
 "sr48","sr49","sr50","sr51","sr52","sr53","sr54","sr55","sr56","sr57",
 "sr58","sr59","sr60","unit_code","created_by","creation_date",
 "last_updated_by","last_update_date","ss_id","del_flag","crt_cycle_id",
 "last_upd_cycle_id","crt_job_instance_id","upd_job_instance_id",
 "dq_improve_flag","last_modified_date","ae_header_id","ae_line_num",
 "application_code","sr1","sr2","sr3","sr4","sr5","sr6","sr7","sr8",
 "sr9","sr10","sr11","sr12","sr13","sr14","sr15","sr16","sr17",
 "sr18","sr19","sr20","sr21","sr22","sr23","sr24","sr25","sr26",
 "sr27","sr28","sr29","sr30","sr31","sr32","sr33","sr34","sr35",
 "sr36","sr37" 
 FROM stgfin.dlt_hah_ae_line_sr_t_02_8663
 ),
    Event_1u18olr AS (
 SELECT 
 "ae_header_id","ae_line_num","application_code","sr1","sr2","sr3",
 "sr4","sr5","sr6","sr7","sr8","sr9","sr10","sr11","sr12","sr13",
 "sr14","sr15","sr16","sr17","sr18","sr19","sr20","sr21","sr22",
 "sr23","sr24","sr25","sr26","sr27","sr28","sr29","sr30","sr31",
 "sr32","sr33","sr34","sr35","sr36","sr37","sr38","sr39","sr40",
 "sr41","sr42","sr43","sr44","sr45","sr46","sr47","sr48","sr49",
 "sr50","sr51","sr52","sr53","sr54","sr55","sr56","sr57","sr58",
 "sr59","sr60","unit_code","created_by","creation_date",
 "last_updated_by","last_update_date","ss_id","del_flag",
 "crt_cycle_id",20230520000000 AS "last_upd_cycle_id",
 -1 AS "crt_job_instance_id",-1 AS "upd_job_instance_id",
 'N' AS "dq_improve_flag",sysdate() AS "last_modified_date" 
 FROM Event_1ix1dzn
 )
 SELECT 
 "ae_header_id","ae_line_num","application_code","sr1","sr2","sr3",
 "sr4","sr5","sr6","sr7","sr8","sr9","sr10","sr11","sr12","sr13",
 "sr14","sr15","sr16","sr17","sr18","sr19","sr20","sr21","sr22",
 "sr23","sr24","sr25","sr26","sr27","sr28","sr29","sr30","sr31",
 "sr32","sr33","sr34","sr35","sr36","sr37","sr38","sr39","sr40",
 "sr41","sr42","sr43","sr44","sr45","sr46","sr47","sr48","sr49",
 "sr50","sr51","sr52","sr53","sr54","sr55","sr56","sr57","sr58",
 "sr59","sr60","unit_code","created_by","creation_date",
 "last_updated_by","last_update_date","ss_id","del_flag",
 "crt_cycle_id","last_upd_cycle_id","crt_job_instance_id",
 "upd_job_instance_id","dq_improve_flag","last_modified_date" 
 FROM Event_1u18olr
) Event_1ix1dzn ON (Event_1u18olr."ae_header_id" = Event_1ix1dzn."ae_header_id" AND Event_1u18olr."ae_line_num" = Event_1ix1dzn."ae_line_num") 
WHEN MATCHED THEN UPDATE SET "application_code" = Event_1ix1dzn."application_code",
 "sr1" = Event_1ix1dzn."sr1",
 "sr2" = Event_1ix1dzn."sr2",
 "sr3" = Event_1ix1dzn."sr3",
 "sr4" = Event_1ix1dzn."sr4",
 "sr5" = Event_1ix1dzn."sr5",
 "sr6" = Event_1ix1dzn."sr6",
 "sr7" = Event_1ix1dzn."sr7",
 "sr8" = Event_1ix1dzn."sr8",
 "sr9" = Event_1ix1dzn."sr9",
 "sr10" = Event_1ix1dzn."sr10",
 "sr11" = Event_1ix1dzn."sr11",
 "sr12" = Event_1ix1dzn."sr12",
 "sr13" = Event_1ix1dzn."sr13",
 "sr14" = Event_1ix1dzn."sr14",
 "sr15" = Event_1ix1dzn."sr15",
 "sr16" = Event_1ix1dzn."sr16",
 "sr17" = Event_1ix1dzn."sr17",
 "sr18" = Event_1ix1dzn."sr18",
 "sr19" = Event_1ix1dzn."sr19",
 "sr20" = Event_1ix1dzn."sr20",
 "sr21" = Event_1ix1dzn."sr21",
 "sr22" = Event_1ix1dzn."sr22",
 "sr23" = Event_1ix1dzn."sr23",
 "sr24" = Event_1ix1dzn."sr24",
 "sr25" = Event_1ix1dzn."sr25",
 "sr26" = Event_1ix1dzn."sr26",
 "sr27" = Event_1ix1dzn."sr27",
 "sr28" = Event_1ix1dzn."sr28",
 "sr29" = Event_1ix1dzn."sr29",
 "sr30" = Event_1ix1dzn."sr30",
 "sr31" = Event_1ix1dzn."sr31",
 "sr32" = Event_1ix1dzn."sr32",
 "sr33" = Event_1ix1dzn."sr33",
 "sr34" = Event_1ix1dzn."sr34",
 "sr35" = Event_1ix1dzn."sr35",
 "sr36" = Event_1ix1dzn."sr36",
 "sr37" = Event_1ix1dzn."sr37",
 "sr38" = Event_1ix1dzn."sr38",
 "sr39" = Event_1ix1dzn."sr39",
 "sr40" = Event_1ix1dzn."sr40",
 "sr41" = Event_1ix1dzn."sr41",
 "sr42" = Event_1ix1dzn."sr42",
 "sr43" = Event_1ix1dzn."sr43",
 "sr44" = Event_1ix1dzn."sr44",
 "sr45" = Event_1ix1dzn."sr45",
 "sr46" = Event_1ix1dzn."sr46",
 "sr47" = Event_1ix1dzn."sr47",
 "sr48" = Event_1ix1dzn."sr48",
 "sr49" = Event_1ix1dzn."sr49",
 "sr50" = Event_1ix1dzn."sr50",
 "sr51" = Event_1ix1dzn."sr51",
 "sr52" = Event_1ix1dzn."sr52",
 "sr53" = Event_1ix1dzn."sr53",
 "sr54" = Event_1ix1dzn."sr54",
 "sr55" = Event_1ix1dzn."sr55",
 "sr56" = Event_1ix1dzn."sr56",
 "sr57" = Event_1ix1dzn."sr57",
 "sr58" = Event_1ix1dzn."sr58",
 "sr59" = Event_1ix1dzn."sr59",
 "sr60" = Event_1ix1dzn."sr60",
 "unit_code" = Event_1ix1dzn."unit_code",
 "created_by" = Event_1ix1dzn."created_by",
 "creation_date" = Event_1ix1dzn."creation_date",
 "last_updated_by" = Event_1ix1dzn."last_updated_by",
 "last_update_date" = Event_1ix1dzn."last_update_date",
 "ss_id" = Event_1ix1dzn."ss_id",
 "del_flag" = Event_1ix1dzn."del_flag",
 "crt_cycle_id" = Event_1ix1dzn."crt_cycle_id",
 "last_upd_cycle_id" = 20230520000000,
 "crt_job_instance_id" = -1,
 "upd_job_instance_id" = -1,
 "dq_improve_flag" = 'N',
 "last_modified_date" = sysdate() 
WHEN NOT MATCHED THEN INSERT("ae_header_id","ae_line_num","application_code","sr1","sr2","sr3","sr4","sr5","sr6","sr7