You need to create indexes on the column cpolicyno of HOMESUPP table.
Tamil
Printable View
You need to create indexes on the column cpolicyno of HOMESUPP table.
Tamil
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.
Do you have an index on the column cpolicyno of HOMESUPP table?
Tamil
I am creating index now.
Thanks
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.Quote:
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.
For example,
... is going to require very little sort space, right?Code:Select Sum(my_num)
From table_with_million_rows
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.Quote:
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
srt, do you have hash joins enabled in your db?
ok, why dont you run the sql statement alone and see how it performs.
For starters, you seem to be using
but you dont seem to be using this reference a or b for the column names in question.Code:from homesupp a, homemain b where cpolicyno
Worked fine with creating an index on the column.
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.