分区字段不在SQL过滤中，悲剧

最新推荐文章于 2022-04-10 15:27:38 发布

?Briella

最新推荐文章于 2022-04-10 15:27:38 发布

阅读量220

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/1272149624/blog/671045

版权

2019独角兽企业重金招聘Python工程师标准>>>

快下班了，朋友发来SQL，说要跑900秒。

  SELECT
         T_01."ANLN1",
         T_01."ANLN2",
         T_01."APLZL",
         T_01."AUFNR",
         T_01."AUFPL",
         T_00."BKTXT",
         T_00."BLDAT",
         T_01."BPMNG",
         T_01."BPRME",
         T_01."BSTME",
         T_01."BSTMG",
         T_00."BUDAT",
         T_01."BUKRS",
         T_01."BWART",
         T_01."BWTAR",
         T_01."CHARG",
         T_00."CPUDT",
         T_00."CPUTM",
         T_01."DMBTR",
         T_01."EBELN",
         T_01."EBELP",
         T_01."ERFME",
         T_01."ERFMG",
         T_01."EXBWR",
         T_01."EXVKW",
         T_01."GRUND",
         T_01."KDAUF",
         T_01."KDEIN",
         T_01."KDPOS",
         T_01."KOSTL",
         T_01."KUNNR",
         T_01."KZBEW",
         T_01."KZVBR",
         T_01."KZZUG",
         T_01."LGORT",
         T_01."LIFNR",
         T_01."MATNR",
         T_00."MBLNR",
         T_01."MEINS",
         T_01."MENGE",
         T_00."MJAHR",
         T_01."NPLNR",
         T_01."PS_PSP_PNR",
         T_01."RSNUM",
         T_01."RSPOS",
         T_01."SHKZG",
         T_01."SOBKZ",
         T_00."USNAM",
         T_00."VGART",
         T_01."VKWRT",
         T_01."WAERS",
         T_01."WERKS",
         T_00."XABLN",
         T_01."XAUTO",
         T_00."XBLNR",
         T_01."ZEILE"
    FROM SAPSR3."MKPF" T_00
   INNER JOIN SAPSR3."MSEG" T_01
      ON T_01."MANDT" = '800'
     AND T_00."MANDT" = T_01."MANDT"
     AND T_00."MBLNR" = T_01."MBLNR"
     AND T_00."MJAHR" = T_01."MJAHR"
   WHERE T_00."MANDT" = '800'
     AND T_00."BUDAT" BETWEEN '20150303' AND '20150930'
     AND T_01."WERKS" = 'YH25'

看看执行计划：

已用时间:  00: 00: 18.82

执行计划
----------------------------------------------------------
Plan hash value: 445835420

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time  | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |   185K|    52M|       |   132K  (1)| 00:26:34 |       |       |
|*  1 |  HASH JOIN                          |           |   185K|    52M|    15M|   132K  (1)| 00:26:34 |       |       |
|   2 |   PARTITION RANGE ALL               |           |   173K|    13M|       | 10779   (1)| 00:02:10 |     1 |    11 |
|*  3 |    TABLE ACCESS FULL                | MKPF      |   173K|    13M|       | 10779   (1)| 00:02:10 |     1 |    11 |
|   4 |   PARTITION RANGE ALL               |           |   337K|    69M|       |   117K  (1)| 00:23:32 |     1 |    11 |
|   5 |    TABLE ACCESS BY LOCAL INDEX ROWID| MSEG      |   337K|    69M|       |   117K  (1)| 00:23:32 |     1 |    11 |
|*  6 |     INDEX RANGE SCAN                | MSEG_MMWB |   337K|       |       | 25064   (1)| 00:05:01 |     1 |    11 |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T_00"."MANDT"="T_01"."MANDT" AND "T_00"."MBLNR"="T_01"."MBLNR" AND "T_00"."MJAHR"="T_01"."MJAHR")
   3 - filter("T_00"."BUDAT">='20150303' AND "T_00"."MANDT"='800' AND "T_00"."BUDAT"<='20150930')
   6 - access("T_01"."MANDT"='800' AND "T_01"."WERKS"='YH25')
       filter("T_01"."WERKS"='YH25')


统计信息
----------------------------------------------------------
          1  recursive calls
          1  db block gets
     222451  consistent gets
          0  physical reads
          0  redo size
    8571412  bytes sent via SQL*Net to client
      89800  bytes received via SQL*Net from client
       8118  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     121741  rows processed

     T_00 : 总数据-1796465
      
T_01 : 总数据-6261512
      
MKPF、MSEG 都为分区表，分区字段-MJAHR（按日期分区）
MSEG:索引 MSEG_MMWB(MANDT, MBLNR, WERKS, BWART)

仔细看：

PARTITION RANGE ALL

这玩意走的是全表扫描，典型的过滤条件不包含分区字段，必然是个死。

解决方法：

在SQL中加入分区字段

 SELECT
         T_01."ANLN1",
         T_01."ANLN2",
         T_01."APLZL",
         T_01."AUFNR",
         T_01."AUFPL",
         T_00."BKTXT",
         T_00."BLDAT",
         T_01."BPMNG",
         T_01."BPRME",
         T_01."BSTME",
         T_01."BSTMG",
         T_00."BUDAT",
         T_01."BUKRS",
         T_01."BWART",
         T_01."BWTAR",
         T_01."CHARG",
         T_00."CPUDT",
         T_00."CPUTM",
         T_01."DMBTR",
         T_01."EBELN",
         T_01."EBELP",
         T_01."ERFME",
         T_01."ERFMG",
         T_01."EXBWR",
         T_01."EXVKW",
         T_01."GRUND",
         T_01."KDAUF",
         T_01."KDEIN",
         T_01."KDPOS",
         T_01."KOSTL",
         T_01."KUNNR",
         T_01."KZBEW",
         T_01."KZVBR",
         T_01."KZZUG",
         T_01."LGORT",
         T_01."LIFNR",
         T_01."MATNR",
         T_00."MBLNR",
         T_01."MEINS",
         T_01."MENGE",
         T_00."MJAHR",
         T_01."NPLNR",
         T_01."PS_PSP_PNR",
         T_01."RSNUM",
         T_01."RSPOS",
         T_01."SHKZG",
         T_01."SOBKZ",
         T_00."USNAM",
         T_00."VGART",
         T_01."VKWRT",
         T_01."WAERS",
         T_01."WERKS",
         T_00."XABLN",
         T_01."XAUTO",
         T_00."XBLNR",
         T_01."ZEILE"
    FROM SAPSR3."MKPF" T_00
   INNER JOIN SAPSR3."MSEG" T_01
      ON T_01."MANDT" = '800'
     AND T_00."MANDT" = T_01."MANDT"
     AND T_00."MBLNR" = T_01."MBLNR"
     AND T_00."MJAHR" = T_01."MJAHR"
   WHERE T_00."MANDT" = '800'
     AND T_00."BUDAT" BETWEEN '20150303' AND '20150930'
     AND T_01."WERKS" = 'YH25'
     AND MJAHR='2015' ---将分区字段加进来

再跑下执行计划如下：