一则sql优化记录

数据库巡检的时候，发现一条高耗sql。

执行计划如下：

其中逻辑读和物理读分别为

从执行计划中可以看出，cost高主要是由于PO_ARRIVEORDER_B和PO_ORDER_BB1的全表扫描引起。

查看过滤条件可知，同时查询得知PO_ARRIVEORDER_B表上的pk_order_bb1选择性很好。可尝试在pk_order_bb1上建立索引。

建立索引

create index test1  on PO_ARRIVEORDER_B(PK_ORDER_BB1);

查看执行计划

同时发现

PO_ORDER_BB1表的过滤条件为

16 -filter(SUBSTR("A1"."DSENDDATE",1,10)>='2018-05-01' AND

           SUBSTR("A1"."DSENDDATE",1,10)<='2018-06-06' AND"A1"."DR"=0)

此处有函数转换，建议将语句修改下，并在PO_ORDER_BB1下建立索引

Create index test on PO_ORDER_BB1 (DSENDDATE);

SELECT t_1.cgzz  CGZZ,

      t_1.gys   GYS,

      t_1.txm   TXM,

      substr(t_1.fhrq, 1, 10) FHRQ,

      t_1.dhrq  DHRQ,

      t_1.ck    CK,

      t_1.cgdd  CGDD,

      t_1.sapdj SAPDJ,

      t_1.hh    HH,

      t_1.cgy   CGY,

      t_1.wlbm  WLBM,

      t_1.wlmc  WLMC,

      t_1.dw    DW,

      t_1.fhsl  FHSL,

      t_1.dhsl  DHSL,

      t_1.pc    PC

 FROM (SELECT a1.vbatchcode pc,

               a1.dsenddate fhrq,

               a1.pk_group jt,

               a7.name cgzz,

               a8.name gys,

               a1.vbdef2 txm,

               a6.vdef14 sapdj,

               a2.crowno hh,

               substr(a1.dplanarrvdate, 1, 10)dhrq,

               a9.code cgy,

               a4.code ck,

               a6.vbillcode cgdd,

               a5.code dw,

               a1.nastnum fhsl,

               a10.arriveqty dhsl,

               a3.code wlbm,

               a3.name wlmc

         FROM po_order_bb1 a1

         LEFT outer JOIN po_order_b a2

           ON a1.pk_order_b = a2.pk_order_b

         LEFT outer JOIN bd_material a3

           ON a1.pk_srcmaterial = a3.pk_material

         LEFT outer JOIN bd_stordoc a4

           ON a1.pk_recvstordoc = a4.pk_stordoc

         LEFT outer JOIN bd_measdoc a5

           ON a1.castunitid = a5.pk_measdoc

         LEFT outer JOIN po_order a6

           ON a1.pk_order = a6.pk_order

         LEFT outer JOIN org_orgs a7

           ON a1.pk_org = a7.pk_org

         LEFT outer JOIN bd_supplier a8

           ON a6.pk_supplier = a8.pk_supplier

         LEFT outer JOIN bd_psndoc a9

           ON a6.cemployeeid = a9.pk_psndoc

         LEFT outer JOIN (SELECT pk_order_bb1 pk_order_bb1,

                                 sum(nnum)arriveqty

                            FROMpo_arriveorder_b

                           WHERE dr = 0

                           GROUP BYpk_order_bb1) a10

           ON a1.pk_order_bb1 = a10.pk_order_bb1

        WHERE a1.dr = 0) t_1

 WHERE (t_1.cgzz = 'xxxxxxxx')

  AND (t_1.fhrq BETWEEN '2018-05-01 00:00:00' AND '2018-06-06 23:59:59')

 ORDER BY t_1.cgzz, t_1.gys, t_1.txm, t_1.wlbm;

逻辑读下降到1w多