Re: Simple query that consumes all temporary space.

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

Your SQL statement is a nightmare.

You can't use GROUP BY and HAVING clauses without an aggregation. Do you see AVG or SUM or COUNT or similar anywhere? I don't.

Then there's the matter of an inner-join on a column named TXT. While I can't query your data dictionary for constraints and indexes this has all the markings of homework you are trying to get answered by someone else.

I'd suggest you discuss this with the instructor if you need futher help.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)