bad sql code?

From: David Spaisman <spaisman_at_worldnet.att.net>
Date: Thu, 6 May 1999 21:38:05 -0400
Message-ID: <7h21q1$1kk$1@bgtnsc02.worldnet.att.net>

Hello:

I am trying to figure out why a new application (100 + tables) and very little data thus far --app is up 2 weeks -- is consuming 2gb of temp tablespace and 50 users are logged in, with about 15 using the code below which is one of the main memo choices:

Can any one help me rewrite this bad code? Thanks.

David Spaisman

---

---

Select ord.ORDER_ID
, min(ord.REFERENCE_NUM)

  ,min(ord.ISSUED_DATE)
, sum(item.UNIT_PRICE * item.QUANTITY)
  , min(ord.CUSTOMER_PO_NUMBER )
, min(org.NAME

 || DECODE(bdem.REFERENCE_NUM, '', '', ' [') || bdem.REFERENCE_NUM || DECODE(bdem.REFERENCE_NUM, '', '', ']')) from ORDERS ord ,

  ORDER_ITEM item      ,
BUS_PARTY_ROLE role      ,
ORGANIZATION org      ,

BP_DEMOGRAPHICS bdem where ord.TYPE = 'INVOICE' and ord.RECEIVED_BY_EDI = 'Y'

   and
 item.ORDER_ID = ord.ORDER_ID
  and
ord.RESELLER_ID = role.BUS_PARTY_ROLE_ID(+)

   and role.BUS_PARTY_ID = org.BUS_PARTY_ID(+)   and bdem.BUS_PARTY_ROLE_ID(+) = role.BUS_PARTY_ROLE_ID

  and not exists          (Select *             from ORDER_ITEM itemsub
where         ord.ORDER_ID = itemsub.ORDER_ID             and ACCOUNT_ID >

0)
group by ord.ORDER_ID Received on Thu May 06 1999 - 20:38:05 CDT