Try:

SELECT C1.desc1, SUM(C1.qty) FROM cti_ C1
WHERE Exists (
SELECT * FROM cti_ C2 WHERE C2.trxmdate = C1.trxmdate and C2.fcode IN
('A1','A2','A3')
)
GROUP BY C1.desc1
ORDER BY 2

This should give good performance (even with the broken optimizer).

-----Original Message-----
From: yeohray@... [mailto:yeohray@...]
Sent: Sunday, April 29, 2001 3:50 AM
To: IB-Architect@yahoogroups.com
Subject: [IB-Architect] Query joins


A query :)

Table information: cti_
Size : ~ 6MB
Total rows : ~47000
Rows meeting sub query : 1500
Indexes : composite index on (fcode, trxmdate)
single index on (trxmdate)


-- direct query
SELECT desc1, SUM(qty) FROM cti_
WHERE trxmdate in (
SELECT trxmdate FROM cti_ WHERE fcode IN ('A1','A2','A3')
)
GROUP BY desc1
ORDER BY 2

= 38 seconds

-- indirect query
CREATE TABLE summary1 (trxmdate VARCHAR(20))

INSERT INTO summary1
SELECT trxmdate FROM cti_ WHERE fcode IN ('A1','A2','A3')

SELECT b.desc1, SUM(b.qty) FROM summary1 a
INNER JOIN cti_ b
GROUP BY b.desc1
ORDER BY 2

= 2 seconds

I believe that I am doing something wrong in the direct query (the second
query would take too much work!). Could someone pls explain why the second
method is so much faster?

Thanks in advance.

Ray Mond


To unsubscribe from this group, send an email to:
IB-Architect-unsubscribe@onelist.com



Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/