数仓实践丨常量标量子查询做全连接导致整体慢
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)性能调优:常量标量子查询做全连接导致整体慢》,作者: Zawami 。
问题描述
由于SQL中存在标量子查询同另一查询做笛卡尔积使SQL整体慢。标量子查询,即结果集只有一行一列的子查询。这里导致的SQL语句执行慢不只是在于做笛卡尔积慢,也会使后续聚合更慢。
原始语句
WITH TMP AS( SELECT case when length('[“202309“]') = 6 then '[“202309“]' || '01' WHEN length('[“202309“]') <> 8 THEN TO_CHAR(CURRENT_DATE, 'YYYYMMDD') END AS V_DATE from DUAL ) SELECT BG_CODE, BG_CN_NAME, BG_EN_NAME, METRIC_CODE --指标ID , METRIC_CN_NAME --指标中文名称 , METRIC_EN_NAME --指标英文名称 , CURRENCY --币种 , OVERSEAS_FLAG, REGION_CODE, REGION_CN_NAME, REGION_EN_NAME, REPOFFICE_CODE, REPOFFICE_CN_NAME, REPOFFICE_EN_NAME, OFFICE_CODE, OFFICE_CN_NAME, OFFICE_EN_NAME, REGION_CUSTCATG_CODE, REGION_CUSTCATG_CN_NAME, REGION_CUSTCATG_EN_NAME, TOP_CUST_CATEGORY_CODE, TOP_CUST_CATEGORY_EN_NAME, TOP_CUST_CATEGORY_CN_NAME, ACCTCUST_HQ_CODE, ACCTCUST_HQ_CN_NAME, ACCTCUST_HQ_EN_NAME, ACCTCUST_BRANCH_CODE, ACCTCUST_BRANCH_CN_NAME, ACCTCUST_BRANCH_EN_NAME, ACCTCUST_SUBSIDIARY_CODE, ACCTCUST_SUBSIDIARY_CN_NAM, ACCTCUST_SUBSIDIARY_EN_NAM, COUNTRY_CODE --新增加入参 , COUNTRY_CN_NAME --新增加入参 , COUNTRY_EN_NAME --新增加入参 , AGREE_AMOUNT --BUSI_DSCT_00001 总优惠 , AGREE_REMAIN_AMOUNT --BUSI_DSCT_00002 即期优惠 , SIGN_AMOUNT --BUSI_DSCT_00003 即期优惠/一次性优惠 , USE_AMOUNT --BUSI_DSCT_00004 即期优惠/单价量折扣 , NOT_USED_VALID_AMOUNT --BUSI_DSCT_00005 延期优惠 , NOT_USED_INVALID_AMOUNT --BUSI_DSCT_00006 voucher , NEW_SIGN_AMOUNT --BUSI_DSCT_00007 其他延期优惠 , NEW_USE_AMOUNT --BUSI_DSCT_00008 本月新使用金额 , EXPIRED_AMOUNT --BUSI_DSCT_00009 本月已过期金额 , IMMED_EXPIRED_AMOUNT --BUSI_DSCT_00010 半年内即将过期金额 FROM ( SELECT C.BG_CODE, C.BG_CN_NAME, C.BG_EN_NAME, C.M_ID AS METRIC_CODE --指标ID , C.M_CN AS METRIC_CN_NAME --指标中文名称 , C.M_EN AS METRIC_EN_NAME --指标英文名称 , C.CURRENCY_CODE AS CURRENCY --币种 ,CASE WHEN 1 = 0 THEN C.OVERSEA_FLAG ELSE NULL END AS OVERSEAS_FLAG,CASE WHEN 1 = 0 THEN C.REGION_CODE ELSE NULL END AS REGION_CODE,CASE WHEN 1 = 0 THEN C.REGION_CN_NAME ELSE NULL END AS REGION_CN_NAME,CASE WHEN 1 = 0 THEN C.REGION_EN_NAME ELSE NULL END AS REGION_EN_NAME,CASE WHEN 1 = 0 THEN C.REPOFFICE_CODE ELSE NULL END AS REPOFFICE_CODE,CASE WHEN 1 = 0 THEN C.REPOFFICE_CN_NAME ELSE NULL END AS REPOFFICE_CN_NAME,CASE WHEN 1 = 0 THEN C.REPOFFICE_EN_NAME ELSE NULL END AS REPOFFICE_EN_NAME,CASE WHEN 1 = 0 THEN C.OFFICE_CODE ELSE NULL END AS OFFICE_CODE,CASE WHEN 1 = 0 THEN C.OFFICE_CN_NAME ELSE NULL END AS OFFICE_CN_NAME,CASE WHEN 1 = 0 THEN C.OFFICE_EN_NAME ELSE NULL END AS OFFICE_EN_NAME,CASE WHEN 1 = 0 THEN C.REGION_CUSTCATG_CODE ELSE NULL END AS REGION_CUSTCATG_CODE,CASE WHEN 1 = 0 THEN C.REGION_CUSTCATG_CN_NAME ELSE NULL END AS REGION_CUSTCATG_CN_NAME,CASE WHEN 1 = 0 THEN C.REGION_CUSTCATG_EN_NAME ELSE NULL END AS REGION_CUSTCATG_EN_NAME,CASE WHEN 1 = 0 THEN C.TOP_CUST_CATEGORY_CODE ELSE NULL END AS TOP_CUST_CATEGORY_CODE,CASE WHEN 1 = 0 THEN C.TOP_CUST_CATEGORY_EN_NAME ELSE NULL END AS TOP_CUST_CATEGORY_EN_NAME,CASE WHEN 1 = 0 THEN C.TOP_CUST_CATEGORY_CN_NAME ELSE NULL END AS TOP_CUST_CATEGORY_CN_NAME,CASE WHEN 1 = 0 THEN C.ACCTCUST_HQ_CODE ELSE NULL END AS ACCTCUST_HQ_CODE,CASE WHEN 1 = 0 THEN C.ACCTCUST_HQ_CN_NAME ELSE NULL END AS ACCTCUST_HQ_CN_NAME,CASE WHEN 1 = 0 THEN C.ACCTCUST_HQ_EN_NAME ELSE NULL END AS ACCTCUST_HQ_EN_NAME,CASE WHEN 1 = 0 THEN C.ACCTCUST_BRANCH_CODE ELSE NULL END AS ACCTCUST_BRANCH_CODE,CASE WHEN 1 = 0 THEN C.ACCTCUST_BRANCH_CN_NAME ELSE NULL END AS ACCTCUST_BRANCH_CN_NAME,CASE WHEN 1 = 0 THEN C.ACCTCUST_BRANCH_EN_NAME ELSE NULL END AS ACCTCUST_BRANCH_EN_NAME,CASE WHEN 1 = 0 THEN C.ACCTCUST_SUBSIDIARY_CODE ELSE NULL END AS ACCTCUST_SUBSIDIARY_CODE,CASE WHEN 1 = 0 THEN C.ACCTCUST_SUBSIDIARY_CN_NAM ELSE NULL END AS ACCTCUST_SUBSIDIARY_CN_NAM,CASE WHEN 1 = 0 THEN C.ACCTCUST_SUBSIDIARY_EN_NAM ELSE NULL END AS ACCTCUST_SUBSIDIARY_EN_NAM,CASE WHEN 1 = 0 THEN C.COUNTRY_CODE ELSE NULL END AS COUNTRY_CODE --新增加入参 ,CASE WHEN 1 = 0 THEN C.COUNTRY_CN_NAME ELSE NULL END AS COUNTRY_CN_NAME --新增加入参 ,CASE WHEN 1 = 0 THEN C.COUNTRY_EN_NAME ELSE NULL END AS COUNTRY_EN_NAME --新增加入参 , SUM(C.AGREE_AMOUNT) AS AGREE_AMOUNT --协议金额 , SUM(C.AGREE_REMAIN_AMOUNT) AS AGREE_REMAIN_AMOUNT --协议剩余金额 , SUM(C.SIGN_AMOUNT) AS SIGN_AMOUNT --可用金额 , SUM(C.USE_AMOUNT) AS USE_AMOUNT --已使用金额 , SUM( CASE WHEN C.DSCT_TYPE = 'VOUCHER' AND NVL( C.EXPIRED_DATE, add_months(C.EFFECTIVE_DATE, C.VALID_MONTH) ) >= to_date(T.V_DATE, 'yyyymmdd') THEN C.AGREE_REMAIN_AMOUNT WHEN C.DSCT_TYPE in ( 'FOC', 'Volume Based List Price Adjustment', 'One-Time Discount' ) AND C.DSCT_END_DATE >= to_date(T.V_DATE, 'yyyymmdd') THEN C.EFFECTIVE_TOTAL_AMOUNT ELSE NULL END ) AS NOT_USED_VALID_AMOUNT --未使用金额(有效期外) , SUM( CASE WHEN C.DSCT_TYPE = 'VOUCHER' THEN C.SIGN_AMOUNT ELSE C.EFFECTIVE_TOTAL_AMOUNT END - CASE WHEN C.DSCT_TYPE = 'VOUCHER' THEN C.USE_AMOUNT ELSE C.USED_TOTAL_AMOUNT END - CASE WHEN C.DSCT_TYPE = 'VOUCHER' AND NVL( C.EXPIRED_DATE, add_months(C.EFFECTIVE_DATE, C.VALID_MONTH) ) >= to_date(T.V_DATE, 'yyyymmdd') THEN C.AGREE_REMAIN_AMOUNT WHEN C.DSCT_TYPE in ( 'FOC', 'Volume Based List Price Adjustment', 'One-Time Discount' ) AND C.DSCT_END_DATE >= to_date(T.V_DATE, 'yyyymmdd') THEN C.EFFECTIVE_TOTAL_AMOUNT ELSE NULL END ) AS NOT_USED_INVALID_AMOUNT --未使用金额(有效期内) , SUM( CASE WHEN C.DSCT_TYPE = 'VOUCHER' AND C.EXPIRED_DATE >= to_date(substr(T.V_DATE, 1, 6), 'yyyymm') and C.EXPIRED_DATE <= LAST_DAY(to_date(T.V_DATE, 'yyyymmdd')) THEN C.SIGN_AMOUNT WHEN C.DSCT_TYPE in ( 'FOC', 'Volume Based List Price Adjustment', 'One-Time Discount' ) AND C.DSCT_START_DATE >= to_date(substr(T.V_DATE, 1, 6), 'yyyymm') and C.DSCT_START_DATE <= LAST_DAY(to_date(T.V_DATE, 'yyyymmdd')) THEN C.EFFECTIVE_TOTAL_AMOUNT ELSE NULL END ) AS NEW_SIGN_AMOUNT --本月新增可用金额 , SUM( CASE WHEN C.DSCT_TYPE = 'VOUCHER' AND C.EFFECTIVE_DATE >= to_date(substr(T.V_DATE, 1, 6), 'yyyymm') and C.EFFECTIVE_DATE <= LAST_DAY(to_date(T.V_DATE, 'yyyymmdd')) THEN C.USE_AMOUNT WHEN C.DSCT_TYPE in ( 'FOC', 'Volume Based List Price Adjustment', 'One-Time Discount' ) AND C.DSCT_START_DATE >= to_date(substr(T.V_DATE, 1, 6), 'yyyymm') and C.DSCT_START_DATE <= LAST_DAY(to_date(T.V_DATE, 'yyyymmdd')) THEN C.USED_TOTAL_AMOUNT ELSE NULL END ) AS NEW_USE_AMOUNT --本月新使用金额 , SUM( CASE WHEN C.DSCT_TYPE = 'VOUCHER' AND C.EXPIRED_DATE < to_date(T.V_DATE, 'yyyymmdd') THEN C.AGREE_REMAIN_AMOUNT WHEN C.DSCT_TYPE in ( 'FOC', 'Volume Based List Price Adjustment', 'One-Time Discount' ) AND C.DSCT_END_DATE < to_date(T.V_DATE, 'yyyymmdd') THEN C.EFFECTIVE_TOTAL_AMOUNT ELSE NULL END ) AS EXPIRED_AMOUNT --本月已过期金额 , SUM( CASE WHEN C.DSCT_TYPE = 'VOUCHER' AND C.EXPIRED_DATE BETWEEN to_date(T.V_DATE, 'yyyymmdd') AND add_months(to_date(T.V_DATE, 'yyyymmdd'), 6) THEN C.AGREE_REMAIN_AMOUNT WHEN C.DSCT_TYPE in ( 'FOC', 'Volume Based List Price Adjustment', 'One-Time Discount' ) AND C.DSCT_END_DATE BETWEEN to_date(T.V_DATE, 'yyyymmdd') AND add_months(to_date(T.V_DATE, 'yyyymmdd'), 6) THEN C.EFFECTIVE_TOTAL_AMOUNT ELSE NULL END ) AS IMMED_EXPIRED_AMOUNT --半年内即将过期金额 FROM DMSALESW.DM_SALE_BUSI_DSCT_SUM_F C LEFT JOIN TMP T ON 1 = 1 WHERE C.CURRENCY_CODE IN ('USD') --改为多值 AND C.BG_CODE IN ('PDCG901159') AND C.M_ID IN ( 'BUSI_DSCT_00001', 'BUSI_DSCT_00002', 'BUSI_DSCT_00003', 'BUSI_DSCT_00004', 'BUSI_DSCT_00005', 'BUSI_DSCT_00006', 'BUSI_DSCT_00007' ) --新增加字段 --AND C.M_CN IN ('#[#P_REPORT_ITEM_NAME#]#') --新增加字段 --新增加字段 GROUP BY C.BG_CODE, C.BG_CN_NAME, C.BG_EN_NAME, C.M_ID --指标ID , C.M_CN --指标中文名称 , C.M_EN --指标英文名称 , C.CURRENCY_CODE --币种 ,CASE WHEN 1 = 0 THEN C.OVERSEA_FLAG ELSE NULL END,CASE WHEN 1 = 0 THEN C.REGION_CODE ELSE NULL END,CASE WHEN 1 = 0 THEN C.REGION_CN_NAME ELSE NULL END,CASE WHEN 1 = 0 THEN C.REGION_EN_NAME ELSE NULL END,CASE WHEN 1 = 0 THEN C.REPOFFICE_CODE ELSE NULL END,CASE WHEN 1 = 0 THEN C.REPOFFICE_CN_NAME ELSE NULL END,CASE WHEN 1 = 0 THEN C.REPOFFICE_EN_NAME ELSE NULL END,CASE WHEN 1 = 0 THEN C.OFFICE_CODE ELSE NULL END,CASE WHEN 1 = 0 THEN C.OFFICE_CN_NAME ELSE NULL END,CASE WHEN 1 = 0 THEN C.OFFICE_EN_NAME ELSE NULL END,CASE WHEN 1 = 0 THEN C.REGION_CUSTCATG_CODE ELSE NULL END,CASE WHEN 1 = 0 THEN C.REGION_CUSTCATG_CN_NAME ELSE NULL END,CASE WHEN 1 = 0 THEN C.REGION_CUSTCATG_EN_NAME ELSE NULL END,CASE WHEN 1 = 0 THEN C.TOP_CUST_CATEGORY_CODE ELSE NULL END,CASE WHEN 1 = 0 THEN C.TOP_CUST_CATEGORY_EN_NAME ELSE NULL END,CASE WHEN 1 = 0 THEN C.TOP_CUST_CATEGORY_CN_NAME ELSE NULL END,CASE WHEN 1 = 0 THEN C.ACCTCUST_HQ_CODE ELSE NULL END,CASE WHEN 1 = 0 THEN C.ACCTCUST_HQ_CN_NAME ELSE NULL END,CASE WHEN 1 = 0 THEN C.ACCTCUST_HQ_EN_NAME ELSE NULL END,CASE WHEN 1 = 0 THEN C.ACCTCUST_BRANCH_CODE ELSE NULL END,CASE WHEN 1 = 0 THEN C.ACCTCUST_BRANCH_CN_NAME ELSE NULL END,CASE WHEN 1 = 0 THEN C.ACCTCUST_BRANCH_EN_NAME ELSE NULL END,CASE WHEN 1 = 0 THEN C.ACCTCUST_SUBSIDIARY_CODE ELSE NULL END,CASE WHEN 1 = 0 THEN C.ACCTCUST_SUBSIDIARY_CN_NAM ELSE NULL END,CASE WHEN 1 = 0 THEN C.ACCTCUST_SUBSIDIARY_EN_NAM ELSE NULL END,CASE WHEN 1 = 0 THEN C.COUNTRY_CODE ELSE NULL END --新增加入参 ,CASE WHEN 1 = 0 THEN C.COUNTRY_CN_NAME ELSE NULL END --新增加入参 ,CASE WHEN 1 = 0 THEN C.COUNTRY_EN_NAME ELSE NULL END ) T --新增加入参
从SQL中可以看到TMP为标量子查询,并且在子查询T中和物理表C做了笛卡尔积。 下面是该SQL的执行计划:
id | operation | A-time | A-rows | E-rows | E-distinct | Peak Memory | E-memory | A-width | E-width | E-costs ----+-------------------------------------------------------------------------+----------------------+---------+---------+------------+----------------+----------+-----------+---------+----------- 1 | -> Row Adapter | 3037.648 | 7 | 245 | | 419KB | | | 1318 | 117210.62 2 | -> Vector Streaming (type: GATHER) | 3037.633 | 7 | 245 | | 777KB | | | 1318 | 117210.62 3 | -> Vector Hash Aggregate | [3031.872, 3032.516] | 7 | 245 | | [4MB, 4MB] | 16MB | [0,870] | 557 | 117128.41 4 | -> Vector Streaming(type: REDISTRIBUTE) | [3031.560, 3032.232] | 112 | 3920 | | [1MB, 1MB] | 2MB | | 557 | 116852.33 5 | -> Vector Hash Aggregate | [2728.059, 2909.255] | 112 | 3920 | | [8MB, 8MB] | 16MB | [833,833] | 557 | 116699.48 6 | -> Vector Nest Loop Left Join (7, 8) | [441.050, 471.725] | 3007901 | 2106919 | | [1MB, 1MB] | 1MB | | 237 | 67316.28 7 | -> CStore Scan on dmsalesw.dm_sale_busi_dsct_sum_f c | [145.354, 158.560] | 3007901 | 2106919 | | [5MB, 5MB] | 1MB | | 205 | 65011.82 8 | -> Vector Materialize | [32.034, 38.902] | 3007901 | 1 | | [288KB, 288KB] | 16MB | [21,21] | 32 | 0.03 9 | -> Vector Subquery Scan on dual | [0.067, 0.093] | 16 | 1 | | [128KB, 128KB] | 1MB | | 32 | 0.02 10 | -> Vector Adapter | [0.005, 0.006] | 16 | 1 | | [40KB, 40KB] | 1MB | | 0 | 0.01 11 | -> Result | [0.001, 0.002] | 16 | 1 | | [8KB, 8KB] | 1MB | | 0 | 0.01
把TMP作为一列放到T中后,性能有明显提升。
EXPLAIN PERFORMANCE SELECT BG_CODE, BG_CN_NAME, BG_EN_NAME, METRIC_CODE --指标ID , METRIC_CN_NAME --指标中文名称 , METRIC_EN_NAME --指标英文名称 , CURRENCY --币种 , OVERSEAS_FLAG, REGION_CODE, REGION_CN_NAME, REGION_EN_NAME, REPOFFICE_CODE, REPOFFICE_CN_NAME, REPOFFICE_EN_NAME, OFFICE_CODE, OFFICE_CN_NAME, OFFICE_EN_NAME, REGION_CUSTCATG_CODE, REGION_CUSTCATG_CN_NAME, REGION_CUSTCATG_EN_NAME, TOP_CUST_CATEGORY_CODE, TOP_CUST_CATEGORY_EN_NAME, TOP_CUST_CATEGORY_CN_NAME, ACCTCUST_HQ_CODE, ACCTCUST_HQ_CN_NAME, ACCTCUST_HQ_EN_NAME, ACCTCUST_BRANCH_CODE, ACCTCUST_BRANCH_CN_NAME, ACCTCUST_BRANCH_EN_NAME, ACCTCUST_SUBSIDIARY_CODE, ACCTCUST_SUBSIDIARY_CN_NAM, ACCTCUST_SUBSIDIARY_EN_NAM, COUNTRY_CODE --新增加入参 , COUNTRY_CN_NAME --新增加入参 , COUNTRY_EN_NAME --新增加入参 , AGREE_AMOUNT --BUSI_DSCT_00001 总优惠 , AGREE_REMAIN_AMOUNT --BUSI_DSCT_00002 即期优惠 , SIGN_AMOUNT --BUSI_DSCT_00003 即期优惠/一次性优惠 , USE_AMOUNT --BUSI_DSCT_00004 即期优惠/单价量折扣 , NOT_USED_VALID_AMOUNT --BUSI_DSCT_00005 延期优惠 , NOT_USED_INVALID_AMOUNT --BUSI_DSCT_00006 voucher , NEW_SIGN_AMOUNT --BUSI_DSCT_00007 其他延期优惠 , NEW_USE_AMOUNT --BUSI_DSCT_00008 本月新使用金额 , EXPIRED_AMOUNT --BUSI_DSCT_00009 本月已过期金额 , IMMED_EXPIRED_AMOUNT --BUSI_DSCT_00010 半年内即将过期金额 FROM ( SELECT case when length('[“202309“]') = 6 then '[“202309“]' || '01' WHEN length('[“202309“]') <> 8 THEN TO_CHAR(CURRENT_DATE, 'YYYYMMDD') END AS V_DATE, C.BG_CODE, C.BG_CN_NAME, C.BG_EN_NAME, C.M_ID AS METRIC_CODE --指标ID , C.M_CN AS METRIC_CN_NAME --指标中文名称 , C.M_EN AS METRIC_EN_NAME --指标英文名称 , C.CURRENCY_CODE AS CURRENCY --币种 ,CASE WHEN 1 = 0 THEN C.OVERSEA_FLAG ELSE NULL END AS OVERSEAS_FLAG,CASE WHEN 1 = 0 THEN C.REGION_CODE ELSE NULL END AS REGION_CODE,CASE WHEN 1 = 0 THEN C.REGION_CN_NAME ELSE NULL END AS REGION_CN_NAME,CASE WHEN 1 = 0 THEN C.REGION_EN_NAME ELSE NULL END AS REGION_EN_NAME,CASE WHEN 1 = 0 THEN C.REPOFFICE_CODE ELSE NULL END AS REPOFFICE_CODE,CASE WHEN 1 = 0 THEN C.REPOFFICE_CN_NAME ELSE NULL END AS REPOFFICE_CN_NAME,CASE WHEN 1 = 0 THEN C.REPOFFICE_EN_NAME ELSE NULL END AS REPOFFICE_EN_NAME,CASE WHEN 1 = 0 THEN C.OFFICE_CODE ELSE NULL END AS OFFICE_CODE,CASE WHEN 1 = 0 THEN C.OFFICE_CN_NAME ELSE NULL END AS OFFICE_CN_NAME,CASE WHEN 1 = 0 THEN C.OFFICE_EN_NAME ELSE NULL END AS OFFICE_EN_NAME,CASE WHEN 1 = 0 THEN C.REGION_CUSTCATG_CODE ELSE NULL END AS REGION_CUSTCATG_CODE,CASE WHEN 1 = 0 THEN C.REGION_CUSTCATG_CN_NAME ELSE NULL END AS REGION_CUSTCATG_CN_NAME,CASE WHEN 1 = 0 THEN C.REGION_CUSTCATG_EN_NAME ELSE NULL END AS REGION_CUSTCATG_EN_NAME,CASE WHEN 1 = 0 THEN C.TOP_CUST_CATEGORY_CODE ELSE NULL END AS TOP_CUST_CATEGORY_CODE,CASE WHEN 1 = 0 THEN C.TOP_CUST_CATEGORY_EN_NAME ELSE NULL END AS TOP_CUST_CATEGORY_EN_NAME,CASE WHEN 1 = 0 THEN C.TOP_CUST_CATEGORY_CN_NAME ELSE NULL END AS TOP_CUST_CATEGORY_CN_NAME,CASE WHEN 1 = 0 THEN C.ACCTCUST_HQ_CODE ELSE NULL END AS ACCTCUST_HQ_CODE,CASE WHEN 1 = 0 THEN C.ACCTCUST_HQ_CN_NAME ELSE NULL END AS ACCTCUST_HQ_CN_NAME,CASE WHEN 1 = 0 THEN C.ACCTCUST_HQ_EN_NAME ELSE NULL END AS ACCTCUST_HQ_EN_NAME,CASE WHEN 1 = 0 THEN C.ACCTCUST_BRANCH_CODE ELSE NULL END AS ACCTCUST_BRANCH_CODE,CASE WHEN 1 = 0 THEN C.ACCTCUST_BRANCH_CN_NAME ELSE NULL END AS ACCTCUST_BRANCH_CN_NAME,CASE WHEN 1 = 0 THEN C.ACCTCUST_BRANCH_EN_NAME ELSE NULL END AS ACCTCUST_BRANCH_EN_NAME,CASE WHEN 1 = 0 THEN C.ACCTCUST_SUBSIDIARY_CODE ELSE NULL END AS ACCTCUST_SUBSIDIARY_CODE,CASE WHEN 1 = 0 THEN C.ACCTCUST_SUBSIDIARY_CN_NAM ELSE NULL END AS ACCTCUST_SUBSIDIARY_CN_NAM,CASE WHEN 1 = 0 THEN C.ACCTCUST_SUBSIDIARY_EN_NAM ELSE NULL END AS ACCTCUST_SUBSIDIARY_EN_NAM,CASE WHEN 1 = 0 THEN C.COUNTRY_CODE ELSE NULL END AS COUNTRY_CODE --新增加入参 ,CASE WHEN 1 = 0 THEN C.COUNTRY_CN_NAME ELSE NULL END AS COUNTRY_CN_NAME --新增加入参 ,CASE WHEN 1 = 0 THEN C.COUNTRY_EN_NAME ELSE NULL END AS COUNTRY_EN_NAME --新增加入参 , SUM(C.AGREE_AMOUNT) AS AGREE_AMOUNT --协议金额 , SUM(C.AGREE_REMAIN_AMOUNT) AS AGREE_REMAIN_AMOUNT --协议剩余金额 , SUM(C.SIGN_AMOUNT) AS SIGN_AMOUNT --可用金额 , SUM(C.USE_AMOUNT) AS USE_AMOUNT --已使用金额 , SUM( CASE WHEN C.DSCT_TYPE = 'VOUCHER' AND NVL( C.EXPIRED_DATE, add_months(C.EFFECTIVE_DATE, C.VALID_MONTH) ) >= to_date(V_DATE, 'yyyymmdd') THEN C.AGREE_REMAIN_AMOUNT WHEN C.DSCT_TYPE in ( 'FOC', 'Volume Based List Price Adjustment', 'One-Time Discount' ) AND C.DSCT_END_DATE >= to_date(V_DATE, 'yyyymmdd') THEN C.EFFECTIVE_TOTAL_AMOUNT ELSE NULL END ) AS NOT_USED_VALID_AMOUNT --未使用金额(有效期外) , SUM( CASE WHEN C.DSCT_TYPE = 'VOUCHER' THEN C.SIGN_AMOUNT ELSE C.EFFECTIVE_TOTAL_AMOUNT END - CASE WHEN C.DSCT_TYPE = 'VOUCHER' THEN C.USE_AMOUNT ELSE C.USED_TOTAL_AMOUNT END - CASE WHEN C.DSCT_TYPE = 'VOUCHER' AND NVL( C.EXPIRED_DATE, add_months(C.EFFECTIVE_DATE, C.VALID_MONTH) ) >= to_date(V_DATE, 'yyyymmdd') THEN C.AGREE_REMAIN_AMOUNT WHEN C.DSCT_TYPE in ( 'FOC', 'Volume Based List Price Adjustment', 'One-Time Discount' ) AND C.DSCT_END_DATE >= to_date(V_DATE, 'yyyymmdd') THEN C.EFFECTIVE_TOTAL_AMOUNT ELSE NULL END ) AS NOT_USED_INVALID_AMOUNT --未使用金额(有效期内) , SUM( CASE WHEN C.DSCT_TYPE = 'VOUCHER' AND C.EXPIRED_DATE >= to_date(substr(V_DATE, 1, 6), 'yyyymm') and C.EXPIRED_DATE <= LAST_DAY(to_date(V_DATE, 'yyyymmdd')) THEN C.SIGN_AMOUNT WHEN C.DSCT_TYPE in ( 'FOC', 'Volume Based List Price Adjustment', 'One-Time Discount' ) AND C.DSCT_START_DATE >= to_date(substr(V_DATE, 1, 6), 'yyyymm') and C.DSCT_START_DATE <= LAST_DAY(to_date(V_DATE, 'yyyymmdd')) THEN C.EFFECTIVE_TOTAL_AMOUNT ELSE NULL END ) AS NEW_SIGN_AMOUNT --本月新增可用金额 , SUM( CASE WHEN C.DSCT_TYPE = 'VOUCHER' AND C.EFFECTIVE_DATE >= to_date(substr(V_DATE, 1, 6), 'yyyymm') and C.EFFECTIVE_DATE <= LAST_DAY(to_date(V_DATE, 'yyyymmdd')) THEN C.USE_AMOUNT WHEN C.DSCT_TYPE in ( 'FOC', 'Volume Based List Price Adjustment', 'One-Time Discount' ) AND C.DSCT_START_DATE >= to_date(substr(V_DATE, 1, 6), 'yyyymm') and C.DSCT_START_DATE <= LAST_DAY(to_date(V_DATE, 'yyyymmdd')) THEN C.USED_TOTAL_AMOUNT ELSE NULL END ) AS NEW_USE_AMOUNT --本月新使用金额 , SUM( CASE WHEN C.DSCT_TYPE = 'VOUCHER' AND C.EXPIRED_DATE < to_date(V_DATE, 'yyyymmdd') THEN C.AGREE_REMAIN_AMOUNT WHEN C.DSCT_TYPE in ( 'FOC', 'Volume Based List Price Adjustment', 'One-Time Discount' ) AND C.DSCT_END_DATE < to_date(V_DATE, 'yyyymmdd') THEN C.EFFECTIVE_TOTAL_AMOUNT ELSE NULL END ) AS EXPIRED_AMOUNT --本月已过期金额 , SUM( CASE WHEN C.DSCT_TYPE = 'VOUCHER' AND C.EXPIRED_DATE BETWEEN to_date(V_DATE, 'yyyymmdd') AND add_months(to_date(V_DATE, 'yyyymmdd'), 6) THEN C.AGREE_REMAIN_AMOUNT WHEN C.DSCT_TYPE in ( 'FOC', 'Volume Based List Price Adjustment', 'One-Time Discount' ) AND C.DSCT_END_DATE BETWEEN to_date(V_DATE, 'yyyymmdd') AND add_months(to_date(V_DATE, 'yyyymmdd'), 6) THEN C.EFFECTIVE_TOTAL_AMOUNT ELSE NULL END ) AS IMMED_EXPIRED_AMOUNT --半年内即将过期金额 FROM DMSALESW.DM_SALE_BUSI_DSCT_SUM_F C WHERE C.CURRENCY_CODE IN ('USD') --改为多值 AND C.BG_CODE IN ('PDCG901159') AND C.M_ID IN ( 'BUSI_DSCT_00001', 'BUSI_DSCT_00002', 'BUSI_DSCT_00003', 'BUSI_DSCT_00004', 'BUSI_DSCT_00005', 'BUSI_DSCT_00006', 'BUSI_DSCT_00007' ) --新增加字段 --AND C.M_CN IN ('#[#P_REPORT_ITEM_NAME#]#') --新增加字段 --新增加字段 GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36
) T --新增加入参
下面是执行计划:
id | operation | A-time | A-rows | E-rows | E-distinct | Peak Memory | E-memory | A-width | E-width | E-costs ----+-------------------------------------------------------------------------+----------------------+---------+---------+------------+----------------+----------+-----------+---------+----------- 1 | -> Row Adapter | 1139.637 | 7 | 245 | | 419KB | | | 1318 | 117002.27 2 | -> Vector Streaming (type: GATHER) | 1139.616 | 7 | 245 | | 777KB | | | 1318 | 117002.27 3 | -> Vector Subquery Scan on t | [1129.463, 1130.072] | 7 | 245 | | [504KB, 504KB] | 1MB | | 1318 | 116920.22 4 | -> Vector Hash Aggregate | [1129.459, 1130.067] | 7 | 245 | | [4MB, 4MB] | 16MB | [0,898] | 523 | 116920.07 5 | -> Vector Streaming(type: REDISTRIBUTE) | [1129.142, 1129.918] | 112 | 3920 | | [1MB, 1MB] | 2MB | | 523 | 116643.28 6 | -> Vector Hash Aggregate | [882.194, 987.474] | 112 | 3920 | | [8MB, 8MB] | 16MB | [861,861] | 523 | 116498.95 7 | -> CStore Scan on dmsalesw.dm_sale_busi_dsct_sum_f c | [126.343, 142.697] | 3080954 | 2135243 | | [5MB, 5MB] | 1MB | | 203 | 66116.77
可以看到,不但省去了Nest Loop的耗时,而且后面Aggregate的耗时也减少了不少。整体从3s+优化到1.2s。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 我与微信审核的“相爱相杀”看个人小程序副业
2022-11-30 云小课|云小课教您如何选择Redis实例类型
2022-11-30 DTSE Tech Talk 第13期:Serverless凭什么被誉为未来云计算范式?
2021-11-30 开发好能重构的代码,都是这么干的
2021-11-30 文本分类:Keras+RNN vs传统机器学习
2021-11-30 带你了解敏捷和DevOps的发布策略
2021-11-30 从1天到10分钟的超越,华为云DRS在背后做了这些
2020-11-30 从应用迁移到平台微认证:鲲鹏技术解读