Re: fix to heavy disk-read query?

From: <michael_bialik_at_my-deja.com>
Date: Tue, 27 Jul 1999 21:31:41 GMT
Message-ID: <7nl8ft$ohu$1@nnrp1.deja.com>

Hi.

 Is it possible for you to post EXPLAIN of the query/  TKPROF of it's execution, and the structure of all  existing indexes?
 I'm not sure that adding index/column will help.

 Michael.

In article <379D3F38.4D781D92_at_intercall.net>,   David Spaisman <davedba_at_intercall.net> wrote:
> 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)
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Tue Jul 27 1999 - 16:31:41 CDT