Re: Tuning: indexes

From: Michael Bialik <michael_bialik_at_my-deja.com>
Date: 2000/06/21
Message-ID: <8ir50a$l4k$1@nnrp1.deja.com>#1/1

Hi.

 It looks like you forgot some conditions in WHERE clause.  Try the same statement with
  WHERE

   b.bch_sref = a.sref AND
   b.sref = (select min(sref) from batch_item_tab where bch_sref =
   a.sref) AND
   c.bch_sref = a.sref AND
   c.sref = (select max(sref) from batch_item_tab where bch_sref =

    a.sref);

 Please post the results.

 HTH. Michael

In article <B5750F65.4B82%manoj_at_mac.com>,   manoj_at_mac.com wrote:
> Hello,
>
> Can anyone help me with ideas on tuning this query? See explain plan
 below..
> Its extremely slow.
>
> CREATE OR REPLACE VIEW BATCH_PERSONAL
> (
> SREF,
> OREF,
> DATE_TRANSACTION,
> ACT_SREF,
> ACT_CODE,
> ACT_NAME,
> PRD_SREF,
> PRD_CODE,
> PRD_NAME,
> NOTE,
> SIDE1_SREF,
> SIDE1_ACC_SREF,
> SIDE1_ACC_NAME,
> SIDE1_DEBIT,
> SIDE1_CREDIT,
> SIDE2_SREF,
> SIDE2_ACC_SREF,
> SIDE2_ACC_NAME,
> SIDE2_DEBIT,
> SIDE2_CREDIT,
> CODE,
> DRAFT,
> ACC_TYP_TYP_SREF,
> ACC_TYP_TYP_CODE,
> ACC_TYP_TYP_NAME,
> SIDE2_MEMO
> )
> AS
> select a.sref, a.oref, a.date_transaction,
> a.act_sref, a.act_code, a.act_name,
> a.prd_sref, a.prd_code, a.prd_name,
> a.note,
> fld(get_srefs(a.sref ), ',',1) side1_sref,
> to_char(b.acc_sref) side1_acc_sref,
> b.acc_name side1_acc_name,
> decode(b.dc,'D',b.amount,0) side1_debit,
> decode(b.dc,'C',b.amount,0) side1_credit,
> fld(get_srefs(a.sref),',',2) side2_sref,
> to_char(c.acc_sref) side2_acc_sref,
> c.acc_name side2_acc_name,
> decode(c.dc,'D',b.amount,0) side2_debit,
> decode(c.dc,'C',b.amount,0) side2_credit,
> a.code,draft,a.acc_typ_typ_sref,a.acc_typ_typ_code,
 a.acc_typ_typ_name,
> c.item_description
> from batch_tab a, batch_item_tab b, batch_item_tab c
> where b.sref = (select min(sref) from batch_item_tab where bch_sref =
> a.sref)
> and c.sref = (select max(sref) from batch_item_tab where bch_sref =
> a.sref);
> QUERY_PLAN
> ----------------------------------------------------------------------

---

> ----
> ----------------------------------------------------------------------
> SELECT STATEMENT Cost = 506923
> 2.1 NESTED LOOPS
> 3.1 NESTED LOOPS
> 4.1 TABLE ACCESS BY INDEX ROWID BATCH_TAB
> 5.1 INDEX RANGE SCAN BATCH_OREF_IDX NON-UNIQUE
> 4.2 TABLE ACCESS BY INDEX ROWID BATCH_ITEM_TAB
> 5.1 INDEX UNIQUE SCAN BATCH_ITEM_PK UNIQUE
> 6.1 SORT AGGREGATE
> 7.1 TABLE ACCESS BY INDEX ROWID BATCH_ITEM_TAB
> 8.1 INDEX RANGE SCAN BCH_ITM__BCH_SREF_IDX NON-UNIQUE
> 3.2 TABLE ACCESS BY INDEX ROWID BATCH_ITEM_TAB
> 4.1 INDEX UNIQUE SCAN BATCH_ITEM_PK UNIQUE
> 5.1 SORT AGGREGATE
> 6.1 TABLE ACCESS BY INDEX ROWID BATCH_ITEM_TAB
> 7.1 INDEX RANGE SCAN BCH_ITM__BCH_SREF_IDX NON-UNIQUE
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jun 21 2000 - 00:00:00 CDT