Re: Out of temp space

Randy Harris wrote:

> I would appreciate some help understanding how Oracle 8i uses Temp space for
> queries.
>
> I have been working with a rather simple query, joining two tables and a
> couple of additional restrictions in the where clause.
>
> Something like:
>
> SELECT t1.field1, t1.field2, t2.field3
> FROM table1 t1, table2 t2
> WHERE t1.pk=t2.fk
> AND criteria1 = 'some value'
> AND criteria2 like 'other value %'
>
> it worked nicely, ran in a few seconds.
>
> Then I added an OR to it:
>
> OR criteria2 like 'value %'
>
> It ran for about 1/2 hour until it crashed with an ODBC error. It wasn't
> difficult to determine that the Temp datafile autoexended repeatedly until
> it finally ran out of disk space. Why would adding that OR criteria to the
> WHERE clause cause it to require so much additional TEMP?

Try it in SQL*Plus and what is the Oracle version?

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