Re: Out of temp space

From: Malcolm Dew-Jones <yf110_at_vtn1.victoria.tc.ca>
Date: 7 Mar 2005 21:42:42 -0800
Message-ID: <422d3b52@news.victoria.tc.ca>

Randy Harris (randy_at_SpamFree.com) wrote:

: 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.

OR has lower precedence than AND. The above is a cartesian join, i.e. you're asking oracle to examine every single combination - not a regular join where the two tables have to "line up" on a common value. Cartesian joins are generally found to be inefficient in time and/or space, and best avoided. Perhaps you meant something like

where

   c.xlocator=r.xlocator
   and
   ( UPPER(c.xcode) like '%14137'AND trunc(c.xstartdate)>'18-Nov-03'

     or
     c.xcode like '%14135'

   )

maybe even

where

   c.xlocator=r.xlocator
   and
   ( UPPER(c.xcode) like '%14137'
     or c.xcode like '%14135'
   )
   AND trunc(c.xstartdate)>'18-Nov-03'

Other variations are possible, I don't have my crystal ball with me to figure out which is the one you need.

I might also ask why UPPER(c.xcode) when you're comparing to digits.

--

This space not for rent.

Received on Mon Mar 07 2005 - 23:42:42 CST