性能优化之ORACLE--SQL改造

zhou66

某屠宰项目：运输单参照发货单拉单，查询极慢。由于客户要求限定1个发货单只能拉1次运输单，故在参照查询中，客开了段逻辑。完整SQL如下：

1. select so_delivery.approver,
   
2.        so_delivery_b.badvfeeflag,
   
3.        so_delivery_b.bbarsettleflag,
   
4.        so_delivery_b.bcheckflag,
   
5.        so_delivery.billmaker,
   
6.        so_delivery_b.blargessflag,
   
7.        so_delivery_b.boutendflag,
   
8.        so_delivery_b.bqualityflag,
   
9.        so_delivery_b.btransendflag,
   
10.        so_delivery_b.btriatradeflag,
    
11.        so_delivery_b.busecheckflag,
    
12.        so_delivery_b.carorgid,
    
13.        so_delivery_b.carorgvid,
    
14.        so_delivery_b.castunitid,
    
15.        so_delivery.cbiztypeid,
    
16.        so_delivery_b.cchanneltypeid,
    
17.        so_delivery_b.cchauffeurid,
    
18.        so_delivery_b.ccurrencyid,
    
19.        so_delivery_b.ccustmaterialid,
    
20.        so_delivery_b.cdeliverybid,
    
21.        so_delivery_b.cdeliveryid,
    
22.        so_delivery_b.cdeptid,
    
23.        so_delivery_b.cdeptvid,
    
24.        so_delivery_b.cemployeeid,
    
25.        so_delivery_b.cfirstbid,
    
26.        so_delivery_b.cfirstid,
    
27.        so_delivery_b.cfreecustid,
    
28.        so_delivery_b.cinstockorgid,
    
29.        so_delivery_b.cinstockorgvid,
    
30.        so_delivery_b.cinstordocid,
    
31.        so_delivery_b.cinvoicecustid,
    
32.        so_delivery_b.cmaterialid,
    
33.        so_delivery_b.cmaterialvid,
    
34.        so_delivery_b.cmffileid,
    
35.        so_delivery_b.cordercustid,
    
36.        so_delivery_b.corigareaid,
    
37.        so_delivery_b.corigcountryid,
    
38.        so_delivery_b.corigcurrencyid,
    
39.        so_delivery_b.cpriceformid,
    
40.        so_delivery_b.cprodlineid,
    
41.        so_delivery_b.cproductorid,
    
42.        so_delivery_b.cprofitcenterid,
    
43.        so_delivery_b.cprofitcentervid,
    
44.        so_delivery_b.cprojectid,
    
45.        so_delivery_b.cqtunitid,
    
46.        so_delivery_b.cqualitylevelid,
    
47.        so_delivery.creationtime,
    
48.        so_delivery.creator,
    
49.        so_delivery_b.crececountryid,
    
50.        so_delivery_b.creceiveadddocid,
    
51.        so_delivery_b.creceiveaddrid,
    
52.        so_delivery_b.creceiveareaid,
    
53.        so_delivery_b.creceivecustid,
    
54.        so_delivery_b.creceivepersonid,
    
55.        so_delivery_b.cretreasonid,
    
56.        so_delivery_b.crowno,
    
57.        so_delivery_b.crprofitcenterid,
    
58.        so_delivery_b.crprofitcentervid,
    
59.        so_delivery_b.csaleorgid,
    
60.        so_delivery_b.csaleorgvid,
    
61.        so_delivery_b.csendadddocid,
    
62.        so_delivery_b.csendaddrid,
    
63.        so_delivery_b.csendareaid,
    
64.        so_delivery_b.csendcountryid,
    
65.        so_delivery.csenddeptid,
    
66.        so_delivery.csenddeptvid,
    
67.        so_delivery.csendemployeeid,
    
68.        so_delivery_b.csendpersonid,
    
69.        so_delivery_b.csendstockorgid,
    
70.        so_delivery_b.csendstockorgvid,
    
71.        so_delivery_b.csendstordocid,
    
72.        so_delivery_b.csettleorgid,
    
73.        so_delivery_b.csettleorgvid,
    
74.        so_delivery_b.cspaceid,
    
75.        so_delivery_b.csprofitcenterid,
    
76.        so_delivery_b.csprofitcentervid,
    
77.        so_delivery_b.csrcbid,
    
78.        so_delivery_b.csrcid,
    
79.        so_delivery_b.csupercargoid,
    
80.        so_delivery_b.ctaxcodeid,
    
81.        so_delivery_b.ctaxcountryid,
    
82.        so_delivery.ctradewordid,
    
83.        so_delivery_b.ctranscustid,
    
84.        so_delivery.ctransportrouteid,
    
85.        so_delivery.ctransporttypeid,
    
86.        so_delivery.ctrantypeid,
    
87.        so_delivery_b.cunitid,
    
88.        so_delivery_b.cvehicleid,
    
89.        so_delivery_b.cvehicletypeid,
    
90.        so_delivery_b.cvendorid,
    
91.        so_delivery_b.dbilldate,
    
92.        so_delivery_b.dhm_f_nreqrsnum,
    
93.        so_delivery_b.dhm_f_ntotalarnum,
    
94.        so_delivery_b.dhm_f_ntotalelignum,
    
95.        so_delivery_b.dhm_f_ntotalestarnum,
    
96.        so_delivery_b.dhm_f_ntotalnotoutnum,
    
97.        so_delivery_b.dhm_f_ntotaloutnum,
    
98.        so_delivery_b.dhm_f_ntotalreportnum,
    
99.        so_delivery_b.dhm_f_ntotalrushnum,
    
100.        so_delivery_b.dhm_f_ntotaltransnum,
     
101.        so_delivery_b.dhm_f_ntotalunelignum,
     
102.        so_delivery_b.dhm_f_ntranslossnum,
     
103.        so_delivery.dmakedate,
     
104.        so_delivery_b.dreceivedate,
     
105.        so_delivery_b.dsenddate,
     
106.        so_delivery_b.fbuysellflag,
     
107.        so_delivery_b.frownote,
     
108.        so_delivery.fstatusflag,
     
109.        so_delivery_b.ftaxtypeflag,
     
110.        so_delivery.ts,
     
111.        so_delivery.iprintcount,
     
112.        so_delivery.modifiedtime,
     
113.        so_delivery.modifier,
     
114.        so_delivery_b.nastnum,
     
115.        so_delivery_b.ncaltaxmny,
     
116.        so_delivery_b.ndiscount,
     
117.        so_delivery_b.ndiscountrate,
     
118.        so_delivery_b.nexchangerate,
     
119.        so_delivery_b.nglobalexchgrate,
     
120.        so_delivery_b.nglobalmny,
     
121.        so_delivery_b.nglobaltaxmny,
     
122.        so_delivery_b.ngroupexchgrate,
     
123.        so_delivery_b.ngroupmny,
     
124.        so_delivery_b.ngrouptaxmny,
     
125.        so_delivery_b.nitemdiscountrate,
     
126.        so_delivery_b.nmny,
     
127.        so_delivery_b.nnetprice,
     
128.        so_delivery_b.nnum,
     
129.        so_delivery_b.norigdiscount,
     
130.        so_delivery_b.norigmny,
     
131.        so_delivery_b.norignetprice,
     
132.        so_delivery_b.norigprice,
     
133.        so_delivery_b.norigtaxmny,
     
134.        so_delivery_b.norigtaxnetprice,
     
135.        so_delivery_b.norigtaxprice,
     
136.        so_delivery_b.npiece,
     
137.        so_delivery_b.nprice,
     
138.        so_delivery_b.nqtnetprice,
     
139.        so_delivery_b.nqtorignetprice,
     
140.        so_delivery_b.nqtorigprice,
     
141.        so_delivery_b.nqtorigtaxnetprc,
     
142.        so_delivery_b.nqtorigtaxprice,
     
143.        so_delivery_b.nqtprice,
     
144.        so_delivery_b.nqttaxnetprice,
     
145.        so_delivery_b.nqttaxprice,
     
146.        so_delivery_b.nqtunitnum,
     
147.        so_delivery_b.nreqrsnum,
     
148.        so_delivery_b.ntax,
     
149.        so_delivery_b.ntaxmny,
     
150.        so_delivery_b.ntaxnetprice,
     
151.        so_delivery_b.ntaxprice,
     
152.        so_delivery_b.ntaxrate,
     
153.        so_delivery_b.ntotalarnum,
     
154.        so_delivery.ntotalastnum,
     
155.        so_delivery_b.ntotalelignum,
     
156.        so_delivery_b.ntotalestarnum,
     
157.        so_delivery_b.ntotalnotoutnum,
     
158.        so_delivery_b.ntotaloutnum,
     
159.        so_delivery.ntotalpiece,
     
160.        so_delivery_b.ntotalreportnum,
     
161.        so_delivery_b.ntotalrushnum,
     
162.        so_delivery_b.ntotaltransnum,
     
163.        so_delivery_b.ntotalunelignum,
     
164.        so_delivery.ntotalvolume,
     
165.        so_delivery.ntotalweight,
     
166.        so_delivery_b.ntranslossnum,
     
167.        so_delivery_b.nvolume,
     
168.        so_delivery_b.nweight,
     
169.        so_delivery_b.pk_batchcode,
     
170.        so_delivery_b.pk_group,
     
171.        so_delivery_b.pk_org,
     
172.        so_delivery.pk_org_v,
     
173.        so_delivery.taudittime,
     
174.        so_delivery_b.ts,
     
175.        so_delivery_b.vbatchcode,
     
176.        so_delivery_b.vbdef1,
     
177.        so_delivery_b.vbdef10,
     
178.        so_delivery_b.vbdef11,
     
179.        so_delivery_b.vbdef12,
     
180.        so_delivery_b.vbdef13,
     
181.        so_delivery_b.vbdef14,
     
182.        so_delivery_b.vbdef15,
     
183.        so_delivery_b.vbdef16,
     
184.        so_delivery_b.vbdef17,
     
185.        so_delivery_b.vbdef18,
     
186.        so_delivery_b.vbdef19,
     
187.        so_delivery_b.vbdef2,
     
188.        so_delivery_b.vbdef20,
     
189.        so_delivery_b.vbdef21,
     
190.        so_delivery_b.vbdef22,
     
191.        so_delivery_b.vbdef23,
     
192.        so_delivery_b.vbdef24,
     
193.        so_delivery_b.vbdef25,
     
194.        so_delivery_b.vbdef26,
     
195.        so_delivery_b.vbdef27,
     
196.        so_delivery_b.vbdef28,
     
197.        so_delivery_b.vbdef29,
     
198.        so_delivery_b.vbdef3,
     
199.        so_delivery_b.vbdef30,
     
200.        so_delivery_b.vbdef31,
     
201.        so_delivery_b.vbdef32,
     
202.        so_delivery_b.vbdef33,
     
203.        so_delivery_b.vbdef34,
     
204.        so_delivery_b.vbdef35,
     
205.        so_delivery_b.vbdef36,
     
206.        so_delivery_b.vbdef37,
     
207.        so_delivery_b.vbdef38,
     
208.        so_delivery_b.vbdef39,
     
209.        so_delivery_b.vbdef4,
     
210.        so_delivery_b.vbdef40,
     
211.        so_delivery_b.vbdef5,
     
212.        so_delivery_b.vbdef6,
     
213.        so_delivery_b.vbdef7,
     
214.        so_delivery_b.vbdef8,
     
215.        so_delivery_b.vbdef9,
     
216.        so_delivery.vbillcode,
     
217.        so_delivery_b.vchangerate,
     
218.        so_delivery.vdef1,
     
219.        so_delivery.vdef10,
     
220.        so_delivery.vdef11,
     
221.        so_delivery.vdef12,
     
222.        so_delivery.vdef13,
     
223.        so_delivery.vdef14,
     
224.        so_delivery.vdef15,
     
225.        so_delivery.vdef16,
     
226.        so_delivery.vdef17,
     
227.        so_delivery.vdef18,
     
228.        so_delivery.vdef19,
     
229.        so_delivery.vdef2,
     
230.        so_delivery.vdef20,
     
231.        so_delivery.vdef3,
     
232.        so_delivery.vdef4,
     
233.        so_delivery.vdef5,
     
234.        so_delivery.vdef6,
     
235.        so_delivery.vdef7,
     
236.        so_delivery.vdef8,
     
237.        so_delivery.vdef9,
     
238.        so_delivery_b.vfirstbilldate,
     
239.        so_delivery_b.vfirstcode,
     
240.        so_delivery_b.vfirstrowno,
     
241.        so_delivery_b.vfirsttrantype,
     
242.        so_delivery_b.vfirsttype,
     
243.        so_delivery_b.vfree1,
     
244.        so_delivery_b.vfree10,
     
245.        so_delivery_b.vfree2,
     
246.        so_delivery_b.vfree3,
     
247.        so_delivery_b.vfree4,
     
248.        so_delivery_b.vfree5,
     
249.        so_delivery_b.vfree6,
     
250.        so_delivery_b.vfree7,
     
251.        so_delivery_b.vfree8,
     
252.        so_delivery_b.vfree9,
     
253.        so_delivery.vnote,
     
254.        so_delivery_b.vqtunitrate,
     
255.        so_delivery_b.vreceivetel,
     
256.        so_delivery_b.vreturnmode,
     
257.        so_delivery_b.vsendtel,
     
258.        so_delivery_b.vsrccode,
     
259.        so_delivery_b.vsrcrowno,
     
260.        so_delivery_b.vsrctrantype,
     
261.        so_delivery_b.vsrctype,
     
262.        so_delivery.vtrantypecode
     
263.   from so_delivery so_delivery
     
264.   left outer join so_delivery_b so_delivery_b
     
265.     on so_delivery_b.cdeliveryid = so_delivery.cdeliveryid
     
266. where (so_delivery.pk_org = '0001A1100000000HB9JI' AND
     
267.        (so_delivery.dbilldate >= '2022-02-21 00:00:00' and
     
268.        so_delivery.dbilldate <= '2022-02-21 23:59:59'))
     
269.    and so_delivery.dr = 0
     
270.    and so_delivery_b.dr = 0
     
271.    and (so_delivery.cbiztypeid in
     
272.        ('0001A1100000000017XB',
     
273.          '1001A1100000000020MH',
     
274.          '1001A110000000002ONY',
     
275.          '1001A11000000000F0WG',
     
276.          '1001A11000000000F0XZ',
     
277.          '1001A11000000000F0YQ',
     
278.          '1001A110000000BNTAF4',
     
279.          '1001A110000000BNYMRL',
     
280.          '1001A110000000BPS90H',
     
281.          '1001A110000000BRIM5M',
     
282.          '1001A110000000BRIMRT',
     
283.          '1001A110000000BRIU28'))
     
284.    --and (so_delivery.fstatusflag in (1))
     
285.    and ((VDEF4 = 'Y') OR (ctrantypeid in ('1001A110000000BB2T8N')))
     
286.    and (so_delivery_b.btransendflag = 'N')
     
287.    and nvl(so_delivery_b.nnum, 0) - nvl(so_delivery_b.ntotaltransnum, 0) > 0
     
288. <b>and so_delivery.cdeliveryid not in
     
289.        (select distinct csrcid
     
290.           from dm_delivbill_b
     
291.          where nvl(dr, 0) = 0
     
292.            and vsrctrantype in
     
293.                (select pk_billtypeid
     
294.                   from bd_billtype
     
295.                  where nvl(dr, 0) = 0
     
296.                    and parentbilltype = '4331'))</b>
     
297.    and so_delivery.ctransporttypeid not in
     
298.        (select distinct pk_transporttype
     
299.           from bd_transporttype
     
300.          where nvl(dr, 0) = 0
     
301.            and code = '4')
     
302.    and so_delivery.pk_group = '0001A110000000000MDY'
     
303. order by so_delivery.vbillcode, so_delivery_b.crowno
     

复制代码

以上标粗的部分，即为增加的逻辑。

前期影响不大，能正常使用。经过两年运行，背景数据达到500万时，性能就很差了。
原因：如下查询子句，无法利用索引，每次全表扫描：

1. and so_delivery.cdeliveryid not in
   
2.        (select distinct csrcid
   
3.           from dm_delivbill_b
   
4.          where nvl(dr, 0) = 0
   
5.            and vsrctrantype in
   
6.                (select pk_billtypeid
   
7.                   from bd_billtype
   
8.                  where nvl(dr, 0) = 0
   
9.                    and parentbilltype = '4331'))

复制代码

将以上通过not exists 做等价替代，如下：csrcid = so_delivery.cdeliveryid 能利用来源线索的索引：

1. and not exists (select 1 from dm_delivbill_b where <b>csrcid = so_delivery.cdeliveryid </b> and nvl(dr,0)=0 and vsrctrantype in
   
2.                (select pk_billtypeid
   
3.                   from bd_billtype
   
4.                  where nvl(dr, 0) = 0
   
5.                    and parentbilltype = '4331'))

复制代码

改造后性能情况：从4分多种到2秒。