Re: Simple query that consumes all temporary space.

On 25 Jan 2005 14:13:46 -0800, ydalepe_at_yahoo.com (ydalepe) wrote:

>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

What is the explain plan of the query?

Jaap. Received on Wed Jan 26 2005 - 05:07:06 CST