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