Re: results of large query exceed TEMP space

On 28 Jan 2004 23:03:22 -0800, archier_at_yahoo.com (archie) wrote:

>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

Your problem is being caused by the fact you force a cartesian product.
If you really need to do the comparison described, convert the code to pl/sql

--
Sybrand Bakker, Senior Oracle DBA