下面这句sql是否还可以优化？

echo_huang

select count(distinct ly1802) from lyplibs/lyf18 where           
ly1840 = '10500016' and ly1809 >;= 20041101 and ly1809<=20041115  
and ly1816 >;= '2' and ly1802 in                                 
  (select ly1802 from lyplibs/lyf18,lyplibs/lyf11                 
   where ly1840 = '10500016' and ly1809 >;= 20041101 and ly1809   
   <=20041115 and ly1816 >;= '2' and  ly1840 = ly1120              
    and ly1806 = ly1101                                          
    group by ly1802                                               
    having (sum(ly1823/ly1112)<=5))        

unix_boy

自己用db2advise先看看吧~!

echo_huang

不好意思，db2advise是本书？
可否提供一下。

toms1981

db2advis is a DB2 command which will advise you what index should create. Besides, you may use explain sql to view which part of your sql cause it become slow...

echo_huang

i c
but could anyone give some advise on my current sql sentence?

toms1981

frankly speaking, it is very difficult to give any comment on your sql becuase I do not know what you trying to do with the sql. Anyway for my opinion, whenever your sql have to link more than 1 table then use join rather than tranditional method.

echo_huang

I used join yet.

I could describe what I want to do:

比如A表是一张明细表，包括编号，地区，数量，那么我先将A表按编号group一下，求出每一个编号的sum(数量)，而我要求的是sum(数量)>;5的那些编号，这个就是我现在用子查询做到的。
最终要求的是sum(数量)>;5的那些编号的总数。

daliwa

可以使用公共表表达式简化你的查询，但具体有没有提高性能，你自己再用存取计划看一下，要不只能从建索引上面下功夫了。
写法如下：
with temp as
(select ly1802 from lyplibs/lyf18,lyplibs/lyf11
where ly1840 = '10500016' and ly1809 >;= 20041101 and ly1809
<=20041115 and ly1816 >;= '2' and ly1840 = ly1120
and ly1806 = ly1101
group by ly1802
having (sum(ly1823/ly1112)<=5))
select count(*) from temp;

daliwa

呵～～
性能应该提高了很多了，你可以试一下。

echo_huang

daliwa
很厉害
PFPF！
谢谢谢谢：）