Re: group by clause filling up temp space
- From: Jaap W. van Dijk <j.w.vandijk.removethis@xxxxxxxxx>
- Date: Tue, 30 Aug 2005 23:18:57 +0200
On 30 Aug 2005 12:11:33 -0700, "ewong" <ewong@xxxxxxxx> 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.
.
- References:
- group by clause filling up temp space
- From: ewong
- group by clause filling up temp space
- Prev by Date: Re: How do I change the interpreted decimal character in an external table file
- Next by Date: Re: Recover old Database
- Previous by thread: Re: group by clause filling up temp space
- Next by thread: Recover old Database
- Index(es):
Relevant Pages
|
