求数据库大神帮忙，SQL优化

Question

小弟数据库方面的知识非常浅薄，只会写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

Answer 1

第一条sql
首先分析问题
1、union all 是非常消耗性能的，而我观察题主的union all 两端区别在于SS_CITIC_BILL_NBR 和SS_CITIC_WAST_NBR，所以需要明确这几个表间的关系，
如果可以不适用union all .最好还是不适用他
2、从返回的结果集来看，别名c的表才是主表，所以表关联部分应该
SS_CITIC_BILL_NBR c
LEFT JOIN op_bill_detail b ON b.pk_id = c.pk_id
LEFT JOIN op_bill a ON a.BILL_FILE_ID = b.BILL_FILE_ID
注意：1楼使用的是inner join 这个需要根据自己业务决定
而题主其实是要合并SS_CITIC_BILL_NBR 和SS_CITIC_WAST_NBR两个表，所以采用1楼的方式，先union all这两表
3、就是条件的执行先后顺序，应该优先使用效率高的条件

具体sql如下：
SELECT
c.*, 108 doc_id,
b.pip_nbr
FROM
(
(
SELECT
DOUBLE (REPLACE(c.doc_nbr, 'QZ', '')) AS nbr,
inv_code,
'1' AS t
FROM
SS_CITIC_BILL_NBR
WHERE
doc_id = 108
AND doc_nbr <> ''
AND to_char (sts_date, 'yyyyMMdd') = '20150819'
)
UNION ALL
(
SELECT
DOUBLE (REPLACE(c.doc_nbr, 'QZ', '')) AS nbr,
inv_code,
'0' AS t
FROM
SS_CITIC_BILL_NBR
WHERE
doc_id = 108
AND doc_nbr <> ''
AND to_char (sts_date, 'yyyyMMdd') = '20150819'
)
) c
LEFT JOIN op_bill_detail b ON b.pk_id = c.pk_id
LEFT JOIN op_bill a ON a.BILL_FILE_ID = b.BILL_FILE_ID
WHERE
a.pvdr_id = 6252
ORDER BY
c.nbr ASC