Tuning: indexes

From: Manoj Thomas <manoj_at_mac.com>
Date: 2000/06/20
Message-ID: <B5750F65.4B82%manoj@mac.com>#1/1

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

Received on Tue Jun 20 2000 - 00:00:00 CDT