***********************
Your mail has been scanned by InterScan.
***********-***********
I can't found out why.....
SELECT
SUM(B.FIRMA_BORC_TUTAR) AS TOPLAM_BORC,
SUM(B.FIRMA_ALACAK_TUTAR) AS TOPLAM_ALACAK ,
B.F_HESAP_KOD AS HESAP_KOD,B.HESAP_TANIM AS HESAP_TANIM,
SUM(CASE WHEN DAYS('2006-12-14')-DAYS(A.TARIH)<15 THEN B.FIRMA_BORC_TUTAR
ELSE 0 END) AS BORC_0_15,
SUM(CASE WHEN ((DAYS('2006-12-14')-DAYS(A.TARIH)<30) AND
(DAYS('2006-12-14')-DAYS(A.TARIH)>=15) ) THEN B.FIRMA_BORC_TUTAR ELSE 0
END) AS BORC_15_30,
SUM(CASE WHEN ((DAYS('2006-12-14')-DAYS(A.TARIH)<45) AND
(DAYS('2006-12-14')-DAYS(A.TARIH)>=30) ) THEN B.FIRMA_BORC_TUTAR ELSE 0
END) AS BORC_30_45,
SUM(CASE WHEN ((DAYS('2006-12-14')-DAYS(A.TARIH)<60) AND
(DAYS('2006-12-14')-DAYS(A.TARIH)>=45) ) THEN B.FIRMA_BORC_TUTAR ELSE 0
END) AS BORC_45_60,
SUM(CASE WHEN ((DAYS('2006-12-14')-DAYS(A.TARIH)<90) AND
(DAYS('2006-12-14')-DAYS(A.TARIH)>=60) ) THEN B.FIRMA_BORC_TUTAR ELSE 0
END) AS BORC_60_90,
SUM(CASE WHEN ((DAYS('2006-12-14')-DAYS(A.TARIH)<120) AND
(DAYS('2006-12-14')-DAYS(A.TARIH)>=90) ) THEN B.FIRMA_BORC_TUTAR ELSE 0
END) AS BORC_90_120,
SUM(CASE WHEN DAYS('2006-12-14')-DAYS(A.TARIH)>=120 THEN
B.FIRMA_BORC_TUTAR ELSE 0 END) AS BORC_120,SUM(CASE WHEN
DAYS('2006-12-14')-DAYS(A.TARIH)<15 THEN B.FIRMA_ALACAK_TUTAR ELSE 0 END)
AS ALACAK_0_15,
SUM(CASE WHEN ((DAYS('2006-12-14')-DAYS(A.TARIH)<30) AND
(DAYS('2006-12-14')-DAYS(A.TARIH)>=15) ) THEN B.FIRMA_ALACAK_TUTAR ELSE 0
END) AS ALACAK_15_30,SUM(CASE WHEN ((DAYS('2006-12-14')-DAYS(A.TARIH)<45)
AND (DAYS('2006-12-14')-DAYS(A.TARIH)>=30) ) THEN B.FIRMA_ALACAK_TUTAR
ELSE 0 END) AS ALACAK_30_45,
SUM(CASE WHEN ((DAYS('2006-12-14')-DAYS(A.TARIH)<60) AND
(DAYS('2006-12-14')-DAYS(A.TARIH)>=45) ) THEN B.FIRMA_ALACAK_TUTAR ELSE 0
END) AS ALACAK_45_60,SUM(CASE WHEN ((DAYS('2006-12-14')-DAYS(A.TARIH)<90)
AND (DAYS('2006-12-14')-DAYS(A.TARIH)>=60) ) THEN B.FIRMA_ALACAK_TUTAR
ELSE 0 END) AS ALACAK_60_90,
SUM(CASE WHEN ((DAYS('2006-12-14')-DAYS(A.TARIH)<120) AND
(DAYS('2006-12-14')-DAYS(A.TARIH)>=90) ) THEN B.FIRMA_ALACAK_TUTAR ELSE 0
END) AS ALACAK_90_120,SUM(CASE WHEN DAYS('2006-12-14')-DAYS(A.TARIH)>=120
THEN B.FIRMA_ALACAK_TUTAR ELSE 0 END) AS ALACAK_120,
B.F_FIRMA_MALIYET_MERKEZ_KOD AS MALIYET_MERKEZ,
COALESCE(F_MUSTERI_GRUP_KOD,'--' ) AS MUSTERI_GRUP,
COALESCE(F_SATIS_DEPT_KOD,'--' ) AS SATIS_DEPT
FROM FINANS.MUHASEBE_FIS AS A, FINANS.MUHASEBE_FIS_KALEM AS B
WHERE
A.F_FIRMA_KOD ='02200' AND B.F_FIRMA_KOD ='02200'
AND A.TARIH<='2006-01-14' AND A.TARIH>='2006-01-01'
AND A.IPTAL='h'
AND B.F_HESAP_KOD>='1000100100001'
AND B.F_HESAP_KOD<='3000100100001'
AND B.ISLEM_TIP<>'1'
GROUP BY (B.F_HESAP_KOD,B.HESAP_TANIM,B.F_FIRMA_MALIYET_MERKEZ_KOD
,COALESCE(F_MUSTERI_GRUP_KOD,'--' ),COALESCE(F_SATIS_DEPT_KOD,'--' ))
ORDER BY B.F_HESAP_KOD
I have 2 tables ... Finans.Muhasebe_Fis A ,Finans.Muhasebe_Fis_Kalem B
Finans.Muhasebe_Fis has >250.000 rows and Finans.Muhasebe_Fis_Kalem has
>3.000.000 rows... (Both of the tables have more than 50 columns)
A has an well reorganized index F_firma_kod,Tarih,Iptal...... and B
has F_firma_kod , F_muhasebe_fis_no (this field is attached to
finans.muhasebe_fis via foreign key),islem_tip,f_hesap_kod
When I "SQL EXPLAIN" the query the diagram shows me that the indexes are
used all right and the timeron is 7434,43253 etc...
But we can't obtain the results although we have been waiting for a long
time.There are sum functions in the query.Could it be the reason for that?
What must I do to improve the performance???
Our system is AIX , DB2 7.2.9 ..... with 4 GB of Ram...
Salih Sipahi
Veritabanı Yöneticisi
Hitit Güneşi
Hattusas
DB2 & UDB email list listserv db2-l LazyDBA home page