group by clause filling up temp space

From: ewong <ewong_at_mail.com>
Date: 30 Aug 2005 12:11:33 -0700
Message-ID: <1125429093.695055.259720@o13g2000cwo.googlegroups.com>

I have this query which filled up 12GB of temp space. tableA has 500K records where tableB has 5M records. sid are indexed. The 2 tables combine only 200 mega bytes and yet this query needs more than 12GB of temp space to perform. I am thinking it's the multiple group by that is causing trouble? Is there any workaround on this? Thanks.

SELECT a.id, a.type1, a.type2, count(b.sid) FROM tableA a, tableB b
WHERE a.sid = b.sid
and b.status = 'Y'
GROUP BY a.id, a.type1, a.type2;

The database is 9.2 EE. The involved tables are analyzed. Received on Tue Aug 30 2005 - 14:11:33 CDT