Re: fix to heavy disk-read query?

From: David Spaisman <davedba_at_intercall.net>
Date: Wed, 28 Jul 1999 21:51:50 -0700
Message-ID: <379FDDE6.6DD3E75@intercall.net>

Jonathan:

You right on the mark. The query I originally posted did not have another balance join. Here is the correct full query.

All of the where clause columns have either a clustered(primary key)index or a unique index with the exception of the three'type' columns(which only have very limited selectivity). Any suggestions how I can rewrite to avoid the disk joins?

Thansk.

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.UN IT_PRICE,  OI.COST_ORIG_VALUE_ID,
OV.ACCOUNT_ID,   OI.PR ODUCT_ID,OI.RECON_STATUS,

OI.SEQUENCE_TIMESTAMP, OI. INVENTORY_BATCH_ID, OI.REL_ORDER_ITEM_ID
FROM ORDERS O,
ORR_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 IB.BALANCE_ID = OI.INVEN_BALANCE_ID
AND EXISTS (SELECT *
       FROM ORDER_ITEM ANYITEM
       WHERE ANYITEM.ACCOUNT_ID = :1
       AND ANYITEM.ORDER_ID = O.ORDER_ID);

Jonathan Lewis wrote:

> David Sisk wrote in message ...
> >You've got the right idea, though. Adding indexes is one of the best ways
> >to reduce the run-time of queries that do lot's of disk reads.
>
> I would rate it as the second best way.
> The best is to determine what the query is
> trying to do and figure out optimum
> strategy before doing anything else.
>
> In the case the query seems to be
> select order/balance for all orders
> where one of the order items is
> for a specific account.
>
> First action, therefore, is to determine
> whether you want the select on order_item
> to drive the query.
>
> Before worrying too much about that
> though, I would question why the BALANCE
> table with alias IB has no apparent join
> condition to the rest of the tables - did you drop
> a line as you copied the code, or is it really
> missing.
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: www.jlcomp.demon.co.uk
Received on Wed Jul 28 1999 - 23:51:50 CDT