GaussDB(DWS)性能调优：宽表加工优化方案

举报
譡里个檔 发表于 2023/07/06 10:02:32 2023/07/06
【摘要】 宽表加工性能优化

1. 业务背景

宽表加工性能慢，在Gauss(DWS)中可以使用DWS的轻量化更新的黑科技实现性能成倍提升

2. 原始逻辑

事实表和维表关联之后插入目标表 dm_cbg_ci_inv_dtl_w_f

INSERT INTO dm_cbg_ci_inv_dtl_w_f
SELECT
    F.PERIOD_ID,
    F.YYYY,
    F.MM,
    F.YYYYMM,
    F.YYYYQ,
    F.QTR_NO,
    F.SALES_LV0_PROD_LIST_CODE,
    F.SALES_LV0_PROD_LIST_CN_NAME,
    F.SALES_LV0_PROD_LIST_EN_NAME,
    F.SALES_LV1_PROD_LIST_CODE,
    F.SALES_LV1_PROD_LIST_CN_NAME,
    F.SALES_LV1_PROD_LIST_EN_NAME,
    F.SALES_LV2_PROD_LIST_CODE,
    F.SALES_LV2_PROD_LIST_CN_NAME,
    F.SALES_LV2_PROD_LIST_EN_NAME,
    F.SALES_LV3_PROD_LIST_CODE,
    F.SALES_LV3_PROD_LIST_CN_NAME,
    F.SALES_LV3_PROD_LIST_EN_NAME,
    F.SALES_LV4_PROD_LIST_CODE,
    F.SALES_LV4_PROD_LIST_CN_NAME,
    F.SALES_LV4_PROD_LIST_EN_NAME,
    F.SALES_LV5_PROD_LIST_CODE,
    F.SALES_LV5_PROD_LIST_EN_NAME,
    F.SALES_LV5_PROD_LIST_CN_NAME,
    F.SALES_PROD_CODE,
    F.SALES_PROD_EN_NAME,
    F.SALES_PROD_CN_NAME,
    F.SALES_COMPANY_BRAND,
    F.SALES_PROD_MKT_NAME,
    F.SALES_REPORT_TYPE_CN_NAME,
    F.SALES_REPORT_TYPE_CODE,
    F.SALES_REPORT_TYPE_EN_NAME,
    F.LV0_PROD_LIST_CODE,
    F.LV0_PROD_LIST_CN_NAME,
    F.LV0_PROD_LIST_EN_NAME,
    F.LV1_PROD_LIST_CODE,
    F.LV1_PROD_LIST_CN_NAME,
    F.LV1_PROD_LIST_EN_NAME,
    F.LV2_PROD_LIST_CODE,
    F.LV2_PROD_LIST_CN_NAME,
    F.LV2_PROD_LIST_EN_NAME,
    F.LV3_PROD_LIST_CODE,
    F.LV3_PROD_LIST_CN_NAME,
    F.LV3_PROD_LIST_EN_NAME,
    F.LV4_PROD_LIST_CODE,
    F.LV4_PROD_LIST_CN_NAME,
    F.LV4_PROD_LIST_EN_NAME,
    F.LV5_PROD_LIST_CODE,
    F.LV5_PROD_LIST_EN_NAME,
    F.LV5_PROD_LIST_CN_NAME,
    F.PROD_CODE,
    F.PROD_EN_NAME,
    F.PROD_CN_NAME,
    F.COMPANY_BRAND,
    F.PROD_MKT_NAME,
    F.REPORT_TYPE_CN_NAME,
    F.REPORT_TYPE_CODE,
    F.REPORT_TYPE_EN_NAME,
    F.PLATFORMCOMPANY_FLAG,
    F.INVENTORY_CLASS_SEQ_NUM,
    F.INVENTORY_CLASS_L1_CODE,
    F.INVENTORY_CLASS_L1_CN_NAME,
    F.INVENTORY_CLASS_L1_EN_NAME,
    F.INVENTORY_CLASS_L2_CODE,
    F.INVENTORY_CLASS_L2_CN_NAME,
    F.INVENTORY_CLASS_L2_EN_NAME,
    F.GLOBAL_CODE,
    F.GLOBAL_CN_NAME,
    F.GLOBAL_EN_NAME,
    F.AREA_CODE,
    F.AREA_CN_NAME,
    F.AREA_EN_NAME,
    F.REGION_CODE,
    F.REGION_CN_NAME,
    F.REGION_EN_NAME,
    F.REPOFFICE_CODE,
    F.REPOFFICE_CN_NAME,
    F.REPOFFICE_EN_NAME,
    F.OFFICE_CODE,
    F.OFFICE_CN_NAME,
    F.OFFICE_EN_NAME,
    F.GEO_PC_CODE,
    F.GEO_PC_CN_NAME,
    F.GEO_PC_EN_NAME,
    F.COUNTRY_CN_NAME,
    F.COUNTRY_EN_NAME,
    F.COUNTRY_CODE,
    F.OVERSEA_FLAG,
    F.COMPANY_EN_NAME,
    F.COMPANY_CN_NAME,
    F.COMPANY_CODE,
    F.COMPANY_DESC,
    F.LC_CODE,
    F.OVERSEA_CORP_FLAG,
    F.SYSTEM_INTER_COMPANY_STATUS,
    F.SUPPLY_CENTER_NEW_CODE,
    F.SUPPLY_CENTER_NEW_CN_NAME,
    F.SUPPLY_CENTER_NEW_EN_NAME,
    F.SUPPLY_CENTER_CODE,
    F.SUPPLY_CENTER_CN_NAME,
    F.SUPPLY_CENTER_EN_NAME,
    F.SUPPLY_GEO_PC_CODE,
    F.FULFIL_COMPANY_CODE,
    F.SIGN_COMPANY_CODE,
    F.SUPPLY_CENTER_TYPE_CODE,
    F.SUPPLY_CENTER_TYPE_CN_NAME,
    F.SUPPLY_CENTER_TYPE_EN_NAME,
    F.REPORT_ITEM_ID,
    F.SUB_ACCOUNT_CODE,
    F.GROUP_ACCOUNT_CODE,
    F.DATA_CATEGORY_ID,
    F.SCENARIO_ID,
    F.SCHEDULE_TYPE_ID,
    F.SUBJECT_AREA_ID,
    F.VERSION_ID,
    F.CIF_FLAG,
    F.CI_FLAG,
    F.COST_CATEGORY,
    F.SOURCE_FLAG,
    F.OVERDUE_INVENTORY_FLAG,
    F.RMB_AAA_QTD_AMT,
    F.RMB_AAP_QTD_AMT,
    F.USD_AAA_QTD_AMT,
    F.USD_AAP_QTD_AMT,
    F.RMB_AAA_PY_PTD_AMT,
    F.USD_AAA_PY_PTD_AMT,
    F.USD_AAP_PY_PTD_AMT,
    F.RMB_AAA_PY_QTD_AMT,
    F.RMB_AAP_PY_QTD_AMT,
    F.USD_AAA_PY_QTD_AMT,
    F.USD_AAP_PY_QTD_AMT,
    F.RMB_AAA_PY_YTD_AMT,
    F.RMB_AAP_PY_YTD_AMT,
    F.USD_AAA_PY_YTD_AMT,
    F.USD_AAP_PY_YTD_AMT,
    F.RMB_AAA_PY_ALL_QTD_AMT,
    F.RMB_AAP_PY_ALL_QTD_AMT,
    F.USD_AAA_PY_ALL_QTD_AMT,
    F.USD_AAP_PY_ALL_QTD_AMT,
    F.RMB_AAA_PY_ALL_YTD_AMT,
    F.RMB_AAP_PY_ALL_YTD_AMT,
    F.USD_AAA_PY_ALL_YTD_AMT,
    F.USD_AAP_PY_ALL_YTD_AMT,
    F.RMB_AAA_PP_PTD_AMT,
    F.RMB_AAP_PP_PTD_AMT,
    F.USD_AAA_PP_PTD_AMT,
    F.USD_AAP_PP_PTD_AMT,
    F.RMB_AAA_PP_QTD_AMT,
    F.RMB_AAP_PP_QTD_AMT,
    F.USD_AAA_PP_QTD_AMT,
    F.USD_AAP_PP_QTD_AMT,
    F.RMB_AAA_CY_OPEN_BAL_AMT,
    F.RMB_AAP_CY_OPEN_BAL_AMT,
    F.USD_AAA_CY_OPEN_BAL_AMT,
    F.USD_AAP_CY_OPEN_BAL_AMT,
    F.RMB_AAA_PY_END_BAL_AMT,
    F.RMB_AAP_PY_END_BAL_AMT,
    F.USD_AAA_PY_END_BAL_AMT,
    F.USD_AAP_PY_END_BAL_AMT,
    F.RMB_AAA_PQ_END_BAL_AMT,
    F.RMB_AAP_PQ_END_BAL_AMT,
    F.USD_AAA_PQ_END_BAL_AMT,
    F.USD_AAP_PQ_END_BAL_AMT,
    F.RMB_AAA_PTD_AMT,
    F.RMB_AAP_PTD_AMT,
    F.USD_AAA_PTD_AMT,
    F.USD_AAP_PTD_AMT,
    F.RMB_AAP_PY_PTD_AMT,
    F.RMB_AAA_YTD_AMT,
    F.RMB_AAP_YTD_AMT,
    F.USD_AAA_YTD_AMT,
    F.USD_AAP_YTD_AMT,
    F.RMB_AAA_END_BAL_AMT,
    F.RMB_AAP_END_BAL_AMT,
    F.USD_AAA_END_BAL_AMT,
    F.USD_AAP_END_BAL_AMT,
    F.RMB_AAA_YEAR_AVG_AMT,
    F.RMB_AAP_YEAR_AVG_AMT,
    F.USD_AAA_YEAR_AVG_AMT,
    F.USD_AAP_YEAR_AVG_AMT,
    F.RMB_AAA_PP_END_BAL_AMT,
    F.RMB_AAP_PP_END_BAL_AMT,
    F.USD_AAA_PP_END_BAL_AMT,
    F.USD_AAP_PP_END_BAL_AMT,
    F.RMB_AAA_LY_END_BAL_AMT,
    F.RMB_AAP_LY_END_BAL_AMT,
    F.USD_AAA_LY_END_BAL_AMT,
    F.USD_AAP_LY_END_BAL_AMT,
    F.RMB_AAA_PY_2POINT_YTD_AMT,
    F.RMB_AAP_PY_2POINT_YTD_AMT,
    F.USD_AAA_PY_2POINT_YTD_AMT,
    F.USD_AAP_PY_2POINT_YTD_AMT,
    F.RMB_AAA_2POINT_YTD_AMT,
    F.RMB_AAP_2POINT_YTD_AMT,
    F.USD_AAA_2POINT_YTD_AMT,
    F.USD_AAP_2POINT_YTD_AMT,
    F.BUSINESS_STATUS_CN_NAME,
    F.BUSINESS_STATUS_EN_NAME,
    F.BUSINESS_STATUS_CODE,
    F.LOCATION_L1_CODE,
    F.LOCATION_L1_CN_NAME,
    F.LOCATION_L1_EN_NAME,
    F.LOCATION_L2_CODE,
    F.LOCATION_L2_CN_NAME,
    F.LOCATION_L2_EN_NAME,
    F.LOCATION_L3_CODE,
    F.LOCATION_L3_CN_NAME,
    F.LOCATION_L3_EN_NAME,
    F.LOCATION_CODE,
    F.LOCATION_CN_NAME,
    F.LOCATION_EN_NAME,
    F.LOCATION_TYPE_CN_NAME,
    F.LOCATION_TYPE_EN_NAME,
    F.TOP_CUST_CATEGORY_CN_NAME,
    F.TOP_CUST_CATEGORY_CODE,
    F.TOP_CUST_CATEGORY_EN_NAME,
    F.ACCTCUST_HQ_CN_NAME,
    F.ACCTCUST_HQ_CODE,
    F.ACCTCUST_HQ_EN_NAME,
    F.ACCTCUST_BRANCH_CN_NAM,
    F.ACCTCUST_BRANCH_CODE,
    F.ACCTCUST_BRANCH_EN_NAM,
    F.ACCTCUST_LV2_BRANCH_CN_NAME,
    F.ACCTCUST_LV2_BRANCH_CODE,
    F.ACCTCUST_LV2_BRANCH_EN_NAME,
    F.ACCTCUST_SUBSIDIARY_CN_NAM,
    F.ACCTCUST_SUBSIDIARY_CODE,
    F.ACCTCUST_SUBSIDIARY_EN_NAM,
    F.BRANCH_CUSTCATG_CN_NAME,
    F.BRANCH_CUSTCATG_CODE,
    F.BRANCH_CUSTCATG_EN_NAME,
    F.CUST_ACCOUNT_NUM,
    F.CUST_CLASS_CN_NAME,
    F.CUST_CLASS_CODE,
    F.CUST_CLASS_EN_NAME,
    F.CUST_EN_NAME,
    F.CUST_NL_NAME,
    F.REGION_CUSTCATG_CN_NAME,
    F.REGION_CUSTCATG_CODE,
    F.REGION_CUSTCATG_EN_NAME,
    F.LV2_BRANCH_CUSTCATG_CN_NAME,
    F.LV2_BRANCH_CUSTCATG_CODE,
    F.LV2_BRANCH_CUSTCATG_EN_NAME,
    F.LVL2_CUST_CLASS_CN_NAME,
    F.LVL2_CUST_CLASS_EN_NAME,
    F.HISI_LV0_PROD_LIST_CODE,
    F.HISI_LV0_PROD_LIST_CN_NAME,
    F.HISI_LV0_PROD_LIST_EN_NAME,
    F.HISI_LV1_PROD_LIST_CODE,
    F.HISI_LV1_PROD_LIST_CN_NAME,
    F.HISI_LV1_PROD_LIST_EN_NAME,
    F.HISI_LV2_PROD_LIST_CODE,
    F.HISI_LV2_PROD_LIST_CN_NAME,
    F.HISI_LV2_PROD_LIST_EN_NAME,
    F.HISI_LV3_PROD_LIST_CODE,
    F.HISI_LV3_PROD_LIST_CN_NAME,
    F.HISI_LV3_PROD_LIST_EN_NAME,
    F.HISI_LV4_PROD_LIST_CODE,
    F.HISI_LV4_PROD_LIST_CN_NAME,
    F.HISI_LV4_PROD_LIST_EN_NAME,
    F.HISI_LV5_PROD_LIST_CODE,
    F.HISI_LV5_PROD_LIST_CN_NAME,
    F.HISI_LV5_PROD_LIST_EN_NAME,
    F.HISI_PROD_CODE,
    F.HISI_PROD_EN_NAME,
    F.HISI_PROD_CN_NAME,
    F.ENTERPRISE_CUST_KEY,
    F.ENTERPRISE_CUST_EN_NAME,
    F.ENTERPRISE_CUST_NL_NAME,
    F.ENTERPRISE_INDUSTRY_CLASS_CODE,
    F.ENTERPRISE_INDUSTRY_CLASS_CN_NAME,
    F.ENTERPRISE_INDUSTRY_CLASS_EN_NAME,
    F.ENTERPRISE_ENT_CUST_CLASS_CODE,
    F.ENTERPRISE_ENT_CUST_CLASS_CN_NAME,
    F.ENTERPRISE_ENT_CUST_CLASS_EN_NAME,
    F.PROJ_NUM,
    F.PROJ_EN_NAME,
    F.PROJ_CN_NAME,
    F.PROJ_GEO_PC_CN_NAME,
    F.PROJ_GEO_PC_EN_NAME,
    F.PROJGEO_PC_CN_NAME,
    F.PROJGEO_PC_EN_NAME,
    F.ACCOUNT_MANAGER_CN_NAME,
    F.ACCOUNT_MANAGER_CODE,
    F.ACCOUNT_MANAGER_EN_NAME,
    F.HW_CONTRACT_NUM,
    F.CUST_CONTRACT_NUM,
    F.CONTRACT_NAME,
    F.FRAMEWORK_CONTRACT_NUM,
    F.END_CUST_NAME,
    F.AGENT_DISTRIBUTION_CUST_NAME,
    F.SIGN_CUST_NAME,
    F.REGISTRATION_DATE,
    F.SALES_MODE_CN_NAME,
    F.SALES_MODE_CODE,
    F.SALES_MODE_EN_NAME,
    F.CONTRACT_FIRST_PUBLISH_DATE,
    F.CONTRACT_STATUS_CN_NAME,
    F.CONTRACT_STATUS_CODE,
    F.CONTRACT_STATUS_EN_NAME,
    F.CREATE_DATE,
    F.LOGIN_DATE,
    F.COMBIND_TO_SINGL_DATE,
    F.SIGN_DATE,
    F.SIGN_PERSON,
    F.ADVANCED_SALES_TO_SALES_DATE,
    F.WARRANTY_START_MSTNE_CN_NAME,
    F.WARRANTY_START_MSTNE_EN_NAME,
    F.WARRANTY_BEGIN_EXTEND_DUR,
    F.HARDWARE_WARRANTY_DURATION,
    F.HARDWARE_EXTEND_WARRANTY_DUR,
    F.SOFTWARE_EXTEND_WARRANTY_DUR,
    F.BRAZIL_TERMINAL_CONTRACT_FLAG,
    F.INTELNAL_OVERSEA_FLAG,
    F.CONTRACT_ID,
    F.CONTRACT_TOTAL_AMT,
    F.CONTRACT_TYPE_CN_NAME,
    F.CONTRACT_TYPE_CODE,
    F.CONTRACT_TYPE_EN_NAME,
    F.CONTRACT_MAIN_TYPE_CN_NAME,
    F.CONTRACT_MAIN_TYPE_CODE,
    F.CONTRACT_MAIN_TYPE_EN_NAME,
    F.CONTRACT_SUB_TYPE_CN_NAME,
    F.CONTRACT_SUB_TYPE_CODE,
    F.CONTRACT_SUB_TYPE_EN_NAME,
    F.CONTRACT_MAIN_PROD_TYPE_CODE,
    F.CTRCT_MAIN_PROD_TYPE_EN_NAME,
    F.CTRCT_MAIN_PROD_TYPE_CN_NAME,
    F.CTRCT_BUSINESS_TYPE_EN_NAME,
    F.CTRCT_BUSINESS_TYPE_CN_NAME,
    F.CONTRACT_BUSINESS_TYPE_CODE,
    F.NEW_MOVE_TYPE_CN_NAME,
    F.NEW_MOVE_TYPE_CODE,
    F.NEW_MOVE_TYPE_EN_NAME,
    F.ADD_EXPAND_TYPE_CN_NAME,
    F.ADD_EXPAND_TYPE_CODE,
    F.ADD_EXPAND_TYPE_EN_NAME,
    F.STOCK_INCREMENT_TYPE_CN_NAME,
    F.STOCK_INCREMENT_TYPE_CODE,
    F.STOCK_INCREMENT_TYPE_EN_NAME,
    F.CURRENCY_CODE,
    F.REVENUE_SHARING_CONTRACT_FLAG,
    F.S3_PILOT_FLAG,
    F.PO_LIST_FLAG,
    F.BENEFICIAL_ENTITY,
    F.SUB_ACCOUNT_EN_NAME,
    F.SUB_ACCOUNT_CN_NAME,
    F.INVENTORY_ORG_CODE,
    F.INVENTORY_ORG_NAME,
    F.SUBINV_BG_CN_NAME,
    F.SUBINV_BG_EN_NAME,
    F.SUBINV_TYPE_CN_NAME,
    F.SUBINV_TYPE_EN_NAME,
    F.SUBINVENTORY_NAME,
    F.GRP_ACCOUNT_CODE_EN_NAME,
    F.GRP_ACCOUNT_CODE_CN_NAME,
    F.JE_CATEGORY_CODE,
    F.JE_CATEGORY_EN_NAME,
    F.JE_CATEGORY_CN_NAME,
    F.JE_SOURCE_CODE,
    F.JE_SOURCE_EN_NAME,
    F.JE_SOURCE_CN_NAME,
    F.HIS_CN_NAME,
    F.HIS_EN_NAME,
    PAR.PAR_PROJ_CN_NAME,
    PAR.PAR_PROJ_NUM,
    PAR.PAR_PROJ_EN_NAME,
    PAR.SUB_PROJ_CN_NAME,
    PAR.SUB_PROJ_NUM,
    PAR.SUB_PROJ_EN_NAME,
    STK.PRODUCT_MANAGER_EMPLOYEE,
    STK.SALEPROJ_MANAGER_EMPLOYEE,
    AGD.INV_AGE_CN_NAME,
    AGD.INV_AGE_EN_NAME,
    AGD.BEGIN_DAY,
    AGD.END_DAY,
    AGD.INV_AGE_TYPE,
    PNT.CONFIRM_POINT_CODE,
    PNT.CONFIRM_POINT_CN_NAME,
    PNT.CONFIRM_POINT_EN_NAME,
    REC.RECOGNISE_TYPE_L1_CODE,
    REC.RECOGNISE_TYPE_L1_CN_NAME,
    REC.RECOGNISE_TYPE_L1_EN_NAME,
    REC.RECOGNISE_TYPE_L2_CODE,
    REC.RECOGNISE_TYPE_L2_CN_NAME,
    REC.RECOGNISE_TYPE_L2_EN_NAME,
    EMP.EMPLOYEE_NAME,
    F.CONTRACT_KEY,
    F.COMPANY_KEY,
    F.subinventory_key,
    F.business_status_key,
    F.je_source_id,
    F.je_category_id,
    F.prod_key,
    F.supply_center_key,
    F.sales_mode_key,
    F.proj_key,
    F.BG_CN_NAME,
    F.CONFIRM_POINT,
    F.CONFIRM_POINT_DELAY_DAYS,
    F.STOCK_CONTRACT_FLAG,
    F.STANDARD_CONTRACT_FLAG,
    F.SIGNED_REP_OFFICE_EN_NAME,
    F.SIGNED_REP_OFFICE_CODE,
    F.SIGNED_REP_OFFICE_CN_NAME,
    F.SIGNED_REGION_EN_NAME,
    F.SIGNED_REGION_CODE,
    F.SIGNED_REGION_CN_NAME,
    F.RES_CODE,
    F.INV_AGE,
    RCD.CBG_AREA_CODE,
    RCD.CBG_AREA_CN_NAME,
    RCD.CBG_AREA_EN_NAME,
    RCD.CBG_REGION_CODE,
    RCD.CBG_REGION_CN_NAME,
    RCD.CBG_REGION_EN_NAME,
    RCD.CBG_REPOFFICE_CODE,
    RCD.CBG_REPOFFICE_CN_NAME,
    RCD.CBG_REPOFFICE_EN_NAME,
    RCD.CBG_OFFICE_CODE,
    RCD.CBG_OFFICE_CN_NAME,
    RCD.CBG_OFFICE_EN_NAME,
    RCD.CBG_COUNTRY_CN_NAME,
    RCD.CBG_COUNTRY_EN_NAME,
    RCD.COUNTRY_CODE                    AS CBG_COUNTRY_CODE,
    HOR.CBG_AREA_CODE                   AS HONOR_AREA_CODE,
    HOR.CBG_AREA_CN_NAME                AS HONOR_AREA_CN_NAME,
    HOR.CBG_AREA_EN_NAME                AS HONOR_AREA_EN_NAME,
    HOR.CBG_REGION_CODE                 AS HONOR_REGION_CODE,
    HOR.CBG_REGION_CN_NAME              AS HONOR_REGION_CN_NAME,
    HOR.CBG_REGION_EN_NAME              AS HONOR_REGION_EN_NAME,
    HOR.CBG_REPOFFICE_CODE              AS HONOR_REPOFFICE_CODE,
    HOR.CBG_REPOFFICE_CN_NAME           AS HONOR_REPOFFICE_CN_NAME,
    HOR.CBG_REPOFFICE_EN_NAME           AS HONOR_REPOFFICE_EN_NAME,
    HOR.CBG_OFFICE_CODE                 AS HONOR_OFFICE_CODE,
    HOR.CBG_OFFICE_CN_NAME              AS HONOR_OFFICE_CN_NAME,
    HOR.CBG_OFFICE_EN_NAME              AS HONOR_OFFICE_EN_NAME,
    HOR.CBG_COUNTRY_CN_NAME             AS HONOR_COUNTRY_CN_NAME,
    HOR.CBG_COUNTRY_EN_NAME             AS HONOR_COUNTRY_EN_NAME,
    HOR.COUNTRY_CODE                    AS HONOR_COUNTRY_CODE,
    F.A_COUNTRY_FLAG
FROM DMCON.DM_CBG_CI_INV_DTL_W_F_TMP_C F
INNER JOIN (SELECT PAR.PAR_PROJ_CN_NAME,
                PAR.PAR_PROJ_NUM,
                PAR.PAR_PROJ_EN_NAME,
                PAR.SUB_PROJ_CN_NAME,
                PAR.SUB_PROJ_NUM,
                PAR.SUB_PROJ_EN_NAME,
                PAR.PROJ_KEY
            FROM (SELECT DISTINCT PROJ_KEY FROM DMCON.DM_CBG_CI_INV_DTL_W_F_TMP_C WHERE PERIOD_ID = 202304 ) T,
            DMDIM.DWR_INV_DIM_PAR_PROJ_D PAR
            WHERE T.PROJ_KEY = PAR.PROJ_KEY 
) PAR ON F.PROJ_KEY = PAR.PROJ_KEY
LEFT JOIN DMDIM.DM_DIM_INV_CONTRACT_STKHOLD_V STK ON F.CONTRACT_KEY = STK.CONTRACT_KEY
LEFT JOIN DMDIM.DM_DIM_INV_AGING_D AGD ON F.INV_AGE between AGD.BEGIN_DAY and AGD.END_DAY AND AGD.INV_AGE_TYPE = 2
INNER JOIN DMDIM.DM_DIM_INV_CONF_POINT_D PNT ON F.CONFIRM_POINT_ID = PNT.CONFIRM_POINT_ID
LEFT JOIN DMDIM.DM_INV_RECOGNISE_TYPE_D REC ON F.RECOGNISE_TYPE_ID = REC.RECOGNISE_TYPE_ID
LEFT JOIN (SELECT 
                EMP.EMPLOYEE_NAME, EMP.EMPLOYEE_KEY 
            FROM (SELECT DISTINCT PROJ_MANAGER_LAST_KEY FROM DMCON.DM_CBG_CI_INV_DTL_W_F_TMP_C WHERE PERIOD_ID = 202304 ) T,
            DMDIM.DM_DIM_EMPLOYEE_D EMP
            WHERE T.PROJ_MANAGER_LAST_KEY = EMP.EMPLOYEE_KEY
) EMP ON F.PROJ_MANAGER_LAST_KEY = EMP.EMPLOYEE_KEY
LEFT JOIN DMDIM.DM_DIM_REGION_RC_CBG_D RCD ON F.GEO_PC_KEY = RCD.GEO_PC_KEY AND RCD.REGION_TREE_CODE = 'HUAWEI_TREE'
LEFT JOIN DMDIM.DM_DIM_REGION_RC_CBG_D HOR ON F.GEO_PC_KEY = HOR.GEO_PC_KEY AND HOR.REGION_TREE_CODE = 'HONOR_TREE'


从topSQL中抓取执行信息如下，看到表 dm_cbg_ci_inv_dtl_w_f_tmp_c 的宽度达到8K，加工后生成的目标表 dm_cbg_ci_inv_dtl_w_f 的宽度是9K。

3. 优化逻辑

3.1：源表字段写入目标表

把表 dm_cbg_ci_inv_dtl_w_f_tmp_c 的数据先导入目标表 dm_cbg_ci_inv_dtl_w_f，对于来源不是表 dm_cbg_ci_inv_dtl_w_f_tmp_c 的字段先置为NULL。

INSERT INTO dm_cbg_ci_inv_dtl_w_f
SELECT
    F.PERIOD_ID,
    F.YYYY,
    F.MM,
    F.YYYYMM,
    F.YYYYQ,
    F.QTR_NO,
    F.SALES_LV0_PROD_LIST_CODE,
    F.SALES_LV0_PROD_LIST_CN_NAME,
    F.SALES_LV0_PROD_LIST_EN_NAME,
    F.SALES_LV1_PROD_LIST_CODE,
    F.SALES_LV1_PROD_LIST_CN_NAME,
    F.SALES_LV1_PROD_LIST_EN_NAME,
    F.SALES_LV2_PROD_LIST_CODE,
    F.SALES_LV2_PROD_LIST_CN_NAME,
    F.SALES_LV2_PROD_LIST_EN_NAME,
    F.SALES_LV3_PROD_LIST_CODE,
    F.SALES_LV3_PROD_LIST_CN_NAME,
    F.SALES_LV3_PROD_LIST_EN_NAME,
    F.SALES_LV4_PROD_LIST_CODE,
    F.SALES_LV4_PROD_LIST_CN_NAME,
    F.SALES_LV4_PROD_LIST_EN_NAME,
    F.SALES_LV5_PROD_LIST_CODE,
    F.SALES_LV5_PROD_LIST_EN_NAME,
    F.SALES_LV5_PROD_LIST_CN_NAME,
    F.SALES_PROD_CODE,
    F.SALES_PROD_EN_NAME,
    F.SALES_PROD_CN_NAME,
    F.SALES_COMPANY_BRAND,
    F.SALES_PROD_MKT_NAME,
    F.SALES_REPORT_TYPE_CN_NAME,
    F.SALES_REPORT_TYPE_CODE,
    F.SALES_REPORT_TYPE_EN_NAME,
    F.LV0_PROD_LIST_CODE,
    F.LV0_PROD_LIST_CN_NAME,
    F.LV0_PROD_LIST_EN_NAME,
    F.LV1_PROD_LIST_CODE,
    F.LV1_PROD_LIST_CN_NAME,
    F.LV1_PROD_LIST_EN_NAME,
    F.LV2_PROD_LIST_CODE,
    F.LV2_PROD_LIST_CN_NAME,
    F.LV2_PROD_LIST_EN_NAME,
    F.LV3_PROD_LIST_CODE,
    F.LV3_PROD_LIST_CN_NAME,
    F.LV3_PROD_LIST_EN_NAME,
    F.LV4_PROD_LIST_CODE,
    F.LV4_PROD_LIST_CN_NAME,
    F.LV4_PROD_LIST_EN_NAME,
    F.LV5_PROD_LIST_CODE,
    F.LV5_PROD_LIST_EN_NAME,
    F.LV5_PROD_LIST_CN_NAME,
    F.PROD_CODE,
    F.PROD_EN_NAME,
    F.PROD_CN_NAME,
    F.COMPANY_BRAND,
    F.PROD_MKT_NAME,
    F.REPORT_TYPE_CN_NAME,
    F.REPORT_TYPE_CODE,
    F.REPORT_TYPE_EN_NAME,
    F.PLATFORMCOMPANY_FLAG,
    F.INVENTORY_CLASS_SEQ_NUM,
    F.INVENTORY_CLASS_L1_CODE,
    F.INVENTORY_CLASS_L1_CN_NAME,
    F.INVENTORY_CLASS_L1_EN_NAME,
    F.INVENTORY_CLASS_L2_CODE,
    F.INVENTORY_CLASS_L2_CN_NAME,
    F.INVENTORY_CLASS_L2_EN_NAME,
    F.GLOBAL_CODE,
    F.GLOBAL_CN_NAME,
    F.GLOBAL_EN_NAME,
    F.AREA_CODE,
    F.AREA_CN_NAME,
    F.AREA_EN_NAME,
    F.REGION_CODE,
    F.REGION_CN_NAME,
    F.REGION_EN_NAME,
    F.REPOFFICE_CODE,
    F.REPOFFICE_CN_NAME,
    F.REPOFFICE_EN_NAME,
    F.OFFICE_CODE,
    F.OFFICE_CN_NAME,
    F.OFFICE_EN_NAME,
    F.GEO_PC_CODE,
    F.GEO_PC_CN_NAME,
    F.GEO_PC_EN_NAME,
    F.COUNTRY_CN_NAME,
    F.COUNTRY_EN_NAME,
    F.COUNTRY_CODE,
    F.OVERSEA_FLAG,
    F.COMPANY_EN_NAME,
    F.COMPANY_CN_NAME,
    F.COMPANY_CODE,
    F.COMPANY_DESC,
    F.LC_CODE,
    F.OVERSEA_CORP_FLAG,
    F.SYSTEM_INTER_COMPANY_STATUS,
    F.SUPPLY_CENTER_NEW_CODE,
    F.SUPPLY_CENTER_NEW_CN_NAME,
    F.SUPPLY_CENTER_NEW_EN_NAME,
    F.SUPPLY_CENTER_CODE,
    F.SUPPLY_CENTER_CN_NAME,
    F.SUPPLY_CENTER_EN_NAME,
    F.SUPPLY_GEO_PC_CODE,
    F.FULFIL_COMPANY_CODE,
    F.SIGN_COMPANY_CODE,
    F.SUPPLY_CENTER_TYPE_CODE,
    F.SUPPLY_CENTER_TYPE_CN_NAME,
    F.SUPPLY_CENTER_TYPE_EN_NAME,
    F.REPORT_ITEM_ID,
    F.SUB_ACCOUNT_CODE,
    F.GROUP_ACCOUNT_CODE,
    F.DATA_CATEGORY_ID,
    F.SCENARIO_ID,
    F.SCHEDULE_TYPE_ID,
    F.SUBJECT_AREA_ID,
    F.VERSION_ID,
    F.CIF_FLAG,
    F.CI_FLAG,
    F.COST_CATEGORY,
    F.SOURCE_FLAG,
    F.OVERDUE_INVENTORY_FLAG,
    F.RMB_AAA_QTD_AMT,
    F.RMB_AAP_QTD_AMT,
    F.USD_AAA_QTD_AMT,
    F.USD_AAP_QTD_AMT,
    F.RMB_AAA_PY_PTD_AMT,
    F.USD_AAA_PY_PTD_AMT,
    F.USD_AAP_PY_PTD_AMT,
    F.RMB_AAA_PY_QTD_AMT,
    F.RMB_AAP_PY_QTD_AMT,
    F.USD_AAA_PY_QTD_AMT,
    F.USD_AAP_PY_QTD_AMT,
    F.RMB_AAA_PY_YTD_AMT,
    F.RMB_AAP_PY_YTD_AMT,
    F.USD_AAA_PY_YTD_AMT,
    F.USD_AAP_PY_YTD_AMT,
    F.RMB_AAA_PY_ALL_QTD_AMT,
    F.RMB_AAP_PY_ALL_QTD_AMT,
    F.USD_AAA_PY_ALL_QTD_AMT,
    F.USD_AAP_PY_ALL_QTD_AMT,
    F.RMB_AAA_PY_ALL_YTD_AMT,
    F.RMB_AAP_PY_ALL_YTD_AMT,
    F.USD_AAA_PY_ALL_YTD_AMT,
    F.USD_AAP_PY_ALL_YTD_AMT,
    F.RMB_AAA_PP_PTD_AMT,
    F.RMB_AAP_PP_PTD_AMT,
    F.USD_AAA_PP_PTD_AMT,
    F.USD_AAP_PP_PTD_AMT,
    F.RMB_AAA_PP_QTD_AMT,
    F.RMB_AAP_PP_QTD_AMT,
    F.USD_AAA_PP_QTD_AMT,
    F.USD_AAP_PP_QTD_AMT,
    F.RMB_AAA_CY_OPEN_BAL_AMT,
    F.RMB_AAP_CY_OPEN_BAL_AMT,
    F.USD_AAA_CY_OPEN_BAL_AMT,
    F.USD_AAP_CY_OPEN_BAL_AMT,
    F.RMB_AAA_PY_END_BAL_AMT,
    F.RMB_AAP_PY_END_BAL_AMT,
    F.USD_AAA_PY_END_BAL_AMT,
    F.USD_AAP_PY_END_BAL_AMT,
    F.RMB_AAA_PQ_END_BAL_AMT,
    F.RMB_AAP_PQ_END_BAL_AMT,
    F.USD_AAA_PQ_END_BAL_AMT,
    F.USD_AAP_PQ_END_BAL_AMT,
    F.RMB_AAA_PTD_AMT,
    F.RMB_AAP_PTD_AMT,
    F.USD_AAA_PTD_AMT,
    F.USD_AAP_PTD_AMT,
    F.RMB_AAP_PY_PTD_AMT,
    F.RMB_AAA_YTD_AMT,
    F.RMB_AAP_YTD_AMT,
    F.USD_AAA_YTD_AMT,
    F.USD_AAP_YTD_AMT,
    F.RMB_AAA_END_BAL_AMT,
    F.RMB_AAP_END_BAL_AMT,
    F.USD_AAA_END_BAL_AMT,
    F.USD_AAP_END_BAL_AMT,
    F.RMB_AAA_YEAR_AVG_AMT,
    F.RMB_AAP_YEAR_AVG_AMT,
    F.USD_AAA_YEAR_AVG_AMT,
    F.USD_AAP_YEAR_AVG_AMT,
    F.RMB_AAA_PP_END_BAL_AMT,
    F.RMB_AAP_PP_END_BAL_AMT,
    F.USD_AAA_PP_END_BAL_AMT,
    F.USD_AAP_PP_END_BAL_AMT,
    F.RMB_AAA_LY_END_BAL_AMT,
    F.RMB_AAP_LY_END_BAL_AMT,
    F.USD_AAA_LY_END_BAL_AMT,
    F.USD_AAP_LY_END_BAL_AMT,
    F.RMB_AAA_PY_2POINT_YTD_AMT,
    F.RMB_AAP_PY_2POINT_YTD_AMT,
    F.USD_AAA_PY_2POINT_YTD_AMT,
    F.USD_AAP_PY_2POINT_YTD_AMT,
    F.RMB_AAA_2POINT_YTD_AMT,
    F.RMB_AAP_2POINT_YTD_AMT,
    F.USD_AAA_2POINT_YTD_AMT,
    F.USD_AAP_2POINT_YTD_AMT,
    F.BUSINESS_STATUS_CN_NAME,
    F.BUSINESS_STATUS_EN_NAME,
    F.BUSINESS_STATUS_CODE,
    F.LOCATION_L1_CODE,
    F.LOCATION_L1_CN_NAME,
    F.LOCATION_L1_EN_NAME,
    F.LOCATION_L2_CODE,
    F.LOCATION_L2_CN_NAME,
    F.LOCATION_L2_EN_NAME,
    F.LOCATION_L3_CODE,
    F.LOCATION_L3_CN_NAME,
    F.LOCATION_L3_EN_NAME,
    F.LOCATION_CODE,
    F.LOCATION_CN_NAME,
    F.LOCATION_EN_NAME,
    F.LOCATION_TYPE_CN_NAME,
    F.LOCATION_TYPE_EN_NAME,
    F.TOP_CUST_CATEGORY_CN_NAME,
    F.TOP_CUST_CATEGORY_CODE,
    F.TOP_CUST_CATEGORY_EN_NAME,
    F.ACCTCUST_HQ_CN_NAME,
    F.ACCTCUST_HQ_CODE,
    F.ACCTCUST_HQ_EN_NAME,
    F.ACCTCUST_BRANCH_CN_NAM,
    F.ACCTCUST_BRANCH_CODE,
    F.ACCTCUST_BRANCH_EN_NAM,
    F.ACCTCUST_LV2_BRANCH_CN_NAME,
    F.ACCTCUST_LV2_BRANCH_CODE,
    F.ACCTCUST_LV2_BRANCH_EN_NAME,
    F.ACCTCUST_SUBSIDIARY_CN_NAM,
    F.ACCTCUST_SUBSIDIARY_CODE,
    F.ACCTCUST_SUBSIDIARY_EN_NAM,
    F.BRANCH_CUSTCATG_CN_NAME,
    F.BRANCH_CUSTCATG_CODE,
    F.BRANCH_CUSTCATG_EN_NAME,
    F.CUST_ACCOUNT_NUM,
    F.CUST_CLASS_CN_NAME,
    F.CUST_CLASS_CODE,
    F.CUST_CLASS_EN_NAME,
    F.CUST_EN_NAME,
    F.CUST_NL_NAME,
    F.REGION_CUSTCATG_CN_NAME,
    F.REGION_CUSTCATG_CODE,
    F.REGION_CUSTCATG_EN_NAME,
    F.LV2_BRANCH_CUSTCATG_CN_NAME,
    F.LV2_BRANCH_CUSTCATG_CODE,
    F.LV2_BRANCH_CUSTCATG_EN_NAME,
    F.LVL2_CUST_CLASS_CN_NAME,
    F.LVL2_CUST_CLASS_EN_NAME,
    F.HISI_LV0_PROD_LIST_CODE,
    F.HISI_LV0_PROD_LIST_CN_NAME,
    F.HISI_LV0_PROD_LIST_EN_NAME,
    F.HISI_LV1_PROD_LIST_CODE,
    F.HISI_LV1_PROD_LIST_CN_NAME,
    F.HISI_LV1_PROD_LIST_EN_NAME,
    F.HISI_LV2_PROD_LIST_CODE,
    F.HISI_LV2_PROD_LIST_CN_NAME,
    F.HISI_LV2_PROD_LIST_EN_NAME,
    F.HISI_LV3_PROD_LIST_CODE,
    F.HISI_LV3_PROD_LIST_CN_NAME,
    F.HISI_LV3_PROD_LIST_EN_NAME,
    F.HISI_LV4_PROD_LIST_CODE,
    F.HISI_LV4_PROD_LIST_CN_NAME,
    F.HISI_LV4_PROD_LIST_EN_NAME,
    F.HISI_LV5_PROD_LIST_CODE,
    F.HISI_LV5_PROD_LIST_CN_NAME,
    F.HISI_LV5_PROD_LIST_EN_NAME,
    F.HISI_PROD_CODE,
    F.HISI_PROD_EN_NAME,
    F.HISI_PROD_CN_NAME,
    F.ENTERPRISE_CUST_KEY,
    F.ENTERPRISE_CUST_EN_NAME,
    F.ENTERPRISE_CUST_NL_NAME,
    F.ENTERPRISE_INDUSTRY_CLASS_CODE,
    F.ENTERPRISE_INDUSTRY_CLASS_CN_NAME,
    F.ENTERPRISE_INDUSTRY_CLASS_EN_NAME,
    F.ENTERPRISE_ENT_CUST_CLASS_CODE,
    F.ENTERPRISE_ENT_CUST_CLASS_CN_NAME,
    F.ENTERPRISE_ENT_CUST_CLASS_EN_NAME,
    F.PROJ_NUM,
    F.PROJ_EN_NAME,
    F.PROJ_CN_NAME,
    F.PROJ_GEO_PC_CN_NAME,
    F.PROJ_GEO_PC_EN_NAME,
    F.PROJGEO_PC_CN_NAME,
    F.PROJGEO_PC_EN_NAME,
    F.ACCOUNT_MANAGER_CN_NAME,
    F.ACCOUNT_MANAGER_CODE,
    F.ACCOUNT_MANAGER_EN_NAME,
    F.HW_CONTRACT_NUM,
    F.CUST_CONTRACT_NUM,
    F.CONTRACT_NAME,
    F.FRAMEWORK_CONTRACT_NUM,
    F.END_CUST_NAME,
    F.AGENT_DISTRIBUTION_CUST_NAME,
    F.SIGN_CUST_NAME,
    F.REGISTRATION_DATE,
    F.SALES_MODE_CN_NAME,
    F.SALES_MODE_CODE,
    F.SALES_MODE_EN_NAME,
    F.CONTRACT_FIRST_PUBLISH_DATE,
    F.CONTRACT_STATUS_CN_NAME,
    F.CONTRACT_STATUS_CODE,
    F.CONTRACT_STATUS_EN_NAME,
    F.CREATE_DATE,
    F.LOGIN_DATE,
    F.COMBIND_TO_SINGL_DATE,
    F.SIGN_DATE,
    F.SIGN_PERSON,
    F.ADVANCED_SALES_TO_SALES_DATE,
    F.WARRANTY_START_MSTNE_CN_NAME,
    F.WARRANTY_START_MSTNE_EN_NAME,
    F.WARRANTY_BEGIN_EXTEND_DUR,
    F.HARDWARE_WARRANTY_DURATION,
    F.HARDWARE_EXTEND_WARRANTY_DUR,
    F.SOFTWARE_EXTEND_WARRANTY_DUR,
    F.BRAZIL_TERMINAL_CONTRACT_FLAG,
    F.INTELNAL_OVERSEA_FLAG,
    F.CONTRACT_ID,
    F.CONTRACT_TOTAL_AMT,
    F.CONTRACT_TYPE_CN_NAME,
    F.CONTRACT_TYPE_CODE,
    F.CONTRACT_TYPE_EN_NAME,
    F.CONTRACT_MAIN_TYPE_CN_NAME,
    F.CONTRACT_MAIN_TYPE_CODE,
    F.CONTRACT_MAIN_TYPE_EN_NAME,
    F.CONTRACT_SUB_TYPE_CN_NAME,
    F.CONTRACT_SUB_TYPE_CODE,
    F.CONTRACT_SUB_TYPE_EN_NAME,
    F.CONTRACT_MAIN_PROD_TYPE_CODE,
    F.CTRCT_MAIN_PROD_TYPE_EN_NAME,
    F.CTRCT_MAIN_PROD_TYPE_CN_NAME,
    F.CTRCT_BUSINESS_TYPE_EN_NAME,
    F.CTRCT_BUSINESS_TYPE_CN_NAME,
    F.CONTRACT_BUSINESS_TYPE_CODE,
    F.NEW_MOVE_TYPE_CN_NAME,
    F.NEW_MOVE_TYPE_CODE,
    F.NEW_MOVE_TYPE_EN_NAME,
    F.ADD_EXPAND_TYPE_CN_NAME,
    F.ADD_EXPAND_TYPE_CODE,
    F.ADD_EXPAND_TYPE_EN_NAME,
    F.STOCK_INCREMENT_TYPE_CN_NAME,
    F.STOCK_INCREMENT_TYPE_CODE,
    F.STOCK_INCREMENT_TYPE_EN_NAME,
    F.CURRENCY_CODE,
    F.REVENUE_SHARING_CONTRACT_FLAG,
    F.S3_PILOT_FLAG,
    F.PO_LIST_FLAG,
    F.BENEFICIAL_ENTITY,
    F.SUB_ACCOUNT_EN_NAME,
    F.SUB_ACCOUNT_CN_NAME,
    F.INVENTORY_ORG_CODE,
    F.INVENTORY_ORG_NAME,
    F.SUBINV_BG_CN_NAME,
    F.SUBINV_BG_EN_NAME,
    F.SUBINV_TYPE_CN_NAME,
    F.SUBINV_TYPE_EN_NAME,
    F.SUBINVENTORY_NAME,
    F.GRP_ACCOUNT_CODE_EN_NAME,
    F.GRP_ACCOUNT_CODE_CN_NAME,
    F.JE_CATEGORY_CODE,
    F.JE_CATEGORY_EN_NAME,
    F.JE_CATEGORY_CN_NAME,
    F.JE_SOURCE_CODE,
    F.JE_SOURCE_EN_NAME,
    F.JE_SOURCE_CN_NAME,
    F.HIS_CN_NAME,
    F.HIS_EN_NAME,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    F.CONTRACT_KEY,
    F.COMPANY_KEY,
    F.subinventory_key,
    F.business_status_key,
    F.je_source_id,
    F.je_category_id,
    F.prod_key,
    F.supply_center_key,
    F.sales_mode_key,
    F.proj_key,
    F.BG_CN_NAME,
    F.CONFIRM_POINT,
    F.CONFIRM_POINT_DELAY_DAYS,
    F.STOCK_CONTRACT_FLAG,
    F.STANDARD_CONTRACT_FLAG,
    F.SIGNED_REP_OFFICE_EN_NAME,
    F.SIGNED_REP_OFFICE_CODE,
    F.SIGNED_REP_OFFICE_CN_NAME,
    F.SIGNED_REGION_EN_NAME,
    F.SIGNED_REGION_CODE,
    F.SIGNED_REGION_CN_NAME,
    F.RES_CODE,
    F.INV_AGE,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    F.A_COUNTRY_FLAG
FROM DMCON.DM_CBG_CI_INV_DTL_W_F_TMP_C F
;


3.2 关联更新填充缺失字段

 把非表 dm_cbg_ci_inv_dtl_w_f_tmp_c 的字段通过轻量化更新（SET enable_light_colupdate = ON）的方式更新到表 dm_cbg_ci_inv_dtl_w_f

SET enable_light_colupdate = ON;
UPDATE dm_cbg_ci_inv_dtl_w_f s SET
    s.par_proj_cn_name          = par.par_proj_cn_name,
    s.par_proj_num              = par.par_proj_num,
    s.par_proj_en_name          = par.par_proj_en_name,
    s.sub_proj_cn_name          = par.sub_proj_cn_name,
    s.sub_proj_num              = par.sub_proj_num,
    s.sub_proj_en_name          = par.sub_proj_en_name,
    s.product_manager_employee  = stk.product_manager_employee,
    s.saleproj_manager_employee = stk.saleproj_manager_employee,
    s.inv_age_cn_name           = agd.inv_age_cn_name,
    s.inv_age_en_name           = agd.inv_age_en_name,
    s.begin_day                 = agd.begin_day,
    s.end_day                   = agd.end_day,
    s.inv_age_type              = agd.inv_age_type,
    s.confirm_point_code        = pnt.confirm_point_code,
    s.confirm_point_cn_name     = pnt.confirm_point_cn_name,
    s.confirm_point_en_name     = pnt.confirm_point_en_name,
    s.recognise_type_l1_code    = rec.recognise_type_l1_code,
    s.recognise_type_l1_cn_name = rec.recognise_type_l1_cn_name,
    s.recognise_type_l1_en_name = rec.recognise_type_l1_en_name,
    s.recognise_type_l2_code    = rec.recognise_type_l2_code,
    s.recognise_type_l2_cn_name = rec.recognise_type_l2_cn_name,
    s.recognise_type_l2_en_name = rec.recognise_type_l2_en_name,
    s.cbg_area_code             = rcd.cbg_area_code,
    s.cbg_area_cn_name          = rcd.cbg_area_cn_name,
    s.cbg_area_en_name          = rcd.cbg_area_en_name,
    s.cbg_region_code           = rcd.cbg_region_code,
    s.cbg_region_cn_name        = rcd.cbg_region_cn_name,
    s.cbg_region_en_name        = rcd.cbg_region_en_name,
    s.cbg_repoffice_code        = rcd.cbg_repoffice_code,
    s.cbg_repoffice_cn_name     = rcd.cbg_repoffice_cn_name,
    s.cbg_repoffice_en_name     = rcd.cbg_repoffice_en_name,
    s.cbg_office_code           = rcd.cbg_office_code,
    s.cbg_office_cn_name        = rcd.cbg_office_cn_name,
    s.cbg_office_en_name        = rcd.cbg_office_en_name,
    s.cbg_country_cn_name       = rcd.cbg_country_cn_name,
    s.cbg_country_en_name       = rcd.cbg_country_en_name,
    s.cbg_country_code          = rcd.country_code,
    s.honor_area_code           = hor.cbg_area_code,
    s.honor_area_cn_name        = hor.cbg_area_cn_name,
    s.honor_area_en_name        = hor.cbg_area_en_name,
    s.honor_region_code         = hor.cbg_region_code,
    s.honor_region_cn_name      = hor.cbg_region_cn_name,
    s.honor_region_en_name      = hor.cbg_region_en_name,
    s.honor_repoffice_code      = hor.cbg_repoffice_code,
    s.honor_repoffice_cn_name   = hor.cbg_repoffice_cn_name,
    s.honor_repoffice_en_name   = hor.cbg_repoffice_en_name,
    s.honor_office_code         = hor.cbg_office_code,
    s.honor_office_cn_name      = hor.cbg_office_cn_name,
    s.honor_office_en_name      = hor.cbg_office_en_name,
    s.honor_country_cn_name     = hor.cbg_country_cn_name,
    s.honor_country_en_name     = hor.cbg_country_en_name,
    s.honor_country_code        = hor.country_code
FROM dmcon.dm_cbg_ci_inv_dtl_w_f_tmp_c f
INNER JOIN (SELECT par.par_proj_cn_name,
                par.par_proj_num,
                par.par_proj_en_name,
                par.sub_proj_cn_name,
                par.sub_proj_num,
                par.sub_proj_en_name,
                par.proj_key
            FROM (SELECT DISTINCT proj_key FROM dmcon.dm_cbg_ci_inv_dtl_w_f_tmp_c WHERE period_id = 202304 ) t, dmdim.dwr_inv_dim_par_proj_d par
            WHERE T.PROJ_KEY = PAR.PROJ_KEY 
) par ON f.proj_key = par.proj_key
LEFT JOIN dmdim.dm_dim_inv_contract_stkhold_v stk ON f.contract_key = stk.contract_key
LEFT JOIN dmdim.dm_dim_inv_aging_d agd ON f.inv_age BETWEEN agd.begin_day AND agd.end_day AND agd.inv_age_type = 2
INNER JOIN dmdim.dm_dim_inv_conf_point_d pnt ON f.confirm_point_id = pnt.confirm_point_id
LEFT JOIN dmdim.dm_inv_recognise_type_d rec ON f.recognise_type_id = rec.recognise_type_id
LEFT JOIN (SELECT 
                emp.employee_name, emp.employee_key 
            FROM (SELECT DISTINCT proj_manager_last_key FROM dmcon.dm_cbg_ci_inv_dtl_w_f_tmp_c WHERE period_id = 202304 ) t, dmdim.dm_dim_employee_d emp
            WHERE T.PROJ_MANAGER_LAST_KEY = EMP.EMPLOYEE_KEY
) emp ON f.proj_manager_last_key = emp.employee_key
LEFT JOIN dmdim.dm_dim_region_rc_cbg_d rcd ON f.geo_pc_key = rcd.geo_pc_key AND rcd.region_tree_code = 'huawei_tree'
LEFT JOIN dmdim.dm_dim_region_rc_cbg_d hor ON f.geo_pc_key = hor.geo_pc_key AND hor.region_tree_code = 'honor_tree'
;
SET enable_light_colupdate = ON;

4.扩展优化

上述step2的更新操作涉及主表dm_cbg_ci_inv_dtl_w_f和多个维表关联操作后进行更新，如果这一步耗时比较长的话，可以对这一步分拆成2个并发执行的语句进行性能加速。

--part1
SET enable_light_colupdate = ON;

UPDATE dm_cbg_ci_inv_dtl_w_f s SET
    s.par_proj_cn_name          = par.par_proj_cn_name,
    s.par_proj_num              = par.par_proj_num,
    s.par_proj_en_name          = par.par_proj_en_name,
    s.sub_proj_cn_name          = par.sub_proj_cn_name,
    s.sub_proj_num              = par.sub_proj_num,
    s.sub_proj_en_name          = par.sub_proj_en_name,
    s.product_manager_employee  = stk.product_manager_employee,
    s.saleproj_manager_employee = stk.saleproj_manager_employee,
    s.inv_age_cn_name           = agd.inv_age_cn_name,
    s.inv_age_en_name           = agd.inv_age_en_name,
    s.begin_day                 = agd.begin_day,
    s.end_day                   = agd.end_day,
    s.inv_age_type              = agd.inv_age_type,
    s.cbg_area_code             = rcd.cbg_area_code,
    s.cbg_area_cn_name          = rcd.cbg_area_cn_name,
    s.cbg_area_en_name          = rcd.cbg_area_en_name,
    s.cbg_region_code           = rcd.cbg_region_code,
    s.cbg_region_cn_name        = rcd.cbg_region_cn_name,
    s.cbg_region_en_name        = rcd.cbg_region_en_name,
    s.cbg_repoffice_code        = rcd.cbg_repoffice_code,
    s.cbg_repoffice_cn_name     = rcd.cbg_repoffice_cn_name,
    s.cbg_repoffice_en_name     = rcd.cbg_repoffice_en_name,
    s.cbg_office_code           = rcd.cbg_office_code,
    s.cbg_office_cn_name        = rcd.cbg_office_cn_name,
    s.cbg_office_en_name        = rcd.cbg_office_en_name,
    s.cbg_country_cn_name       = rcd.cbg_country_cn_name,
    s.cbg_country_en_name       = rcd.cbg_country_en_name,
    s.cbg_country_code          = rcd.country_code
FROM dmcon.dm_cbg_ci_inv_dtl_w_f_tmp_c f
INNER JOIN (SELECT par.par_proj_cn_name,
                par.par_proj_num,
                par.par_proj_en_name,
                par.sub_proj_cn_name,
                par.sub_proj_num,
                par.sub_proj_en_name,
                par.proj_key
            FROM (SELECT DISTINCT proj_key FROM dmcon.dm_cbg_ci_inv_dtl_w_f_tmp_c WHERE period_id = 202304 ) t, dmdim.dwr_inv_dim_par_proj_d par
            WHERE T.PROJ_KEY = PAR.PROJ_KEY 
) par ON f.proj_key = par.proj_key
LEFT JOIN dmdim.dm_dim_inv_contract_stkhold_v stk ON f.contract_key = stk.contract_key
LEFT JOIN dmdim.dm_dim_inv_aging_d agd ON f.inv_age BETWEEN agd.begin_day AND agd.end_day AND agd.inv_age_type = 2
LEFT JOIN dmdim.dm_dim_region_rc_cbg_d rcd ON f.geo_pc_key = rcd.geo_pc_key AND rcd.region_tree_code = 'huawei_tree';

SET enable_light_colupdate = ON;

--part2
SET enable_light_colupdate = ON;

UPDATE dm_cbg_ci_inv_dtl_w_f s SET
    s.confirm_point_code        = pnt.confirm_point_code,
    s.confirm_point_cn_name     = pnt.confirm_point_cn_name,
    s.confirm_point_en_name     = pnt.confirm_point_en_name,
    s.recognise_type_l1_code    = rec.recognise_type_l1_code,
    s.recognise_type_l1_cn_name = rec.recognise_type_l1_cn_name,
    s.recognise_type_l1_en_name = rec.recognise_type_l1_en_name,
    s.recognise_type_l2_code    = rec.recognise_type_l2_code,
    s.recognise_type_l2_cn_name = rec.recognise_type_l2_cn_name,
    s.recognise_type_l2_en_name = rec.recognise_type_l2_en_name,
    s.honor_area_code           = hor.cbg_area_code,
    s.honor_area_cn_name        = hor.cbg_area_cn_name,
    s.honor_area_en_name        = hor.cbg_area_en_name,
    s.honor_region_code         = hor.cbg_region_code,
    s.honor_region_cn_name      = hor.cbg_region_cn_name,
    s.honor_region_en_name      = hor.cbg_region_en_name,
    s.honor_repoffice_code      = hor.cbg_repoffice_code,
    s.honor_repoffice_cn_name   = hor.cbg_repoffice_cn_name,
    s.honor_repoffice_en_name   = hor.cbg_repoffice_en_name,
    s.honor_office_code         = hor.cbg_office_code,
    s.honor_office_cn_name      = hor.cbg_office_cn_name,
    s.honor_office_en_name      = hor.cbg_office_en_name,
    s.honor_country_cn_name     = hor.cbg_country_cn_name,
    s.honor_country_en_name     = hor.cbg_country_en_name,
    s.honor_country_code        = hor.country_code
FROM dmcon.dm_cbg_ci_inv_dtl_w_f_tmp_c f
INNER JOIN dmdim.dm_dim_inv_conf_point_d pnt ON f.confirm_point_id = pnt.confirm_point_id
LEFT JOIN dmdim.dm_inv_recognise_type_d rec ON f.recognise_type_id = rec.recognise_type_id
LEFT JOIN (SELECT 
                emp.employee_name, emp.employee_key 
            FROM (SELECT DISTINCT proj_manager_last_key FROM dmcon.dm_cbg_ci_inv_dtl_w_f_tmp_c WHERE period_id = 202304 ) t, dmdim.dm_dim_employee_d emp
            WHERE T.PROJ_MANAGER_LAST_KEY = EMP.EMPLOYEE_KEY
) emp ON f.proj_manager_last_key = emp.employee_key
LEFT JOIN dmdim.dm_dim_region_rc_cbg_d hor ON f.geo_pc_key = hor.geo_pc_key AND hor.region_tree_code = 'honor_tree';

SET enable_light_colupdate = ON;


【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
EI企业智能 SQL 云数据仓库 GaussDB(DWS) 应用性能调优
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入