Re: Out of temp space

From: Randy Harris <randy_at_SpamFree.com>
Date: Tue, 08 Mar 2005 03:53:12 GMT
Message-ID: <Ik9Xd.17617$hU7.15460@newssvr33.news.prodigy.com>

"DA Morgan" <damorgan_at_x.washington.edu> wrote in message news:1110251898.72993_at_yasure...
> 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)

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production JServer Release 8.1.7.4.1 - Production

SQL> SELECT
  2 C.xcode AS Course_Code,
  3 trunc(C.xstartdate) AS Start_Date,

  4    C.xname AS Class_Name,
  5    C.xteacher AS Instructor,
  6    C.xmaxcap AS Capacity,
  7    R.order_no AS ZELO_Order_No_

  8 FROM
  9 Class C,
 10 Event R
 11 WHERE
 12 c.xlocator=r.xlocator AND
 13 UPPER(c.xcode)like '%14137' AND
 14 trunc(c.xstartdate)>'18-Nov-03' OR  15 c.xcode like '%14135'
 16 ORDER BY R.server_id
 17 ;
  Class C,
  *
ERROR at line 9:
ORA-01652: unable to extend temp segment by 8 in tablespace TEMP

Without that OR on the end, it runs in a second, returns 67 records.

-- 
Randy Harris
(tech at promail dot com)

Received on Mon Mar 07 2005 - 21:53:12 CST