全部标签
写点什么
OpenHarmony算法元宇宙MySQL移动开发学习方法Web3.0高效工作数据库Python音视频前端AI大数据团队管理程序员运维深度思考低代码redisgolang微服务架构flutter 查看更多

DWS 轻量化更新黑科技：宽表加工优化

作者：华为云开发者联盟
  • 2023-07-28
    广东

  • 本文字数：15721 字

    阅读完需：约 52 分钟

DWS轻量化更新黑科技：宽表加工优化

本文分享自华为云社区《GaussDB(DWS)性能调优：宽表加工优化方案》，作者：譡里个檔 。

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;
复制代码


点击关注，第一时间了解华为云新鲜技术~

划线
评论
复制
发布于: 刚刚阅读数: 4

版权声明: 本文为 InfoQ 作者【华为云开发者联盟】的原创文章。

原文链接:【http://xie.infoq.cn/article/0b770c5154be7cee9890d160a】。文章转载请联系作者。

数据库后端华为云华为云开发者联盟企业号 7 月 PK 榜
用户头像

华为云开发者联盟

关注

提供全面深入的云计算技术干货 2020-07-14 加入

生于云，长于云，让开发者成为决定性力量

评论

发布
暂无评论
Copyright © 2023, Geekbang Technology Ltd. All rights reserved. 极客邦控股（北京）有限公司 | 京 ICP 备 16027448 号 - 5京公网安备京公网安备 11010502039052号 | 产品资质
DWS轻量化更新黑科技：宽表加工优化_数据库_华为云开发者联盟_InfoQ写作社区