小弟数据库方面的知识非常浅薄,只会写sql语句实现查询,但是确不会做优化,以下有两个sql语句,在数据库中执行的非常慢,DB2,有没有数据库大神,帮忙优化一下,不胜感激。
--单证号是否连续
select * from (
select double(replace(c.doc_nbr, 'QZ', '')) as nbr,c.doc_id, b.pip_nbr,c.inv_code,'1' as t
from op_bill a,op_bill_detail b,SS_CITIC_BILL_NBR c
where a.BILL_FILE_ID = b.BILL_FILE_ID
and b.pk_id = c.pk_id
and a.pvdr_id = 6252
and to_char(c.sts_date,'yyyyMMdd') = '20150819'
and doc_nbr <> ''
and c.doc_id = 108
union all
select double(replace(c.doc_nbr, 'QZ', '')) as nbr,c.doc_id, b.pip_nbr,c.inv_code,'0' as t
from op_bill a,op_bill_detail b,SS_CITIC_WAST_NBR c
where a.BILL_FILE_ID = b.BILL_FILE_ID
and b.pk_id = c.pk_id
and a.pvdr_id = 6252
and to_char(c.sts_date,'yyyyMMdd') = '20150819'
and doc_nbr <> ''
and c.doc_id = 108
) order by nbr asc
--单证号是否重复
select t.inv_code,t.doc_id,t.doc_nbr,count(*) from
(select a.inv_code,a.doc_id,a.doc_nbr,b.pvdr_id from SS_CITIC_BILL_NBR a,op_bill b,op_bill_detail d where a.bill_file_id=b.bill_file_id and a.pk_id=d.pk_id and b.pvdr_id =6252 and to_char(a.sts_date,'yyyyMMdd')<='20150819' and a.doc_nbr <>''
union all
select a.inv_code,a.doc_id,a.doc_nbr,b.pvdr_id from SS_CITIC_WAST_NBR a,op_bill b,op_bill_detail d where a.bill_file_id=b.bill_file_id and a.pk_id=d.pk_id and b.pvdr_id =6252 and to_char(a.sts_date,'yyyyMMdd')<='20150819' and a.doc_nbr <>'')
t where t.doc_nbr
in(
select doc_nbr from SS_CITIC_BILL_NBR s,op_bill b,op_bill_detail d where s.bill_file_id=b.bill_file_id and s.pk_id=d.pk_id and b.pvdr_id =t.pvdr_id and to_char(s.sts_date,'yyyyMMdd')='20150819' and s.doc_nbr <>'' and t.doc_nbr=s.doc_nbr and t.inv_code=s.inv_code
union all
select doc_nbr from SS_CITIC_WAST_NBR s,op_bill b,op_bill_detail d where s.bill_file_id=b.bill_file_id and s.pk_id=d.pk_id and b.pvdr_id =t.pvdr_id and to_char(s.sts_date,'yyyyMMdd')='20150819' and s.doc_nbr <>'' and t.doc_nbr=s.doc_nbr and t.inv_code=s.inv_code
) group by t.doc_nbr,t.doc_id,t.inv_code having count(*)>1