Re: group by clause filling up temp space

ewong wrote:
> Here is the explain plan. I've tried 0=1 it doesn't help. And yeah
> this is the only query that fills up temp. Thanks.
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3162293791 Card=1847
> 2014 Bytes=664992504)
>
> 1 0 SORT (GROUP BY) (Cost=3162293791 Card=18472014 Bytes=66499
> 2504)
>
> 2 1 HASH JOIN (Cost=2480 Card=193301902913 Bytes=69588685048
> 68)
>
> 3 2 TABLE ACCESS (FULL) OF 'TABLEA' (Cost=727 Card=2
> 48875 Bytes=7964000)
>
> 4 2 TABLE ACCESS (FULL) OF 'TABLEB' (Cost=644
> Card=77
> 9648 Bytes=3118592)

Cost 3162293791? Ouch.

Look at www.psoug.org
click on Morgan's Library
click on GROUP BY

I have one or two alternatives to using the GROUP BY clause that might help.

Why no indexes with what look to be your primary key columns: sid? Or are they there and you are still getting FTE?

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)