数仓中典型的几种不下推语句整改案例
本文分享自华为云社区《GaussDB(DWS)性能调优:典型不下推语句整改案例》,作者: 譡里个檔 。
场景1:With-Recursive contains only values rte is not shippable
根因:递归语句的某个分支中没有FROM字句(只有 VALUES 或者类似 SELECT 1 这样的语句)
案例1:递归驱动分支没有FROM字句
原始语句
【资料图】
SELECT T.RPT_ITEM_ID, --报表项IDT.RPT_ITEM_CODE,T.USER_GROUP_CODE AS USER_GROUP_CODE --用户组FROM BIF.BIF_RPT_ITEM_DEF_T T,(WITH recursive cte AS (SELECT DISTINCT TRIM(SUBSTR("" :: text, INSTR("", ",", 1, 1) + 1, INSTR("", ",", 1, 2) - INSTR("", ",", 1, 1) - 1)) AS cte_RPT_ITEM_CODE,1 AS levelFROM (SELECT "") AS tb0UNION ALLSELECT DISTINCT TRIM(SUBSTR("" :: text, INSTR("", ",", 1, cte.level + 1) + 1, INSTR("", ",", 1, cte.level + 2) - INSTR("", ",", 1, cte.level + 1) - 1)),cte.level + 1FROM (SELECT "") AS tb0, cteWHERE cte.level + 1 <= LENGTH("") - LENGTH(REPLACE("", ",", "")) - 1)SELECTDISTINCT cte_RPT_ITEM_CODE AS RPT_ITEM_CODEFROM cte) T5WHERE NVL(INSTR(T.RPT_ITEM_FREQUENCE, "M"), 0) > 0AND T.RPT_ITEM_CODE = NVL(T5.RPT_ITEM_CODE, T.RPT_ITEM_CODE)AND T.RPT_ITEM_TYPE = 1 --是否是叶子报表项,1=是,0=否,基本报表项AND T.ENABLE_FLAG = 1AND T.VERSION = "202308" --使用快照,增加条件限制ORDER BY T.RPT_ITEM_ID
改写语句
SELECT T.RPT_ITEM_ID, --报表项IDT.RPT_ITEM_CODE,T.USER_GROUP_CODE AS USER_GROUP_CODE --用户组FROM BIF.BIF_RPT_ITEM_DEF_T T,(WITH recursive cte AS (SELECT DISTINCT TRIM(SUBSTR("" :: text, INSTR("", ",", 1, 1) + 1, INSTR("", ",", 1, 2) - INSTR("", ",", 1, 1) - 1)) AS cte_RPT_ITEM_CODE,1 AS levelFROM (SELECT "") AS tb0UNION ALLSELECT DISTINCT TRIM(SUBSTR("" :: text, INSTR("", ",", 1, cte.level + 1) + 1, INSTR("", ",", 1, cte.level + 2) - INSTR("", ",", 1, cte.level + 1) - 1)),cte.level + 1FROM (SELECT "") AS tb0, cteWHERE cte.level + 1 <= LENGTH("") - LENGTH(REPLACE("", ",", "")) - 1)SELECTDISTINCT cte_RPT_ITEM_CODE AS RPT_ITEM_CODEFROM cte) T5WHERE NVL(INSTR(T.RPT_ITEM_FREQUENCE, "M"), 0) > 0AND T.RPT_ITEM_CODE = NVL(T5.RPT_ITEM_CODE, T.RPT_ITEM_CODE)AND T.RPT_ITEM_TYPE = 1 --是否是叶子报表项,1=是,0=否,基本报表项AND T.ENABLE_FLAG = 1AND T.VERSION = "202308" --使用快照,增加条件限制ORDER BY T.RPT_ITEM_ID
修改点比对
案例2:递归驱动分支没有FROM字句
原始语句
SELECT A.DYNM_COMP_ID,DECODE(B.LINE_NO, 1, "202308", A.VERSION)FROM BIF.BIF_DYNM_COMP_SOU_TBL_V A,(WITH recursive cte AS (SELECT 1 AS levelUNION ALLSELECT cte.level + 1FROM cteWHERE cte.level + 1 < 3)SELECTlevel as LINE_NOFROM cte) BWHERE EXISTS (SELECT 1FROM BIF.BIF_RPT_ITEM_DEF_MV RPT,BIF.BIF_PUB_SUBJECT_AREA_T SBJ,BIF.BIF_SNAPSHORT_SUBJECT_V TYPWHERE A.DYNM_COMP_ID = RPT.DYNM_COMP_IDAND RPT.VERSION = "current"AND RPT.SUBJECT_AREA_ID = SBJ.SUBJECT_AREA_IDAND SBJ.SUBJECT_AREA_CODE =TYP.SUBJECT_CODEAND TYP.SUBJECT_TYPE ="TAX")AND A.VERSION = "current"
改写语句
SELECT A.DYNM_COMP_ID,DECODE(B.LINE_NO, 1, "202308", A.VERSION)FROM BIF.BIF_DYNM_COMP_SOU_TBL_V A,(SELECT*FROM generate_series(1, 2) AS cte(LINE_NO)) BWHERE EXISTS (SELECT 1FROM BIF.BIF_RPT_ITEM_DEF_MV RPT,BIF.BIF_PUB_SUBJECT_AREA_T SBJ,BIF.BIF_SNAPSHORT_SUBJECT_V TYPWHERE A.DYNM_COMP_ID = RPT.DYNM_COMP_IDAND RPT.VERSION = "current"AND RPT.SUBJECT_AREA_ID = SBJ.SUBJECT_AREA_IDAND SBJ.SUBJECT_AREA_CODE =TYP.SUBJECT_CODEAND TYP.SUBJECT_TYPE ="TAX")AND A.VERSION = "current"
修改点比对
案例3:递归驱动分支是VALUES字句
原始语句
WITH RECURSIVE t(n) AS (VALUES (1)UNION ALLSELECTn+1FROM tWHERE n < (SELECT MAX(LENGTH(COMP_CODE)-LENGTH(REPLACE(COMP_CODE,",","")))+1 MAX_TOKENSFROM (SELECT DEPT_CODE,to_char(APPLICABLE_GEO_PC_CODE) COMP_CODEFROM SDIHR.MDM_CDM_DEPT_ACT_INFO_T_3600)))SELECT n AS LVL FROM t
改写语句
WITH RECURSIVE t(n) AS (SELECT * FROM generate_series(1, 1)UNION ALLSELECTn+1FROM tWHERE n < (SELECT MAX(LENGTH(COMP_CODE)-LENGTH(REPLACE(COMP_CODE,",","")))+1 MAX_TOKENSFROM (SELECT DEPT_CODE,to_char(APPLICABLE_GEO_PC_CODE) COMP_CODEFROM SDIHR.MDM_CDM_DEPT_ACT_INFO_T_3600)))SELECT n AS LVL FROM t
修改点比对
案例4:递归驱动分支是VALUES字句
原始语句
WITH RECURSIVE t(n) AS (VALUES (1)UNION ALLSELECTn+1FROM tWHERE n < (SELECT MAX(LENGTH(COMP_CODE)-LENGTH(REPLACE(COMP_CODE,",","")))+1 MAX_TOKENSFROM (SELECT DEPT_CODE,to_char(APPLICABLE_GEO_PC_CODE) COMP_CODEFROM SDIHR.MDM_CDM_DEPT_ACT_INFO_T_3600)))SELECT n AS LVL FROM t
改写语句
SELECT*FROM generate_series(1, (SELECT MAX(LENGTH(COMP_CODE)-LENGTH(REPLACE(COMP_CODE,",","")))+1 MAX_TOKENSFROM (SELECT DEPT_CODE,to_char(APPLICABLE_GEO_PC_CODE) COMP_CODEFROM SDIHR.MDM_CDM_DEPT_ACT_INFO_T_3600))) AS t(lvl)
修改点比对
场景2:With-Recursive contains system table is not shippable
根因:递归语句的某个分支中没有FROM字句中只用系统表或者系统视图(DUAL也被视为系统视图)
案例1:递归驱动分支是FROM DUAL查询
原始语句
WITH recursive cte AS (SELECTTO_DATE(201701, "YYYYMM") as level ,TO_DATE(20170131, "YYYYMMDD") LASTDAYFROM dualUNION ALLSELECTADD_MONTHS(cte.LEVEL, 1) AS PERIOD,LAST_DAY(ADD_MONTHS(cte.LEVEL, 1)) AS LASTDAYFROM cte WHERE cte.LEVEL <=SYSDATE)SELECTTO_CHAR(cte.level,"YYYYMMDD") AS PERIOD , cte.LASTDAYFROM cteWHERE TO_CHAR(cte.level,"YYYYMMDD")<= TO_CHAR(SYSDATE,"YYYYMMDD")
改写语句
WITH recursive cte AS (SELECTTO_DATE(201701, "YYYYMM") as level ,TO_DATE(20170131, "YYYYMMDD") LASTDAYFROM dualUNION ALLSELECTADD_MONTHS(cte.LEVEL, 1) AS PERIOD,LAST_DAY(ADD_MONTHS(cte.LEVEL, 1)) AS LASTDAYFROM cte WHERE cte.LEVEL <=SYSDATE)SELECTTO_CHAR(cte.level,"YYYYMMDD") AS PERIOD , cte.LASTDAYFROM cteWHERE TO_CHAR(cte.level,"YYYYMMDD")<= TO_CHAR(SYSDATE,"YYYYMMDD")
修改点对比
场景3:SubPlan exec on CN can"t be shipped
根因:某个子查询语句只能在CN上执行,通常是这个子查询有不下推因素,比如有系统表、系统视图调用,或者存在不下推函数等
案例1:子查询中系统表/系统视图查询
原始语句
WITH error_log AS NOT MATERIALIZED (SELECTupper(log_column_name) AS log_column_name,log_error_code,s.char_length AS data_length,s.data_type,s.nullableFROM (SELECTdistinctunnest(string_to_array(bad_log_column_name,",")) AS log_column_name,unnest(string_to_array(bad_log_error_code,",")) AS log_error_codeFROM stgltc.BAD_cfs_inv_invoice_ad_2500) T,(SELECT * FROM user_tab_columns WHERE table_name=lower("dlt_cfs_inv_invoice_ad_2500")) SWHERE upper(T.log_column_name)=upper(S.column_name))SELECTCASE WHEN upper("ACTIVITY_NAME") IN (SELECT log_column_name FROM error_log WHERE data_type IN ("varchar","char","character","nchar","character varying","varchar2","nvarchar2","clob","text") AND log_error_code="22001"/*字符超长*/)THEN SUBSTRB(ACTIVITY_NAME,0,(SELECT distinct DATA_LENGTH FROM error_log WHERE upper(log_column_name)=upper("ACTIVITY_NAME")))ELSE ACTIVITY_NAMEEND AS ACTIVITY_NAME,CASE WHEN upper("ADJUSTMENT_ID") IN (SELECT log_column_name FROM error_log WHERE data_type IN ("varchar","char","character","nchar","character varying","varchar2","nvarchar2","clob","text") AND log_error_code="22001"/*字符超长*/)THEN SUBSTRB(ADJUSTMENT_ID,0,(SELECT distinct DATA_LENGTH FROM error_log WHERE upper(log_column_name)=upper("ADJUSTMENT_ID")))ELSE ADJUSTMENT_IDEND AS ADJUSTMENT_IDFROM stgltc.BAD_cfs_inv_invoice_ad_2500
改写语句
-- 识别不下推的子查询为WITH error_log字句中的-- SELECT * FROM user_tab_columns WHERE table_name=lower("dlt_cfs_inv_invoice_ad_2500")---- 因为这部分为系统表查询,无论如何都不能下推,所以此处把这部分结果转储到一个中间表中-- 中间表创建成行存表CREATE TEMP TABLE s WITH(orientation=row) DISTRIBUTE BY ROUNDROBIN ASSELECT * FROM user_tab_columns WHERE table_name=lower("dlt_cfs_inv_invoice_ad_2500")-- 因为整个查询涉及到的表都是列存表,之后前面创建的临时表s为行存表-- 所以此处加一个强制走向量化的hintWITH error_log AS NOT MATERIALIZED (SELECTupper(log_column_name) AS log_column_name,log_error_code,s.char_length AS data_length,s.data_type,s.nullableFROM (SELECTdistinctunnest(string_to_array(bad_log_column_name,",")) AS log_column_name,unnest(string_to_array(bad_log_error_code,",")) AS log_error_codeFROM stgltc.bad_cfs_inv_invoice_ad_2500) T,pg_temp.SWHERE upper(T.log_column_name)=upper(S.column_name))SELECT /*+ set global(enable_force_vector_engine on)*/CASE WHEN upper("ACTIVITY_NAME") IN (SELECT log_column_name FROM error_log WHERE data_type IN ("varchar","char","character","nchar","character varying","varchar2","nvarchar2","clob","text") AND log_error_code="22001"/*字符超长*/)THEN SUBSTRB(ACTIVITY_NAME,0,(SELECT distinct DATA_LENGTH FROM error_log WHERE upper(log_column_name)=upper("ACTIVITY_NAME")))ELSE ACTIVITY_NAMEEND AS ACTIVITY_NAME,CASE WHEN upper("ADJUSTMENT_ID") IN (SELECT log_column_name FROM error_log WHERE data_type IN ("varchar","char","character","nchar","character varying","varchar2","nvarchar2","clob","text") AND log_error_code="22001"/*字符超长*/)THEN SUBSTRB(ADJUSTMENT_ID,0,(SELECT distinct DATA_LENGTH FROM error_log WHERE upper(log_column_name)=upper("ADJUSTMENT_ID")))ELSE ADJUSTMENT_IDEND AS ADJUSTMENT_IDFROM stgltc.bad_cfs_inv_invoice_ad_2500
修改点对比
场景4:Type of Record in TargetList can not be shipped
根因:输出列中存在record类型,这种类型的不下推一般是不会体现在最外层的输出列上,一般这类报错有两个场景
1.SQL书写逻辑错误,导致输出列上出现了(...)形式的输出列
2.SQL业务逻辑正确, 这种场景需要了解业务含义,把record字段强转为text类型,然后再使用record字段的地方做特殊适配
案例1:输出列书写错误,出现(...)形式的输出列
原始语句
SELECTd.id,coalesce(d.period, "snull") AS period,(d.plan_unit_code, "snull") AS plan_unit_code,coalesce(d.product_type_model, "snull") AS product_type_model,coalesce(d.revision, "snull") AS revision,d.start_dateFROM (SELECT *FROM cdcscm.cdc_mp_d_forecast_t_6120 tWHERE t.cdc_timestamp > to_date("2023-07-06 00:00:00", "yyyy-mm-dd hh24:mi:ss") - 7AND t.cdc_timestamp < to_date("2023-08-08 00:00:00", "yyyy-mm-dd hh24:mi:ss")) t1,sdiscm.mp_d_forecast_t_6120 dWHERE (t1.audit_op_type = "delete" AND t1.audit_op_option = "before")AND d.id = t1.id
改写语句
SELECTd.id,coalesce(d.period, "snull") AS period,coalesce(d.plan_unit_code, "snull") AS plan_unit_code,coalesce(d.product_type_model, "snull") AS product_type_model,coalesce(d.revision, "snull") AS revision,d.start_dateFROM (SELECT *FROM cdcscm.cdc_mp_d_forecast_t_6120 tWHERE t.cdc_timestamp > to_date("2023-07-06 00:00:00", "yyyy-mm-dd hh24:mi:ss") - 7AND t.cdc_timestamp < to_date("2023-08-08 00:00:00", "yyyy-mm-dd hh24:mi:ss")) t1,sdiscm.mp_d_forecast_t_6120 dWHERE (t1.audit_op_type = "delete" AND t1.audit_op_option = "before")AND d.id = t1.id
修改点对比
备注:改写前后语句不等价,不等价的原因是因为原始SQL书写有问题,正确的写法是就是coalesce(d.plan_unit_code, "snull") AS plan_unit_code。
点击关注,第一时间了解华为云新鲜技术~
关键词:
上一篇:可判7年以上有期徒刑!“两高”联合发布司法解释,严惩污染环境罪
下一篇:最后一页
- 数仓中典型的几种不下推语句整改案例
- “江浙沪独生女”走红网络,这些区域的家庭,重男轻女观念消失了
- 潍坊滨海区:科技创新增添活力
- 可判7年以上有期徒刑!“两高”联合发布司法解释,严惩污染环境罪
- “部队来支援咱们这里了” 通信兵翻山越岭架起“生命线”
- “中华大粮仓”众志成城护安澜——来自黑龙江的抗洪一线报告
- 开发出主题景区、火锅店、书屋、博物馆等多种业态——重庆防空洞变身休闲“打卡地”
- 五新隧装:8月11日融资买入2.25万元,融资融券余额97.49万元
- 7月金融数据存贷双降,存量房贷降利率到底刺激了啥,民众应咋办
- 百度网盘关闭音频自动播放(百度网盘关闭)
-
百度网盘关闭音频自动播放(百度网盘关闭)
1、进入手机设置界面,点击【应用设置】。2、单击[应用程序管理]。3、
-
2023上半年京东折叠屏销量同比增长100%
8月14日消息,据京东销售情况显示,2023上半年京东折叠屏销量同比增长1
-
以学促管 训管并重 河北省衡水市市场监督管理局桃城区分局组织召开药械妆规范化经营培训会
中国质量新闻网讯为进一步促进药械妆领域质量安全监管能力提升,促进药
-
内马尔加盟利雅得新月只差体检 巴黎转1亿欧他赚1.6亿欧
北京时间8月14日,据多家媒体报道,内马尔加盟利雅得新月一事取得了突
-
中国光伏还有那些材料“受制于人”?
中国光伏还有那些材料“受制于人”?,经过20年的努力,中国光伏产业从
-
全责!小伙为炫朋友圈无证试驾新车,结果……
近日,佛山一名21岁小伙驾车失控撞上分隔带。面对交警的检查,他竟拿出
-
服贸会首钢园区新增“一高炉元宇宙乐园”,市政、交通等优化升级
2023年是中国国际服务贸易交易会以“一会两址”形式在石景山区首钢园举
-
机械工业主要经济指标较快增长
原标题:机械工业主要经济指标较快增长经济日报记者刘瑾中国机械工业联
-
iPhone 14电池为啥如此不耐用?今天来分析一下!
各位应该还记得前几天的一则消息,那就是网上有不少人爆出iPhone14系列
-
河北涿州:企业陆续复工复产 生产生活秩序加快恢复
消息:随着河北省涿州市的水电、交通等基础设施逐步恢复,当地多家企业
X 关闭
江永“五高”标准推进文旅融合
荆楚好老师群星计划|她一人带3个年级8门课,退休后还主动申请留在教学点支教
留学女高管评谷爱凌大学成绩:包装高大上只敢发国内,其实很一般
男子要女孩微信被拒后对其爸爸动手?系抖音用户编造杜撰视频
X 关闭
香江控股(600162)8月14日主力资金净卖出731.49万元
e5300cpu怎么样 e5300cpu性能评测 e5300配什么主板
农业农村部紧急部署蔬菜抗涝减灾和秋冬稳产保供工作
姚振华再失利,宝能系起诉南玻A遭驳回,中炬高新可能面临什么?
下滑94%!2000亿巨头业绩“暴雷”,二季度净利润仅1.1亿