热门搜索
分布式
数据库一体机
云数据库
建设银行
保险核心
密集交易
注册

空格对SQL执行时间的影响

达梦技术-郭洋 2025/04/16 224 2

为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。
【DM版本】:03134284336-20250117-257733-20132
【操作系统】：麒麟V10
【CPU】:Kunpeng-920 16核
【问题描述】
在一次给用户调优SQL的事情中发现：

SELECT
/*+ENABLE_RQ_TO_NONREF_SPL(1)*/
 r.region_code AS id,
 r.region_name AS name,
 r.region_type,
 IFNULL( sum( a.xfbm_wsl_qxn ), 0 ) AS count1,
 IFNULL( sum( a.xfbm_wsl_jdq ), 0 ) AS count2,
 IFNULL( sum( a.xfbm_wsl_yyq ), 0 ) AS count3,
 IFNULL( sum( a.zrdw_wsl_qxn ), 0 ) AS count4,
 IFNULL( sum( a.zrdw_wsl_jdq ), 0 ) AS count5,
 IFNULL( sum( a.zrdw_wsl_yyq ), 0 ) AS count6,
 IFNULL( sum( a.zrdw_wdf_qxn ), 0 ) AS count7,
 IFNULL( sum( a.zrdw_wdf_jdq ), 0 ) AS count8,
 IFNULL( sum( a.zrdw_wdf_yyq ), 0 ) AS count9,
 IFNULL( sum( a.zrdw_whb_qxn ), 0 ) AS count10,
 IFNULL( sum( a.zrdw_whb_jdq ), 0 ) AS count11,
 IFNULL( sum( a.zrdw_whb_yyq ), 0 ) AS count12,
 IFNULL( sum( a.zrdw_wfk_qxn ), 0 ) AS count13,
 IFNULL( sum( a.zrdw_wfk_jdq ), 0 ) AS count14,
 IFNULL( sum( a.zrdw_wfk_yyq ), 0 ) AS count15,
 IFNULL( sum( a.xfbm_wpj_qxn ), 0 ) AS count16,
 IFNULL( sum( a.xfbm_wpj_jdq ), 0 ) AS count17,
 IFNULL( sum( a.xfbm_wpj_yyq ), 0 ) AS count18,
 IFNULL( sum( a.zrdw_wpj_qxn ), 0 ) AS count19,
 IFNULL( sum( a.zrdw_wpj_jdq ), 0 ) AS count20,
 IFNULL( sum( a.zrdw_wpj_yyq ), 0 ) AS count21 
FROM
 rpt_region r
 LEFT JOIN (
 SELECT DISTINCT
  x.xfjbh,
  substr( IFNULL( e.qxjgdm, x.wtsddm ), 1, 4 ) AS wtsddm,
  x.xfbm_wsl_qxn,
  x.xfbm_wsl_jdq,
  x.xfbm_wsl_yyq,
  x.zrdw_wsl_qxn,
  x.zrdw_wsl_jdq,
  x.zrdw_wsl_yyq,
  x.zrdw_wdf_qxn,
  x.zrdw_wdf_jdq,
  x.zrdw_wdf_yyq,
  x.zrdw_whb_qxn,
  x.zrdw_whb_jdq,
  x.zrdw_whb_yyq,
  x.xfbm_wpj_qxn,
  x.xfbm_wpj_jdq,
  x.xfbm_wpj_yyq,
  x.zrdw_wpj_qxn,
  x.zrdw_wpj_jdq,
  x.zrdw_wpj_yyq,
  x.zrdw_wfk_qxn,
  x.zrdw_wfk_jdq,
  x.zrdw_wfk_yyq 
 FROM
  rpt_xfjxx x
  INNER JOIN xf_dbxx e ON e.xfjbh = x.xfjbh
  LEFT JOIN rpt_blfsxx b ON b.xfjbh = x.xfjbh 
 WHERE
  1 = 1 
  AND x.djsj >= TO_DATE ( '2024-04-09', 'YYYY-MM-DD' ) 
  AND x.djsj < DATE_ADD( TO_DATE ( '2025-04-09', 'YYYY-MM-DD' ), INTERVAL '1' DAY ) 
  AND ( x.djjgdm = '1300000000000000299' OR b.qxmcdm = '1300000000000000299' ) 
 ) a ON LEFT(r.region_code,4) = a.wtsddm 
WHERE
 r.region_code LIKE '13__00000000' 
GROUP BY
 r.region_code,
 r.region_name,
 r.region_type 
 UNION
SELECT
/*+ENABLE_RQ_TO_NONREF_SPL(1)*/
 substr(
  r.region_code,
  1,
 ( 4 - 2 )) AS id,
 '合计' AS name,
 r.region_type,
 IFNULL( sum( a.xfbm_wsl_qxn ), 0 ) AS count1,
 IFNULL( sum( a.xfbm_wsl_jdq ), 0 ) AS count2,
 IFNULL( sum( a.xfbm_wsl_yyq ), 0 ) AS count3,
 IFNULL( sum( a.zrdw_wsl_qxn ), 0 ) AS count4,
 IFNULL( sum( a.zrdw_wsl_jdq ), 0 ) AS count5,
 IFNULL( sum( a.zrdw_wsl_yyq ), 0 ) AS count6,
 IFNULL( sum( a.zrdw_wdf_qxn ), 0 ) AS count7,
 IFNULL( sum( a.zrdw_wdf_jdq ), 0 ) AS count8,
 IFNULL( sum( a.zrdw_wdf_yyq ), 0 ) AS count9,
 IFNULL( sum( a.zrdw_whb_qxn ), 0 ) AS count10,
 IFNULL( sum( a.zrdw_whb_jdq ), 0 ) AS count11,
 IFNULL( sum( a.zrdw_whb_yyq ), 0 ) AS count12,
 IFNULL( sum( a.zrdw_wfk_qxn ), 0 ) AS count13,
 IFNULL( sum( a.zrdw_wfk_jdq ), 0 ) AS count14,
 IFNULL( sum( a.zrdw_wfk_yyq ), 0 ) AS count15,
 IFNULL( sum( a.xfbm_wpj_qxn ), 0 ) AS count16,
 IFNULL( sum( a.xfbm_wpj_jdq ), 0 ) AS count17,
 IFNULL( sum( a.xfbm_wpj_yyq ), 0 ) AS count18,
 IFNULL( sum( a.zrdw_wpj_qxn ), 0 ) AS count19,
 IFNULL( sum( a.zrdw_wpj_jdq ), 0 ) AS count20,
 IFNULL( sum( a.zrdw_wpj_yyq ), 0 ) AS count21 
FROM
 rpt_region r
 LEFT JOIN (
 SELECT DISTINCT
  x.xfjbh,
  substr(
   IFNULL( e.qxjgdm, x.wtsddm ),
   1,
  ( 4 - 2 )) AS wtsddm,
  x.xfbm_wsl_qxn,
  x.xfbm_wsl_jdq,
  x.xfbm_wsl_yyq,
  x.zrdw_wsl_qxn,
  x.zrdw_wsl_jdq,
  x.zrdw_wsl_yyq,
  x.zrdw_wdf_qxn,
  x.zrdw_wdf_jdq,
  x.zrdw_wdf_yyq,
  x.zrdw_whb_qxn,
  x.zrdw_whb_jdq,
  x.zrdw_whb_yyq,
  x.xfbm_wpj_qxn,
  x.xfbm_wpj_jdq,
  x.xfbm_wpj_yyq,
  x.zrdw_wpj_qxn,
  x.zrdw_wpj_jdq,
  x.zrdw_wpj_yyq,
  x.zrdw_wfk_qxn,
  x.zrdw_wfk_jdq,
  x.zrdw_wfk_yyq 
 FROM
  rpt_xfjxx x
  INNER JOIN xf_dbxx e ON e.xfjbh = x.xfjbh
  LEFT JOIN rpt_blfsxx b ON b.xfjbh = x.xfjbh 
 WHERE
  1 = 1 
  AND x.djsj >= TO_DATE ( '2024-04-09', 'YYYY-MM-DD' ) 
  AND x.djsj < DATE_ADD( TO_DATE ( '2025-04-09', 'YYYY-MM-DD' ), INTERVAL '1' DAY ) 
  AND ( x.djjgdm = '1300000000000000299' OR b.qxmcdm = '1300000000000000299' )  ) a ON 
  LEFT(r.region_code,2) = a.wtsddm 
WHERE
 r.region_code LIKE '130000000000' 
GROUP BY
 r.region_code,
 r.region_type

该SQL与如下SQL，仅在rpt_xfjxx x后多了两个空格。

SELECT
/*+ENABLE_RQ_TO_NONREF_SPL(1)*/
 r.region_code AS id,
 r.region_name AS name,
 r.region_type,
 IFNULL( sum( a.xfbm_wsl_qxn ), 0 ) AS count1,
 IFNULL( sum( a.xfbm_wsl_jdq ), 0 ) AS count2,
 IFNULL( sum( a.xfbm_wsl_yyq ), 0 ) AS count3,
 IFNULL( sum( a.zrdw_wsl_qxn ), 0 ) AS count4,
 IFNULL( sum( a.zrdw_wsl_jdq ), 0 ) AS count5,
 IFNULL( sum( a.zrdw_wsl_yyq ), 0 ) AS count6,
 IFNULL( sum( a.zrdw_wdf_qxn ), 0 ) AS count7,
 IFNULL( sum( a.zrdw_wdf_jdq ), 0 ) AS count8,
 IFNULL( sum( a.zrdw_wdf_yyq ), 0 ) AS count9,
 IFNULL( sum( a.zrdw_whb_qxn ), 0 ) AS count10,
 IFNULL( sum( a.zrdw_whb_jdq ), 0 ) AS count11,
 IFNULL( sum( a.zrdw_whb_yyq ), 0 ) AS count12,
 IFNULL( sum( a.zrdw_wfk_qxn ), 0 ) AS count13,
 IFNULL( sum( a.zrdw_wfk_jdq ), 0 ) AS count14,
 IFNULL( sum( a.zrdw_wfk_yyq ), 0 ) AS count15,
 IFNULL( sum( a.xfbm_wpj_qxn ), 0 ) AS count16,
 IFNULL( sum( a.xfbm_wpj_jdq ), 0 ) AS count17,
 IFNULL( sum( a.xfbm_wpj_yyq ), 0 ) AS count18,
 IFNULL( sum( a.zrdw_wpj_qxn ), 0 ) AS count19,
 IFNULL( sum( a.zrdw_wpj_jdq ), 0 ) AS count20,
 IFNULL( sum( a.zrdw_wpj_yyq ), 0 ) AS count21 
FROM
 rpt_region r
 LEFT JOIN (
 SELECT DISTINCT
  x.xfjbh,
  substr( IFNULL( e.qxjgdm, x.wtsddm ), 1, 4 ) AS wtsddm,
  x.xfbm_wsl_qxn,
  x.xfbm_wsl_jdq,
  x.xfbm_wsl_yyq,
  x.zrdw_wsl_qxn,
  x.zrdw_wsl_jdq,
  x.zrdw_wsl_yyq,
  x.zrdw_wdf_qxn,
  x.zrdw_wdf_jdq,
  x.zrdw_wdf_yyq,
  x.zrdw_whb_qxn,
  x.zrdw_whb_jdq,
  x.zrdw_whb_yyq,
  x.xfbm_wpj_qxn,
  x.xfbm_wpj_jdq,
  x.xfbm_wpj_yyq,
  x.zrdw_wpj_qxn,
  x.zrdw_wpj_jdq,
  x.zrdw_wpj_yyq,
  x.zrdw_wfk_qxn,
  x.zrdw_wfk_jdq,
  x.zrdw_wfk_yyq 
 FROM
  rpt_xfjxx x
  INNER JOIN xf_dbxx e ON e.xfjbh = x.xfjbh
  LEFT JOIN rpt_blfsxx b ON b.xfjbh = x.xfjbh 
 WHERE
  1 = 1 
  AND x.djsj >= TO_DATE ( '2024-04-09', 'YYYY-MM-DD' ) 
  AND x.djsj < DATE_ADD( TO_DATE ( '2025-04-09', 'YYYY-MM-DD' ), INTERVAL '1' DAY ) 
  AND ( x.djjgdm = '1300000000000000299' OR b.qxmcdm = '1300000000000000299' ) 
 ) a ON LEFT(r.region_code,4) = a.wtsddm 
WHERE
 r.region_code LIKE '13__00000000' 
GROUP BY
 r.region_code,
 r.region_name,
 r.region_type 
 UNION
SELECT
/*+ENABLE_RQ_TO_NONREF_SPL(1)*/
 substr(
  r.region_code,
  1,
 ( 4 - 2 )) AS id,
 '合计' AS name,
 r.region_type,
 IFNULL( sum( a.xfbm_wsl_qxn ), 0 ) AS count1,
 IFNULL( sum( a.xfbm_wsl_jdq ), 0 ) AS count2,
 IFNULL( sum( a.xfbm_wsl_yyq ), 0 ) AS count3,
 IFNULL( sum( a.zrdw_wsl_qxn ), 0 ) AS count4,
 IFNULL( sum( a.zrdw_wsl_jdq ), 0 ) AS count5,
 IFNULL( sum( a.zrdw_wsl_yyq ), 0 ) AS count6,
 IFNULL( sum( a.zrdw_wdf_qxn ), 0 ) AS count7,
 IFNULL( sum( a.zrdw_wdf_jdq ), 0 ) AS count8,
 IFNULL( sum( a.zrdw_wdf_yyq ), 0 ) AS count9,
 IFNULL( sum( a.zrdw_whb_qxn ), 0 ) AS count10,
 IFNULL( sum( a.zrdw_whb_jdq ), 0 ) AS count11,
 IFNULL( sum( a.zrdw_whb_yyq ), 0 ) AS count12,
 IFNULL( sum( a.zrdw_wfk_qxn ), 0 ) AS count13,
 IFNULL( sum( a.zrdw_wfk_jdq ), 0 ) AS count14,
 IFNULL( sum( a.zrdw_wfk_yyq ), 0 ) AS count15,
 IFNULL( sum( a.xfbm_wpj_qxn ), 0 ) AS count16,
 IFNULL( sum( a.xfbm_wpj_jdq ), 0 ) AS count17,
 IFNULL( sum( a.xfbm_wpj_yyq ), 0 ) AS count18,
 IFNULL( sum( a.zrdw_wpj_qxn ), 0 ) AS count19,
 IFNULL( sum( a.zrdw_wpj_jdq ), 0 ) AS count20,
 IFNULL( sum( a.zrdw_wpj_yyq ), 0 ) AS count21 
FROM
 rpt_region r
 LEFT JOIN (
 SELECT DISTINCT
  x.xfjbh,
  substr(
   IFNULL( e.qxjgdm, x.wtsddm ),
   1,
  ( 4 - 2 )) AS wtsddm,
  x.xfbm_wsl_qxn,
  x.xfbm_wsl_jdq,
  x.xfbm_wsl_yyq,
  x.zrdw_wsl_qxn,
  x.zrdw_wsl_jdq,
  x.zrdw_wsl_yyq,
  x.zrdw_wdf_qxn,
  x.zrdw_wdf_jdq,
  x.zrdw_wdf_yyq,
  x.zrdw_whb_qxn,
  x.zrdw_whb_jdq,
  x.zrdw_whb_yyq,
  x.xfbm_wpj_qxn,
  x.xfbm_wpj_jdq,
  x.xfbm_wpj_yyq,
  x.zrdw_wpj_qxn,
  x.zrdw_wpj_jdq,
  x.zrdw_wpj_yyq,
  x.zrdw_wfk_qxn,
  x.zrdw_wfk_jdq,
  x.zrdw_wfk_yyq 
 FROM
  rpt_xfjxx x  
  INNER JOIN xf_dbxx e ON e.xfjbh = x.xfjbh
  LEFT JOIN rpt_blfsxx b ON b.xfjbh = x.xfjbh 
 WHERE
  1 = 1 
  AND x.djsj >= TO_DATE ( '2024-04-09', 'YYYY-MM-DD' ) 
  AND x.djsj < DATE_ADD( TO_DATE ( '2025-04-09', 'YYYY-MM-DD' ), INTERVAL '1' DAY ) 
  AND ( x.djjgdm = '1300000000000000299' OR b.qxmcdm = '1300000000000000299' )  ) a ON 
  LEFT(r.region_code,2) = a.wtsddm 
WHERE
 r.region_code LIKE '130000000000' 
GROUP BY
 r.region_code,
 r.region_type

但从执行时常来看却相差一倍还多。
image.png
image.png

请问产生如上现象的原因是什么？？

回答 0
暂无回答
扫一扫
联系客服
logo
logo
logo
鄂Copyright © 达梦国产化应用创新实验室
达梦在线服务平台社区用户管理规范

鄂ICP备18017926号-1