Temporary tablespace filled up

[tamilselvan]

You need to create indexes on the column cpolicyno of HOMESUPP table.

Tamil

[srt]

You can do one more test. Remove the group by and see how many rows are returned after the 2 tables join. That will give you good idea how much space is needed


select shows 5075 rows wihtout group by. It took 30 minutes to run the select statement.

[tamilselvan]

Do you have an index on the column cpolicyno of HOMESUPP table?

Tamil

[srt]

I am creating index now.
Thanks

[slimdave]

Originally posted by tamilselvan
You can do one more test. Remove the group by and see how many rows are returned after the 2 tables join. That will give you good idea how much space is needed.

I don't think so -- the space requirement is related more to the size of the output from the grouping than it is to the number of ungrouped rows.

For example,

Select Sum(my_num)
From   table_with_million_rows

... is going to require very little sort space, right?

[tamilselvan]

Slimdave,
It is not the group by clause causing the problem b/c the result set after joining 2 big tables is small. MERGE JOIN operation is causing real problem of filling out the temp space. I am trying to eliminate MERGE JOIN by adding an INDEX.

Tamil

[slimdave]

Originally posted by tamilselvan
Slimdave,
It is not the group by clause causing the problem b/c the result set after joining 2 big tables is small. MERGE JOIN operation is causing real problem of filling out the temp space. I am trying to eliminate MERGE JOIN by adding an INDEX.

Tamil

OK I see ... well I'd think that a hash join would be worth trying also.

srt, do you have hash joins enabled in your db?

[thomasp]

ok, why dont you run the sql statement alone and see how it performs.
For starters, you seem to be using

from homesupp a, homemain b where cpolicyno

but you dont seem to be using this reference a or b for the column names in question.

[srt]

Worked fine with creating an index on the column.

[hubbardgd]

Assuming that none of the extents are currently used, you can issue the "alter tablespace" command below to clean up temp.

alter tablespace temp
default storage (pctincrease 0);

This will tell smon to clean up the segments in the tablespace that are not used.