Simple query that consumes all temporary space.

Hi.
We have table T with this structure:

LINE number(9)

POS     number(2)
TXT     varchar2(100)

It has information from a big text file, with 4 to 10 words per line (average), and the meaning of the fields is:

LINE  --> line number
POS   --> position of a word in the line
TXT   --> the word

The primary key of the table is (LINE, POS).

We need to find every couple of lines that have 3 or more words in common, so we run the following query:

SELECT T1.LINE LINE1, T2.LINE LINE2
  FROM T T1, T T2
 WHERE T2.LINE > T1.LINE
   AND T1.TXT = T2.TXT
GROUP BY T1.LINE, T2.LINE
HAVING COUNT(*) >= 3 The table has 1.500.000 records.

Unfortunately, it starts consuming temporary space so fast, and it breaks (ORA-01652) after a few minutes, even though we have set the temporary space to a size of 10 GB.

We run the query with Oracle 9i and 10g, with the same results. Have also tried with different indices and hints, with no luck.

Any help will be much appreciated.

Thanks in advance,
ydalepe Received on Tue Jan 25 2005 - 16:13:46 CST