results of large query exceed TEMP space

I'm doing a query to calculate the correlation of 25,000 variables in Oracle 9i.

The final results will be 600M rows, each with three rows -- variable A (8-letter string), variable B (8-letter string), and a numeric correlation between A & B. I want to discard the results with very low correlations. The results of the query will go into another table.

The query is easy enough to write, but the while calculating the results, I run out of temp space. My temp tablespace is about 20GB.

I can split the query up so that I address 5,000 variables at a time instead of 25,000 using pl/sql or another language, but it would be cleaner if I could do the whole thing in SQL.

Does anyone have any suggestions for how to execute this query without filling up temp space? Would partitioning help?

The query is roughly

SELECT
A.VALUE, B.VALUE, CORR(A,B)
FROM
MYTABLE A, MYTABLE B
GROUP BY
A.VALUE, B.VALUE
HAVING CORR(A.VALUE,B.VALUE) > 0.05 (it is a cartesian product since I want to compare all the 25,000 rows in mytable to all the other rows in mytable)

Thanks,
Archie Received on Thu Jan 29 2004 - 01:03:22 CST