DWS轻量化更新黑科技:宽表加工优化
1.数仓实践丨主动预防-DWS关键工具安装确认2.一条SQL如何被MySQL架构中的各个组件操作执行的?3.GaussDB(DWS)网络流控与管控效果4.GaussDB(DWS)字符串处理函数返回错误结果集排查5.从缓存的本质说起,说服技术大佬用Redis6.这年头怕数据泄露?全密态数据库:无所谓,我会出手7.华为云新一代分布式数据库GaussDB,给世界一个更优选择8.GaussDB技术解读丨高级压缩9.掌数科技携手华为云GaussDB,助力金融科技创新,联合打造行业标杆10.一文带你全面了解openGemini11.GaussDB(for Redis)多租户:读写权限控制和数据库隔离的完美融合12.5分钟迁移关系型数据库到图数据库13.数仓现网案例丨超大结果集接收异常
14.DWS轻量化更新黑科技:宽表加工优化
15.数据库行业需要什么样的人才?高校老师这样说16.数仓性能优化:倾斜优化-表达式计算倾斜的hint优化17.GaussDB技术解读系列之SQL Audit,面向应用开发的SQL审核工具18.带你认识数仓的监控系统TopSQL19.带你走进数仓大集群内幕丨详解关于作业hang及残留问题定位20.实时入库不用愁,HStore帮分忧21.openGauss数据库在CentOS上的安装实践22.揭秘华为云GaussDB(for Redis)丨大key治理23.GaussDB(DWS)函数不同写法引发的结果差异24.数仓中典型的几种不下推语句整改案例25.GaussDB技术解读系列之应用无损透明(ALT)26.华为云GaussDB(for Influx)单机版上线,企业降本增效利器来了27.数仓备份经验分享丨详解roach备份原理及问题处理套路28.中国云数据仓库,双第一!29.华为云GaussDB打造最可信的数据库,给世界一个更优选择30.GaussDB技术解读系列:高级压缩之OLTP表压缩31.十年磨一剑的华为云GES,高明在哪32.使用DWS集群,用户被锁定如何解锁33.GaussDB技术解读系列:高安全之密态等值34.GaussDB技术解读:应用无损透明(ALT)35.数仓资源管控理论已掌握,是时候实战了36.row_number函数的不稳定性37.GaussDB技术解读丨数据库迁移创新实践38.聊聊GaussDB AP是如何执行SQL的39.Navicat 携手华为云GaussDB,联合打造便捷高效的数据库开发和建模工具方案40.GaussDB技术解读系列丨运维自动驾驶探索41.一次性全讲透GaussDB(DWS)锁的问题42.GaussDB(DWS)性能调优:Sort+Groupagg聚集引起的性能瓶颈案例43.多主架构:VLDB技术论文《Taurus MM: bringing multi-master to the cloud》解读44.GaussDB(for Redis)游戏实践:玩家下线行为上报45.一文详解数据仓库的物理细粒度备份恢复46.华为云HBase冷热分离最佳实践47.四问复合索引,让你的数据查询速度飞起48.GaussDB(DWS)案例丨MERGE场景下语句不下推引起的性能瓶颈问题49.如何强制SQL走性能更优的hash join50.如何使用GaussDB(DWS)的本地临时表进行数据处理51.华为云GaussDB亮相金融业数据库技术大会52.2个数仓中不等值关联优化案例53.数仓实时场景下表行数估算不准确引起的的性能瓶颈问题案例54.详解GuassDB数据库权限命令:GRANT和REVOKE55.DWS临时内存不可用报错: memory temporarily unavailable56.华为云GaussDB城市沙龙活动走进安徽,助力金融行业数字化转型57.理论+应用,带你了解数据库资源池58.人人用数不用愁,动态数据脱敏为您解忧59.实例讲解数据库的数据去重60.数仓实践丨表扫描时过滤行数过多引起的性能瓶颈问题61.实例详解构建数仓中的行列转换62.Proxy下的Prepare透传,让GaussDB(for MySQL)更稳固,性能更卓越63.浅析KV存储之长尾时延解决办法64.实例讲解数据库的定义重载函数65.详解数据库SQL中的三个语句:DROP、TRUNCATE 、DELETE66.华为云GaussDB助力工商银行、华夏银行斩获“十佳卓越实践奖”67.Navicat 基于 GaussDB 主备版的快速入门68.数仓实时算子难以观测,快来试试算子级监控吧69.列举数据库缓存使用场景实例和命令速查表70.带你认识多模数据库GeminiDB架构与应用实践71.3招解决时序数据高基数难题,性能多维度提升!72.数仓性能调优:row_number() over(p)-rn=1性能瓶颈发现和改写套路73.数仓实践丨常量标量子查询做全连接导致整体慢74.细说GaussDB(DWS)的2种查询优化技术75.细说SQL与ETL之间的小秘密76.从概念到实践,带你掌握层次递归查询77.GeminiDB Cassandra接口新特性PITR发布:支持任意时间点恢复78.你的JoinHint为什么不生效79.六步走向无忧,华为云数据库高可用的秘密武器80.数仓调优实践丨SQL改写消除相关子查询81.GaussDB(for MySQL)新特性TDE发布:支持透明数据加密82.详解GaussDB(DWS)通信安全的小妙招:连接认证机制83.GaussDB(for MySQL) RegionlessDB发布:全球数据库技术84.5分钟带您了解DRS录制回放85.ICDM'23 BICE论文解读:基于双向LSTM和集成学习的模型框架86.数仓如何递归查询视图依赖87.支撑核心系统分布式改造,GaussDB为江南农商银行筑稳根基88.近6成金融机构的选择!华为云GaussDB加快金融核心系统转型89.GaussDB(for MySQL)剪枝功能,让查询性能提升70倍!90.2023年度十佳课题公布:华为云GaussDB获权威认可91.详解如何在数仓中搭建细粒度容灾应用92.对话苏光牛:国内数据库市场已进入关键转折点,2024年或是分水岭93.GaussDB通信运维:详解stream连接池设计原理94.GaussDB(for MySQL) Serverless全面商用:无感弹性,极致性价比95.华为云GaussDB支撑农行超级网银业务,性能和稳定性备受认可96.实例详解数据库的游标管理97.数仓实践丨从CU入手优化HStore表98.数仓的等待视图中,为什么会有Hashjoin-nestloop99.如何基于Sharding-JDBC实现GaussDB在客户端应用的读写分离100.如何迅速并识别处理MDL锁阻塞问题本文分享自华为云社区《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;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 我与微信审核的“相爱相杀”看个人小程序副业
2022-07-28 零代码修改,教你Spring Cloud应用轻松接入CSE
2022-07-28 KubeEdge发布云原生边缘计算威胁模型及安全防护技术白皮书
2022-07-28 管理区解耦架构见过吗?能帮客户搞定大难题的
2021-07-28 从源码角度详解Java的Callable接口
2021-07-28 云图说|云上应用监控神器——应用性能监控APM2.0
2021-07-28 带你了解弯曲文本检测算法的两种思路:区域重组和像素分割
2021-07-28 Java实战:教你如何进行数据库分库分表