问题描述
使用某些连接语句时,在Oracle、MaraiDB等数据库中都可以三四秒的速度运行出来,但在达梦中,居然要运行整整四十分钟。经调整,在子查询中多加了几个字段,反而速度很快。
慢语句及执行计划:
SQL:
select distinct ndqj.FS,ndqj.RS,ndqj.MS,ndqj.nd yqnd,a.bookcode,a.nd,
b.bookname booknameB,b.bookcode bookcodeB,b.nd ndB,b.fzlxbh,b.fzlxmc,b.fznrbh, b.fznrmc,left(b.fzlxmc,2) kslx
from (select distinct a.bookcode,a.nd,a.fznrbh,a.fznrmc,a.fzlxbh,a.fzlxmc,
jjsj RS,
count(a.je)over(partition by a.fznrbh,a.fzlxbh,a.fznrmc,bookcode,nd) FS,
sum(a.je)over(partition by a.fznrbh,a.fzlxbh,a.fznrmc,bookcode,nd) MS
from
(select a.nd,a.bookcode,b.fzlxbh,b.fzlxmc,fznrbh,fznrmc,pzrq,c.zjjysj,c.jjsj,case when jfje=0 then dfje else jfje end je
from audit_voucher a,audit_voucher_item b ,analy_kswlfx c
where a.bookcode=b.bookcode and a.fzbh=b.fzbh
and (b.fzlxmc like '客户%' or b.fzlxmc like '供应商%' or b.fzlxmc like '客商%')
and c.fzlxbh=b.fzlxbh
and c.ksbh=b.fznrbh
and c.ksmc=b.fznrmc
and c.bookcode=b.bookcode ) a) ndqj,audit_voucher a,audit_voucher_item b
where a.bookcode=b.bookcode and a.fzbh=b.fzbh
and a.bookcode=ndqj.bookcode and a.nd>=ndqj.nd
and b.fzlxbh=ndqj.fzlxbh and b.fznrbh=ndqj.fznrbh
and (b.fzlxmc like '客户%' or b.fzlxmc like '供应商%' or b.fzlxmc like '客商%');
执行计划:
快语句及执行计划:
SQL:
select distinct ndqj.FS,ndqj.RS,ndqj.MS,ndqj.nd yqnd,a.bookcode,a.nd,
b.bookname booknameB,b.bookcode bookcodeB,b.nd ndB,b.fzlxbh,b.fzlxmc,b.fznrbh,b.ysfznrbh,b.fznrmc,left(b.fzlxmc,2) kslx
from (select distinct a.bookcode,a.nd,a.fznrbh,a.fznrmc,a.fzlxbh,a.fzlxmc,
jjsj RS,
count(a.je)over(partition by a.fznrbh,a.fzlxbh,a.fznrmc,bookcode,nd) FS,
sum(a.je)over(partition by a.fznrbh,a.fzlxbh,a.fznrmc,bookcode,nd) MS
from
(select a.nd,a.bookcode,b.fzlxbh,b.fzlxmc,fznrbh,fznrmc,pzrq,c.zjjysj,c.jjsj,case when jfje=0 then dfje else jfje end je
from audit_voucher a,audit_voucher_item b ,analy_kswlfx c
where a.bookcode=b.bookcode and a.fzbh=b.fzbh
and (b.fzlxmc like '客户%' or b.fzlxmc like '供应商%' or b.fzlxmc like '客商%')
and c.fzlxbh=b.fzlxbh
and c.ksbh=b.fznrbh
and c.ksmc=b.fznrmc
and c.bookcode=b.bookcode ) a) ndqj,audit_voucher a,audit_voucher_item b
where a.bookcode=b.bookcode and a.fzbh=b.fzbh
and a.bookcode=ndqj.bookcode and a.nd>=ndqj.nd
and b.fzlxbh=ndqj.fzlxbh and b.fznrbh=ndqj.fznrbh
and (b.fzlxmc like '客户%' or b.fzlxmc like '供应商%' or b.fzlxmc like '客商%');
执行计划:
可以看出,快的SQL只是比慢的SQL多查询了b.bookname booknameB,b.bookcode bookcodeB,b.nd ndB,b.ysfznrbh这几个字段。经试验,这几个字段并非特定,只要比慢语句多查询b表的一个字段(包括但不仅限于上面举例的字段),查询速度就会很快。
原因
- 可能是因为数据量占比问题,在字段少时返回数据占比太大(?)所以达梦认为全表扫描会比走索引快;
- 统计信息没有收集,数据库错误制定了执行计划。
解决方法
- 执行收集统计信息的语句:
DBMS_STATS.GATHER_SCHEMA_STATS(‘NCW’,100,TRUE,‘FOR ALL INDEXED COLUMNS SIZE AUTO’); - 或者强制走索引:select /+index( 表名 索引名)/ 字段a,字段b from …