fix to heavy disk-read query?

From: David Spaisman <davedba_at_intercall.net>
Date: Mon, 26 Jul 1999 22:10:16 -0700
Message-ID: <379D3F38.4D781D92@intercall.net>

Hello:

The attached query has indexes on each column excpet the 'type' columns. This is the worst peforming query every day that I check -- the most disk reads and buffer gets.

With the exception of adding a column to the balance table, I don't see much to do to remove the excessive disk reads.

Does any one hvae any ideas?

Thanks.

David Spaisman

---

SELECT O.ORDER_ID, O.REFERENCE_NUM,
 O.CUSTOMER_PO_NUMBER,
 O.RECEIVED_BY_EDI,  O.ISSUED_DATE,

 O.RESELLER_ID,
 O.RELATED_ORDER_ID, O.SHIP_TO_STATION_ID,

 OI.ORDER_ID,
 OI.ORDER_ITEM_ID, OI.ITEM_NUMBER,
 OI.ACCOUNT_ID,
 OI.QUANTITY,  OI.INVEN_BALANCE_ID,
 IB.ACCOUNT_ID,
OI.UNIT_PRICE,  OI.COST_ORIG_VALUE_ID,
OV.ACCOUNT_ID,
OI.PRODUCT_ID,    OI.RECON_STATUS,

OI.SEQUENCE_TIMESTAMP,
OI.INVENTORY_BATCH_ID,OI.REL_ORDER_ITEM_ID FROM ORDERS O,
ORDER_ITEM OI, BALANCE OV,
BALANCE IB
WHERE O.TYPE = 'INVOICE'

AND OI.ORDER_ID = O.ORDER_ID
AND OI.TYPE = 'INVOICE'
AND OV.TYPE' = 'ORIGVAL'
AND OV.BALANCE_ID = OI.COST_ORIG_VALUE_ID
AND IB.TYPE = 'INVENTORY'
        AND EXISTS (SELECT *
       FROM ORDER_ITEM ANYITEM
       WHERE ANYITEM.ACCOUNT_ID = :1
       AND ANYITEM.ORDER_ID = O.ORDER_ID)

Received on Tue Jul 27 1999 - 00:10:16 CDT