db2-求數據庫大神幫忙，SQL優化

求數據庫大神幫忙，SQL優化

小弟數據庫方面的知識非常淺薄，只會寫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

最佳回答：

第一條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