Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Out of temp space
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
![]() |
![]() |