Re: Query Help..

>The following query causes our 1Gb TEMP tablespace to overflow with the
>'Unable to extend TEMP tablespace' message and consequently cannot be
>run to completion.
>
>We have analysed it up and down but to no avail.
>
>Here are some other Stats:
>
>The tables are all under 100,000 rows with the exception of the _AW
>table which is about 6,000,000 rows.
>All the joined columns are indexed in single key indexes.
>All the _AW indexes are bitmaps
>Not all the group by columns are indexed but indexing them seems to make
>no difference.
>
>Thanks for any help.
>
>MICHAELJ_at_CAPITAL-RE.COM

It has been my experience that when you use a "group by" Oracle creates a  sorted list of something; every "group by" makes another list. You are  grouping by 11 different columns; you are getting 11 of these lists. You can  see this if you look at used extents in your temp tablespace as the query  runs.

If this is the problem, all you can do is break up the query somehow so it uses  less space. You can add another datafile, but that will just postpone the  problem until the tables get a little bigger.

Dan Received on Tue Oct 07 1997 - 00:00:00 CEST