Re: group by clause filling up temp space

On 30 Aug 2005 12:11:33 -0700, "ewong" <ewong_at_mail.com> wrote:

>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.

Firstly: how did you conclude that this query takes up so much temp space?

Secondly: suppose two extreme scenarios: 1) sid's are unique in both tables. The hash-join would result in 500k records at the most.
2) both tables have only one equal value for sid. The hash-join would be equal to an cartesian product, which would produce 500k * 5M = 2.5 TB records.

So if your value distribution leans to the second scenario, your hash join, together with the group by sort, would easily get 12 GB filled, before the group by itself would make this smaller.

Jaap. Received on Tue Aug 30 2005 - 16:18:57 CDT